ngpt_tverskaya

 SET hive.tez.container.SIZE = 6656
;

DROP TABLE sandbox.YURBASOV_NGPT_TVER_LIST1
;

DROP TABLE sandbox.YURBASOV_NGPT_TVER_QUIZ1
;

DROP TABLE sandbox.YURBASOV_NGPT_TVER_DATA1
;

DROP TABLE sandbox.YURBASOV_NGPT_TVER_TEL1
;

CREATE TABLE sandbox.YURBASOV_NGPT_TVER_LIST1
AS
SELECT "м1" AS t, "А_м1" AS s
UNION ALL SELECT "м2" AS t, "А_м2" AS s
UNION ALL SELECT "м3" AS t, "А_м3" AS s
UNION ALL SELECT "м6" AS t, "А_м6" AS s
UNION ALL SELECT "м10" AS t, "А_м10" AS s
UNION ALL SELECT "м27" AS t, "А_м27" AS s
UNION ALL SELECT "№ 101" AS t, "А_101" AS s
UNION ALL SELECT "№ 144" AS t, "А_144" AS s
UNION ALL SELECT "№ 243" AS t, "А_243" AS s
UNION ALL SELECT "№ 904" AS t, "А_904" AS s
UNION ALL SELECT "н1 " AS t, "А_Н1" AS s
UNION ALL SELECT "н2 " AS t, "А_Н2" AS s
UNION ALL SELECT "н11 " AS t, "А_11" AS s
UNION ALL SELECT "400 " AS t, "АЗ_400" AS s
UNION ALL SELECT "400э " AS t, "АЗ_400э" AS s
UNION ALL SELECT "8 " AS t, "АЗ_8" AS s
UNION ALL SELECT "8 " AS t, "А_8" AS s
UNION ALL SELECT "9 " AS t, "А_9" AS s
UNION ALL SELECT "11 " AS t, "А_11" AS s
UNION ALL SELECT "27 " AS t, "А_27" AS s
UNION ALL SELECT "31 " AS t, "А_31" AS s
UNION ALL SELECT "А " AS t, "А_А" AS s
UNION ALL SELECT "т13 " AS t, "А_Т13" AS s
UNION ALL SELECT "24 " AS t, "А_24" AS s
UNION ALL SELECT "38 " AS t, "А_38" AS s
UNION ALL SELECT "255 " AS t, "А_255" AS s
UNION ALL SELECT "507 " AS t, "А_507" AS s
UNION ALL SELECT "746 " AS t, "А_746" AS s
UNION ALL SELECT "761 " AS t, "А_761" AS s
UNION ALL SELECT "707 " AS t, "А_707" AS s
UNION ALL SELECT "779 " AS t, "А_779" AS s
UNION ALL SELECT "809 " AS t, "А_809" AS s
UNION ALL SELECT "814 " AS t, "А_814" AS s
UNION ALL SELECT "830 " AS t, "А_830" AS s
UNION ALL SELECT "844 " AS t, "А_844" AS s
UNION ALL SELECT "866 " AS t, "А_866" AS s
UNION ALL SELECT "870 " AS t, "АП_870" AS s
UNION ALL SELECT "876 " AS t, "АП_876" AS s
UNION ALL SELECT "878 " AS t, "АП_878" AS s
UNION ALL SELECT "879 " AS t, "АП_879" AS s
UNION ALL SELECT "881 " AS t, "АП_881" AS s
UNION ALL SELECT "890 " AS t, "АП_890" AS s
UNION ALL SELECT "902 " AS t, "А_902" AS s
UNION ALL SELECT "м9 " AS t, "А_м9" AS s
UNION ALL SELECT "т13 " AS t, "А_Т13" AS s
UNION ALL SELECT "15 " AS t, "А_15" AS s
UNION ALL SELECT "24 " AS t, "А_24" AS s
UNION ALL SELECT "38 " AS t, "А_38" AS s
UNION ALL SELECT "1 " AS t, "А_1" AS s
UNION ALL SELECT "2 " AS t, "А_2" AS s
UNION ALL SELECT "3 " AS t, "А_3" AS s
UNION ALL SELECT "10 " AS t, "А_10" AS s
UNION ALL SELECT "12 " AS t, "А_12" AS s
UNION ALL SELECT "19 " AS t, "А_т19" AS s
UNION ALL SELECT "19 " AS t, "АЗ_19" AS s
UNION ALL SELECT "19 " AS t, "КА_19" AS s
UNION ALL SELECT "29 " AS t, "А_29" AS s
UNION ALL SELECT "32 " AS t, "А_32" AS s
UNION ALL SELECT "32к " AS t, "АЗ_32к" AS s
UNION ALL SELECT "5 " AS t, "АЗ_5" AS s
UNION ALL SELECT "767 " AS t, "А_767" AS s
UNION ALL SELECT "810 " AS t, "А_810" AS s
UNION ALL SELECT "814 " AS t, "А_814" AS s
;

