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