an anonymous user ·· SQL
WITH t1 as ( SELECT id, week_end_date, amt_value/units as PRICE_UNIT, base_amt_value/base_units as BASE_PRICE_UNIT, MAX(amt_value/units) OVER (PARTITION BY product_code ORDER BY week_end_date ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) AS RSP_UNIT ((units-base_units)*100)/base_units as UPLIFT_PER, FROM germany_rms ), tx as( SELECT week_end_date ,SUM(acv_distribution) FROM germany_rms GROUP BY week_end_date,brand SUM(acv_distribution) OVER(PARTITION BY week_end_date,brand) as TDP, ) , t8 as( SELECT date_add('day', 5, DATE_TRUNC('week',date)) as week_end_date, COUNT(*) FROM ( SELECT * FROM germany_calendar1 WHERE event IN ('New Year''s Day','St Patricks Day', 'Good Friday' ,'Easter Monday','May Day', 'Spring Bank Holiday', 'Summer Bank Holiday','St Andrew''s Day', 'Christmas', 'Boxing Day') ) GROUP BY 1; ), t9 as ( SELECT date_add('day', 5, DATE_TRUNC('week',date)) as week_end_date, COUNT(*) FROM germany_calendar1 GROUP BY 1 ; ), t10 as ( SELECT grms.id,grms.units, CASE WHEN (t1.RSP_UNIT-t1.PRICE_UNIT) > 0 THEN (t1.RSP_UNIT-t1.PRICE_UNIT) ELSE 0 END AS DISCOUNT_UNIT, t1.RSP_UNIT as RSP_UNIT, t1.PRICE_UNIT as PRICE_UNIT, t1.week_end_date as week_end_date, t1.category as category FROM germany_rms grms JOIN t1 ON grms.id=t1.id ), t12 as ( SELECT id, (t10.DISCOUNT_UNIT*100)/t10.RSP_UNIT as DISCOUNT_PER, AVG(t10.DISCOUNT_UNIT) OVER (PARTITION BY t10.week_end_date,t10.category) as COMP_AVG_DISCOUNT_UNIT, AVG((t10.DISCOUNT_UNIT*100)/t10.RSP_UNIT) OVER (PARTITION BY t10.week_end_date,t10.category) as COMP_AVG_DISCOUNT_PER, AVG(t10.price_unit) OVER (PARTITION BY t10.week_end_date,t10.category) as COMP_AVG_PRICE_UNIT, AVG(t10.units) OVER (PARTITION BY t10.week_end_date,t10.category) as COMP_AVG_UNITS FROM t10; ), t17 as ( SELECT id,week_end_date,product_code, TI_TOTAL, NR_TEMP-TI_TOTAL as NR_TOTAL, NR_TEMP-TI_TOTAL- GM_TEMP as GM_TOTAL, TI_TOTAL/units as TI_UNIT, (NR_TEMP-TI_TOTAL)/units as NR_UNIT, (NR_TEMP-TI_TOTAL- GM_TEMP)/units as GM_UNIT FROM ( SELECT grms.id as id, grms.week_end_date as week_end_date, grms.product_code as product_code, grms.units as units, TRY_CAST(gmap.retro_fund_unit as double)*grms.units as TI_TOTAL, gmap.stp_unit*grms.units as NR_TEMP, gmap.cogs_unit*grms.units as GM_TEMP FROM germany_rms grms JOIN germany_map gmap ON (grms.week_end_date = gmap.week_end_date) AND (grms.product_code=gmap.product_code) ) ), t23 as ( SELECT gmap.ppg, gmap.week_end_date, sum(units) as PPG_UNITS, sum(amt_value) as PPG_AMT_VALUE, sum(amt_value/units) as PPG_PRICE_UNIT FROM germany_rms grms JOIN germany_map gmap ON (gmap.product_code = grms.product_code) and (germany_map.week_end_date = germany_rms.week_end_date) group BY gmap.week_end_date , gmap.ppg; )
Click on the Run button to get started.
The code/input has changed since you last clicked on Run. Click it again to see the updated changes.