CREATE TABLE sandbox.YURBASOV_NGPT_TVER_QUIZ1
AS
WITH s AS
(
SELECT ticket_type AS d_ticket_type FROM sandbox.d_ticket_type
)
,t AS
(SELECT ticket_code, ticket_type, route_code, pass_datetime FROM MOSGORTR_DATA.PASS_MGT m
JOIN sandbox.YURBASOV_NGPT_TVER_LIST1 l ON l.s = m.route_code
WHERE pass_datetime >= '2018-06-24' AND pass_datetime <= '2018-08-21'
 AND PMOD(DATEDIFF(from_unixtime(unix_timestamp(pass_datetime, 'dd.MM.yyyy HH:mm:ss'),'yyyy-MM-dd'), '2012-01-01'), 7) IN (0, 5, 6)
), a AS
(
SELECT * FROM t JOIN s ON t.ticket_type = s.d_ticket_type AND s.d_ticket_type NOT IN ("60 поездок 90 м", "60 поездок ЕД")
), b AS
(
 SELECT route_code, pass_datetime, ticket_code, ticket_type FROM a GROUP BY route_code, pass_datetime, ticket_code, ticket_type
), c AS
(
  SELECT ticket_code, ticket_type, route_code, 1 AS total FROM b GROUP BY ticket_code, ticket_type, route_code HAVING COUNT(pass_datetime) >= 8
) SELECT ticket_code, ticket_type, route_code, SUM(total) AS sum_total FROM c GROUP BY ticket_code, ticket_type, route_code
;

CREATE TABLE sandbox.YURBASOV_NGPT_TVER_DATA1
AS
WITH a AS
(SELECT DISTINCT e.ticket_number, t.ticket_type, e.hash_uid, d.hash_msisdn
FROM case_profile_calc.d_phone_x_ticket d
JOIN metro_data.entries e
ON UPPER(d.hash_ticket_uid) = UPPER(e.hash_uid)
JOIN sandbox.d_ticket_type t
ON t.ticket_code = e.ticket_type
AND t.ticket_type NOT IN ("60 поездок 90 м", "60 поездок ЕД")
), b AS
(
SELECT ticket_code, ticket_type, route_code, sum_total FROM sandbox.yurbasov_ngpt_TVER_quiz1
) SELECT a.*, b.ticket_code, b.route_code, b.sum_total FROM b LEFT JOIN a ON a.ticket_number = b.ticket_code AND a.ticket_type = b.ticket_type
WHERE a.hash_msisdn IS NOT NULL
;

CREATE TABLE sandbox.YURBASOV_NGPT_TVER_TEL1
AS
WITH a AS
(SELECT * FROM sandbox.YURBASOV_NGPT_TVER_DATA1)
, b AS
(SELECT s.phone, a.* FROM a LEFT JOIN secret.phones s ON LOWER(a.hash_msisdn) = regexp_replace(s.phone_hash, '^[0]*', ''))
SELECT * FROM b