| ==== |
| ---- QUERY: TPCDS-Q14-1 |
| with cross_items as |
| (select i_item_sk ss_item_sk |
| from item, |
| (select iss.i_brand_id brand_id |
| ,iss.i_class_id class_id |
| ,iss.i_category_id category_id |
| from store_sales |
| ,item iss |
| ,date_dim d1 |
| where ss_item_sk = iss.i_item_sk |
| and ss_sold_date_sk = d1.d_date_sk |
| and d1.d_year between 1999 AND 1999 + 2 |
| intersect |
| select ics.i_brand_id |
| ,ics.i_class_id |
| ,ics.i_category_id |
| from catalog_sales |
| ,item ics |
| ,date_dim d2 |
| where cs_item_sk = ics.i_item_sk |
| and cs_sold_date_sk = d2.d_date_sk |
| and d2.d_year between 1999 AND 1999 + 2 |
| intersect |
| select iws.i_brand_id |
| ,iws.i_class_id |
| ,iws.i_category_id |
| from web_sales |
| ,item iws |
| ,date_dim d3 |
| where ws_item_sk = iws.i_item_sk |
| and ws_sold_date_sk = d3.d_date_sk |
| and d3.d_year between 1999 AND 1999 + 2) t1 |
| where i_brand_id = brand_id |
| and i_class_id = class_id |
| and i_category_id = category_id |
| ), |
| avg_sales as |
| (select avg(quantity*list_price) average_sales |
| from (select ss_quantity quantity |
| ,ss_list_price list_price |
| from store_sales |
| ,date_dim |
| where ss_sold_date_sk = d_date_sk |
| and d_year between 1999 and 1999 + 2 |
| union all |
| select cs_quantity quantity |
| ,cs_list_price list_price |
| from catalog_sales |
| ,date_dim |
| where cs_sold_date_sk = d_date_sk |
| and d_year between 1999 and 1999 + 2 |
| union all |
| select ws_quantity quantity |
| ,ws_list_price list_price |
| from web_sales |
| ,date_dim |
| where ws_sold_date_sk = d_date_sk |
| and d_year between 1999 and 1999 + 2) x) |
| select channel, i_brand_id,i_class_id,i_category_id,sum(sales), sum(number_sales) |
| from( |
| select 'store' channel, i_brand_id,i_class_id |
| ,i_category_id,sum(ss_quantity*ss_list_price) sales |
| , count(*) number_sales |
| from store_sales |
| ,item |
| ,date_dim |
| where ss_item_sk in (select ss_item_sk from cross_items) |
| and ss_item_sk = i_item_sk |
| and ss_sold_date_sk = d_date_sk |
| and d_year = 1999+2 |
| and d_moy = 11 |
| group by i_brand_id,i_class_id,i_category_id |
| having sum(ss_quantity*ss_list_price) > (select average_sales from avg_sales) |
| union all |
| select 'catalog' channel, i_brand_id,i_class_id,i_category_id, sum(cs_quantity*cs_list_price) sales, count(*) number_sales |
| from catalog_sales |
| ,item |
| ,date_dim |
| where cs_item_sk in (select ss_item_sk from cross_items) |
| and cs_item_sk = i_item_sk |
| and cs_sold_date_sk = d_date_sk |
| and d_year = 1999+2 |
| and d_moy = 11 |
| group by i_brand_id,i_class_id,i_category_id |
| having sum(cs_quantity*cs_list_price) > (select average_sales from avg_sales) |
| union all |
| select 'web' channel, i_brand_id,i_class_id,i_category_id, sum(ws_quantity*ws_list_price) sales , count(*) number_sales |
| from web_sales |
| ,item |
| ,date_dim |
| where ws_item_sk in (select ss_item_sk from cross_items) |
| and ws_item_sk = i_item_sk |
| and ws_sold_date_sk = d_date_sk |
| and d_year = 1999+2 |
| and d_moy = 11 |
| group by i_brand_id,i_class_id,i_category_id |
| having sum(ws_quantity*ws_list_price) > (select average_sales from avg_sales) |
| ) y |
| group by rollup (channel, i_brand_id,i_class_id,i_category_id) |
| order by channel,i_brand_id,i_class_id,i_category_id |
| LIMIT 100 |
| ---- RESULTS |
| 'catalog',1001001,1,1,115019.61,20 |
| 'catalog',1001001,1,2,146344.47,27 |
| 'catalog',1001001,1,3,22597.19,3 |
| 'catalog',1001001,1,4,107555.43,23 |
| 'catalog',1001001,1,5,122521.31,25 |
| 'catalog',1001001,1,6,16883.97,3 |
| 'catalog',1001001,1,7,46329.78,9 |
| 'catalog',1001001,1,8,77861.85,13 |
| 'catalog',1001001,1,9,99985.35,21 |
| 'catalog',1001001,1,10,100105.28,23 |
| 'catalog',1001001,1,NULL,855204.24,167 |
| 'catalog',1001001,2,2,43967.97,7 |
| 'catalog',1001001,2,3,68565.38,14 |
| 'catalog',1001001,2,5,12633.87,3 |
| 'catalog',1001001,2,NULL,125167.22,24 |
| 'catalog',1001001,3,1,11100.79,5 |
| 'catalog',1001001,3,2,60551.64,14 |
| 'catalog',1001001,3,4,28455.23,4 |
| 'catalog',1001001,3,6,36821.61,7 |
| 'catalog',1001001,3,7,17250.82,6 |
| 'catalog',1001001,3,8,14426.92,4 |
| 'catalog',1001001,3,9,30078.07,3 |
| 'catalog',1001001,3,NULL,198685.08,43 |
| 'catalog',1001001,4,2,45473.85,13 |
| 'catalog',1001001,4,3,16558.92,8 |
| 'catalog',1001001,4,4,47553.20,10 |
| 'catalog',1001001,4,NULL,109585.97,31 |
| 'catalog',1001001,5,9,30112.11,12 |
| 'catalog',1001001,5,10,29678.50,5 |
| 'catalog',1001001,5,NULL,59790.61,17 |
| 'catalog',1001001,6,9,10261.82,3 |
| 'catalog',1001001,6,NULL,10261.82,3 |
| 'catalog',1001001,7,7,18244.94,3 |
| 'catalog',1001001,7,NULL,18244.94,3 |
| 'catalog',1001001,8,7,28872.49,7 |
| 'catalog',1001001,8,10,26895.97,6 |
| 'catalog',1001001,8,NULL,55768.46,13 |
| 'catalog',1001001,9,6,30944.19,5 |
| 'catalog',1001001,9,NULL,30944.19,5 |
| 'catalog',1001001,11,9,82810.87,12 |
| 'catalog',1001001,11,NULL,82810.87,12 |
| 'catalog',1001001,12,10,38427.52,9 |
| 'catalog',1001001,12,NULL,38427.52,9 |
| 'catalog',1001001,15,9,53508.79,7 |
| 'catalog',1001001,15,10,59329.31,13 |
| 'catalog',1001001,15,NULL,112838.10,20 |
| 'catalog',1001001,NULL,NULL,1697729.02,347 |
| 'catalog',1001002,1,1,2673969.89,530 |
| 'catalog',1001002,1,NULL,2673969.89,530 |
| 'catalog',1001002,2,1,140831.91,29 |
| 'catalog',1001002,2,NULL,140831.91,29 |
| 'catalog',1001002,3,1,320175.87,67 |
| 'catalog',1001002,3,NULL,320175.87,67 |
| 'catalog',1001002,4,1,133287.96,21 |
| 'catalog',1001002,4,NULL,133287.96,21 |
| 'catalog',1001002,5,1,16606.90,9 |
| 'catalog',1001002,5,NULL,16606.90,9 |
| 'catalog',1001002,6,1,15133.01,4 |
| 'catalog',1001002,6,NULL,15133.01,4 |
| 'catalog',1001002,7,1,24471.26,10 |
| 'catalog',1001002,7,NULL,24471.26,10 |
| 'catalog',1001002,8,1,63773.05,12 |
| 'catalog',1001002,8,NULL,63773.05,12 |
| 'catalog',1001002,9,1,9167.19,3 |
| 'catalog',1001002,9,NULL,9167.19,3 |
| 'catalog',1001002,12,1,29108.42,7 |
| 'catalog',1001002,12,NULL,29108.42,7 |
| 'catalog',1001002,15,1,31143.45,6 |
| 'catalog',1001002,15,NULL,31143.45,6 |
| 'catalog',1001002,16,1,70162.42,8 |
| 'catalog',1001002,16,NULL,70162.42,8 |
| 'catalog',1001002,NULL,NULL,3527831.33,706 |
| 'catalog',1002001,1,1,76392.13,14 |
| 'catalog',1002001,1,2,118394.33,21 |
| 'catalog',1002001,1,4,29395.79,5 |
| 'catalog',1002001,1,5,35541.97,4 |
| 'catalog',1002001,1,6,26104.36,3 |
| 'catalog',1002001,1,9,18793.97,4 |
| 'catalog',1002001,1,10,44071.42,4 |
| 'catalog',1002001,1,NULL,348693.97,55 |
| 'catalog',1002001,2,1,239511.02,51 |
| 'catalog',1002001,2,2,147993.14,26 |
| 'catalog',1002001,2,3,100086.93,17 |
| 'catalog',1002001,2,4,53524.42,13 |
| 'catalog',1002001,2,5,48494.06,10 |
| 'catalog',1002001,2,6,142857.04,20 |
| 'catalog',1002001,2,7,116557.98,16 |
| 'catalog',1002001,2,8,92743.93,24 |
| 'catalog',1002001,2,9,203943.99,38 |
| 'catalog',1002001,2,10,88249.19,10 |
| 'catalog',1002001,2,NULL,1233961.70,225 |
| 'catalog',1002001,3,2,25171.13,6 |
| 'catalog',1002001,3,7,27766.70,3 |
| 'catalog',1002001,3,8,38116.49,8 |
| 'catalog',1002001,3,NULL,91054.32,17 |
| 'catalog',1002001,4,1,66896.68,15 |
| 'catalog',1002001,4,3,43672.63,7 |
| 'catalog',1002001,4,4,41393.52,6 |
| 'catalog',1002001,4,5,30464.86,4 |
| 'catalog',1002001,4,NULL,182427.69,32 |
| ---- TYPES |
| STRING, INT, INT, INT, DECIMAL, BIGINT |
| ==== |
