0001 WITH ss AS (
0002 SELECT
0003 i_item_id,
0004 sum(ss_ext_sales_price) total_sales
0005 FROM store_sales, date_dim, customer_address, item
0006 WHERE
0007 i_item_id IN (SELECT i_item_id
0008 FROM item
0009 WHERE i_category IN ('Music'))
0010 AND ss_item_sk = i_item_sk
0011 AND ss_sold_date_sk = d_date_sk
0012 AND d_year = 1998
0013 AND d_moy = 9
0014 AND ss_addr_sk = ca_address_sk
0015 AND ca_gmt_offset = -5
0016 GROUP BY i_item_id),
0017 cs AS (
0018 SELECT
0019 i_item_id,
0020 sum(cs_ext_sales_price) total_sales
0021 FROM catalog_sales, date_dim, customer_address, item
0022 WHERE
0023 i_item_id IN (SELECT i_item_id
0024 FROM item
0025 WHERE i_category IN ('Music'))
0026 AND cs_item_sk = i_item_sk
0027 AND cs_sold_date_sk = d_date_sk
0028 AND d_year = 1998
0029 AND d_moy = 9
0030 AND cs_bill_addr_sk = ca_address_sk
0031 AND ca_gmt_offset = -5
0032 GROUP BY i_item_id),
0033 ws AS (
0034 SELECT
0035 i_item_id,
0036 sum(ws_ext_sales_price) total_sales
0037 FROM web_sales, date_dim, customer_address, item
0038 WHERE
0039 i_item_id IN (SELECT i_item_id
0040 FROM item
0041 WHERE i_category IN ('Music'))
0042 AND ws_item_sk = i_item_sk
0043 AND ws_sold_date_sk = d_date_sk
0044 AND d_year = 1998
0045 AND d_moy = 9
0046 AND ws_bill_addr_sk = ca_address_sk
0047 AND ca_gmt_offset = -5
0048 GROUP BY i_item_id)
0049 SELECT
0050 i_item_id,
0051 sum(total_sales) total_sales
0052 FROM (SELECT *
0053 FROM ss
0054 UNION ALL
0055 SELECT *
0056 FROM cs
0057 UNION ALL
0058 SELECT *
0059 FROM ws) tmp1
0060 GROUP BY i_item_id
0061 ORDER BY i_item_id, total_sales
0062 LIMIT 100
