0001
0002 with ssr as(
0003 select
0004 s_store_id,
0005 sum(sales_price) as sales,
0006 sum(profit) as profit,
0007 sum(return_amt) as returns,
0008 sum(net_loss) as profit_loss
0009 from (
0010 select
0011 ss_store_sk as store_sk,
0012 ss_sold_date_sk as date_sk,
0013 ss_ext_sales_price as sales_price,
0014 ss_net_profit as profit,
0015 cast(0 as decimal(7,2)) as return_amt,
0016 cast(0 as decimal(7,2)) as net_loss
0017 from
0018 store_sales
0019 union all
0020 select
0021 sr_store_sk as store_sk,
0022 sr_returned_date_sk as date_sk,
0023 cast(0 as decimal(7,2)) as sales_price,
0024 cast(0 as decimal(7,2)) as profit,
0025 sr_return_amt as return_amt,
0026 sr_net_loss as net_loss
0027 from
0028 store_returns) salesreturns,
0029 date_dim,
0030 store
0031 where
0032 date_sk = d_date_sk and d_date between cast('1998-08-04' as date)
0033 and (cast('1998-08-04' as date) + INTERVAL 14 days)
0034 and store_sk = s_store_sk
0035 group by
0036 s_store_id),
0037 csr as (
0038 select
0039 cp_catalog_page_id,
0040 sum(sales_price) as sales,
0041 sum(profit) as profit,
0042 sum(return_amt) as returns,
0043 sum(net_loss) as profit_loss
0044 from (
0045 select
0046 cs_catalog_page_sk as page_sk,
0047 cs_sold_date_sk as date_sk,
0048 cs_ext_sales_price as sales_price,
0049 cs_net_profit as profit,
0050 cast(0 as decimal(7,2)) as return_amt,
0051 cast(0 as decimal(7,2)) as net_loss
0052 from catalog_sales
0053 union all
0054 select
0055 cr_catalog_page_sk as page_sk,
0056 cr_returned_date_sk as date_sk,
0057 cast(0 as decimal(7,2)) as sales_price,
0058 cast(0 as decimal(7,2)) as profit,
0059 cr_return_amount as return_amt,
0060 cr_net_loss as net_loss
0061 from catalog_returns) salesreturns,
0062 date_dim,
0063 catalog_page
0064 where
0065 date_sk = d_date_sk
0066 and d_date between cast('1998-08-04' as date)
0067 and (cast('1998-08-04' as date) + INTERVAL 14 days)
0068 and page_sk = cp_catalog_page_sk
0069 group by
0070 cp_catalog_page_id),
0071 wsr as (
0072 select
0073 web_site_id,
0074 sum(sales_price) as sales,
0075 sum(profit) as profit,
0076 sum(return_amt) as returns,
0077 sum(net_loss) as profit_loss
0078 from (
0079 select
0080 ws_web_site_sk as wsr_web_site_sk,
0081 ws_sold_date_sk as date_sk,
0082 ws_ext_sales_price as sales_price,
0083 ws_net_profit as profit,
0084 cast(0 as decimal(7,2)) as return_amt,
0085 cast(0 as decimal(7,2)) as net_loss
0086 from
0087 web_sales
0088 union all
0089 select
0090 ws_web_site_sk as wsr_web_site_sk,
0091 wr_returned_date_sk as date_sk,
0092 cast(0 as decimal(7,2)) as sales_price,
0093 cast(0 as decimal(7,2)) as profit,
0094 wr_return_amt as return_amt,
0095 wr_net_loss as net_loss
0096 from
0097 web_returns
0098 left outer join web_sales on (
0099 wr_item_sk = ws_item_sk and wr_order_number = ws_order_number)
0100 ) salesreturns,
0101 date_dim,
0102 web_site
0103 where
0104 date_sk = d_date_sk and d_date between cast('1998-08-04' as date)
0105 and (cast('1998-08-04' as date) + INTERVAL 14 days)
0106 and wsr_web_site_sk = web_site_sk
0107 group by
0108 web_site_id),
0109 results as (
0110 select
0111 channel,
0112 id,
0113 sum(sales) as sales,
0114 sum(returns) as returns,
0115 sum(profit) as profit
0116 from (
0117 select
0118 'store channel' as channel,
0119 'store' || s_store_id as id,
0120 sales,
0121 returns,
0122 (profit - profit_loss) as profit
0123 from
0124 ssr
0125 union all
0126 select
0127 'catalog channel' as channel,
0128 'catalog_page' || cp_catalog_page_id as id,
0129 sales,
0130 returns,
0131 (profit - profit_loss) as profit
0132 from
0133 csr
0134 union all
0135 select
0136 'web channel' as channel,
0137 'web_site' || web_site_id as id,
0138 sales,
0139 returns,
0140 (profit - profit_loss) as profit
0141 from
0142 wsr) x
0143 group by
0144 channel, id)
0145 select
0146 channel, id, sales, returns, profit
0147 from (
0148 select channel, id, sales, returns, profit
0149 from results
0150 union
0151 select channel, null as id, sum(sales), sum(returns), sum(profit)
0152 from results
0153 group by channel
0154 union
0155 select null as channel, null as id, sum(sales), sum(returns), sum(profit)
0156 from results) foo
0157 order by channel, id
0158 limit 100
