0001
0002 with ss as (
0003 select
0004 s_store_sk,
0005 sum(ss_ext_sales_price) as sales,
0006 sum(ss_net_profit) as profit
0007 from
0008 store_sales, date_dim, store
0009 where
0010 ss_sold_date_sk = d_date_sk
0011 and d_date between cast('1998-08-04' as date)
0012 and (cast('1998-08-04' as date) + interval 30 days)
0013 and ss_store_sk = s_store_sk
0014 group by
0015 s_store_sk),
0016 sr as (
0017 select
0018 s_store_sk,
0019 sum(sr_return_amt) as returns,
0020 sum(sr_net_loss) as profit_loss
0021 from
0022 store_returns, date_dim, store
0023 where
0024 sr_returned_date_sk = d_date_sk
0025 and d_date between cast('1998-08-04' as date)
0026 and (cast('1998-08-04' as date) + interval 30 days)
0027 and sr_store_sk = s_store_sk
0028 group by
0029 s_store_sk),
0030 cs as (
0031 select
0032 cs_call_center_sk,
0033 sum(cs_ext_sales_price) as sales,
0034 sum(cs_net_profit) as profit
0035 from
0036 catalog_sales,
0037 date_dim
0038 where
0039 cs_sold_date_sk = d_date_sk
0040 and d_date between cast('1998-08-04' as date)
0041 and (cast('1998-08-04' as date) + interval 30 days)
0042 group by
0043 cs_call_center_sk),
0044 cr as (
0045 select
0046 sum(cr_return_amount) as returns,
0047 sum(cr_net_loss) as profit_loss
0048 from catalog_returns,
0049 date_dim
0050 where
0051 cr_returned_date_sk = d_date_sk
0052 and d_date between cast('1998-08-04' as date)
0053 and (cast('1998-08-04' as date) + interval 30 days)),
0054 ws as ( select wp_web_page_sk,
0055 sum(ws_ext_sales_price) as sales,
0056 sum(ws_net_profit) as profit
0057 from web_sales,
0058 date_dim,
0059 web_page
0060 where ws_sold_date_sk = d_date_sk
0061 and d_date between cast('1998-08-04' as date)
0062 and (cast('1998-08-04' as date) + interval 30 days)
0063 and ws_web_page_sk = wp_web_page_sk
0064 group by wp_web_page_sk),
0065 wr as
0066 (select wp_web_page_sk,
0067 sum(wr_return_amt) as returns,
0068 sum(wr_net_loss) as profit_loss
0069 from web_returns,
0070 date_dim,
0071 web_page
0072 where wr_returned_date_sk = d_date_sk
0073 and d_date between cast('1998-08-04' as date)
0074 and (cast('1998-08-04' as date) + interval 30 days)
0075 and wr_web_page_sk = wp_web_page_sk
0076 group by wp_web_page_sk)
0077 ,
0078 results as
0079 (select channel
0080 , id
0081 , sum(sales) as sales
0082 , sum(returns) as returns
0083 , sum(profit) as profit
0084 from
0085 (select 'store channel' as channel
0086 , ss.s_store_sk as id
0087 , sales
0088 , coalesce(returns, 0) as returns
0089 , (profit - coalesce(profit_loss,0)) as profit
0090 from ss left join sr
0091 on ss.s_store_sk = sr.s_store_sk
0092 union all
0093 select 'catalog channel' as channel
0094 , cs_call_center_sk as id
0095 , sales
0096 , returns
0097 , (profit - profit_loss) as profit
0098 from cs
0099 , cr
0100 union all
0101 select 'web channel' as channel
0102 , ws.wp_web_page_sk as id
0103 , sales
0104 , coalesce(returns, 0) returns
0105 , (profit - coalesce(profit_loss,0)) as profit
0106 from ws left join wr
0107 on ws.wp_web_page_sk = wr.wp_web_page_sk
0108 ) x
0109 group by channel, id )
0110
0111 select *
0112 from (
0113 select channel, id, sales, returns, profit from results
0114 union
0115 select channel, NULL AS id, sum(sales) as sales, sum(returns) as returns, sum(profit) as profit from results group by channel
0116 union
0117 select NULL AS channel, NULL AS id, sum(sales) as sales, sum(returns) as returns, sum(profit) as profit from results
0118 ) foo
0119 order by
0120 channel, id
0121 limit 100
