0001 WITH ss AS (
0002 SELECT
0003 i_manufact_id,
0004 sum(ss_ext_sales_price) total_sales
0005 FROM
0006 store_sales, date_dim, customer_address, item
0007 WHERE
0008 i_manufact_id IN (SELECT i_manufact_id
0009 FROM item
0010 WHERE i_category IN ('Electronics'))
0011 AND ss_item_sk = i_item_sk
0012 AND ss_sold_date_sk = d_date_sk
0013 AND d_year = 1998
0014 AND d_moy = 5
0015 AND ss_addr_sk = ca_address_sk
0016 AND ca_gmt_offset = -5
0017 GROUP BY i_manufact_id), cs AS
0018 (SELECT
0019 i_manufact_id,
0020 sum(cs_ext_sales_price) total_sales
0021 FROM catalog_sales, date_dim, customer_address, item
0022 WHERE
0023 i_manufact_id IN (
0024 SELECT i_manufact_id
0025 FROM item
0026 WHERE
0027 i_category IN ('Electronics'))
0028 AND cs_item_sk = i_item_sk
0029 AND cs_sold_date_sk = d_date_sk
0030 AND d_year = 1998
0031 AND d_moy = 5
0032 AND cs_bill_addr_sk = ca_address_sk
0033 AND ca_gmt_offset = -5
0034 GROUP BY i_manufact_id),
0035 ws AS (
0036 SELECT
0037 i_manufact_id,
0038 sum(ws_ext_sales_price) total_sales
0039 FROM
0040 web_sales, date_dim, customer_address, item
0041 WHERE
0042 i_manufact_id IN (SELECT i_manufact_id
0043 FROM item
0044 WHERE i_category IN ('Electronics'))
0045 AND ws_item_sk = i_item_sk
0046 AND ws_sold_date_sk = d_date_sk
0047 AND d_year = 1998
0048 AND d_moy = 5
0049 AND ws_bill_addr_sk = ca_address_sk
0050 AND ca_gmt_offset = -5
0051 GROUP BY i_manufact_id)
0052 SELECT
0053 i_manufact_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_manufact_id
0064 ORDER BY total_sales
0065 LIMIT 100
