0001 SELECT
0002 channel,
0003 col_name,
0004 d_year,
0005 d_qoy,
0006 i_category,
0007 COUNT(*) sales_cnt,
0008 SUM(ext_sales_price) sales_amt
0009 FROM (
0010 SELECT
0011 'store' AS channel,
0012 ss_store_sk col_name,
0013 d_year,
0014 d_qoy,
0015 i_category,
0016 ss_ext_sales_price ext_sales_price
0017 FROM store_sales, item, date_dim
0018 WHERE ss_store_sk IS NULL
0019 AND ss_sold_date_sk = d_date_sk
0020 AND ss_item_sk = i_item_sk
0021 UNION ALL
0022 SELECT
0023 'web' AS channel,
0024 ws_ship_customer_sk col_name,
0025 d_year,
0026 d_qoy,
0027 i_category,
0028 ws_ext_sales_price ext_sales_price
0029 FROM web_sales, item, date_dim
0030 WHERE ws_ship_customer_sk IS NULL
0031 AND ws_sold_date_sk = d_date_sk
0032 AND ws_item_sk = i_item_sk
0033 UNION ALL
0034 SELECT
0035 'catalog' AS channel,
0036 cs_ship_addr_sk col_name,
0037 d_year,
0038 d_qoy,
0039 i_category,
0040 cs_ext_sales_price ext_sales_price
0041 FROM catalog_sales, item, date_dim
0042 WHERE cs_ship_addr_sk IS NULL
0043 AND cs_sold_date_sk = d_date_sk
0044 AND cs_item_sk = i_item_sk) foo
0045 GROUP BY channel, col_name, d_year, d_qoy, i_category
0046 ORDER BY channel, col_name, d_year, d_qoy, i_category
0047 LIMIT 100
