Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- with ws as
- (select d_year AS ws_sold_year, ws_item_sk,
- ws_bill_customer_sk ws_customer_sk,
- sum(ws_quantity) ws_qty,
- sum(ws_wholesale_cost) ws_wc,
- sum(ws_sales_price) ws_sp
- from test.web_sales
- left join test.web_returns on wr_order_number=ws_order_number and ws_item_sk=wr_item_sk
- join test.date_dim on ws_sold_date_sk = d_date_sk
- where /* --removed Cloudera cheat ws_sold_date_sk between 2451911 and 2452275
- and */ wr_order_number is null
- group by d_year, ws_item_sk, ws_bill_customer_sk
- ),
- cs as
- (select d_year AS cs_sold_year, cs_item_sk,
- cs_bill_customer_sk cs_customer_sk,
- sum(cs_quantity) cs_qty,
- sum(cs_wholesale_cost) cs_wc,
- sum(cs_sales_price) cs_sp
- from test.catalog_sales
- left join test.catalog_returns on cr_order_number=cs_order_number and cs_item_sk=cr_item_sk
- join test.date_dim on cs_sold_date_sk = d_date_sk
- where /* --removed Cloudera cheat cs_sold_date_sk between 2451911 and 2452275
- and */ cr_order_number is null
- group by d_year, cs_item_sk, cs_bill_customer_sk
- ),
- ss as
- (select d_year AS ss_sold_year, ss_item_sk,
- ss_customer_sk,
- sum(ss_quantity) ss_qty,
- sum(ss_wholesale_cost) ss_wc,
- sum(ss_sales_price) ss_sp
- from test.store_sales
- left join test.store_returns on sr_ticket_number=ss_ticket_number and ss_item_sk=sr_item_sk
- join test.date_dim on ss_sold_date_sk = d_date_sk
- where /* --removed Cloudera cheat ss_sold_date_sk between 2451911 and 2452275
- and */ sr_ticket_number is null
- group by d_year, ss_item_sk, ss_customer_sk
- )
- select
- ss_customer_sk,
- round(ss_qty/(coalesce(ws_qty+cs_qty,1)),2) ratio,
- ss_qty store_qty, ss_wc store_wholesale_cost, ss_sp store_sales_price,
- coalesce(ws_qty,0)+coalesce(cs_qty,0) other_chan_qty,
- coalesce(ws_wc,0)+coalesce(cs_wc,0) other_chan_wholesale_cost,
- coalesce(ws_sp,0)+coalesce(cs_sp,0) other_chan_sales_price
- from ss
- left join ws on (ws_sold_year=ss_sold_year and ws_item_sk=ss_item_sk and ws_customer_sk=ss_customer_sk)
- left join cs on (cs_sold_year=ss_sold_year and cs_item_sk=cs_item_sk and cs_customer_sk=ss_customer_sk)
- where coalesce(ws_qty,0)>0 and coalesce(cs_qty, 0)>0 and ss_sold_year=2001
- order by
- ss_customer_sk,
- ss_qty desc, ss_wc desc, ss_sp desc,
- other_chan_qty,
- other_chan_wholesale_cost,
- other_chan_sales_price,
- round(ss_qty/(coalesce(ws_qty+cs_qty,1)),2)
- limit 100;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement