GaPa - TTL Category

PHOTO EMBED

Thu May 06 2021 19:03:48 GMT+0000 (UTC)

Saved by @k8hohenstatt

//Totals By month, Brand vs. Category
with b as (select to_date(date_trunc('month',RECEIPT_PURCHASE_DATE)) as date,
    panel_cont_15m,
    --  case when barcode_brand in ('DIXIE','DIXIE EVERYDAY','DIXIE PATHWAYS','DIXIE ULTRA',
--'DIXIE ULTRA MOMENTS','DIXIE EVERYDAY','DIXIE TO GO','DIXIE PERFECTOUCH','DIXIE PERFECTOUCH TO GO',
--'DIXIE ULTRA DEEP DISH','DIXIE CRAFT IMALS') THEN 'Dixie'
--when barcode_brand in ('VANITY FAIR','VANITY FAIR EVERYDAY','VANITY FAIR IMPRESSIONS') THEN 'Vanity Fair'
--when barcode_brand in ('MARDI GRAS') THEN 'Mardi Gras'
        --   ELSE 'other_brand'
        --  END AS Brand,
     Case when barcode_category_1 in ('PAPER NAPKINS') THEN 'Paper Napkins'
   when barcode_category_1 in ('DISPOSABLE CP/CP RFL') Then 'Cups'
     when barcode_category_1 in ('PLASTIC CUTLERY','PR/PLST/STYFM/PLT/BW') then 'Plastic Cutlery'
           else 'other_category'
           END as Category,
    receipt_id,
    user_id,
    sum(ITEM_DISCOUNTED_EXTENDED_PRICE) as sales,           
    sum(ITEM_QUANTITY) as units
from "DATAVERSE"."FETCH360"."TRANSACTION360_IRI"
where 1=1
    and user_active=true
    and receipt_purchase_date::date between '2019-03-01' and '2021-03-31'
    and barcode_manufacturer in ('GEORGIA PACIFIC CONSUMER PRODUCTS') 
  --  and barcode_brand in ('Dixie','Vanity Fair','Mardi Gras')
    and category in ('Paper Napkins','Cups','Plastic Cutlery')

   -- and retailer_banner in ('WALMART')
 group by 1,2,3,4,5
),
c as (select to_date(date_trunc('month',RECEIPT_PURCHASE_DATE)) as date,
    panel_cont_15m,
    barcode_manufacturer,
  --  case when barcode_brand in ('DIXIE','DIXIE EVERYDAY','DIXIE PATHWAYS','DIXIE ULTRA',
--'DIXIE ULTRA MOMENTS','DIXIE EVERYDAY','DIXIE TO GO','DIXIE PERFECTOUCH','DIXIE PERFECTOUCH TO GO',
--'DIXIE ULTRA DEEP DISH','DIXIE CRAFT IMALS') THEN 'Dixie'
--when barcode_brand in ('VANITY FAIR','VANITY FAIR EVERYDAY','VANITY FAIR IMPRESSIONS') THEN 'Vanity Fair'
--when barcode_brand in ('MARDI GRAS') THEN 'Mardi Gras'
     --      ELSE 'other_brand'
     --      END AS Brand,
       Case when barcode_category_1 in ('PAPER NAPKINS') THEN 'Paper Napkins'
   when barcode_category_1 in ('DISPOSABLE CP/CP RFL') Then 'Cups'
     when barcode_category_1 in ('PLASTIC CUTLERY','PR/PLST/STYFM/PLT/BW') then 'Plastic Cutlery'
      else 'other_category'
      END as category,
    receipt_id,
    user_id,  
    sum(ITEM_DISCOUNTED_EXTENDED_PRICE) as sales,
    sum(ITEM_QUANTITY) as units
from "DATAVERSE"."FETCH360"."TRANSACTION360_IRI"
where 1=1
    and user_active=true
    and receipt_purchase_date::date between '2019-03-01' and '2021-03-31' 
--and barcode_manufacturer in ('GEORGIA PACIFIC CONSUMER PRODUCTS')
   --  and brand in ('Dixie','Vanity Fair','Mardi Gras')
   and category in ('Paper Napkins','Cups','Plastic Cutlery')
 group by 1,2,3,4,5,6
),
b_15mcp as (select *
from b
where panel_cont_15m=true                       
 --group by 1,2,3,4,5,6
),
c_15mcp as (select *
from c
where panel_cont_15m=true                         
 --group by 1,2,3,4,5,6
)
select distinct
    type, date, CATEGORY, sales, trips, users, units
