- To keep it, select "Keep existing code".
- To replace it with an example, select "Replace with example".
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.
Keep existing code?
You are trying to switch languages while there's code in the editor.