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 (SELECT
0037 ss_quantity quantity,
0038 ss_list_price list_price
0039 FROM store_sales, date_dim
0040 WHERE ss_sold_date_sk = d_date_sk AND d_year BETWEEN 1999 AND 1999 + 2
0041 UNION ALL
0042 SELECT
0043 cs_quantity quantity,
0044 cs_list_price list_price
0045 FROM catalog_sales, date_dim
0046 WHERE cs_sold_date_sk = d_date_sk AND d_year BETWEEN 1999 AND 1999 + 2
0047 UNION ALL
0048 SELECT
0049 ws_quantity quantity,
0050 ws_list_price list_price
0051 FROM web_sales, date_dim
0052 WHERE ws_sold_date_sk = d_date_sk AND d_year BETWEEN 1999 AND 1999 + 2) x)
0053 SELECT *
0054 FROM
0055 (SELECT
0056 'store' channel,
0057 i_brand_id,
0058 i_class_id,
0059 i_category_id,
0060 sum(ss_quantity * ss_list_price) sales,
0061 count(*) number_sales
0062 FROM store_sales, item, date_dim
0063 WHERE ss_item_sk IN (SELECT ss_item_sk
0064 FROM cross_items)
0065 AND ss_item_sk = i_item_sk
0066 AND ss_sold_date_sk = d_date_sk
0067 AND d_week_seq = (SELECT d_week_seq
0068 FROM date_dim
0069 WHERE d_year = 1999 + 1 AND d_moy = 12 AND d_dom = 11)
0070 GROUP BY i_brand_id, i_class_id, i_category_id
0071 HAVING sum(ss_quantity * ss_list_price) > (SELECT average_sales
0072 FROM avg_sales)) this_year,
0073 (SELECT
0074 'store' channel,
0075 i_brand_id,
0076 i_class_id,
0077 i_category_id,
0078 sum(ss_quantity * ss_list_price) sales,
0079 count(*) number_sales
0080 FROM store_sales, item, date_dim
0081 WHERE ss_item_sk IN (SELECT ss_item_sk
0082 FROM cross_items)
0083 AND ss_item_sk = i_item_sk
0084 AND ss_sold_date_sk = d_date_sk
0085 AND d_week_seq = (SELECT d_week_seq
0086 FROM date_dim
0087 WHERE d_year = 1999 AND d_moy = 12 AND d_dom = 11)
0088 GROUP BY i_brand_id, i_class_id, i_category_id
0089 HAVING sum(ss_quantity * ss_list_price) > (SELECT average_sales
0090 FROM avg_sales)) last_year
0091 WHERE this_year.i_brand_id = last_year.i_brand_id
0092 AND this_year.i_class_id = last_year.i_class_id
0093 AND this_year.i_category_id = last_year.i_category_id
0094 ORDER BY this_year.channel, this_year.i_brand_id, this_year.i_class_id, this_year.i_category_id
0095 LIMIT 100
