yurbasov_park_ilyinka

CREATE TABLE sandbox.yurbasov_park_ilyinka
-- Необходимо выгрузить автомобилистов кто парковался на парковке 0304 >=8 раз за последний месяц(октябрь)
AS
WITH a AS
(
  SELECT parkingstart, phoneno AS full_hash, carno FROM parking_data.parking m
WHERE placecode IN ( 304 )
AND PMOD(DATEDIFF(from_unixtime(unix_timestamp(parkingstart, 'dd.MM.yyyy HH:mm:ss'),'yyyy-MM-dd'), '2012-01-01'), 7) IN (0, 1, 2, 3, 4, 5, 6)
AND from_unixtime(unix_timestamp(parkingstart, 'dd.MM.yyyy HH:mm:ss'),'yyyy-MM-dd') >= '2018-10-01'
AND from_unixtime(unix_timestamp(parkingstart, 'dd.MM.yyyy HH:mm:ss'),'yyyy-MM-dd') <= '2018-10-30'
), b AS
(
  SELECT COUNT(a.parkingstart) AS cnt, a.full_hash, a.carno FROM a GROUP BY full_hash, carno
), c AS
(
  SELECT cnt, full_hash, carno, s.phone FROM b JOIN secret.phones s ON s.phone_hash = b.full_hash WHERE b.cnt >= 6
), res AS
(
  SELECT cnt, carno, phone FROM c JOIN secret.regno s ON s.regno_hash = c.carno
) SELECT * FROM res;