metro_rama

CREATE TABLE sandbox.yurbasov_metro_rama
AS
WITH base1 AS (
  SELECT hash_msisdn, fdt, startstid, DAY FROM maxima_data.metro m
    -- WHERE DAY >= '2018-05-01' AND DAY <= '2018-08-28' AND startstid IN (208)
    WHERE DAY >= '2018-07-01' AND DAY <= '2018-08-28' AND startstid IN (208)
), uniq1 AS (
  SELECT '1A' AS tags, hash_msisdn, startstid, 1 AS total FROM base1 GROUP BY hash_msisdn, startstid, DAY
), pre1 AS (
  SELECT SUM(total) AS cnt, tags, hash_msisdn, startstid AS a FROM uniq1 GROUP BY tags, hash_msisdn, startstid
), raw1 AS (
  SELECT cnt, tags, hash_msisdn, a FROM pre1 WHERE cnt >= 15 -- 10
), done1 AS (
  SELECT cnt, hash_msisdn, concat(m.station_name_rus, '-', a) AS a FROM raw1
    LEFT JOIN sandbox.line_station_codes_new m
    ON raw1.a = m.station_id
), res1 AS (
  SELECT s.phone, done1.cnt, done1.a, done1.hash_msisdn FROM done1
    JOIN secret.phones s ON done1.hash_msisdn = regexp_replace(s.phone_hash, '^[0]*', '')
 ) SELECT phone, MAX(cnt) AS cnt, MAX(a) AS a, MAX(hash_msisdn) AS msisdn FROM res1 GROUP BY phone