SELECT

       t2.BUSINESS_TYPE,

       t2.CLIENT_TYPE,

       CASE WHEN t2.ExecutionKey IN (32, 1024) THEN 1 ELSE 0 END AS Upsale,

       t1.history_date,

       COUNT(t1.application_account) count_accounts,

       SUM(t1.portfolio) sum_portfolio,

       SUM(t1.cess45_new_discont) sum_cess,

       SUM(t1.cession_protfolio) sum_cess_portfolio,

       SUM(t1.delta_revenue) AS sum_delta_revenue,

       SUM(t1.delta_provisions) AS sum_delta_provisions,

       SUM(t1.delta_portfolio) AS sum_delta_portfolio,

       SUM(t1.delta_ecl) AS sum_delta_ecl,

       CASE WHEN ROW_NUMBER() OVER(PARTITION BY BUSINESS_TYPE, CLIENT_TYPE, CASE WHEN t2.ExecutionKey IN (32, 1024) THEN 1 ELSE 0 END ORDER BY t1.history_date) <= 12 THEN 0 ELSE

                                                             SUM(SUM(t1.delta_revenue)) OVER(PARTITION BY BUSINESS_TYPE, CLIENT_TYPE, CASE WHEN t2.ExecutionKey IN (32, 1024) THEN 1 ELSE 0 END

                                                             ORDER BY t1.history_date

                                                             ROWS 11 PRECEDING) END AS delta_revenue_12,

       CASE WHEN ROW_NUMBER() OVER(PARTITION BY BUSINESS_TYPE, CLIENT_TYPE, CASE WHEN t2.ExecutionKey IN (32, 1024) THEN 1 ELSE 0 END ORDER BY t1.history_date) <= 12 THEN 0 ELSE

                                                             SUM(SUM(t1.delta_provisions)) OVER(PARTITION BY BUSINESS_TYPE, CLIENT_TYPE, CASE WHEN t2.ExecutionKey IN (32, 1024) THEN 1 ELSE 0 END

                                                             ORDER BY t1.history_date

                                                             ROWS 11 PRECEDING) END AS delta_provisions_12,

       CASE WHEN ROW_NUMBER() OVER(PARTITION BY BUSINESS_TYPE, CLIENT_TYPE, CASE WHEN t2.ExecutionKey IN (32, 1024) THEN 1 ELSE 0 END ORDER BY t1.history_date) <= 12 THEN 0 ELSE

                                                             SUM(SUM(t1.delta_portfolio)) OVER(PARTITION BY BUSINESS_TYPE, CLIENT_TYPE, CASE WHEN t2.ExecutionKey IN (32, 1024) THEN 1 ELSE 0 END

                                                             ORDER BY t1.history_date

                                                             ROWS 11 PRECEDING) END AS delta_portfolio_12,

       CASE WHEN ROW_NUMBER() OVER(PARTITION BY BUSINESS_TYPE, CLIENT_TYPE, CASE WHEN t2.ExecutionKey IN (32, 1024) THEN 1 ELSE 0 END ORDER BY t1.history_date) <= 12 THEN 0 ELSE

                                                             SUM(SUM(t1.delta_ecl)) OVER(PARTITION BY BUSINESS_TYPE, CLIENT_TYPE, CASE WHEN t2.ExecutionKey IN (32, 1024) THEN 1 ELSE 0 END

                                                             ORDER BY t1.history_date

                                                             ROWS 11 PRECEDING) END AS delta_ecl_12



FROM (SELECT *, cession_protfolio = CASE WHEN cess45_new_discont > 0 THEN portfolio ELSE 0 END,

                           delta_portfolio = CASE WHEN portfolio - LAG(portfolio) OVER(PARTITION BY application_account ORDER BY application_account, history_date) + LAG(CASE WHEN cess45_new_discont > 0 THEN portfolio ELSE 0 END) OVER(PARTITION BY application_account ORDER BY application_account, history_date) IS NULL THEN 0

                           ELSE portfolio - LAG(portfolio) OVER(PARTITION BY application_account ORDER BY application_account, history_date) + LAG(CASE WHEN cess45_new_discont > 0 THEN portfolio ELSE 0 END) OVER(PARTITION BY application_account ORDER BY application_account, history_date) END,

                           delta_ecl = CASE WHEN ecl - LAG(ecl) OVER(PARTITION BY application_account ORDER BY application_account, history_date) + LAG(cess45_new_discont) OVER(PARTITION BY application_account ORDER BY application_account, history_date) IS NULL THEN 0

                           ELSE ecl - LAG(ecl) OVER(PARTITION BY application_account ORDER BY application_account, history_date) + LAG(cess45_new_discont) OVER(PARTITION BY application_account ORDER BY application_account, history_date) END

FROM [DWH_FIRE_WINDOW].[dbo].[tmp_revenues]) AS t1



LEFT JOIN DSS.spr.spr_all_data AS t2

       ON t1.application_account = t2.APPLICATION_ACCOUNT

WHERE DAY(t1.history_date) BETWEEN 27 AND 32

GROUP BY t2.BUSINESS_TYPE, t2.CLIENT_TYPE, CASE WHEN t2.ExecutionKey IN (32, 1024) THEN 1 ELSE 0 END, t1.history_date

ORDER BY t2.BUSINESS_TYPE, t2.CLIENT_TYPE, CASE WHEN t2.ExecutionKey IN (32, 1024) THEN 1 ELSE 0 END, t1.history_date