voronka-c

 SET hive.tez.container.SIZE = 6656
;

 DROP TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_01
;

 DROP TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_02
;

 DROP TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_03
;

 DROP TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_04
;

 DROP TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_05
;

 DROP TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_06
;

 DROP TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_07
;

 DROP TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_08
;

 DROP TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_09
;

 DROP TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_10
;

 DROP TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_11
;

 DROP TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_12
;

 DROP TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_13
;

 DROP TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_14
;

 DROP TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_15
;

 DROP TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_16
;

 DROP TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_17
;

 DROP TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_18
;

 DROP TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_19
;

 DROP TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_20
;

 DROP TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_21
;

 DROP TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_22
;

 DROP TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_23
;

 DROP TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_24
;

 DROP TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_25
;

 DROP TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_26
;

 DROP TABLE CITY_EVENT_DATA.D_50C_METRO_CALC_S_NGPT_DATA
;

 SET hivevar:START_DAY_01='2018-08-24'
;

 SET hivevar:END_DAY_01='2018-08-25'
;

 SET hivevar:START_DAY_02='2018-08-31'
;

 SET hivevar:END_DAY_02='2018-09-01'
;

 CREATE TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_01
AS
SELECT nm
,line
,MIN(check_day) AS min_day FROM SANDBOX.YURBASOV_D_50B_METRO_CALC_S_NGPT_DATA_V2_12 GROUP BY nm, line ORDER BY nm, line
;

 CREATE TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_02
AS
SELECT nm
,line
,route
,ticket_code
,cnt
,check_day
,CASE WHEN nm IN ('Мичуринский проспект','Озёрная','Говорово','Солнцево','Боровское шоссе','Новопеределкино','Рассказовка') THEN 1 ELSE 0 END AS is_main
,CASE WHEN nm IN ('Мичуринский проспект','Озёрная','Говорово','Солнцево','Боровское шоссе','Новопеределкино','Рассказовка') THEN 0 ELSE 1 END AS is_extra FROM SANDBOX.YURBASOV_D_50B_METRO_CALC_S_NGPT_DATA_V2_12
;

 CREATE TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_03
AS
SELECT ticket_code, check_day
,MAX(is_main) AS main_flg
,MAX(is_extra) AS extra_flg FROM SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_02 GROUP BY ticket_code, check_day
;

 CREATE TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_04
AS
SELECT ticket_code AS x_ticket_code
,main_flg AS main_flg_a
,extra_flg AS extra_flg_a FROM SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_03 WHERE check_day < ${hivevar:START_DAY_02}
;

 CREATE TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_05
AS
SELECT ticket_code AS y_ticket_code
,main_flg AS main_flg_b
,extra_flg AS extra_flg_b FROM SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_03 WHERE check_day >= ${hivevar:START_DAY_02}
;

 CREATE TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_06
AS
SELECT ticket_code
,CASE WHEN extra_flg_a + main_flg_a > 0 THEN 1 ELSE 0 END AS is_old
,CASE WHEN extra_flg_a + main_flg_a <= 0 THEN 1 ELSE 0 END AS is_new
,main_flg_a
,main_flg_b
,extra_flg_a
,extra_flg_b FROM SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_03 JOIN SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_04 ON x_ticket_code = ticket_code JOIN SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_05 ON y_ticket_code = ticket_code
;

 CREATE TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_07
AS
SELECT ticket_code AS o_ticket_code
,is_old FROM SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_06 WHERE is_old > 0
;

 CREATE TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_08
AS
SELECT ticket_code AS n_ticket_code
,is_new
,main_flg_a
,main_flg_b
,extra_flg_a
,extra_flg_b FROM SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_06 WHERE is_new > 0
;

 CREATE TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_09
AS
SELECT nm
,line
,route
,ticket_code
,cnt
,check_day
,is_old
,is_new
,main_flg_a
,main_flg_b
,extra_flg_a
,extra_flg_b FROM SANDBOX.YURBASOV_D_50B_METRO_CALC_S_NGPT_DATA_V2_12 LEFT JOIN SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_07 ON o_ticket_code = ticket_code LEFT JOIN SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_08 ON n_ticket_code = ticket_code
;

 CREATE TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_10
AS
SELECT nm
,line
,route
,ticket_code
,CAST(cnt AS BIGINT) AS cnt
,check_day
,is_old
,is_new
,main_flg_a
,main_flg_b
,extra_flg_a
,extra_flg_b FROM SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_09 WHERE check_day < ${hivevar:START_DAY_02}
;

 CREATE TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_11
AS
SELECT nm AS nm_11
,line AS line_11
,SUM(cnt) AS total_cnt_a FROM SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_10 GROUP BY nm, line
;

 CREATE TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_12
AS
SELECT nm
,line
,route
,ticket_code
,CAST(cnt AS BIGINT) AS cnt
,check_day
,is_old
,is_new
,main_flg_a
,main_flg_b
,extra_flg_a
,extra_flg_b FROM SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_09 WHERE check_day >= ${hivevar:START_DAY_02}
;

 CREATE TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_13
AS
SELECT nm AS nm_13
,line AS line_13
,SUM(cnt) AS total_cnt_b FROM SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_12 GROUP BY nm, line
;

 CREATE TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_14