from
//BRAND ALL USERS
(select distinct 'BRAND - ALL USERS' as type, CATEGORY, 
 date, sum(sales) sales, count(distinct receipt_id) trips,count(distinct user_id) users, sum(units) units
 from b
 group by 1,2,3
union
--CATEGORY ALL USERS
 select distinct 'TOTAL CATEGORY - ALL USERS' as type,CATEGORY,
 date, sum(sales) sales, count(distinct receipt_id) trips,count(distinct user_id) users, sum(units) units
 from c
 group by 1,2,3
union
-- BRAND 15MCP 
 select distinct 'BRAND - 15MCP' as type, CATEGORY, 
 date, sum(sales) sales, count(distinct receipt_id) trips,count(distinct user_id) users, sum(units) units
 from b_15mcp
 group by 1,2,3
union
-- CATEGORY 15MCP
select distinct 'TOTAL CATEGORY - 15MCP' as type, CATEGORY,
    date, sum(sales) sales, count(distinct receipt_id) trips,count(distinct user_id) users, sum(units) units
 from c_15mcp
 group by 1,2,3
 union
--OTHER
 select distinct 'OTHER - ALL USERS' as type, CATEGORY,
    date, sum(sales) sales, count(distinct receipt_id) trips,count(distinct user_id) users, sum(units) units
 from c
 where barcode_manufacturer not in ('GEORGIA PACIFIC CONSUMER PRODUCTS')
 group by 1,2,3
 union
select distinct 'OTHER - 15MCP' as type, CATEGORY,
    date, sum(sales) sales, count(distinct receipt_id) trips,count(distinct user_id) users, sum(units) units
 from c_15mcp
 where barcode_manufacturer not in ('GEORGIA PACIFIC CONSUMER PRODUCTS') 
 group by 1,2,3
)
order by 2,3 desc
;




