WITH year_total AS (SELECT c_customer_id customer_id, d_year dyear, sum(( ( ss_ext_list_price - ss_ext_wholesale_cost - ss_ext_discount_amt ) + ss_ext_sales_price ) / 2) year_total, 's' sale_type FROM tpcds.customer, tpcds.store_sales, tpcds.date_dim WHERE c_customer_sk = ss_customer_sk AND ss_sold_date_sk = d_date_sk GROUP BY c_customer_id, d_year UNION ALL SELECT c_customer_id customer_id, d_year dyear, sum(( ( ( cs_ext_list_price - cs_ext_wholesale_cost - cs_ext_discount_amt ) + cs_ext_sales_price ) / 2 )) year_total, 'c' sale_type FROM tpcds.customer, tpcds.catalog_sales, tpcds.date_dim WHERE c_customer_sk = cs_bill_customer_sk AND cs_sold_date_sk = d_date_sk GROUP BY c_customer_id, d_year UNION ALL SELECT c_customer_id customer_id, d_year dyear, sum(( ( ( ws_ext_list_price - ws_ext_wholesale_cost - ws_ext_discount_amt ) + ws_ext_sales_price ) / 2 )) year_total, 'w' sale_type FROM tpcds.customer, tpcds.web_sales, tpcds.date_dim WHERE c_customer_sk = ws_bill_customer_sk AND ws_sold_date_sk = d_date_sk GROUP BY c_customer_id, d_year) SELECT t_s_secyear.customer_id, customer.c_first_name, customer.c_last_name, customer.c_preferred_cust_flag FROM year_total t_s_firstyear, year_total t_s_secyear, year_total t_c_firstyear, year_total t_c_secyear, year_total t_w_firstyear, year_total t_w_secyear, customer WHERE t_s_secyear.customer_id = t_s_firstyear.customer_id AND t_s_firstyear.customer_id = t_c_secyear.customer_id AND t_s_firstyear.customer_id = t_c_firstyear.customer_id AND t_s_firstyear.customer_id = t_w_firstyear.customer_id AND t_s_firstyear.customer_id = t_w_secyear.customer_id AND t_s_secyear.customer_id = customer.c_customer_id AND t_s_firstyear.sale_type = 's' AND t_c_firstyear.sale_type = 'c' AND t_w_firstyear.sale_type = 'w' AND t_s_secyear.sale_type = 's' AND t_c_secyear.sale_type = 'c' AND t_w_secyear.sale_type = 'w' AND t_s_firstyear.dyear = 2001 AND t_s_secyear.dyear = 2001 + 1 AND t_c_firstyear.dyear = 2001 AND t_c_secyear.dyear = 2001 + 1 AND t_w_firstyear.dyear = 2001 AND t_w_secyear.dyear = 2001 + 1 AND t_s_firstyear.year_total > 0 AND t_c_firstyear.year_total > 0 AND t_w_firstyear.year_total > 0 AND CASE WHEN t_c_firstyear.year_total > 0 THEN t_c_secyear.year_total / t_c_firstyear.year_total ELSE NULL END > CASE WHEN t_s_firstyear.year_total > 0 THEN t_s_secyear.year_total / t_s_firstyear.year_total ELSE NULL END AND CASE WHEN t_c_firstyear.year_total > 0 THEN t_c_secyear.year_total / t_c_firstyear.year_total ELSE NULL END > CASE WHEN t_w_firstyear.year_total > 0 THEN t_w_secyear.year_total / t_w_firstyear.year_total ELSE NULL END ORDER BY t_s_secyear.customer_id LIMIT 100
WITH item_ss AS ( SELECT DISTINCT iss.i_brand_id, iss.i_class_id, iss.i_category_id FROM tpcds.store_sales, tpcds.item iss, tpcds.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 ), item_cs AS ( SELECT DISTINCT ics.i_brand_id, ics.i_class_id, ics.i_category_id FROM tpcds.catalog_sales, tpcds.item ics, tpcds.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 ), item_ws AS ( SELECT DISTINCT iws.i_brand_id, iws.i_class_id, iws.i_category_id FROM tpcds.web_sales, tpcds.item iws, tpcds.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 ), item_intersect AS ( SELECT item_ss.i_brand_id brand_id, item_ss.i_class_id class_id, item_ss.i_category_id category_id FROM item_ss JOIN item_ws ON item_ss.i_brand_id = item_ws.i_brand_id AND item_ss.i_class_id = item_ws.i_class_id AND item_ss.i_category_id = item_ws.i_category_id JOIN item_cs ON item_ss.i_brand_id = item_cs.i_brand_id AND item_ss.i_class_id = item_cs.i_class_id AND item_ss.i_category_id = item_cs.i_category_id ), cross_items AS ( SELECT i_item_sk ss_item_sk FROM tpcds.item, item_intersect 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 tpcds.store_sales, tpcds.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 tpcds.catalog_sales, tpcds.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 tpcds.web_sales, tpcds.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 tpcds.store_sales, tpcds.item, tpcds.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 tpcds.catalog_sales, tpcds.item, tpcds.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 tpcds.web_sales, tpcds.item, tpcds.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 channel, i_brand_id, i_class_id, i_category_id ORDER BY channel, i_brand_id, i_class_id, i_category_id LIMIT 100
WITH frequent_ss_items AS (SELECT substr(i_item_desc, 1, 30) itemdesc, i_item_sk item_sk, d_date solddate, count(*) cnt FROM tpcds.store_sales, tpcds.date_dim, tpcds.item WHERE ss_sold_date_sk = d_date_sk AND ss_item_sk = i_item_sk AND d_year IN ( 1998, 1998 + 1, 1998 + 2, 1998 + 3 ) GROUP BY 1, i_item_sk, d_date HAVING count(*) > 4), max_store_sales AS (SELECT Max(csales) tpcds_cmax FROM (SELECT c_customer_sk, sum(ss_quantity * ss_sales_price) csales FROM tpcds.store_sales, tpcds.customer, tpcds.date_dim WHERE ss_customer_sk = c_customer_sk AND ss_sold_date_sk = d_date_sk AND d_year IN ( 1998, 1998 + 1, 1998 + 2, 1998 + 3 ) GROUP BY c_customer_sk)), best_ss_customer AS (SELECT c_customer_sk, sum(ss_quantity * ss_sales_price) ssales FROM tpcds.store_sales, tpcds.customer WHERE ss_customer_sk = c_customer_sk GROUP BY c_customer_sk HAVING sum(ss_quantity * ss_sales_price) > ( 95 / 100.0 ) * (SELECT * FROM max_store_sales)) SELECT sum(sales) FROM (SELECT cs_quantity * cs_list_price sales FROM tpcds.catalog_sales, tpcds.date_dim WHERE d_year = 1998 AND d_moy = 6 AND cs_sold_date_sk = d_date_sk AND cs_item_sk IN (SELECT item_sk FROM frequent_ss_items) AND cs_bill_customer_sk IN (SELECT c_customer_sk FROM best_ss_customer) UNION ALL SELECT ws_quantity * ws_list_price sales FROM tpcds.web_sales, tpcds.date_dim WHERE d_year = 1998 AND d_moy = 6 AND ws_sold_date_sk = d_date_sk AND ws_item_sk IN (SELECT item_sk FROM frequent_ss_items) AND ws_bill_customer_sk IN (SELECT c_customer_sk FROM best_ss_customer)) LIMIT 100
select * from (select i_category ,i_class ,i_brand ,i_product_name ,d_year ,d_qoy ,d_moy ,s_store_id ,sumsales ,rank() over (partition by i_category order by sumsales desc) rk from (select i_category ,i_class ,i_brand ,i_product_name ,d_year ,d_qoy ,d_moy ,s_store_id ,sum(coalesce(ss_sales_price*ss_quantity,0)) sumsales from store_sales ,date_dim ,store ,item where ss_sold_date_sk=d_date_sk and ss_item_sk=i_item_sk and ss_store_sk = s_store_sk and d_month_seq between 1212 and 1212+11 group by rollup(i_category, i_class, i_brand, i_product_name, d_year, d_qoy, d_moy,s_store_id))dw1) dw2 where rk <= 100 order by i_category ,i_class ,i_brand ,i_product_name ,d_year ,d_qoy ,d_moy ,s_store_id ,sumsales ,rk LIMIT 100
SELECT c_last_name, c_first_name, substr(s_city, 1, 30), ss_ticket_number, amt, profit FROM (SELECT ss_ticket_number, ss_customer_sk, store.s_city, sum(ss_coupon_amt) amt, sum(ss_net_profit) profit FROM tpcds.store_sales, tpcds.date_dim, tpcds.store, tpcds.household_demographics WHERE store_sales.ss_sold_date_sk = date_dim.d_date_sk AND store_sales.ss_store_sk = store.s_store_sk AND store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk AND ( household_demographics.hd_dep_count = 8 OR household_demographics.hd_vehicle_count > 4 ) AND date_dim.d_dow = 1 AND date_dim.d_year IN ( 2000, 2000 + 1, 2000 + 2 ) AND store.s_number_employees BETWEEN 200 AND 295 GROUP BY ss_ticket_number, ss_customer_sk, ss_addr_sk, store.s_city) ms, customer WHERE ss_customer_sk = c_customer_sk ORDER BY c_last_name, c_first_name, substr(s_city, 1, 30), profit LIMIT 100