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 1999 AND 1999 + 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 1999 AND 1999 + 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 1999 AND 1999 + 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 1999 and 2001
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 results AS (
0073 select
0074 channel,
0075 i_brand_id,
0076 i_class_id,
0077 i_category_id,
0078 sum(sales) sum_sales,
0079 sum(number_sales) number_sales
0080 from (
0081 select
0082 'store' channel,
0083 i_brand_id,i_class_id,
0084 i_category_id,
0085 sum(ss_quantity*ss_list_price) sales,
0086 count(*) number_sales
0087 from
0088 store_sales, item, date_dim
0089 where
0090 ss_item_sk in (select ss_item_sk from cross_items)
0091 and ss_item_sk = i_item_sk
0092 and ss_sold_date_sk = d_date_sk
0093 and d_year = 1998 + 2
0094 and d_moy = 11
0095 group by
0096 i_brand_id,
0097 i_class_id,
0098 i_category_id
0099 having
0100 sum(ss_quantity * ss_list_price) > (select average_sales from avg_sales)
0101 union all
0102 select
0103 'catalog' channel,
0104 i_brand_id,
0105 i_class_id,
0106 i_category_id,
0107 sum(cs_quantity*cs_list_price) sales,
0108 count(*) number_sales
0109 from
0110 catalog_sales, item, date_dim
0111 where
0112 cs_item_sk in (select ss_item_sk from cross_items)
0113 and cs_item_sk = i_item_sk
0114 and cs_sold_date_sk = d_date_sk
0115 and d_year = 1998+2
0116 and d_moy = 11
0117 group by
0118 i_brand_id,i_class_id,i_category_id
0119 having
0120 sum(cs_quantity*cs_list_price) > (select average_sales from avg_sales)
0121 union all
0122 select
0123 'web' channel,
0124 i_brand_id,
0125 i_class_id,
0126 i_category_id,
0127 sum(ws_quantity*ws_list_price) sales,
0128 count(*) number_sales
0129 from
0130 web_sales, item, date_dim
0131 where
0132 ws_item_sk in (select ss_item_sk from cross_items)
0133 and ws_item_sk = i_item_sk
0134 and ws_sold_date_sk = d_date_sk
0135 and d_year = 1998 + 2
0136 and d_moy = 11
0137 group by
0138 i_brand_id,
0139 i_class_id,
0140 i_category_id
0141 having
0142 sum(ws_quantity*ws_list_price) > (select average_sales from avg_sales)) y
0143 group by
0144 channel,
0145 i_brand_id,
0146 i_class_id,
0147 i_category_id)
0148 select
0149 channel,
0150 i_brand_id,
0151 i_class_id,
0152 i_category_id,
0153 sum_sales,
0154 number_sales
0155 from (
0156 select
0157 channel,
0158 i_brand_id,
0159 i_class_id,
0160 i_category_id,
0161 sum_sales,
0162 number_sales
0163 from
0164 results
0165 union
0166 select
0167 channel,
0168 i_brand_id,
0169 i_class_id,
0170 null as i_category_id,
0171 sum(sum_sales),
0172 sum(number_sales)
0173 from results
0174 group by
0175 channel,
0176 i_brand_id,
0177 i_class_id
0178 union
0179 select
0180 channel,
0181 i_brand_id,
0182 null as i_class_id,
0183 null as i_category_id,
0184 sum(sum_sales),
0185 sum(number_sales)
0186 from results
0187 group by
0188 channel,
0189 i_brand_id
0190 union
0191 select
0192 channel,
0193 null as i_brand_id,
0194 null as i_class_id,
0195 null as i_category_id,
0196 sum(sum_sales),
0197 sum(number_sales)
0198 from results
0199 group by
0200 channel
0201 union
0202 select
0203 null as channel,
0204 null as i_brand_id,
0205 null as i_class_id,
0206 null as i_category_id,
0207 sum(sum_sales),
0208 sum(number_sales)
0209 from results) z
0210 order by
0211 channel,
0212 i_brand_id,
0213 i_class_id,
0214 i_category_id
0215 limit 100
