0001 WITH cross_items AS
0002 (SELECT i_item_sk ss_item_sk
0003 FROM item,
0004 (SELECT
0005 iss.i_brand_id brand_id,
0006 iss.i_class_id class_id,
0007 iss.i_category_id category_id
0008 FROM store_sales, item iss, date_dim d1
0009 WHERE ss_item_sk = iss.i_item_sk
0010 AND ss_sold_date_sk = d1.d_date_sk
0011 AND d1.d_year BETWEEN 1999 AND 1999 + 2
0012 INTERSECT
0013 SELECT
0014 ics.i_brand_id,
0015 ics.i_class_id,
0016 ics.i_category_id
0017 FROM catalog_sales, item ics, date_dim d2
0018 WHERE cs_item_sk = ics.i_item_sk
0019 AND cs_sold_date_sk = d2.d_date_sk
0020 AND d2.d_year BETWEEN 1999 AND 1999 + 2
0021 INTERSECT
0022 SELECT
0023 iws.i_brand_id,
0024 iws.i_class_id,
0025 iws.i_category_id
0026 FROM web_sales, item iws, date_dim d3
0027 WHERE ws_item_sk = iws.i_item_sk
0028 AND ws_sold_date_sk = d3.d_date_sk
0029 AND d3.d_year BETWEEN 1999 AND 1999 + 2) x
0030 WHERE i_brand_id = brand_id
0031 AND i_class_id = class_id
0032 AND i_category_id = category_id
0033 ),
0034 avg_sales AS
0035 (SELECT avg(quantity * list_price) average_sales
0036 FROM (
0037 SELECT
0038 ss_quantity quantity,
0039 ss_list_price list_price
0040 FROM store_sales, date_dim
0041 WHERE ss_sold_date_sk = d_date_sk
0042 AND d_year BETWEEN 1999 AND 2001
0043 UNION ALL
0044 SELECT
0045 cs_quantity quantity,
0046 cs_list_price list_price
0047 FROM catalog_sales, date_dim
0048 WHERE cs_sold_date_sk = d_date_sk
0049 AND d_year BETWEEN 1999 AND 1999 + 2
0050 UNION ALL
0051 SELECT
0052 ws_quantity quantity,
0053 ws_list_price list_price
0054 FROM web_sales, date_dim
0055 WHERE ws_sold_date_sk = d_date_sk
0056 AND d_year BETWEEN 1999 AND 1999 + 2) x)
0057 SELECT
0058 channel,
0059 i_brand_id,
0060 i_class_id,
0061 i_category_id,
0062 sum(sales),
0063 sum(number_sales)
0064 FROM (
0065 SELECT
0066 'store' channel,
0067 i_brand_id,
0068 i_class_id,
0069 i_category_id,
0070 sum(ss_quantity * ss_list_price) sales,
0071 count(*) number_sales
0072 FROM store_sales, item, date_dim
0073 WHERE ss_item_sk IN (SELECT ss_item_sk
0074 FROM cross_items)
0075 AND ss_item_sk = i_item_sk
0076 AND ss_sold_date_sk = d_date_sk
0077 AND d_year = 1999 + 2
0078 AND d_moy = 11
0079 GROUP BY i_brand_id, i_class_id, i_category_id
0080 HAVING sum(ss_quantity * ss_list_price) > (SELECT average_sales
0081 FROM avg_sales)
0082 UNION ALL
0083 SELECT
0084 'catalog' channel,
0085 i_brand_id,
0086 i_class_id,
0087 i_category_id,
0088 sum(cs_quantity * cs_list_price) sales,
0089 count(*) number_sales
0090 FROM catalog_sales, item, date_dim
0091 WHERE cs_item_sk IN (SELECT ss_item_sk
0092 FROM cross_items)
0093 AND cs_item_sk = i_item_sk
0094 AND cs_sold_date_sk = d_date_sk
0095 AND d_year = 1999 + 2
0096 AND d_moy = 11
0097 GROUP BY i_brand_id, i_class_id, i_category_id
0098 HAVING sum(cs_quantity * cs_list_price) > (SELECT average_sales FROM avg_sales)
0099 UNION ALL
0100 SELECT
0101 'web' channel,
0102 i_brand_id,
0103 i_class_id,
0104 i_category_id,
0105 sum(ws_quantity * ws_list_price) sales,
0106 count(*) number_sales
0107 FROM web_sales, item, date_dim
0108 WHERE ws_item_sk IN (SELECT ss_item_sk
0109 FROM cross_items)
0110 AND ws_item_sk = i_item_sk
0111 AND ws_sold_date_sk = d_date_sk
0112 AND d_year = 1999 + 2
0113 AND d_moy = 11
0114 GROUP BY i_brand_id, i_class_id, i_category_id
0115 HAVING sum(ws_quantity * ws_list_price) > (SELECT average_sales
0116 FROM avg_sales)
0117 ) y
0118 GROUP BY ROLLUP (channel, i_brand_id, i_class_id, i_category_id)
0119 ORDER BY channel, i_brand_id, i_class_id, i_category_id
0120 LIMIT 100
