auto_mkad_away

 DROP TABLE SANDBOX.YURBASOV_IG493_RADIUS_01
;

 DROP TABLE SANDBOX.YURBASOV_IG493_RADIUS_02
;

 DROP TABLE SANDBOX.YURBASOV_IG493_RADIUS_03
;

 DROP TABLE SANDBOX.YURBASOV_IG493_RADIUS_04
;

 DROP TABLE SANDBOX.YURBASOV_IG493_RADIUS_05
;

 DROP TABLE SANDBOX.YURBASOV_IG493_RADIUS_06
;

 DROP TABLE SANDBOX.YURBASOV_IG493_RADIUS_07
;

 DROP TABLE SANDBOX.YURBASOV_IG493_RADIUS_08
;

 DROP TABLE SANDBOX.YURBASOV_IG493_RADIUS_09
;

 DROP TABLE SANDBOX.YURBASOV_IG493_RADIUS_10
;

 DROP TABLE CITY_EVENT_DATA.IG493_RADIUS
;

 SET hivevar:START_DAY='2018-03-01'
;

 SET hivevar:END_DAY='2018-04-30'
;

 CREATE TABLE SANDBOX.YURBASOV_IG493_RADIUS_01
AS WITH t_01 AS
(
SELECT
  DAY,
  v_regno,
  CASE WHEN codd_kafka.v_gps_y > 55 THEN codd_kafka.v_gps_y ELSE codd_kafka.v_gps_x END AS camera_x,
  CASE WHEN codd_kafka.v_gps_y > 55 THEN codd_kafka.v_gps_x ELSE codd_kafka.v_gps_y END AS camera_y,
  v_time_check
FROM codd_data.codd codd_kafka
WHERE DAY <= '2018-04-31'
AND PMOD(DATEDIFF(from_unixtime(unix_timestamp(v_time_check, 'dd.MM.yyyy HH:mm:ss'),'yyyy-MM-dd'), '2012-01-01'), 7) IN (1, 2, 3, 4, 5)
AND DAY >= '2018-03-01'
) SELECT * FROM t_01
;

 CREATE TABLE SANDBOX.YURBASOV_IG493_RADIUS_02
AS WITH t_02 AS (
SELECT 55.71677 AS v_gps_x, 37.40415 AS v_gps_y
UNION ALL SELECT 55.71689 AS v_gps_x, 37.407196 AS v_gps_y
UNION ALL SELECT 55.911114 AS v_gps_x, 37.54446 AS v_gps_y
UNION ALL SELECT 55.91438 AS v_gps_x, 37.54427 AS v_gps_y
UNION ALL SELECT 55.83083 AS v_gps_x, 37.3796 AS v_gps_y
UNION ALL SELECT 55.829914 AS v_gps_x, 37.37475 AS v_gps_y
UNION ALL SELECT 55.829647 AS v_gps_x, 37.374035 AS v_gps_y
UNION ALL SELECT 55.825462 AS v_gps_x, 37.36427 AS v_gps_y
UNION ALL SELECT 55.57221 AS v_gps_x, 37.597527 AS v_gps_y
UNION ALL SELECT 55.570908 AS v_gps_x, 37.598785 AS v_gps_y
UNION ALL SELECT 55.876934 AS v_gps_x, 37.48232 AS v_gps_y
UNION ALL SELECT 55.872738 AS v_gps_x, 37.48292 AS v_gps_y
UNION ALL SELECT 55.87125 AS v_gps_x, 37.45795 AS v_gps_y
UNION ALL SELECT 55.871475 AS v_gps_x, 37.458572 AS v_gps_y
) SELECT * FROM t_02
;

 CREATE TABLE SANDBOX.YURBASOV_IG493_RADIUS_03
AS WITH t_03 AS (
SELECT 55.725147 AS v_gps_x, 37.38045 AS v_gps_y
UNION ALL SELECT 55.69867 AS v_gps_x, 37.40296 AS v_gps_y
UNION ALL SELECT 55.884384 AS v_gps_x, 37.472168 AS v_gps_y
UNION ALL SELECT 55.891403 AS v_gps_x, 37.49469 AS v_gps_y
UNION ALL SELECT 55.878273 AS v_gps_x, 37.43426 AS v_gps_y
UNION ALL SELECT 55.88252 AS v_gps_x, 37.448956 AS v_gps_y
) SELECT * FROM t_03
;

 CREATE TABLE SANDBOX.YURBASOV_IG493_RADIUS_04
