Untitled

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
;