Untitled
plain_text
2 months ago
8.0 kB
1
Indexable
Never
WITH DATES AS (SELECT '2023-07-01' AS START_DATE) , CREATED AS ( SELECT Department_main, CAST(CP_item_created AS DATE) AS Date, COUNT(DISTINCT CODE) AS Items_Created FROM `tsum-dwh-users.tsum_collect.stream_seller_items_funnel_data_table`, DATES WHERE CAST(CP_item_created AS DATE) >= CAST(START_DATE AS DATE) GROUP BY 1, 2 ORDER BY 1 ) , CREATED_back AS ( SELECT Department_main, CAST(CP_item_created AS DATE) AS Date, COUNT(DISTINCT CODE) AS Items_Created FROM `tsum-dwh-users.tsum_collect.stream_seller_items_funnel_data_table` s, DATES left join (select item_code, location_id, lower(brand) as brand from `tsum-dwh-users.tsum_collect.assortment`) a on s.code=a.item_code WHERE CAST(CP_item_created AS DATE) >= CAST(START_DATE AS DATE) and (curr_return_status not in ('Заказан', 'Готов к выдаче') or curr_return_status is null) and a.brand != 'no brand in list' and (location_id != 'ЦК/Воз' or (DATE_DIFF(return_date, CP_item_created, day) <=5 and location_id = 'ЦК/Воз') ) GROUP BY 1, 2 ORDER BY 1 ) , AUTH AS ( SELECT Department_main, CAST(CAST(Auth_dt AS DATETIME) AS DATE) AS Auth_dt, COUNT(DISTINCT CODE) AS Items_Auth, count(distinct case when DATE_DIFF(Auth_dt, CP_item_created, hour) < 18 then code end) as Items_Auth_sla FROM `tsum-dwh-users.tsum_collect.stream_seller_items_funnel_data_table`, DATES WHERE CAST(Auth_dt AS DATETIME) >= CAST(START_DATE AS DATE) GROUP BY 1, 2 ) , AUTH_back AS ( SELECT Department_main, CAST(CAST(Auth_dt AS DATETIME) AS DATE) AS Auth_dt, COUNT(DISTINCT CODE) AS Items_Auth, count(distinct case when DATE_DIFF(Auth_dt, CP_item_created, hour) < 18 then code end) as Items_Auth_sla FROM `tsum-dwh-users.tsum_collect.stream_seller_items_funnel_data_table` s, DATES left join (select item_code, location_id, lower(brand) as brand from `tsum-dwh-users.tsum_collect.assortment`) a on s.code=a.item_code WHERE CAST(Auth_dt AS DATETIME) >= CAST(START_DATE AS DATE) and (curr_return_status not in ('Заказан', 'Готов к выдаче') or curr_return_status is null) and a.brand != 'no brand in list' and (location_id != 'ЦК/Воз' or DATE_DIFF(return_date, CP_item_created, day) <=5) and CP_Auth_status != 'Не аутентифицировано' GROUP BY 1, 2 ) , PRC AS ( SELECT Department_main, CAST(CAST(PRC_dt AS DATETIME) AS DATE) AS PRC_DATE, COUNT(DISTINCT s.CODE) AS Items_PRC, count(distinct case when PRC_Time < 18 then s.code end) as Items_PRC_sla FROM `tsum-dwh-users.tsum_collect.stream_seller_items_funnel_data_table` s, DATES left join (select distinct code, PRC_Time from `tsum-dwh-users.tsum_collect.stream_seller_SLA_dashboard` ) sla on s.code=sla.code WHERE CAST(PRC_dt AS DATETIME) >= CAST(START_DATE AS DATE) GROUP BY 1, 2 ) , PRC_back AS ( SELECT Department_main, CAST(CAST(PRC_dt AS DATETIME) AS DATE) AS PRC_DATE, COUNT(DISTINCT s.CODE) AS Items_PRC FROM `tsum-dwh-users.tsum_collect.stream_seller_items_funnel_data_table` s, DATES left join (select item_code, location_id, lower(brand) as brand from `tsum-dwh-users.tsum_collect.assortment`) a on s.code=a.item_code WHERE CAST(PRC_dt AS DATETIME) >= CAST(START_DATE AS DATE) and (curr_return_status not in ('Заказан', 'Готов к выдаче') or curr_return_status is null) and a.brand != 'no brand in list' and (location_id != 'ЦК/Воз' or DATE_DIFF(return_date, CP_item_created, day) <=5) and CP_Auth_status != 'Не аутентифицировано' GROUP BY 1, 2 ) , PRICE_ESTIMATED AS ( SELECT Department_main, CAST(CAST(Price_estimated_draft_dt AS DATETIME) AS DATE) AS Est_Date, COUNT(DISTINCT s.CODE) AS Items_Est, count(distinct case when PRC_Time < 18 and Auth_Estimated < 18 then s.code when PRC_Time > 18 and Auth_Estimated < (36 - PRC_Time) then s.code end) as Items_Est_sla FROM `tsum-dwh-users.tsum_collect.stream_seller_items_funnel_data_table` s, DATES left join (select distinct code, PRC_Time, Auth_Estimated from `tsum-dwh-users.tsum_collect.stream_seller_SLA_dashboard` ) sla on s.code=sla.code WHERE CAST(Price_estimated_draft_dt AS DATETIME) >= CAST(START_DATE AS DATE) GROUP BY 1, 2 ) , PRICE_ESTIMATED_back AS ( SELECT Department_main, CAST(CAST(Price_estimated_draft_dt AS DATETIME) AS DATE) AS Est_Date, COUNT(DISTINCT s.CODE) AS Items_Est FROM `tsum-dwh-users.tsum_collect.stream_seller_items_funnel_data_table` s, DATES left join (select item_code, location_id, lower(brand) as brand from `tsum-dwh-users.tsum_collect.assortment`) a on s.code=a.item_code WHERE CAST(Price_estimated_draft_dt AS DATETIME) >= CAST(START_DATE AS DATE) and (curr_return_status not in ('Заказан', 'Готов к выдаче') or curr_return_status is null) and a.brand != 'no brand in list' and (location_id != 'ЦК/Воз' or DATE_DIFF(return_date, CP_item_created, day) <=5) and CP_Auth_status != 'Не аутентифицировано' GROUP BY 1, 2 ) , cum_sum as ( select C.Department_main, c.Date, c.Items_Created, A.Items_Auth, PRC_back.Items_PRC, P.Items_Est, sum(c.Items_Created) over (partition by C.Department_main order by c.Date rows between unbounded preceding and current row) as cum_Items_Created, sum(A.Items_Auth) over (partition by C.Department_main order by c.Date rows between unbounded preceding and current row) as cum_Items_Auth, sum(PRC_back.Items_PRC) over (partition by C.Department_main order by c.Date rows between unbounded preceding and current row) as cum_Items_PRC, sum(P.Items_Est) over (partition by C.Department_main order by c.Date rows between unbounded preceding and current row) as cum_Items_Est from CREATED_back c left join AUTH_back A ON C.Date = A.Auth_dt AND C.Department_main = A.Department_main LEFT JOIN PRICE_ESTIMATED_back P ON C.Date = P.Est_Date AND C.Department_main = P.Department_main LEFT JOIN PRC_back ON C.Date = PRC_back.PRC_DATE AND C.Department_main = PRC_back.Department_main ) , back as ( select cs.Department_main, cs.Date, cum_Items_Created - ifnull(lag(cum_Items_Auth) over (partition by Department_main order by Date), 0) as back_auth, cum_Items_Auth - ifnull(lag(cum_Items_PRC) over (partition by Department_main order by Date), 0) as back_prc, cum_Items_PRC - ifnull(lag(cum_Items_Est) over (partition by Department_main order by Date), 0) as back_est, from cum_sum cs ) SELECT C.Department_main, c.Date, Items_Created, Items_Auth, Items_Auth_sla, Items_Est, Items_Est_sla, Items_PRC, Items_PRC_sla, case /*Костыль на беклог, который был за период до выборки*/ when C.Department_main = 'VIP' then b.back_auth when C.Department_main = 'КЦ' then b.back_auth + 4 when C.Department_main = 'ФЛ. Поставщик' then b.back_auth + 2 else b.back_auth end as back_auth, case --when C.Department_main = 'VIP' then b.back_prc + 295 when C.Department_main = 'VIP' then b.back_prc when C.Department_main = 'КЦ' then b.back_prc + 507 when C.Department_main = 'ФЛ. Поставщик' then b.back_prc + 2 when C.Department_main = 'Внешнее обращение' then b.back_prc + 89 else b.back_prc end as back_prc, case when C.Department_main = 'VIP' then b.back_est + 299 when C.Department_main = 'КЦ' then b.back_est + 512 when C.Department_main = 'ФЛ. Поставщик' then b.back_est + 2 when C.Department_main = 'Внешнее обращение' then b.back_est + 88 else b.back_est end as back_est FROM CREATED C LEFT JOIN AUTH A ON C.Date = A.Auth_dt AND C.Department_main = A.Department_main LEFT JOIN PRICE_ESTIMATED P ON C.Date = P.Est_Date AND C.Department_main = P.Department_main LEFT JOIN PRC ON C.Date = PRC.PRC_DATE AND C.Department_main = PRC.Department_main left join back b on c.date=b.date and c.Department_main=b.Department_main ORDER BY 1 DESC, 2 DESC ;