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