0001
0002 with cross_items as (
0003 select
0004 i_item_sk ss_item_sk
0005 from item, (
0006 select
0007 iss.i_brand_id brand_id,
0008 iss.i_class_id class_id,
0009 iss.i_category_id category_id
0010 from
0011 store_sales, item iss, date_dim d1
0012 where
0013 ss_item_sk = iss.i_item_sk
0014 and ss_sold_date_sk = d1.d_date_sk
0015 and d1.d_year between 1998 AND 1998 + 2
0016 intersect
0017 select
0018 ics.i_brand_id,
0019 ics.i_class_id,
0020 ics.i_category_id
0021 from
0022 catalog_sales, item ics, date_dim d2
0023 where
0024 cs_item_sk = ics.i_item_sk
0025 and cs_sold_date_sk = d2.d_date_sk
0026 and d2.d_year between 1998 AND 1998 + 2
0027 intersect
0028 select
0029 iws.i_brand_id,
0030 iws.i_class_id,
0031 iws.i_category_id
0032 from
0033 web_sales, item iws, date_dim d3
0034 where
0035 ws_item_sk = iws.i_item_sk
0036 and ws_sold_date_sk = d3.d_date_sk
0037 and d3.d_year between 1998 AND 1998 + 2) x
0038 where
0039 i_brand_id = brand_id
0040 and i_class_id = class_id
0041 and i_category_id = category_id),
0042 avg_sales as (
0043 select
0044 avg(quantity*list_price) average_sales
0045 from (
0046 select
0047 ss_quantity quantity,
0048 ss_list_price list_price
0049 from
0050 store_sales, date_dim
0051 where
0052 ss_sold_date_sk = d_date_sk
0053 and d_year between 1998 and 1998 + 2
0054 union all
0055 select
0056 cs_quantity quantity,
0057 cs_list_price list_price
0058 from
0059 catalog_sales, date_dim
0060 where
0061 cs_sold_date_sk = d_date_sk
0062 and d_year between 1998 and 1998 + 2
0063 union all
0064 select
0065 ws_quantity quantity,
0066 ws_list_price list_price
0067 from
0068 web_sales, date_dim
0069 where
0070 ws_sold_date_sk = d_date_sk
0071 and d_year between 1998 and 1998 + 2) x)
0072 select
0073 *
0074 from (
0075 select
0076 'store' channel,
0077 i_brand_id,
0078 i_class_id,
0079 i_category_id,
0080 sum(ss_quantity * ss_list_price) sales,
0081 count(*) number_sales
0082 from
0083 store_sales, item, date_dim
0084 where
0085 ss_item_sk in (select ss_item_sk from cross_items)
0086 and ss_item_sk = i_item_sk
0087 and ss_sold_date_sk = d_date_sk
0088 and d_week_seq = (
0089 select d_week_seq
0090 from date_dim
0091 where d_year = 1998 + 1
0092 and d_moy = 12
0093 and d_dom = 16)
0094 group by
0095 i_brand_id,
0096 i_class_id,
0097 i_category_id
0098 having
0099 sum(ss_quantity*ss_list_price) > (select average_sales from avg_sales)) this_year,
0100 (
0101 select
0102 'store' channel,
0103 i_brand_id,
0104 i_class_id,
0105 i_category_id,
0106 sum(ss_quantity * ss_list_price) sales,
0107 count(*) number_sales
0108 from
0109 store_sales, item, date_dim
0110 where
0111 ss_item_sk in (select ss_item_sk from cross_items)
0112 and ss_item_sk = i_item_sk
0113 and ss_sold_date_sk = d_date_sk
0114 and d_week_seq = (
0115 select d_week_seq
0116 from date_dim
0117 where d_year = 1998
0118 and d_moy = 12
0119 and d_dom = 16)
0120 group by
0121 i_brand_id,
0122 i_class_id,
0123 i_category_id
0124 having
0125 sum(ss_quantity * ss_list_price) > (select average_sales from avg_sales)) last_year
0126 where
0127 this_year.i_brand_id = last_year.i_brand_id
0128 and this_year.i_class_id = last_year.i_class_id
0129 and this_year.i_category_id = last_year.i_category_id
0130 order by
0131 this_year.channel,
0132 this_year.i_brand_id,
0133 this_year.i_class_id,
0134 this_year.i_category_id
0135 limit 100