AS WITH t_04 AS
(
SELECT
  v_regno,
  camera_x, camera_y,
  weekofyear(v_time_check) AS w_no,
  COUNT(*) AS cnt
FROM SANDBOX.YURBASOV_IG493_RADIUS_01 base
 JOIN SANDBOX.YURBASOV_IG493_RADIUS_02 tgt1
 ON acos(sin((90.0 - v_gps_y) * pi() / 180.0) * sin((90.0 - camera_y) * pi() / 180.0) * cos(radians(v_gps_x) - radians(camera_x))
       + cos((90.0 - v_gps_y) * pi() / 180.0) * cos((90.0 - camera_y) * pi() / 180.0)) * 6371000 <= 100
GROUP BY v_regno,
         camera_x, camera_y,
         weekofyear(v_time_check)

) SELECT * FROM t_04
;

 CREATE TABLE SANDBOX.YURBASOV_IG493_RADIUS_05
AS WITH t_05 AS
(
SELECT pre1.v_regno, pre1.cnt FROM SANDBOX.YURBASOV_IG493_RADIUS_04 pre1
) SELECT * FROM t_05
;

 CREATE TABLE SANDBOX.YURBASOV_IG493_RADIUS_06
AS WITH t_06 AS
(
SELECT DISTINCT
  v_regno
FROM SANDBOX.YURBASOV_IG493_RADIUS_04 f1
 WHERE cnt >= 24

) SELECT * FROM t_06
;

 CREATE TABLE SANDBOX.YURBASOV_IG493_RADIUS_07
AS WITH t_07 AS
(

SELECT
    xxxx1.v_regno AS v_regno
    , sec.regno_hash AS regno_hash
    , sec.regno AS regno
FROM SANDBOX.YURBASOV_IG493_RADIUS_06 xxxx1
 JOIN secret.regno AS sec ON xxxx1.v_regno = sec.regno_hash
 GROUP BY
 v_regno, regno_hash, regno
) SELECT * FROM t_07
;

 CREATE TABLE SANDBOX.YURBASOV_IG493_RADIUS_08
AS WITH t_08 AS
(
SELECT
  extra1.v_regno,
  extra1.regno,
  par.phoneno
FROM SANDBOX.YURBASOV_IG493_RADIUS_07 extra1
 JOIN city_event_data.d_calc0a_regno_x_phoneno par
  ON extra1.v_regno = par.carno
WHERE par.carno IS NOT NULL
GROUP BY
extra1.v_regno, extra1.regno,
par.phoneno
) SELECT * FROM t_08
;

 CREATE TABLE SANDBOX.YURBASOV_IG493_RADIUS_09
AS WITH t_09 AS
(

    SELECT res1.phoneno, res1.regno, res1.v_regno FROM SANDBOX.YURBASOV_IG493_RADIUS_08 res1
) SELECT * FROM t_09
;

 CREATE TABLE SANDBOX.YURBASOV_MKAD_AWAY
-- Необходимо собрать список телефонов аудитории, которая минимум 3 раза в неделю в будние дни фиксируется
--  на пограничных (МКАД) камерах с утра на въезд и вечером на выезд.
-- по следующим шоссе:
-- Можайскому
-- 55.71677_37.40415    VS 55.725147_37.38045 --
-- 55.71689_37.407196    VS 55.69867_37.40296 --
-- Дмитровскому
-- 55.911114_37.54446 --
-- 55.91438_37.54427 --
-- Минскому
--
-- Волоколамскому
-- 55.83083_37.3796 --
-- 55.829914_37.37475 --
-- 55.829647_37.374035 --
-- 55.825462_37.36427 --
-- Симферопольскому
-- 55.57221_37.597527 --
-- 55.570908_37.598785 --
-- Варшавскому
--
-- Лобненскому
-- 55.876934_37.48232    VS 55.884384_37.472168 --
-- 55.872738_37.48292    VS 55.891403_37.49469 --
-- Ленинградскому шоссе
-- 55.87125_37.45795   VS 55.878273_37.43426 --
-- 55.871475_37.458572    VS 55.88252_37.448956 --
AS WITH t_10 AS
(
SELECT s.phone, res.regno, res.v_regno FROM SANDBOX.YURBASOV_IG493_RADIUS_09 res JOIN secret.phones s ON s.phone_hash = res.phoneno

) SELECT * FROM t_10
;