AS
SELECT nm
,line
,route
,ticket_code
,CAST(cnt AS BIGINT) AS cnt
,check_day
,is_old
,is_new
,main_flg_a
,main_flg_b
,extra_flg_a
,extra_flg_b FROM SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_09 WHERE route != 'метро' AND check_day < ${hivevar:START_DAY_02}
;

 CREATE TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_15
AS
SELECT nm AS nm_15
,line AS line_15
,SUM(cnt) AS ngpt_cnt_a FROM SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_14 GROUP BY nm, line
;

 CREATE TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_16
AS
SELECT nm
,line
,route
,ticket_code
,CAST(cnt AS BIGINT) AS cnt
,check_day
,is_old
,is_new
,main_flg_a
,main_flg_b
,extra_flg_a
,extra_flg_b FROM SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_09 WHERE route != 'метро' AND check_day >= ${hivevar:START_DAY_02}
;

 CREATE TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_17
AS
SELECT nm AS nm_17
,line AS line_17
,SUM(cnt) AS ngpt_cnt_b FROM SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_16 GROUP BY nm, line
;

 CREATE TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_18
AS
SELECT nm AS nm_18
,line AS line_18
,SUM(cnt) AS new_total_cnt_a FROM SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_10 GROUP BY nm, line, is_new HAVING is_new > 0
;

 CREATE TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_19
AS
SELECT nm AS nm_19
,line AS line_19
,SUM(cnt) AS new_total_cnt_b FROM SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_12 GROUP BY nm, line, is_new HAVING is_new > 0
;

 CREATE TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_20
AS
SELECT nm AS nm_20
,line AS line_20
,SUM(cnt) AS new_ngpt_cnt_a FROM SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_14 GROUP BY nm, line, is_new HAVING is_new > 0
;

 CREATE TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_21
AS
SELECT nm AS nm_21
,line AS line_21
,SUM(cnt) AS new_ngpt_cnt_b FROM SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_16 GROUP BY nm, line, is_new HAVING is_new > 0
;

 CREATE TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_22
AS
SELECT nm AS nm_22
,line AS line_22
,SUM(cnt) AS old_total_cnt_a FROM SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_10 GROUP BY nm, line, is_old HAVING is_old > 0
;

 CREATE TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_23
AS
SELECT nm AS nm_23
,line AS line_23
,SUM(cnt) AS old_total_cnt_b FROM SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_12 GROUP BY nm, line, is_old HAVING is_old > 0
;

 CREATE TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_24
AS
SELECT nm AS nm_24
,line AS line_24
,SUM(cnt) AS old_ngpt_cnt_a FROM SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_14 GROUP BY nm, line, is_old HAVING is_old > 0
;

 CREATE TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_25
AS
SELECT nm AS nm_25
,line AS line_25
,SUM(cnt) AS old_ngpt_cnt_b FROM SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_16 GROUP BY nm, line, is_old HAVING is_old > 0
;

 CREATE TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_26
AS
SELECT nm
,line
,CASE WHEN total_cnt_a + total_cnt_b > 0 THEN 1 - total_cnt_b / (total_cnt_a + total_cnt_b) ELSE 0.0 END AS p
,total_cnt_a
,total_cnt_b
,ngpt_cnt_a
,ngpt_cnt_b
        ,CASE WHEN new_total_cnt_a > 0 THEN new_total_cnt_a ELSE 0 END AS new_total_cnt_a
        ,CASE WHEN new_total_cnt_b > 0 THEN new_total_cnt_b ELSE 0 END AS new_total_cnt_b
        ,CASE WHEN new_ngpt_cnt_a > 0 THEN new_ngpt_cnt_a ELSE 0 END AS new_ngpt_cnt_a
        ,CASE WHEN new_ngpt_cnt_b > 0 THEN new_ngpt_cnt_b ELSE 0 END AS new_ngpt_cnt_b
        ,old_total_cnt_a
        ,old_total_cnt_b
        ,old_ngpt_cnt_a
        ,old_ngpt_cnt_b
        ,CASE WHEN new_ngpt_cnt_b + new_ngpt_cnt_a > 0 THEN old_total_cnt_b + new_total_cnt_b - old_total_cnt_a - new_total_cnt_a ELSE old_total_cnt_b - old_total_cnt_a END AS auto_cnt_delta
        ,min_day FROM SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_01 JOIN SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_11 ON nm = nm_11 AND line = line_11 JOIN SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_13 ON nm = nm_13 AND line = line_13 LEFT JOIN SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_15 ON nm = nm_15 AND line = line_15 LEFT JOIN SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_17 ON nm = nm_17 AND line = line_17 LEFT JOIN SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_18 ON nm = nm_18 AND line = line_18 LEFT JOIN SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_19 ON nm = nm_19 AND line = line_19 LEFT JOIN SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_20 ON nm = nm_20 AND line = line_20 LEFT JOIN SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_21 ON nm = nm_21 AND line = line_21 LEFT JOIN SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_22 ON nm = nm_22 AND line = line_22 LEFT JOIN SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_23 ON nm = nm_23 AND line = line_23 LEFT JOIN SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_24 ON nm = nm_24 AND line = line_24 LEFT JOIN SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_V2_25 ON nm = nm_25 AND line = line_25 ORDER BY p DESC
;