khorvino_maxima

CREATE TABLE sandbox.YURBASOV_KHOVRINO_MAXIMA
-- Вход:  Ховрино
-- Выход: Любой

-- VALUES 209: 2,2,209,Ховрино,Khovrino

-- за последние два месяца( с 16 мая по 15 июля) фиксировался на Ховрино >12 раз в будни и >6 раз в выходные
AS
WITH base1 AS (
  SELECT hash_msisdn, fdt, startstid,
  from_unixtime(unix_timestamp(fdt, 'dd.MM.yyyy HH:mm:ss'),'yyyy-MM-dd') AS d,
  DATEDIFF(fdt, from_unixtime(unix_timestamp(fdt, 'dd.MM.yyyy HH:mm:ss'),'yyyy-MM-dd')) tm,
  weekofyear(unix_timestamp(fdt, 'dd.MM.yyyy HH:mm:ss')) wy,
  PMOD(DATEDIFF(from_unixtime(unix_timestamp(fdt, 'dd.MM.yyyy HH:mm:ss'),'yyyy-MM-dd'), '2012-01-01'), 7) ww
  FROM maxima_data.metro m
  WHERE DAY <= '2018-07-16' AND startstid IN (209) AND
   (
   from_unixtime(unix_timestamp(fdt, 'dd.MM.yyyy HH:mm:ss'),'yyyy-MM-dd') >= '2018-05-16'
   AND from_unixtime(unix_timestamp(fdt, 'dd.MM.yyyy HH:mm:ss'),'yyyy-MM-dd') <= '2018-07-15'
   )
), uniq1 AS (
  SELECT '0' AS r, 'A' AS g, hash_msisdn, startstid AS fromst, 1 AS total, d, wy, MIN(ww) AS min_ww, MAX(tm) AS max_tm, MIN(tm) AS min_tm
  FROM base1 WHERE ww IN (0,1,2,3,4,5,6) AND startstid IN (209) GROUP BY hash_msisdn, startstid, d, wy
), aggg1 AS (
  SELECT r, g, hash_msisdn, fromst, SUM(total) AS cnt, MAX(d) AS max_d, MIN(d) AS min_d, MAX(max_tm) AS max_tm, MIN(min_tm) AS min_tm FROM uniq1 WHERE r = '0' GROUP BY hash_msisdn, fromst, r, g HAVING SUM(total) >= 6
), res1 AS (
  SELECT a.cnt AS fromcnt, a.r, a.g, a.hash_msisdn, a.fromst, a.min_d, a.max_d, a.max_tm, a.min_tm FROM aggg1 a
), proto AS (
  SELECT res1.fromcnt AS tocnt, res1.min_d AS min_d, res1.max_d AS max_d,
  res1.hash_msisdn, res1.r AS r, res1.g AS g, res1.fromst AS a, '-' AS b FROM res1
), raw AS
(
  SELECT hash_msisdn, MAX(tocnt) AS tocnt, MIN(min_d) AS min_d, MAX(max_d) AS max_d,
  MAX(CONCAT(lpad(tocnt, 3, "0"), ' ', r, ' ', g, ' ', lpad(a, 3, "0"), ' ', lpad(b, 3, "0"))) AS info,
  substr(MAX(CONCAT(lpad(tocnt, 3, "0"), ' ', r, ' ', g, ' ', lpad(a, 3, "0"), ' ', lpad(b, 3, "0"))), 5, 1) AS r,
  substr(MAX(CONCAT(lpad(tocnt, 3, "0"), ' ', r, ' ', g, ' ', lpad(a, 3, "0"), ' ', lpad(b, 3, "0"))), 7, 1) AS g,
  CAST (substr(MAX(CONCAT(lpad(tocnt, 3, "0"), ' ', r, ' ', g, ' ', lpad(a, 3, "0"), ' ', lpad(b, 3, "0"))), 9, 3) AS INT) AS a,
  CAST (substr(MAX(CONCAT(lpad(tocnt, 3, "0"), ' ', r, ' ', g, ' ', lpad(a, 3, "0"), ' ', lpad(b, 3, "0"))), 13, 3) AS INT) AS b
  FROM proto GROUP BY hash_msisdn
), done AS (
  SELECT raw.tocnt, raw.min_d, raw.max_d, raw.hash_msisdn, raw.r, raw.g, concat(m.station_name_rus, '-', raw.a) AS a, raw.b FROM raw
    LEFT JOIN sandbox.line_station_codes_new m
    ON raw.a = m.station_id
), res AS (
 SELECT s.phone, done.tocnt, done.min_d, done.max_d, done.r, done.g, done.a, done.b, done.hash_msisdn FROM done JOIN secret.phones s ON done.hash_msisdn = regexp_replace(s.phone_hash, '^[0]*', '')
 ) SELECT phone, MAX(tocnt) AS cnt, MAX(r) AS r, MAX(g) AS g, MAX(a) AS a, MAX(b) AS b, MAX(hash_msisdn) AS msisdn, MIN(min_d) AS min_d, MAX(max_d) AS max_d FROM res GROUP BY phone