//Totals By Brand vs. Category
with b as (select panel_cont_15m,
         --  case when barcode_brand in ('DIXIE','DIXIE EVERYDAY','DIXIE PATHWAYS','DIXIE ULTRA',
--'DIXIE ULTRA MOMENTS','DIXIE EVERYDAY','DIXIE TO GO','DIXIE PERFECTOUCH','DIXIE PERFECTOUCH TO GO',
--'DIXIE ULTRA DEEP DISH','DIXIE CRAFT IMALS') THEN 'Dixie'
--when barcode_brand in ('VANITY FAIR','VANITY FAIR EVERYDAY','VANITY FAIR IMPRESSIONS') THEN 'Vanity Fair'
--when barcode_brand in ('MARDI GRAS') THEN 'Mardi Gras'
       --    ELSE 'other_brand'
        --  END AS Brand,
     Case when barcode_category_1 in ('PAPER NAPKINS') THEN 'Paper Napkins'
   when barcode_category_1 in ('DISPOSABLE CP/CP RFL') Then 'Cups'
     when barcode_category_1 in ('PLASTIC CUTLERY','PR/PLST/STYFM/PLT/BW') then 'Plastic Cutlery'
           else 'other_category'
           END as Category,
   -- and retailer_banner in ('WALMART')
    receipt_id,
    user_id,
    sum(ITEM_DISCOUNTED_EXTENDED_PRICE) as sales,           
    sum(ITEM_QUANTITY) as units
from "DATAVERSE"."FETCH360"."TRANSACTION360_IRI"
where 1=1
    and user_active=true
    and receipt_purchase_date::date between '2019-03-01' and '2021-03-31'
 and barcode_manufacturer in ('GEORGIA PACIFIC CONSUMER PRODUCTS')
    --and brand in ('Dixie','Vanity Fair','Mardi Gras')
     and category in ('Paper Napkins','Cups','Plastic Cutlery')
 group by 1,2,3,4
),
c as (select panel_cont_15m,
    barcode_manufacturer,
   --  case when barcode_brand in ('DIXIE','DIXIE EVERYDAY','DIXIE PATHWAYS','DIXIE ULTRA',
--'DIXIE ULTRA MOMENTS','DIXIE EVERYDAY','DIXIE TO GO','DIXIE PERFECTOUCH','DIXIE PERFECTOUCH TO GO',
--'DIXIE ULTRA DEEP DISH','DIXIE CRAFT IMALS') THEN 'Dixie'
--when barcode_brand in ('VANITY FAIR','VANITY FAIR EVERYDAY','VANITY FAIR IMPRESSIONS') THEN 'Vanity Fair'
--when barcode_brand in ('MARDI GRAS') THEN 'Mardi Gras'
          -- ELSE 'other_brand'
         -- END AS Brand,
     Case when barcode_category_1 in ('PAPER NAPKINS') THEN 'Paper Napkins'
   when barcode_category_1 in ('DISPOSABLE CP/CP RFL') Then 'Cups'
     when barcode_category_1 in ('PLASTIC CUTLERY','PR/PLST/STYFM/PLT/BW') then 'Plastic Cutlery'
           else 'other_category'
           END as Category,
    receipt_id,
    user_id,  
    sum(ITEM_DISCOUNTED_EXTENDED_PRICE) as sales,
    sum(ITEM_QUANTITY) as units
from "DATAVERSE"."FETCH360"."TRANSACTION360_IRI"
where 1=1
    and user_active=true
    and receipt_purchase_date::date between '2019-03-01' and '2021-03-31'
     --and brand in ('Dixie','Vanity Fair','Mardi Gras')
    --and retailer_banner in ('WALMART')
       and category in ('Paper Napkins','Cups','Plastic Cutlery')
 group by 1,2,3,4,5
),
b_15mcp as (select *
from b
where panel_cont_15m=true                       
-- group by 1,2,3,4,5,6
),
c_15mcp as (select *
from c
where panel_cont_15m=true                         
 --group by 1,2,3,4,5,6
)
select distinct
    type, Category, sales, trips, users, units
from
//BRAND ALL USERS
(select distinct 'BRAND - ALL USERS' as type, Category,
    sum(sales) sales, count(distinct receipt_id) trips,count(distinct user_id) users, sum(units) units
 from b
 group by 1,2
union
--CATEGORY ALL USERS
 select distinct 'TOTAL CATEGORY - ALL USERS' as type, Category,
 sum(sales) sales, count(distinct receipt_id) trips,count(distinct user_id) users, sum(units) units
 from c
 group by 1,2
union
-- BRAND 15MCP 
 select distinct 'BRAND - 15MCP' as type, Category,
 sum(sales) sales, count(distinct receipt_id) trips,count(distinct user_id) users, sum(units) units
 from b_15mcp
 group by 1,2
union
-- CATEGORY 15MCP
select distinct 'TOTAL CATEGORY - 15MCP' as type, Category,
    sum(sales) sales, count(distinct receipt_id) trips,count(distinct user_id) users, sum(units) units
 from c_15mcp
 group by 1,2
 union
--OTHER
 select distinct 'OTHER - ALL USERS' as type,Category,
    sum(sales) sales, count(distinct receipt_id) trips,count(distinct user_id) users, sum(units) units
 from c
 where barcode_manufacturer not in ('GEORGIA PACIFIC CONSUMER PRODUCTS')
 group by 1,2
 union
select distinct 'OTHER - 15MCP' as type, Category,
    sum(sales) sales, count(distinct receipt_id) trips,count(distinct user_id) users, sum(units) units
 from c_15mcp
 where barcode_manufacturer not in ('GEORGIA PACIFIC CONSUMER PRODUCTS')
 group by 1,2
)
order by 2 desc
;
content_copyCOPY