Dixie/VanityFair/MardiGras - TTL Brand and Categories Combined

PHOTO EMBED

Thu May 06 2021 19:01:58 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','Vanity Fair','Mardi Gras') THEN 'DIXIE/VANITYFAIR/MARDIGRAS'
        ELSE 'other_brand'
        END AS Brand,
      Case when barcode_category_1 in ('PAPER NAPKINS','DISPOSABLE CP/CP RFL','PLASTIC CUTLERY',
                                 'PR/PLST/STYFM/PLT/BW') then 'CUPS/PAPERNAPKINS/PLASTICCUTLERY'
       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 ('CUPS/PAPERNAPKINS/PLASTICCUTLERY')
           
   -- and retailer_banner in ('WALMART')
 group by 1,2,3,4,5,6
),
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','Vanity Fair','Mardi Gras') THEN 'DIXIE/VANITYFAIR/MARDIGRAS'
           ELSE 'other_brand'
          END AS Brand,
     Case when barcode_category_1 in ('PAPER NAPKINS','DISPOSABLE CP/CP RFL','PLASTIC CUTLERY',
                                 'PR/PLST/STYFM/PLT/BW') then 'CUPS/PAPERNAPKINS/PLASTICCUTLERY'
           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 ('CUPS/PAPERNAPKINS/PLASTICCUTLERY')
 group by 1,2,3,4,5,6,7
),
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, BRAND, CATEGORY, sales, trips, users, units
from
//BRAND ALL USERS
(select distinct 'BRAND - ALL USERS' as type, BRAND, 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,4
union
--CATEGORY ALL USERS
 select distinct 'TOTAL CATEGORY - ALL USERS' as type, BRAND, 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,4
union
-- BRAND 15MCP 
 select distinct 'BRAND - 15MCP' as type, BRAND, 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,4
union
-- CATEGORY 15MCP
select distinct 'TOTAL CATEGORY - 15MCP' as type, BRAND, 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,4
 union
--OTHER
 select distinct 'OTHER - ALL USERS' as type, BRAND, 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,4
 union
select distinct 'OTHER - 15MCP' as type, BRAND, 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,4
)
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 KRAZY KRITTERS',
'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 'barcode_category_1'
           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,5
),
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 KRAZY KRITTERS',
'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 'barcode_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,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, Brand, Category, sales, trips, users, units
from
//BRAND ALL USERS
(select distinct 'BRAND - ALL USERS' as type, Brand, Category,
    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, Brand, Category,
 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, Brand, Category,
 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, Brand, Category,
    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, Brand, 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,3
 union
select distinct 'OTHER - 15MCP' as type, Brand, 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,3
)
order by 2 desc
;




content_copyCOPY