ksl-sportivnaya

 CREATE TABLE sandbox.yurbasov_ksl_sportivnaya_01
AS
SELECT z_master_station_id
,z_master_station_nm
,z_master_line_id
,z_master_line_nm
,entrance_id AS y_entrance_id FROM SANDBOX.YURBASOV_D_50A_METRO_CALC_S_NGPT_DATA_V2_02 LEFT JOIN CITY_EVENT_DATA.D_CALC44_ENTRANCE_X_STATION ON master_station_id = z_master_station_id UNION ALL SELECT 0 AS z_master_station_id, 'Мичуринский проспект' AS z_master_station_nm, 0 AS z_master_line_id, 'Солнцевская' AS z_master_line_nm, 2232 AS y_entrance_id UNION ALL SELECT 0 AS z_master_station_id, 'Мичуринский проспект' AS z_master_station_nm, 0 AS z_master_line_id, 'Солнцевская' AS z_master_line_nm, 2233 AS y_entrance_id UNION ALL SELECT 0 AS z_master_station_id, 'Озерная' AS z_master_station_nm, 0 AS z_master_line_id, 'Солнцевская' AS z_master_line_nm, 2234 AS y_entrance_id UNION ALL SELECT 0 AS z_master_station_id, 'Озерная' AS z_master_station_nm, 0 AS z_master_line_id, 'Солнцевская' AS z_master_line_nm, 2235 AS y_entrance_id UNION ALL SELECT 0 AS z_master_station_id, 'Говорово' AS z_master_station_nm, 0 AS z_master_line_id, 'Солнцевская' AS z_master_line_nm, 2236 AS y_entrance_id UNION ALL SELECT 0 AS z_master_station_id, 'Говорово' AS z_master_station_nm, 0 AS z_master_line_id, 'Солнцевская' AS z_master_line_nm, 2237 AS y_entrance_id UNION ALL SELECT 0 AS z_master_station_id, 'Солнцево' AS z_master_station_nm, 0 AS z_master_line_id, 'Солнцевская' AS z_master_line_nm, 2238 AS y_entrance_id UNION ALL SELECT 0 AS z_master_station_id, 'Солнцево' AS z_master_station_nm, 0 AS z_master_line_id, 'Солнцевская' AS z_master_line_nm, 2239 AS y_entrance_id UNION ALL SELECT 0 AS z_master_station_id, 'Боровское шоссе' AS z_master_station_nm, 0 AS z_master_line_id, 'Солнцевская' AS z_master_line_nm, 2240 AS y_entrance_id UNION ALL SELECT 0 AS z_master_station_id, 'Боровское шоссе' AS z_master_station_nm, 0 AS z_master_line_id, 'Солнцевская' AS z_master_line_nm, 2241 AS y_entrance_id UNION ALL SELECT 0 AS z_master_station_id, 'Новопеределкино' AS z_master_station_nm, 0 AS z_master_line_id, 'Солнцевская' AS z_master_line_nm, 2242 AS y_entrance_id UNION ALL SELECT 0 AS z_master_station_id, 'Новопеределкино' AS z_master_station_nm, 0 AS z_master_line_id, 'Солнцевская' AS z_master_line_nm, 2243 AS y_entrance_id UNION ALL SELECT 0 AS z_master_station_id, 'Рассказовка' AS z_master_station_nm, 0 AS z_master_line_id, 'Солнцевская' AS z_master_line_nm, 2244 AS y_entrance_id UNION ALL SELECT 0 AS z_master_station_id, 'Рассказовка' AS z_master_station_nm, 0 AS z_master_line_id, 'Солнцевская' AS z_master_line_nm, 2245 AS y_entrance_id UNION ALL SELECT 0 AS z_master_station_id, 'Саларьево' AS z_master_station_nm, 0 AS z_master_line_id, 'Сокольническая' AS z_master_line_nm, 2132 AS y_entrance_id UNION ALL SELECT 0 AS z_master_station_id, 'Саларьево' AS z_master_station_nm, 0 AS z_master_line_id, 'Сокольническая' AS z_master_line_nm, 2133 AS y_entrance_id UNION ALL SELECT 0 AS z_master_station_id, 'Румянцево' AS z_master_station_nm, 0 AS z_master_line_id, 'Сокольническая' AS z_master_line_nm, 2130 AS y_entrance_id UNION ALL SELECT 0 AS z_master_station_id, 'Румянцево' AS z_master_station_nm, 0 AS z_master_line_id, 'Сокольническая' AS z_master_line_nm, 2131 AS y_entrance_id UNION ALL SELECT 0 AS z_master_station_id, 'Румянцево' AS z_master_station_nm, 0 AS z_master_line_id, 'Сокольническая' AS z_master_line_nm, 2128 AS y_entrance_id UNION ALL SELECT 0 AS z_master_station_id, 'Румянцево' AS z_master_station_nm, 0 AS z_master_line_id, 'Сокольническая' AS z_master_line_nm, 2129 AS y_entrance_id
;

CREATE TABLE sandbox.yurbasov_ksl_ramenki_02
AS
SELECT psg_date
,psg_device
,crd_no
,crd_serial_no_hash
,in_date
,gd_id
,pl_id
,pass_type
,passes_done
,passes_left
,uid
,FLOOR(HOUR(psg_date)) AS h
,DAY FROM METRO_DATA.PSG_PASS_PPR_HASH_V_ALL WHERE DAY='2018-08-24' AND pass_type NOT IN ("60 поездок 90 м", "60 поездок ЕД")

CREATE TABLE sandbox.yurbasov_ksl_ramenki_03
AS
SELECT psg_date
,psg_device
,crd_no
,crd_serial_no_hash
,in_date
,gd_id
,pl_id
,pass_type
,passes_done
,passes_left
,uid
,FLOOR(HOUR(psg_date)) AS h
,DAY FROM sandbox.yurbasov_ksl_ramenki_01 JOIN sandbox.yurbasov_ksl_ramenki_02 ON y_entrance_id = pl_id

CREATE TABLE sandbox.yurbasov_ksl_ramenki_04
AS
SELECT crd_no FROM sandbox.yurbasov_ksl_ramenki_03 WHERE z_master_station_nm = 'Раменки' GROUP BY crd_no

CREATE TABLE sandbox.yurbasov_ksl_ramenki_05
AS
SELECT psg_date
,psg_device
,crd_no AS x_crd_no
,crd_serial_no_hash
,in_date
,gd_id
,pl_id
,pass_type
,passes_done
,passes_left
,uid
,FLOOR(HOUR(psg_date)) AS h
,DAY FROM METRO_DATA.PSG_PASS_PPR_HASH_V_ALL WHERE DAY='2018-08-31'

CREATE TABLE sandbox.yurbasov_ksl_ramenki_06
AS
SELECT x_crd_no FROM sandbox.yurbasov_ksl_ramenki_05 GROUP BY x_crd_no

CREATE TABLE sandbox.yurbasov_ksl_ramenki_07
AS
SELECT * FROM sandbox.yurbsasov_ksl_ramenki_04 LEFT JOIN  sandbox.yurbasov_ksl_ramenki_05 ON crd_no = x_crd_no

CREATE TABLE sandbox.yurbasov_ksl_ramenki_08
AS
SELECT * FROM sandbox.yurbasov_ksl_ramenki_07 WHERE x_crd_no IS NULL