GaPa

//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
;





//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 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,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') 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,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
;




//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
;

Similiar Collections

Python strftime reference pandas.Period.strftime python - Formatting Quarter time in pandas columns - Stack Overflow python - Pandas: Change day - Stack Overflow python - Check if multiple columns exist in a df - Stack Overflow Pandas DataFrame apply() - sending arguments examples python - How to filter a dataframe of dates by a particular month/day? - Stack Overflow python - replace a value in the entire pandas data frame - Stack Overflow python - Replacing blank values (white space) with NaN in pandas - Stack Overflow python - get list from pandas dataframe column - Stack Overflow python - How to drop rows of Pandas DataFrame whose value in a certain column is NaN - Stack Overflow python - How to drop rows of Pandas DataFrame whose value in a certain column is NaN - Stack Overflow python - How to lowercase a pandas dataframe string column if it has missing values? - Stack Overflow How to Convert Integers to Strings in Pandas DataFrame - Data to Fish How to Convert Integers to Strings in Pandas DataFrame - Data to Fish create a dictionary of two pandas Dataframe columns? - Stack Overflow python - ValueError: No axis named node2 for object type <class 'pandas.core.frame.DataFrame'> - Stack Overflow Python Pandas iterate over rows and access column names - Stack Overflow python - Creating dataframe from a dictionary where entries have different lengths - Stack Overflow python - Deleting DataFrame row in Pandas based on column value - Stack Overflow python - How to check if a column exists in Pandas - Stack Overflow python - Import pandas dataframe column as string not int - Stack Overflow python - What is the most efficient way to create a dictionary of two pandas Dataframe columns? - Stack Overflow Python Loop through Excel sheets, place into one df - Stack Overflow python - How do I get the row count of a Pandas DataFrame? - Stack Overflow python - How to save a new sheet in an existing excel file, using Pandas? - Stack Overflow Python Loop through Excel sheets, place into one df - Stack Overflow How do I select a subset of a DataFrame? — pandas 1.2.4 documentation python - Delete column from pandas DataFrame - Stack Overflow python - Convert list of dictionaries to a pandas DataFrame - Stack Overflow How to Add or Insert Row to Pandas DataFrame? - Python Examples python - Check if a value exists in pandas dataframe index - Stack Overflow python - Set value for particular cell in pandas DataFrame using index - Stack Overflow python - Pandas Dataframe How to cut off float decimal points without rounding? - Stack Overflow python - Pandas: Change day - Stack Overflow python - Clean way to convert quarterly periods to datetime in pandas - Stack Overflow Pandas - Number of Months Between Two Dates - Stack Overflow python - MonthEnd object result in <11 * MonthEnds> instead of number - Stack Overflow python - Extracting the first day of month of a datetime type column in pandas - Stack Overflow
כמה עוד נשאר למשלוח חינם גם לעגלה ולצקאאוט הוספת צ'קבוקס לאישור דיוור בצ'קאאוט הסתרת אפשרויות משלוח אחרות כאשר משלוח חינם זמין דילוג על מילוי כתובת במקרה שנבחרה אפשרות איסוף עצמי הוספת צ'קבוקס לאישור דיוור בצ'קאאוט שינוי האפשרויות בתפריט ה-סידור לפי בווקומרס שינוי הטקסט "אזל מהמלאי" הערה אישית לסוף עמוד העגלה הגבלת רכישה לכל המוצרים למקסימום 1 מכל מוצר קבלת שם המוצר לפי ה-ID בעזרת שורטקוד הוספת כפתור וואטסאפ לקנייה בלופ ארכיון מוצרים הפיכה של מיקוד בצ'קאאוט ללא חובה מעבר ישיר לצ'קאאוט בלחיתה על הוספה לסל (דילוג עגלה) התראה לקבלת משלוח חינם בדף עגלת הקניות גרסה 1 התראה לקבלת משלוח חינם בדף עגלת הקניות גרסה 2 קביעה של מחיר הזמנה מינימלי (מוצג בעגלה ובצ'קאאוט) העברת קוד הקופון ל-ORDER REVIEW העברת קוד הקופון ל-ORDER REVIEW Kadence WooCommerce Email Designer קביעת פונט אסיסנט לכל המייל בתוסף מוצרים שאזלו מהמלאי - יופיעו מסומנים באתר, אבל בתחתית הארכיון הוספת כפתור "קנה עכשיו" למוצרים הסתרת אפשרויות משלוח אחרות כאשר משלוח חינם זמין שיטה 2 שינוי סימן מטבע ש"ח ל-ILS להפוך סטטוס הזמנה מ"השהייה" ל"הושלם" באופן אוטומטי תצוגת הנחה באחוזים שינוי טקסט "בחר אפשרויות" במוצרים עם וריאציות חיפוש מוצר לפי מק"ט שינוי תמונת מוצר לפי וריאציה אחרי בחירה של וריאציה אחת במקרה של וריאציות מרובות הנחה קבועה לפי תפקיד בתעריף קבוע הנחה קבועה לפי תפקיד באחוזים הסרה של שדות משלוח לקבצים וירטואליים הסתרת טאבים מעמוד מוצר הצגת תגית "אזל מהמלאי" בלופ המוצרים להפוך שדות ל-לא חובה בצ'קאאוט שינוי טקסט "אזל מהמלאי" לוריאציות שינוי צבע ההודעות המובנות של ווקומרס הצגת ה-ID של קטגוריות המוצרים בעמוד הקטגוריות אזל מהמלאי- שינוי ההודעה, תגית בלופ, הודעה בדף המוצר והוספת אזל מהמלאי על וריאציה הוספת שדה מחיר ספק לדף העריכה שינוי טקסט אזל מהמלאי