sss

an anonymous user · September 16, 2021 · 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;
)























Comments

Please sign up or log in to contribute to the discussion.