WITH customer_total_return AS (SELECT sr_customer_sk AS ctr_customer_sk, sr_store_sk AS ctr_store_sk, sum(sr_return_amt) AS ctr_total_return FROM tpcds.store_returns, tpcds.date_dim WHERE sr_returned_date_sk = d_date_sk AND d_year = 2001 GROUP BY sr_customer_sk, sr_store_sk), high_return AS ( SELECT ctr_store_sk, avg(ctr_total_return) * 1.2 AS return_limit FROM customer_total_return ctr2 GROUP BY ctr_store_sk ) SELECT c_customer_id FROM customer_total_return ctr1, store, customer, high_return WHERE ctr1.ctr_total_return > high_return.return_limit AND s_store_sk = ctr1.ctr_store_sk AND s_state = 'TN' AND ctr1.ctr_customer_sk = c_customer_sk AND ctr1.ctr_store_sk = high_return.ctr_store_sk ORDER BY c_customer_id LIMIT 100
WITH wscs AS (SELECT sold_date_sk, sales_price FROM (SELECT ws_sold_date_sk sold_date_sk, ws_ext_sales_price sales_price FROM web_sales) UNION ALL (SELECT cs_sold_date_sk sold_date_sk, cs_ext_sales_price sales_price FROM catalog_sales)), wswscs AS (SELECT d_week_seq, sum(CASE WHEN ( d_day_name = 'Sunday' ) THEN sales_price ELSE NULL END) sun_sales, sum(CASE WHEN ( d_day_name = 'Monday' ) THEN sales_price ELSE NULL END) mon_sales, sum(CASE WHEN ( d_day_name = 'Tuesday' ) THEN sales_price ELSE NULL END) tue_sales, sum(CASE WHEN ( d_day_name = 'Wednesday' ) THEN sales_price ELSE NULL END) wed_sales, sum(CASE WHEN ( d_day_name = 'Thursday' ) THEN sales_price ELSE NULL END) thu_sales, sum(CASE WHEN ( d_day_name = 'Friday' ) THEN sales_price ELSE NULL END) fri_sales, sum(CASE WHEN ( d_day_name = 'Saturday' ) THEN sales_price ELSE NULL END) sat_sales FROM wscs, date_dim WHERE d_date_sk = sold_date_sk GROUP BY d_week_seq) SELECT d_week_seq1, round(sun_sales1 / sun_sales2, 2), round(mon_sales1 / mon_sales2, 2), round(tue_sales1 / tue_sales2, 2), round(wed_sales1 / wed_sales2, 2), round(thu_sales1 / thu_sales2, 2), round(fri_sales1 / fri_sales2, 2), round(sat_sales1 / sat_sales2, 2) FROM (SELECT wswscs.d_week_seq d_week_seq1, sun_sales sun_sales1, mon_sales mon_sales1, tue_sales tue_sales1, wed_sales wed_sales1, thu_sales thu_sales1, fri_sales fri_sales1, sat_sales sat_sales1 FROM wswscs, date_dim WHERE date_dim.d_week_seq = wswscs.d_week_seq AND d_year = 1998) y, (SELECT wswscs.d_week_seq d_week_seq2, sun_sales sun_sales2, mon_sales mon_sales2, tue_sales tue_sales2, wed_sales wed_sales2, thu_sales thu_sales2, fri_sales fri_sales2, sat_sales sat_sales2 FROM wswscs, date_dim WHERE date_dim.d_week_seq = wswscs.d_week_seq AND d_year = 1998 + 1) z WHERE d_week_seq1 = d_week_seq2 - 53 ORDER BY d_week_seq1
SELECT dt.d_year, item.i_brand_id brand_id, item.i_brand brand, sum(ss_ext_discount_amt) sum_agg FROM tpcds.date_dim dt, tpcds.store_sales, tpcds.item WHERE dt.d_date_sk = store_sales.ss_sold_date_sk AND store_sales.ss_item_sk = item.i_item_sk AND item.i_manufact_id = 427 AND dt.d_moy = 11 GROUP BY dt.d_year, item.i_brand, item.i_brand_id ORDER BY dt.d_year, sum_agg DESC, brand_id LIMIT 100
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 ssr AS ( SELECT s_store_id, sum(sales_price) AS sales, sum(profit) AS profit, sum(return_amt) AS returns1, sum(net_loss) AS profit_loss FROM ( SELECT ss_store_sk AS store_sk, ss_sold_date_sk AS date_sk, ss_ext_sales_price AS sales_price, ss_net_profit AS profit, 0 AS return_amt, 0 AS net_loss FROM tpcds.store_sales UNION ALL SELECT sr_store_sk AS store_sk, sr_returned_date_sk AS date_sk, 0 AS sales_price, 0 AS profit, sr_return_amt AS return_amt, sr_net_loss AS net_loss FROM tpcds.store_returns ) salesreturns, date_dim, store WHERE date_sk = d_date_sk AND Cast(d_date AS DATE) BETWEEN Cast('2002-08-22' AS DATE) AND ( Cast('2002-09-05' AS DATE)) AND store_sk = s_store_sk GROUP BY s_store_id) , csr AS ( SELECT cp_catalog_page_id, sum(sales_price) AS sales, sum(profit) AS profit, sum(return_amt) AS returns1, sum(net_loss) AS profit_loss FROM ( SELECT cs_catalog_page_sk AS page_sk, cs_sold_date_sk AS date_sk, cs_ext_sales_price AS sales_price, cs_net_profit AS profit, 0 AS return_amt, 0 AS net_loss FROM tpcds.catalog_sales UNION ALL SELECT cr_catalog_page_sk AS page_sk, cr_returned_date_sk AS date_sk, 0 AS sales_price, 0 AS profit, cr_return_amount AS return_amt, cr_net_loss AS net_loss FROM tpcds.catalog_returns ) salesreturns, date_dim, catalog_page WHERE date_sk = d_date_sk AND Cast(d_date AS DATE) BETWEEN cast('2002-08-22' AS date) AND ( Cast('2002-09-05' AS DATE)) AND page_sk = cp_catalog_page_sk GROUP BY cp_catalog_page_id) , wsr AS ( SELECT web_site_id, sum(sales_price) AS sales, sum(profit) AS profit, sum(return_amt) AS returns1, sum(net_loss) AS profit_loss FROM ( SELECT ws_web_site_sk AS wsr_web_site_sk, ws_sold_date_sk AS date_sk, ws_ext_sales_price AS sales_price, ws_net_profit AS profit, 0 AS return_amt, 0 AS net_loss FROM tpcds.web_sales UNION ALL SELECT ws_web_site_sk AS wsr_web_site_sk, wr_returned_date_sk AS date_sk, 0 AS sales_price, 0 AS profit, wr_return_amt AS return_amt, wr_net_loss AS net_loss FROM tpcds.web_returns LEFT OUTER JOIN tpcds.web_sales ON ( wr_item_sk = ws_item_sk AND wr_order_number = ws_order_number) ) salesreturns, date_dim, web_site WHERE date_sk = d_date_sk AND Cast(d_date AS DATE) BETWEEN cast('2002-08-22' AS date) AND ( Cast('2002-09-05' AS DATE)) AND wsr_web_site_sk = web_site_sk GROUP BY web_site_id) SELECT channel , id , sum(sales) AS sales , sum(returns1) AS returns1 , sum(profit) AS profit FROM ( SELECT 'store channel' AS channel , concat('store', s_store_id) AS id , sales , returns1 , (profit - profit_loss) AS profit FROM ssr UNION ALL SELECT 'catalog channel' AS channel , concat('catalog_page', cp_catalog_page_id) AS id , sales , returns1 , (profit - profit_loss) AS profit FROM csr UNION ALL SELECT 'web channel' AS channel , concat('web_site', web_site_id) AS id , sales , returns1 , (profit - profit_loss) AS profit FROM wsr ) x GROUP BY channel, id ORDER BY channel , id LIMIT 100
SELECT a.ca_state state, count(*) cnt FROM tpcds.customer_address a, tpcds.customer c, tpcds.store_sales s, tpcds.date_dim d, tpcds.item i WHERE a.ca_address_sk = c.c_current_addr_sk AND c.c_customer_sk = s.ss_customer_sk AND s.ss_sold_date_sk = d.d_date_sk AND s.ss_item_sk = i.i_item_sk AND d.d_month_seq = (SELECT DISTINCT ( d_month_seq ) FROM tpcds.date_dim WHERE d_year = 1998 AND d_moy = 7) AND i.i_current_price > 1.2 * (SELECT avg(j.i_current_price) FROM tpcds.item j WHERE j.i_category = i.i_category) GROUP BY a.ca_state HAVING count(*) >= 10 ORDER BY cnt LIMIT 100
SELECT i_item_id, avg(ss_quantity) agg1, avg(ss_list_price) agg2, avg(ss_coupon_amt) agg3, avg(ss_sales_price) agg4 FROM tpcds.store_sales, tpcds.customer_demographics, tpcds.date_dim, tpcds.item, tpcds.promotion WHERE ss_sold_date_sk = d_date_sk AND ss_item_sk = i_item_sk AND ss_cdemo_sk = cd_demo_sk AND ss_promo_sk = p_promo_sk AND cd_gender = 'F' AND cd_marital_status = 'W' AND cd_education_status = '2 yr Degree' AND ( p_channel_email = 'N' OR p_channel_event = 'N' ) AND d_year = 1998 GROUP BY i_item_id ORDER BY i_item_id LIMIT 100
WITH ca_zips AS ( SELECT substr(ca_zip, 1, 5) AS ca_zip FROM tpcds.customer_address WHERE substr(ca_zip, 1, 5) IN ( '67436', '26121', '38443', '63157', '68856', '19485', '86425', '26741', '70991', '60899', '63573', '47556', '56193', '93314', '87827', '62017', '85067', '95390', '48091', '10261', '81845', '41790', '42853', '24675', '12840', '60065', '84430', '57451', '24021', '91735', '75335', '71935', '34482', '56943', '70695', '52147', '56251', '28411', '86653', '23005', '22478', '29031', '34398', '15365', '42460', '33337', '59433', '73943', '72477', '74081', '74430', '64605', '39006', '11226', '49057', '97308', '42663', '18187', '19768', '43454', '32147', '76637', '51975', '11181', '45630', '33129', '45995', '64386', '55522', '26697', '20963', '35154', '64587', '49752', '66386', '30586', '59286', '13177', '66646', '84195', '74316', '36853', '32927', '12469', '11904', '36269', '17724', '55346', '12595', '53988', '65439', '28015', '63268', '73590', '29216', '82575', '69267', '13805', '91678', '79460', '94152', '14961', '15419', '48277', '62588', '55493', '28360', '14152', '55225', '18007', '53705', '56573', '80245', '71769', '57348', '36845', '13039', '17270', '22363', '83474', '25294', '43269', '77666', '15488', '99146', '64441', '43338', '38736', '62754', '48556', '86057', '23090', '38114', '66061', '18910', '84385', '23600', '19975', '27883', '65719', '19933', '32085', '49731', '40473', '27190', '46192', '23949', '44738', '12436', '64794', '68741', '15333', '24282', '49085', '31844', '71156', '48441', '17100', '98207', '44982', '20277', '71496', '96299', '37583', '22206', '89174', '30589', '61924', '53079', '10976', '13104', '42794', '54772', '15809', '56434', '39975', '13874', '30753', '77598', '78229', '59478', '12345', '55547', '57422', '42600', '79444', '29074', '29752', '21676', '32096', '43044', '39383', '37296', '36295', '63077', '16572', '31275', '18701', '40197', '48242', '27219', '49865', '84175', '30446', '25165', '13807', '72142', '70499', '70464', '71429', '18111', '70857', '29545', '36425', '52706', '36194', '42963', '75068', '47921', '74763', '90990', '89456', '62073', '88397', '73963', '75885', '62657', '12530', '81146', '57434', '25099', '41429', '98441', '48713', '52552', '31667', '14072', '13903', '44709', '85429', '58017', '38295', '44875', '73541', '30091', '12707', '23762', '62258', '33247', '78722', '77431', '14510', '35656', '72428', '92082', '35267', '43759', '24354', '90952', '11512', '21242', '22579', '56114', '32339', '52282', '41791', '24484', '95020', '28408', '99710', '11899', '43344', '72915', '27644', '62708', '74479', '17177', '32619', '12351', '91339', '31169', '57081', '53522', '16712', '34419', '71779', '44187', '46206', '96099', '61910', '53664', '12295', '31837', '33096', '10813', '63048', '31732', '79118', '73084', '72783', '84952', '46965', '77956', '39815', '32311', '75329', '48156', '30826', '49661', '13736', '92076', '74865', '88149', '92397', '52777', '68453', '32012', '21222', '52721', '24626', '18210', '42177', '91791', '75251', '82075', '44372', '45542', '20609', '60115', '17362', '22750', '90434', '31852', '54071', '33762', '14705', '40718', '56433', '30996', '40657', '49056', '23585', '66455', '41021', '74736', '72151', '37007', '21729', '60177', '84558', '59027', '93855', '60022', '86443', '19541', '86886', '30532', '39062', '48532', '34713', '52077', '22564', '64638', '15273', '31677', '36138', '62367', '60261', '80213', '42818', '25113', '72378', '69802', '69096', '55443', '28820', '13848', '78258', '37490', '30556', '77380', '28447', '44550', '26791', '70609', '82182', '33306', '43224', '22322', '86959', '68519', '14308', '46501', '81131', '34056', '61991', '19896', '87804', '65774', '92564' ) ), common_zips AS ( SELECT ca_zip FROM (SELECT substr(ca_zip, 1, 5) ca_zip, count(*) cnt FROM tpcds.customer_address, tpcds.customer WHERE ca_address_sk = c_current_addr_sk AND c_preferred_cust_flag = 'Y' GROUP BY ca_zip HAVING count(*) > 10) ), chosen_zips AS ( SELECT ca_zip FROM ca_zips WHERE ca_zip IN (SELECT ca_zip FROM common_zips) ) SELECT s_store_name, sum(ss_net_profit) FROM tpcds.store_sales, tpcds.date_dim, tpcds.store, common_zips WHERE ss_store_sk = s_store_sk AND ss_sold_date_sk = d_date_sk AND d_qoy = 2 AND d_year = 2000 AND ( substr(s_zip, 1, 2) = substr(ca_zip, 1, 2) ) GROUP BY s_store_name ORDER BY s_store_name LIMIT 100
SELECT CASE WHEN (SELECT count(*) FROM tpcds.store_sales WHERE ss_quantity BETWEEN 1 AND 20) > 3672 THEN (SELECT avg(ss_ext_list_price) FROM tpcds.store_sales WHERE ss_quantity BETWEEN 1 AND 20) ELSE (SELECT avg(ss_net_profit) FROM tpcds.store_sales WHERE ss_quantity BETWEEN 1 AND 20) END bucket1, CASE WHEN (SELECT count(*) FROM tpcds.store_sales WHERE ss_quantity BETWEEN 21 AND 40) > 3392 THEN (SELECT avg(ss_ext_list_price) FROM tpcds.store_sales WHERE ss_quantity BETWEEN 21 AND 40) ELSE (SELECT avg(ss_net_profit) FROM tpcds.store_sales WHERE ss_quantity BETWEEN 21 AND 40) END bucket2, CASE WHEN (SELECT count(*) FROM tpcds.store_sales WHERE ss_quantity BETWEEN 41 AND 60) > 32784 THEN (SELECT avg(ss_ext_list_price) FROM tpcds.store_sales WHERE ss_quantity BETWEEN 41 AND 60) ELSE (SELECT avg(ss_net_profit) FROM tpcds.store_sales WHERE ss_quantity BETWEEN 41 AND 60) END bucket3, CASE WHEN (SELECT count(*) FROM tpcds.store_sales WHERE ss_quantity BETWEEN 61 AND 80) > 26032 THEN (SELECT avg(ss_ext_list_price) FROM tpcds.store_sales WHERE ss_quantity BETWEEN 61 AND 80) ELSE (SELECT avg(ss_net_profit) FROM tpcds.store_sales WHERE ss_quantity BETWEEN 61 AND 80) END bucket4, CASE WHEN (SELECT count(*) FROM tpcds.store_sales WHERE ss_quantity BETWEEN 81 AND 100) > 23982 THEN (SELECT avg(ss_ext_list_price) FROM tpcds.store_sales WHERE ss_quantity BETWEEN 81 AND 100) ELSE (SELECT avg(ss_net_profit) FROM tpcds.store_sales WHERE ss_quantity BETWEEN 81 AND 100) END bucket5 FROM tpcds.reason WHERE r_reason_sk = 1
SELECT cd_gender, cd_marital_status, cd_education_status, count(*) cnt1, cd_purchase_estimate, count(*) cnt2, cd_credit_rating, count(*) cnt3, cd_dep_count, count(*) cnt4, cd_dep_employed_count, count(*) cnt5, cd_dep_college_count, count(*) cnt6 FROM tpcds.customer c, tpcds.customer_address ca, tpcds.customer_demographics WHERE c.c_current_addr_sk = ca.ca_address_sk AND ca_county IN ( 'Lycoming County', 'Sheridan County', 'Kandiyohi County', 'Pike County', 'Greene County' ) AND cd_demo_sk = c.c_current_cdemo_sk AND EXISTS (SELECT * FROM tpcds.store_sales, tpcds.date_dim WHERE c.c_customer_sk = ss_customer_sk AND ss_sold_date_sk = d_date_sk AND d_year = 2002 AND d_moy BETWEEN 4 AND 4 + 3) AND ( EXISTS (SELECT * FROM tpcds.web_sales, tpcds.date_dim WHERE c.c_customer_sk = ws_bill_customer_sk AND ws_sold_date_sk = d_date_sk AND d_year = 2002 AND d_moy BETWEEN 4 AND 4 + 3) OR EXISTS (SELECT * FROM tpcds.catalog_sales, tpcds.date_dim WHERE c.c_customer_sk = cs_ship_customer_sk AND cs_sold_date_sk = d_date_sk AND d_year = 2002 AND d_moy BETWEEN 4 AND 4 + 3) ) GROUP BY cd_gender, cd_marital_status, cd_education_status, cd_purchase_estimate, cd_credit_rating, cd_dep_count, cd_dep_employed_count, cd_dep_college_count ORDER BY cd_gender, cd_marital_status, cd_education_status, cd_purchase_estimate, cd_credit_rating, cd_dep_count, cd_dep_employed_count, cd_dep_college_count LIMIT 100
WITH year_total AS (SELECT c_customer_id customer_id, c_first_name customer_first_name , c_last_name customer_last_name, c_preferred_cust_flag customer_preferred_cust_flag , c_birth_country customer_birth_country, c_login customer_login, c_email_address customer_email_address, d_year dyear, sum(ss_ext_list_price - ss_ext_discount_amt) 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, c_first_name, c_last_name, c_preferred_cust_flag, c_birth_country, c_login, c_email_address, d_year UNION ALL SELECT c_customer_id customer_id, c_first_name customer_first_name , c_last_name customer_last_name, c_preferred_cust_flag customer_preferred_cust_flag , c_birth_country customer_birth_country, c_login customer_login, c_email_address customer_email_address, d_year dyear, sum(ws_ext_list_price - ws_ext_discount_amt) 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, c_first_name, c_last_name, c_preferred_cust_flag, c_birth_country, c_login, c_email_address, d_year) SELECT t_s_secyear.customer_id, t_s_secyear.customer_first_name, t_s_secyear.customer_last_name, t_s_secyear.customer_birth_country FROM year_total t_s_firstyear, year_total t_s_secyear, year_total t_w_firstyear, year_total t_w_secyear WHERE t_s_secyear.customer_id = t_s_firstyear.customer_id AND t_s_firstyear.customer_id = t_w_secyear.customer_id AND t_s_firstyear.customer_id = t_w_firstyear.customer_id AND t_s_firstyear.sale_type = 's' AND t_w_firstyear.sale_type = 'w' AND t_s_secyear.sale_type = 's' AND t_w_secyear.sale_type = 'w' AND t_s_firstyear.dyear = 2001 AND t_s_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_w_firstyear.year_total > 0 AND CASE WHEN t_w_firstyear.year_total > 0 THEN t_w_secyear.year_total / t_w_firstyear.year_total ELSE 0.0 END > CASE WHEN t_s_firstyear.year_total > 0 THEN t_s_secyear.year_total / t_s_firstyear.year_total ELSE 0.0 END ORDER BY t_s_secyear.customer_id, t_s_secyear.customer_first_name, t_s_secyear.customer_last_name, t_s_secyear.customer_birth_country LIMIT 100
SELECT i_item_id , i_item_desc , i_category , i_class , i_current_price , sum(ws_ext_sales_price) AS itemrevenue , sum(ws_ext_sales_price)*100/sum(sum(ws_ext_sales_price)) OVER (partition BY i_class) AS revenueratio FROM tpcds.web_sales , tpcds.item , tpcds.date_dim WHERE ws_item_sk = i_item_sk AND i_category IN ('Home', 'Men', 'Women') AND ws_sold_date_sk = d_date_sk AND Cast(d_date AS DATE) BETWEEN Cast('2000-05-11' AS DATE) AND ( Cast('2000-06-11' AS DATE)) GROUP BY i_item_id , i_item_desc , i_category , i_class , i_current_price ORDER BY i_category , i_class , i_item_id , i_item_desc , revenueratio LIMIT 100
SELECT avg(ss_quantity), avg(ss_ext_sales_price), avg(ss_ext_wholesale_cost), sum(ss_ext_wholesale_cost) FROM tpcds.store_sales, tpcds.store, tpcds.customer_demographics, tpcds.household_demographics, tpcds.customer_address, tpcds.date_dim WHERE s_store_sk = ss_store_sk AND ss_sold_date_sk = d_date_sk AND d_year = 2001 AND ( ( ss_hdemo_sk = hd_demo_sk AND cd_demo_sk = ss_cdemo_sk AND cd_marital_status = 'U' AND cd_education_status = 'Advanced Degree' AND ss_sales_price BETWEEN 100.00 AND 150.00 AND hd_dep_count = 3 ) OR ( ss_hdemo_sk = hd_demo_sk AND cd_demo_sk = ss_cdemo_sk AND cd_marital_status = 'M' AND cd_education_status = 'Primary' AND ss_sales_price BETWEEN 50.00 AND 100.00 AND hd_dep_count = 1 ) OR ( ss_hdemo_sk = hd_demo_sk AND cd_demo_sk = ss_cdemo_sk AND cd_marital_status = 'D' AND cd_education_status = 'Secondary' AND ss_sales_price BETWEEN 150.00 AND 200.00 AND hd_dep_count = 1 ) ) AND ( ( ss_addr_sk = ca_address_sk AND ca_country = 'United States' AND ca_state IN ( 'AZ', 'NE', 'IA' ) AND ss_net_profit BETWEEN 100 AND 200 ) OR ( ss_addr_sk = ca_address_sk AND ca_country = 'United States' AND ca_state IN ( 'MS', 'CA', 'NV' ) AND ss_net_profit BETWEEN 150 AND 300 ) OR ( ss_addr_sk = ca_address_sk AND ca_country = 'United States' AND ca_state IN ( 'GA', 'TX', 'NJ' ) AND ss_net_profit BETWEEN 50 AND 250 ) )
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
SELECT ca_zip, sum(cs_sales_price) FROM tpcds.catalog_sales, tpcds.customer, tpcds.customer_address, tpcds.date_dim WHERE cs_bill_customer_sk = c_customer_sk AND c_current_addr_sk = ca_address_sk AND ( substr(ca_zip, 1, 5) IN ( '85669', '86197', '88274', '83405', '86475', '85392', '85460', '80348', '81792' ) OR ca_state IN ( 'CA', 'WA', 'GA' ) OR cs_sales_price > 500 ) AND cs_sold_date_sk = d_date_sk AND d_qoy = 1 AND d_year = 1998 GROUP BY ca_zip ORDER BY ca_zip LIMIT 100
SELECT count(DISTINCT cs_order_number) AS order_count , sum(cs_ext_ship_cost) AS total_shipping_cost , sum(cs_net_profit) AS total_net_profit FROM tpcds.catalog_sales cs1 , tpcds.date_dim , tpcds.customer_address , tpcds.call_center WHERE Cast(d_date AS DATE) BETWEEN Cast('2002-3-01' AS DATE) AND ( Cast('2002-5-01' AS DATE)) AND cs1.cs_ship_date_sk = d_date_sk AND cs1.cs_ship_addr_sk = ca_address_sk AND ca_state = 'IA' AND cs1.cs_call_center_sk = cc_call_center_sk AND cc_county IN ('Williamson County', 'Williamson County', 'Williamson County', 'Williamson County', 'Williamson County' ) AND EXISTS ( SELECT * FROM tpcds.catalog_sales cs2 WHERE cs1.cs_order_number = cs2.cs_order_number AND cs1.cs_warehouse_sk <> cs2.cs_warehouse_sk) AND NOT EXISTS ( SELECT * FROM tpcds.catalog_returns cr1 WHERE cs1.cs_order_number = cr1.cr_order_number) ORDER BY count(DISTINCT cs_order_number) LIMIT 100
SELECT i_item_id, i_item_desc, s_state, count(ss_quantity) AS store_sales_quantitycount, avg(ss_quantity) AS store_sales_quantityave, stddev_samp(ss_quantity) AS store_sales_quantitystdev, stddev_samp(ss_quantity) / avg(ss_quantity) AS store_sales_quantitycov, count(sr_return_quantity) AS store_returns_quantitycount, avg(sr_return_quantity) AS store_returns_quantityave, stddev_samp(sr_return_quantity) AS store_returns_quantitystdev, stddev_samp(sr_return_quantity) / avg(sr_return_quantity) AS store_returns_quantitycov, count(cs_quantity) AS catalog_sales_quantitycount, avg(cs_quantity) AS catalog_sales_quantityave, stddev_samp(cs_quantity) / avg(cs_quantity) AS catalog_sales_quantitystdev, stddev_samp(cs_quantity) / avg(cs_quantity) AS catalog_sales_quantitycov FROM tpcds.store_sales, tpcds.store_returns, tpcds.catalog_sales, tpcds.date_dim d1, tpcds.date_dim d2, tpcds.date_dim d3, tpcds.store, tpcds.item WHERE d1.d_quarter_name = '1999Q1' AND d1.d_date_sk = ss_sold_date_sk AND i_item_sk = ss_item_sk AND s_store_sk = ss_store_sk AND ss_customer_sk = sr_customer_sk AND ss_item_sk = sr_item_sk AND ss_ticket_number = sr_ticket_number AND sr_returned_date_sk = d2.d_date_sk AND d2.d_quarter_name IN ( '1999Q1', '1999Q2', '1999Q3' ) AND sr_customer_sk = cs_bill_customer_sk AND sr_item_sk = cs_item_sk AND cs_sold_date_sk = d3.d_date_sk AND d3.d_quarter_name IN ( '1999Q1', '1999Q2', '1999Q3' ) GROUP BY i_item_id, i_item_desc, s_state ORDER BY i_item_id, i_item_desc, s_state LIMIT 100
SELECT i_item_id, ca_country, ca_state, ca_county, avg(cs_quantity) agg1, avg(cs_list_price) agg2, avg(cs_coupon_amt) agg3, avg(cs_sales_price) agg4, avg(cs_net_profit) agg5, avg(c_birth_year) agg6, avg(cd1.cd_dep_count) agg7 FROM tpcds.catalog_sales, tpcds.customer_demographics cd1, tpcds.customer_demographics cd2, tpcds.customer, tpcds.customer_address, tpcds.date_dim, tpcds.item WHERE cs_sold_date_sk = d_date_sk AND cs_item_sk = i_item_sk AND cs_bill_cdemo_sk = cd1.cd_demo_sk AND cs_bill_customer_sk = c_customer_sk AND cd1.cd_gender = 'F' AND cd1.cd_education_status = 'Secondary' AND c_current_cdemo_sk = cd2.cd_demo_sk AND c_current_addr_sk = ca_address_sk AND c_birth_month IN ( 8, 4, 2, 5, 11, 9 ) AND d_year = 2001 AND ca_state IN ( 'KS', 'IA', 'AL', 'UT', 'VA', 'NC', 'TX' ) GROUP BY i_item_id, ca_country, ca_state, ca_county ORDER BY ca_country, ca_state, ca_county, i_item_id LIMIT 100
SELECT i_brand_id brand_id, i_brand brand, i_manufact_id, i_manufact, sum(ss_ext_sales_price) ext_price FROM tpcds.date_dim, tpcds.store_sales, tpcds.item, tpcds.customer, tpcds.customer_address, tpcds.store WHERE d_date_sk = ss_sold_date_sk AND ss_item_sk = i_item_sk AND i_manager_id = 38 AND d_moy = 12 AND d_year = 1998 AND ss_customer_sk = c_customer_sk AND c_current_addr_sk = ca_address_sk AND substr(ca_zip, 1, 5) <> substr(s_zip, 1, 5) AND ss_store_sk = s_store_sk GROUP BY i_brand, i_brand_id, i_manufact_id, i_manufact ORDER BY ext_price DESC, i_brand, i_brand_id, i_manufact_id, i_manufact LIMIT 100
SELECT i_item_id , i_item_desc , i_category , i_class , i_current_price , sum(cs_ext_sales_price) AS itemrevenue , sum(cs_ext_sales_price)*100/sum(sum(cs_ext_sales_price)) OVER (partition BY i_class) AS revenueratio FROM tpcds.catalog_sales , tpcds.item , tpcds.date_dim WHERE cs_item_sk = i_item_sk AND i_category IN ('Children', 'Women', 'Electronics') AND cs_sold_date_sk = d_date_sk AND Cast(d_date AS DATE) BETWEEN Cast('2001-02-03' AS DATE) AND ( Cast('2001-03-03' AS DATE)) GROUP BY i_item_id , i_item_desc , i_category , i_class , i_current_price ORDER BY i_category , i_class , i_item_id , i_item_desc , revenueratio LIMIT 100
SELECT * FROM ( SELECT w_warehouse_name , i_item_id , sum( CASE WHEN ( Cast(d_date AS DATE) < Cast ('2000-05-13' AS DATE)) THEN inv_quantity_on_hand ELSE 0 END) AS inv_before , sum( CASE WHEN ( Cast(d_date AS DATE) >= Cast ('2000-05-13' AS DATE)) THEN inv_quantity_on_hand ELSE 0 END) AS inv_after FROM tpcds.inventory , tpcds.warehouse , tpcds.item , tpcds.date_dim WHERE i_current_price BETWEEN 0.99 AND 1.49 AND i_item_sk = inv_item_sk AND inv_warehouse_sk = w_warehouse_sk AND inv_date_sk = d_date_sk AND Cast(d_date AS DATE) BETWEEN (Cast ('2000-04-13' AS DATE)) AND ( cast ('2000-06-13' AS date)) GROUP BY w_warehouse_name, i_item_id) x WHERE ( CASE WHEN inv_before > 0 THEN inv_after / inv_before ELSE NULL END) BETWEEN 2.0/3.0 AND 3.0/2.0 ORDER BY w_warehouse_name , i_item_id LIMIT 100
SELECT i_product_name, i_brand, i_class, i_category, avg(inv_quantity_on_hand) qoh FROM tpcds.inventory, tpcds.date_dim, tpcds.item, tpcds.warehouse WHERE inv_date_sk = d_date_sk AND inv_item_sk = i_item_sk AND inv_warehouse_sk = w_warehouse_sk AND d_month_seq BETWEEN 1205 AND 1205 + 11 GROUP BY i_product_name, i_brand, i_class, i_category ORDER BY qoh, i_product_name, i_brand, i_class, i_category 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
WITH ssales AS (SELECT c_last_name, c_first_name, s_store_name, ca_state, s_state, i_color, i_current_price, i_manager_id, i_units, i_size, sum(ss_net_profit) netpaid FROM tpcds.store_sales, tpcds.store_returns, tpcds.store, tpcds.item, tpcds.customer, tpcds.customer_address WHERE ss_ticket_number = sr_ticket_number AND ss_item_sk = sr_item_sk AND ss_customer_sk = c_customer_sk AND ss_item_sk = i_item_sk AND ss_store_sk = s_store_sk AND c_birth_country = upper(ca_country) AND s_zip = ca_zip AND s_market_id = 6 GROUP BY c_last_name, c_first_name, s_store_name, ca_state, s_state, i_color, i_current_price, i_manager_id, i_units, i_size) SELECT c_last_name, c_first_name, s_store_name, sum(netpaid) paid FROM ssales WHERE i_color = 'papaya' GROUP BY c_last_name, c_first_name, s_store_name HAVING sum(netpaid) > (SELECT 0.05 * avg(netpaid) FROM ssales)
SELECT i_item_id, i_item_desc, s_store_id, s_store_name, Max(ss_net_profit) AS store_sales_profit, Max(sr_net_loss) AS store_returns_loss, Max(cs_net_profit) AS catalog_sales_profit FROM tpcds.store_sales, tpcds.store_returns, tpcds.catalog_sales, tpcds.date_dim d1, tpcds.date_dim d2, tpcds.date_dim d3, tpcds.store, tpcds.item WHERE d1.d_moy = 4 AND d1.d_year = 2001 AND d1.d_date_sk = ss_sold_date_sk AND i_item_sk = ss_item_sk AND s_store_sk = ss_store_sk AND ss_customer_sk = sr_customer_sk AND ss_item_sk = sr_item_sk AND ss_ticket_number = sr_ticket_number AND sr_returned_date_sk = d2.d_date_sk AND d2.d_moy BETWEEN 4 AND 10 AND d2.d_year = 2001 AND sr_customer_sk = cs_bill_customer_sk AND sr_item_sk = cs_item_sk AND cs_sold_date_sk = d3.d_date_sk AND d3.d_moy BETWEEN 4 AND 10 AND d3.d_year = 2001 GROUP BY i_item_id, i_item_desc, s_store_id, s_store_name ORDER BY i_item_id, i_item_desc, s_store_id, s_store_name LIMIT 100
SELECT i_item_id, avg(cs_quantity) agg1, avg(cs_list_price) agg2, avg(cs_coupon_amt) agg3, avg(cs_sales_price) agg4 FROM tpcds.catalog_sales, tpcds.customer_demographics, tpcds.date_dim, tpcds.item, tpcds.promotion WHERE cs_sold_date_sk = d_date_sk AND cs_item_sk = i_item_sk AND cs_bill_cdemo_sk = cd_demo_sk AND cs_promo_sk = p_promo_sk AND cd_gender = 'F' AND cd_marital_status = 'W' AND cd_education_status = 'Secondary' AND ( p_channel_email = 'N' OR p_channel_event = 'N' ) AND d_year = 2000 GROUP BY i_item_id ORDER BY i_item_id LIMIT 100
SELECT i_item_id, s_state, avg(ss_quantity) agg1, avg(ss_list_price) agg2, avg(ss_coupon_amt) agg3, avg(ss_sales_price) agg4 FROM tpcds.store_sales, tpcds.customer_demographics, tpcds.date_dim, tpcds.store, tpcds.item WHERE ss_sold_date_sk = d_date_sk AND ss_item_sk = i_item_sk AND ss_store_sk = s_store_sk AND ss_cdemo_sk = cd_demo_sk AND cd_gender = 'M' AND cd_marital_status = 'D' AND cd_education_status = 'College' AND d_year = 2000 AND s_state IN ( 'TN', 'TN', 'TN', 'TN', 'TN', 'TN' ) GROUP BY i_item_id, s_state ORDER BY i_item_id, s_state LIMIT 100
SELECT * FROM (SELECT avg(ss_list_price) B1_LP, count(ss_list_price) B1_CNT, count(DISTINCT ss_list_price) B1_CNTD FROM tpcds.store_sales WHERE ss_quantity BETWEEN 0 AND 5 AND ( ss_list_price BETWEEN 18 AND 18 + 10 OR ss_coupon_amt BETWEEN 1939 AND 1939 + 1000 OR ss_wholesale_cost BETWEEN 34 AND 34 + 20 )) B1, (SELECT avg(ss_list_price) B2_LP, count(ss_list_price) B2_CNT, count(DISTINCT ss_list_price) B2_CNTD FROM tpcds.store_sales WHERE ss_quantity BETWEEN 6 AND 10 AND ( ss_list_price BETWEEN 1 AND 1 + 10 OR ss_coupon_amt BETWEEN 35 AND 35 + 1000 OR ss_wholesale_cost BETWEEN 50 AND 50 + 20 )) B2, (SELECT avg(ss_list_price) B3_LP, count(ss_list_price) B3_CNT, count(DISTINCT ss_list_price) B3_CNTD FROM tpcds.store_sales WHERE ss_quantity BETWEEN 11 AND 15 AND ( ss_list_price BETWEEN 91 AND 91 + 10 OR ss_coupon_amt BETWEEN 1412 AND 1412 + 1000 OR ss_wholesale_cost BETWEEN 17 AND 17 + 20 )) B3, (SELECT avg(ss_list_price) B4_LP, count(ss_list_price) B4_CNT, count(DISTINCT ss_list_price) B4_CNTD FROM tpcds.store_sales WHERE ss_quantity BETWEEN 16 AND 20 AND ( ss_list_price BETWEEN 9 AND 9 + 10 OR ss_coupon_amt BETWEEN 5270 AND 5270 + 1000 OR ss_wholesale_cost BETWEEN 29 AND 29 + 20 )) B4, (SELECT avg(ss_list_price) B5_LP, count(ss_list_price) B5_CNT, count(DISTINCT ss_list_price) B5_CNTD FROM tpcds.store_sales WHERE ss_quantity BETWEEN 21 AND 25 AND ( ss_list_price BETWEEN 45 AND 45 + 10 OR ss_coupon_amt BETWEEN 826 AND 826 + 1000 OR ss_wholesale_cost BETWEEN 5 AND 5 + 20 )) B5, (SELECT avg(ss_list_price) B6_LP, count(ss_list_price) B6_CNT, count(DISTINCT ss_list_price) B6_CNTD FROM tpcds.store_sales WHERE ss_quantity BETWEEN 26 AND 30 AND ( ss_list_price BETWEEN 174 AND 174 + 10 OR ss_coupon_amt BETWEEN 5548 AND 5548 + 1000 OR ss_wholesale_cost BETWEEN 42 AND 42 + 20 )) B6 LIMIT 100
SELECT i_item_id, i_item_desc, s_store_id, s_store_name, avg(ss_quantity) AS store_sales_quantity, avg(sr_return_quantity) AS store_returns_quantity, avg(cs_quantity) AS catalog_sales_quantity FROM tpcds.store_sales, tpcds.store_returns, tpcds.catalog_sales, tpcds.date_dim d1, tpcds.date_dim d2, tpcds.date_dim d3, tpcds.store, tpcds.item WHERE d1.d_moy = 4 AND d1.d_year = 1998 AND d1.d_date_sk = ss_sold_date_sk AND i_item_sk = ss_item_sk AND s_store_sk = ss_store_sk AND ss_customer_sk = sr_customer_sk AND ss_item_sk = sr_item_sk AND ss_ticket_number = sr_ticket_number AND sr_returned_date_sk = d2.d_date_sk AND d2.d_moy BETWEEN 4 AND 4 + 3 AND d2.d_year = 1998 AND sr_customer_sk = cs_bill_customer_sk AND sr_item_sk = cs_item_sk AND cs_sold_date_sk = d3.d_date_sk AND d3.d_year IN ( 1998, 1998 + 1, 1998 + 2 ) GROUP BY i_item_id, i_item_desc, s_store_id, s_store_name ORDER BY i_item_id, i_item_desc, s_store_id, s_store_name LIMIT 100
WITH customer_total_return AS (SELECT wr_returning_customer_sk AS ctr_customer_sk, ca_state AS ctr_state, sum(wr_return_amt) AS ctr_total_return FROM tpcds.web_returns, tpcds.date_dim, tpcds.customer_address WHERE wr_returned_date_sk = d_date_sk AND d_year = 2000 AND wr_returning_addr_sk = ca_address_sk GROUP BY wr_returning_customer_sk, ca_state) SELECT c_customer_id, c_salutation, c_first_name, c_last_name, c_preferred_cust_flag, c_birth_day, c_birth_month, c_birth_year, c_birth_country, c_login, c_email_address, c_last_review_date, ctr_total_return FROM customer_total_return ctr1, tpcds.customer_address, tpcds.customer WHERE ctr1.ctr_total_return > (SELECT avg(ctr_total_return) * 1.2 FROM customer_total_return ctr2 WHERE ctr1.ctr_state = ctr2.ctr_state) AND ca_address_sk = c_current_addr_sk AND ca_state = 'IN' AND ctr1.ctr_customer_sk = c_customer_sk ORDER BY c_customer_id, c_salutation, c_first_name, c_last_name, c_preferred_cust_flag, c_birth_day, c_birth_month, c_birth_year, c_birth_country, c_login, c_email_address, c_last_review_date, ctr_total_return LIMIT 100
WITH ss AS (SELECT ca_county, d_qoy, d_year, sum(ss_ext_sales_price) AS store_sales FROM tpcds.store_sales, tpcds.date_dim, tpcds.customer_address WHERE ss_sold_date_sk = d_date_sk AND ss_addr_sk = ca_address_sk GROUP BY ca_county, d_qoy, d_year), ws AS (SELECT ca_county, d_qoy, d_year, sum(ws_ext_sales_price) AS web_sales FROM tpcds.web_sales, tpcds.date_dim, tpcds.customer_address WHERE ws_sold_date_sk = d_date_sk AND ws_bill_addr_sk = ca_address_sk GROUP BY ca_county, d_qoy, d_year) SELECT ss1.ca_county, ss1.d_year, ws2.web_sales / ws1.web_sales web_q1_q2_increase, ss2.store_sales / ss1.store_sales store_q1_q2_increase, ws3.web_sales / ws2.web_sales web_q2_q3_increase, ss3.store_sales / ss2.store_sales store_q2_q3_increase FROM ss ss1, ss ss2, ss ss3, ws ws1, ws ws2, ws ws3 WHERE ss1.d_qoy = 1 AND ss1.d_year = 2001 AND ss1.ca_county = ss2.ca_county AND ss2.d_qoy = 2 AND ss2.d_year = 2001 AND ss2.ca_county = ss3.ca_county AND ss3.d_qoy = 3 AND ss3.d_year = 2001 AND ss1.ca_county = ws1.ca_county AND ws1.d_qoy = 1 AND ws1.d_year = 2001 AND ws1.ca_county = ws2.ca_county AND ws2.d_qoy = 2 AND ws2.d_year = 2001 AND ws1.ca_county = ws3.ca_county AND ws3.d_qoy = 3 AND ws3.d_year = 2001 AND CASE WHEN ws1.web_sales > 0 THEN ws2.web_sales / ws1.web_sales ELSE NULL END > CASE WHEN ss1.store_sales > 0 THEN ss2.store_sales / ss1.store_sales ELSE NULL END AND CASE WHEN ws2.web_sales > 0 THEN ws3.web_sales / ws2.web_sales ELSE NULL END > CASE WHEN ss2.store_sales > 0 THEN ss3.store_sales / ss2.store_sales ELSE NULL END ORDER BY ss1.d_year
SELECT sum(cs_ext_discount_amt) AS excess_discount_amount FROM tpcds.catalog_sales , tpcds.item , tpcds.date_dim WHERE i_manufact_id = 610 AND i_item_sk = cs_item_sk AND Cast(d_date AS DATE) BETWEEN Cast('2001-03-04' AS DATE) AND ( Cast('2001-06-03' AS DATE)) AND d_date_sk = cs_sold_date_sk AND cs_ext_discount_amt > ( SELECT 1.3 * avg(cs_ext_discount_amt) FROM tpcds.catalog_sales , tpcds.date_dim WHERE cs_item_sk = i_item_sk AND Cast(d_date AS DATE) BETWEEN Cast('2001-03-04' AS DATE) AND ( Cast('2001-06-03' AS DATE)) AND d_date_sk = cs_sold_date_sk ) LIMIT 100
WITH ss AS (SELECT i_manufact_id, sum(ss_ext_sales_price) total_sales FROM tpcds.store_sales, tpcds.date_dim, tpcds.customer_address, tpcds.item WHERE i_manufact_id IN (SELECT i_manufact_id FROM tpcds.item WHERE i_category IN ( 'Books' )) AND ss_item_sk = i_item_sk AND ss_sold_date_sk = d_date_sk AND d_year = 1999 AND d_moy = 3 AND ss_addr_sk = ca_address_sk AND ca_gmt_offset = -5 GROUP BY i_manufact_id), cs AS (SELECT i_manufact_id, sum(cs_ext_sales_price) total_sales FROM tpcds.catalog_sales, tpcds.date_dim, tpcds.customer_address, tpcds.item WHERE i_manufact_id IN (SELECT i_manufact_id FROM tpcds.item WHERE i_category IN ( 'Books' )) AND cs_item_sk = i_item_sk AND cs_sold_date_sk = d_date_sk AND d_year = 1999 AND d_moy = 3 AND cs_bill_addr_sk = ca_address_sk AND ca_gmt_offset = -5 GROUP BY i_manufact_id), ws AS (SELECT i_manufact_id, sum(ws_ext_sales_price) total_sales FROM tpcds.web_sales, tpcds.date_dim, tpcds.customer_address, tpcds.item WHERE i_manufact_id IN (SELECT i_manufact_id FROM tpcds.item WHERE i_category IN ( 'Books' )) AND ws_item_sk = i_item_sk AND ws_sold_date_sk = d_date_sk AND d_year = 1999 AND d_moy = 3 AND ws_bill_addr_sk = ca_address_sk AND ca_gmt_offset = -5 GROUP BY i_manufact_id) SELECT i_manufact_id, sum(total_sales) total_sales FROM (SELECT * FROM ss UNION ALL SELECT * FROM cs UNION ALL SELECT * FROM ws) tmp1 GROUP BY i_manufact_id ORDER BY total_sales LIMIT 100
SELECT c_last_name, c_first_name, c_salutation, c_preferred_cust_flag, ss_ticket_number, cnt FROM (SELECT ss_ticket_number, ss_customer_sk, count(*) cnt 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 ( date_dim.d_dom BETWEEN 1 AND 3 OR date_dim.d_dom BETWEEN 25 AND 28 ) AND ( household_demographics.hd_buy_potential = '>10000' OR household_demographics.hd_buy_potential = 'unknown' ) AND household_demographics.hd_vehicle_count > 0 AND ( CASE WHEN household_demographics.hd_vehicle_count > 0 THEN household_demographics.hd_dep_count / household_demographics.hd_vehicle_count ELSE NULL END ) > 1.2 AND date_dim.d_year IN ( 1999, 1999 + 1, 1999 + 2 ) AND store.s_county IN ( 'Williamson County', 'Williamson County', 'Williamson County', 'Williamson County' , 'Williamson County', 'Williamson County', 'Williamson County', 'Williamson County' ) GROUP BY ss_ticket_number, ss_customer_sk) dn, customer WHERE ss_customer_sk = c_customer_sk AND cnt BETWEEN 15 AND 20 ORDER BY c_last_name, c_first_name, c_salutation, c_preferred_cust_flag DESC
SELECT ca_state, cd_gender, cd_marital_status, cd_dep_count, count(*) cnt1, stddev_samp(cd_dep_count), avg(cd_dep_count), Max(cd_dep_count), cd_dep_employed_count, count(*) cnt2, stddev_samp(cd_dep_employed_count), avg(cd_dep_employed_count), Max(cd_dep_employed_count), cd_dep_college_count, count(*) cnt3, stddev_samp(cd_dep_college_count), avg(cd_dep_college_count), Max(cd_dep_college_count) FROM tpcds.customer c, tpcds.customer_address ca, tpcds.customer_demographics WHERE c.c_current_addr_sk = ca.ca_address_sk AND cd_demo_sk = c.c_current_cdemo_sk AND EXISTS (SELECT * FROM tpcds.store_sales, tpcds.date_dim WHERE c.c_customer_sk = ss_customer_sk AND ss_sold_date_sk = d_date_sk AND d_year = 2001 AND d_qoy < 4) AND ( EXISTS (SELECT * FROM tpcds.web_sales, tpcds.date_dim WHERE c.c_customer_sk = ws_bill_customer_sk AND ws_sold_date_sk = d_date_sk AND d_year = 2001 AND d_qoy < 4) OR EXISTS (SELECT * FROM tpcds.catalog_sales, tpcds.date_dim WHERE c.c_customer_sk = cs_ship_customer_sk AND cs_sold_date_sk = d_date_sk AND d_year = 2001 AND d_qoy < 4) ) GROUP BY ca_state, cd_gender, cd_marital_status, cd_dep_count, cd_dep_employed_count, cd_dep_college_count ORDER BY ca_state, cd_gender, cd_marital_status, cd_dep_count, cd_dep_employed_count, cd_dep_college_count LIMIT 100
SELECT sum(ss_net_profit) / sum(ss_ext_sales_price) AS gross_margin, i_category, i_class, rank() OVER ( PARTITION BY i_category, i_class ORDER BY sum(ss_net_profit)/sum(ss_ext_sales_price) ASC) AS rank_within_parent FROM tpcds.store_sales, tpcds.date_dim d1, tpcds.item, tpcds.store WHERE d1.d_year = 2000 AND d1.d_date_sk = ss_sold_date_sk AND i_item_sk = ss_item_sk AND s_store_sk = ss_store_sk AND s_state IN ( 'TN', 'TN', 'TN', 'TN', 'TN', 'TN', 'TN', 'TN' ) GROUP BY i_category, i_class ORDER BY i_category, rank_within_parent LIMIT 100
SELECT i_item_id , i_item_desc , i_current_price FROM tpcds.item, tpcds.inventory, tpcds.date_dim, tpcds.catalog_sales WHERE i_current_price BETWEEN 20 AND 20 + 30 AND inv_item_sk = i_item_sk AND d_date_sk=inv_date_sk AND Cast(d_date AS DATE) BETWEEN Cast('1999-03-06' AS DATE) AND Cast('1999-05-06' AS DATE) AND i_manufact_id IN (843,815,850,840) AND inv_quantity_on_hand BETWEEN 100 AND 500 AND cs_item_sk = i_item_sk GROUP BY i_item_id, i_item_desc, i_current_price ORDER BY i_item_id LIMIT 100
WITH g1 AS ( SELECT DISTINCT c_last_name, c_first_name, d_date FROM tpcds.store_sales, tpcds.date_dim, tpcds.customer WHERE store_sales.ss_sold_date_sk = date_dim.d_date_sk AND store_sales.ss_customer_sk = customer.c_customer_sk AND d_month_seq BETWEEN 1188 AND 1188 + 11 ), g2 AS ( SELECT DISTINCT c_last_name, c_first_name, d_date FROM tpcds.catalog_sales, tpcds.date_dim, tpcds.customer WHERE catalog_sales.cs_sold_date_sk = date_dim.d_date_sk AND catalog_sales.cs_bill_customer_sk = customer.c_customer_sk AND d_month_seq BETWEEN 1188 AND 1188 + 11 ), g3 AS ( SELECT DISTINCT c_last_name, c_first_name, d_date FROM tpcds.web_sales, tpcds.date_dim, tpcds.customer WHERE web_sales.ws_sold_date_sk = date_dim.d_date_sk AND web_sales.ws_bill_customer_sk = customer.c_customer_sk AND d_month_seq BETWEEN 1188 AND 1188 + 11 ) SELECT count(*) FROM g1 JOIN g2 ON g1.c_last_name = g2.c_last_name AND g1.c_first_name = g2.c_first_name AND g1.d_date = g2.d_date JOIN g3 ON g1.c_last_name = g3.c_last_name AND g1.c_first_name = g3.c_first_name AND g1.d_date = g3.d_date LIMIT 100
WITH inv AS (SELECT w_warehouse_name, w_warehouse_sk, i_item_sk, d_moy, stdev, mean, CASE mean WHEN 0 THEN NULL ELSE stdev / mean END cov FROM (SELECT w_warehouse_name, w_warehouse_sk, i_item_sk, d_moy, stddev_samp(inv_quantity_on_hand) stdev, avg(inv_quantity_on_hand) mean FROM tpcds.inventory, tpcds.item, tpcds.warehouse, tpcds.date_dim WHERE inv_item_sk = i_item_sk AND inv_warehouse_sk = w_warehouse_sk AND inv_date_sk = d_date_sk AND d_year = 2002 GROUP BY w_warehouse_name, w_warehouse_sk, i_item_sk, d_moy) foo WHERE CASE mean WHEN 0 THEN 0 ELSE stdev / mean END > 1) SELECT inv1.w_warehouse_sk, inv1.i_item_sk, inv1.d_moy, inv1.mean, inv1.cov, inv2.w_warehouse_sk AS w_warehouse_sk_2, inv2.i_item_sk AS i_item_sk_2, inv2.d_moy AS d_moy_2, inv2.mean AS mean_2, inv2.cov AS cov_2 FROM inv inv1, inv inv2 WHERE inv1.i_item_sk = inv2.i_item_sk AND inv1.w_warehouse_sk = inv2.w_warehouse_sk AND inv1.d_moy = 1 AND inv2.d_moy = 1 + 1 ORDER BY inv1.w_warehouse_sk, inv1.i_item_sk, inv1.d_moy, inv1.mean, inv1.cov, inv2.d_moy, inv2.mean, inv2.cov
SELECT w_state , i_item_id , sum( CASE WHEN ( Cast(d_date AS DATE) < Cast ('2002-06-01' AS DATE)) THEN cs_sales_price - coalesce(cr_refunded_cash,0) ELSE 0 END) AS sales_before , sum( CASE WHEN ( Cast(d_date AS DATE) >= Cast ('2002-06-01' AS DATE)) THEN cs_sales_price - coalesce(cr_refunded_cash,0) ELSE 0 END) AS sales_after FROM tpcds.catalog_sales LEFT OUTER JOIN tpcds.catalog_returns ON ( cs_order_number = cr_order_number AND cs_item_sk = cr_item_sk) , warehouse , item , date_dim WHERE i_current_price BETWEEN 0.99 AND 1.49 AND i_item_sk = cs_item_sk AND cs_warehouse_sk = w_warehouse_sk AND cs_sold_date_sk = d_date_sk AND Cast(d_date AS DATE) BETWEEN (Cast ('2002-05-01' AS DATE)) AND cast ('2002-07-01' AS date) GROUP BY w_state, i_item_id ORDER BY w_state, i_item_id LIMIT 100
SELECT Distinct(i_product_name) FROM item i1 WHERE i_manufact_id BETWEEN 765 AND 765 + 40 AND (SELECT count(*) AS item_cnt FROM tpcds.item WHERE ( i_manufact = i1.i_manufact AND ( ( i_category = 'Women' AND ( i_color = 'dim' OR i_color = 'green' ) AND ( i_units = 'Gross' OR i_units = 'Dozen' ) AND ( i_size = 'economy' OR i_size = 'petite' ) ) OR ( i_category = 'Women' AND ( i_color = 'navajo' OR i_color = 'aquamarine' ) AND ( i_units = 'Case' OR i_units = 'Unknown' ) AND ( i_size = 'large' OR i_size = 'N/A' ) ) OR ( i_category = 'Men' AND ( i_color = 'indian' OR i_color = 'dark' ) AND ( i_units = 'Oz' OR i_units = 'Lb' ) AND ( i_size = 'extra large' OR i_size = 'small' ) ) OR ( i_category = 'Men' AND ( i_color = 'peach' OR i_color = 'purple' ) AND ( i_units = 'Tbl' OR i_units = 'Bunch' ) AND ( i_size = 'economy' OR i_size = 'petite' ) ) ) ) OR ( i_manufact = i1.i_manufact AND ( ( i_category = 'Women' AND ( i_color = 'orchid' OR i_color = 'peru' ) AND ( i_units = 'Carton' OR i_units = 'Cup' ) AND ( i_size = 'economy' OR i_size = 'petite' ) ) OR ( i_category = 'Women' AND ( i_color = 'violet' OR i_color = 'papaya' ) AND ( i_units = 'Ounce' OR i_units = 'Box' ) AND ( i_size = 'large' OR i_size = 'N/A' ) ) OR ( i_category = 'Men' AND ( i_color = 'drab' OR i_color = 'grey' ) AND ( i_units = 'Each' OR i_units = 'N/A' ) AND ( i_size = 'extra large' OR i_size = 'small' ) ) OR ( i_category = 'Men' AND ( i_color = 'chocolate' OR i_color = 'antique' ) AND ( i_units = 'Dram' OR i_units = 'Gram' ) AND ( i_size = 'economy' OR i_size = 'petite' ) ) ) )) > 0 ORDER BY i_product_name LIMIT 100
SELECT dt.d_year, item.i_category_id, item.i_category, sum(ss_ext_sales_price) FROM tpcds.date_dim dt, tpcds.store_sales, tpcds.item WHERE dt.d_date_sk = store_sales.ss_sold_date_sk AND store_sales.ss_item_sk = item.i_item_sk AND item.i_manager_id = 1 AND dt.d_moy = 12 AND dt.d_year = 2000 GROUP BY dt.d_year, item.i_category_id, item.i_category ORDER BY sum(ss_ext_sales_price) DESC, dt.d_year, item.i_category_id, item.i_category LIMIT 100
SELECT s_store_name, s_store_id, sum(CASE WHEN ( d_day_name = 'Sunday' ) THEN ss_sales_price ELSE NULL END) sun_sales, sum(CASE WHEN ( d_day_name = 'Monday' ) THEN ss_sales_price ELSE NULL END) mon_sales, sum(CASE WHEN ( d_day_name = 'Tuesday' ) THEN ss_sales_price ELSE NULL END) tue_sales, sum(CASE WHEN ( d_day_name = 'Wednesday' ) THEN ss_sales_price ELSE NULL END) wed_sales, sum(CASE WHEN ( d_day_name = 'Thursday' ) THEN ss_sales_price ELSE NULL END) thu_sales, sum(CASE WHEN ( d_day_name = 'Friday' ) THEN ss_sales_price ELSE NULL END) fri_sales, sum(CASE WHEN ( d_day_name = 'Saturday' ) THEN ss_sales_price ELSE NULL END) sat_sales FROM tpcds.date_dim, tpcds.store_sales, tpcds.store WHERE d_date_sk = ss_sold_date_sk AND s_store_sk = ss_store_sk AND s_gmt_offset = -5 AND d_year = 2002 GROUP BY s_store_name, s_store_id ORDER BY s_store_name, s_store_id, sun_sales, mon_sales, tue_sales, wed_sales, thu_sales, fri_sales, sat_sales LIMIT 100
SELECT asceding.rnk, i1.i_product_name best_performing, i2.i_product_name worst_performing FROM (SELECT * FROM (SELECT item_sk, rank() OVER ( ORDER BY rank_col ASC) rnk FROM (SELECT ss_item_sk item_sk, avg(ss_net_profit) rank_col FROM tpcds.store_sales ss1 WHERE ss_store_sk = 4 GROUP BY ss_item_sk HAVING avg(ss_net_profit) > 0.9 * (SELECT avg(ss_net_profit) rank_col FROM tpcds.store_sales WHERE ss_store_sk = 4 AND ss_cdemo_sk IS NULL GROUP BY ss_store_sk))V1) V11 WHERE rnk < 11) asceding, (SELECT * FROM (SELECT item_sk, rank() OVER ( ORDER BY rank_col DESC) rnk FROM (SELECT ss_item_sk item_sk, avg(ss_net_profit) rank_col FROM tpcds.store_sales ss1 WHERE ss_store_sk = 4 GROUP BY ss_item_sk HAVING avg(ss_net_profit) > 0.9 * (SELECT avg(ss_net_profit) rank_col FROM tpcds.store_sales WHERE ss_store_sk = 4 AND ss_cdemo_sk IS NULL GROUP BY ss_store_sk))V2) V21 WHERE rnk < 11) descending, item i1, item i2 WHERE asceding.rnk = descending.rnk AND i1.i_item_sk = asceding.item_sk AND i2.i_item_sk = descending.item_sk ORDER BY asceding.rnk LIMIT 100
SELECT ca_zip, ca_state, sum(ws_sales_price) FROM tpcds.web_sales, tpcds.customer, tpcds.customer_address, tpcds.date_dim, tpcds.item WHERE ws_bill_customer_sk = c_customer_sk AND c_current_addr_sk = ca_address_sk AND ws_item_sk = i_item_sk AND ( substr(ca_zip, 1, 5) IN ( '85669', '86197', '88274', '83405', '86475', '85392', '85460', '80348', '81792' ) OR i_item_id IN (SELECT i_item_id FROM tpcds.item WHERE i_item_sk IN ( 2, 3, 5, 7, 11, 13, 17, 19, 23, 29 )) ) AND ws_sold_date_sk = d_date_sk AND d_qoy = 1 AND d_year = 2000 GROUP BY ca_zip, ca_state ORDER BY ca_zip, ca_state LIMIT 100
SELECT c_last_name, c_first_name, ca_city, bought_city, ss_ticket_number, amt, profit FROM (SELECT ss_ticket_number, ss_customer_sk, ca_city bought_city, sum(ss_coupon_amt) amt, sum(ss_net_profit) profit FROM tpcds.store_sales, tpcds.date_dim, tpcds.store, tpcds.household_demographics, tpcds.customer_address 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 store_sales.ss_addr_sk = customer_address.ca_address_sk AND ( household_demographics.hd_dep_count = 6 OR household_demographics.hd_vehicle_count = 0 ) AND date_dim.d_dow IN ( 6, 0 ) AND date_dim.d_year IN ( 2000, 2000 + 1, 2000 + 2 ) AND store.s_city IN ( 'Midway', 'Fairview', 'Fairview', 'Fairview', 'Fairview' ) GROUP BY ss_ticket_number, ss_customer_sk, ss_addr_sk, ca_city) dn, customer, customer_address current_addr WHERE ss_customer_sk = c_customer_sk AND customer.c_current_addr_sk = current_addr.ca_address_sk AND current_addr.ca_city <> bought_city ORDER BY c_last_name, c_first_name, ca_city, bought_city, ss_ticket_number LIMIT 100
WITH v1 AS (SELECT i_category, i_brand, s_store_name, s_company_name, d_year, d_moy, sum(ss_sales_price) sum_sales, avg(sum(ss_sales_price)) OVER ( partition BY i_category, i_brand, s_store_name, s_company_name, d_year) avg_monthly_sales, rank() OVER ( partition BY i_category, i_brand, s_store_name, s_company_name ORDER BY d_year, d_moy) rn FROM tpcds.item, tpcds.store_sales, tpcds.date_dim, tpcds.store WHERE ss_item_sk = i_item_sk AND ss_sold_date_sk = d_date_sk AND ss_store_sk = s_store_sk AND ( d_year = 1999 OR ( d_year = 1999 - 1 AND d_moy = 12 ) OR ( d_year = 1999 + 1 AND d_moy = 1 ) ) GROUP BY i_category, i_brand, s_store_name, s_company_name, d_year, d_moy), v2 AS (SELECT v1.i_category, v1.d_year, v1.d_moy, v1.avg_monthly_sales, v1.sum_sales, v1_lag.sum_sales psum, v1_lead.sum_sales nsum FROM v1, v1 v1_lag, v1 v1_lead WHERE v1.i_category = v1_lag.i_category AND v1.i_category = v1_lead.i_category AND v1.i_brand = v1_lag.i_brand AND v1.i_brand = v1_lead.i_brand AND v1.s_store_name = v1_lag.s_store_name AND v1.s_store_name = v1_lead.s_store_name AND v1.s_company_name = v1_lag.s_company_name AND v1.s_company_name = v1_lead.s_company_name AND v1.rn = v1_lag.rn + 1 AND v1.rn = v1_lead.rn - 1) SELECT * FROM v2 WHERE d_year = 1999 AND avg_monthly_sales > 0 AND CASE WHEN avg_monthly_sales > 0 THEN abs(sum_sales - avg_monthly_sales) / avg_monthly_sales ELSE NULL END > 0.1 ORDER BY sum_sales - avg_monthly_sales, 3 LIMIT 100
SELECT sum (ss_quantity) FROM tpcds.store_sales, tpcds.store, tpcds.customer_demographics, tpcds.customer_address, tpcds.date_dim WHERE s_store_sk = ss_store_sk AND ss_sold_date_sk = d_date_sk AND d_year = 1999 AND ( ( cd_demo_sk = ss_cdemo_sk AND cd_marital_status = 'W' AND cd_education_status = 'Secondary' AND ss_sales_price BETWEEN 100.00 AND 150.00 ) OR ( cd_demo_sk = ss_cdemo_sk AND cd_marital_status = 'M' AND cd_education_status = 'Advanced Degree' AND ss_sales_price BETWEEN 50.00 AND 100.00 ) OR ( cd_demo_sk = ss_cdemo_sk AND cd_marital_status = 'D' AND cd_education_status = '2 yr Degree' AND ss_sales_price BETWEEN 150.00 AND 200.00 ) ) AND ( ( ss_addr_sk = ca_address_sk AND ca_country = 'United States' AND ca_state IN ( 'TX', 'NE', 'MO' ) AND ss_net_profit BETWEEN 0 AND 2000 ) OR ( ss_addr_sk = ca_address_sk AND ca_country = 'United States' AND ca_state IN ( 'CO', 'TN', 'ND' ) AND ss_net_profit BETWEEN 150 AND 3000 ) OR ( ss_addr_sk = ca_address_sk AND ca_country = 'United States' AND ca_state IN ( 'OK', 'PA', 'CA' ) AND ss_net_profit BETWEEN 50 AND 25000 ) )
select channel, item, return_ratio, return_rank, currency_rank FROM ( SELECT 'web' AS channel, web.item, web.return_ratio, web.return_rank, web.currency_rank FROM (SELECT item, return_ratio, currency_ratio, rank() OVER ( ORDER BY return_ratio) AS return_rank, rank() OVER ( ORDER BY currency_ratio) AS currency_rank FROM (SELECT ws.ws_item_sk AS item, ( sum(coalesce(wr.wr_return_quantity, 0)) / sum(coalesce(ws.ws_quantity, 0)) ) AS return_ratio, ( sum(coalesce(wr.wr_return_amt, 0)) / sum(coalesce(ws.ws_net_paid, 0)) ) AS currency_ratio FROM tpcds.web_sales ws LEFT OUTER JOIN tpcds.web_returns wr ON ( ws.ws_order_number = wr.wr_order_number AND ws.ws_item_sk = wr.wr_item_sk ), date_dim WHERE wr.wr_return_amt > 10000 AND ws.ws_net_profit > 1 AND ws.ws_net_paid > 0 AND ws.ws_quantity > 0 AND ws_sold_date_sk = d_date_sk AND d_year = 1999 AND d_moy = 12 GROUP BY ws.ws_item_sk) in_web) web WHERE ( web.return_rank <= 10 OR web.currency_rank <= 10 ) UNION ALL SELECT 'catalog' AS channel, catalog.item, catalog.return_ratio, catalog.return_rank, catalog.currency_rank FROM (SELECT item, return_ratio, currency_ratio, rank() OVER ( ORDER BY return_ratio) AS return_rank, rank() OVER ( ORDER BY currency_ratio) AS currency_rank FROM (SELECT cs.cs_item_sk AS item, ( sum(coalesce(cr.cr_return_quantity, 0)) / sum(coalesce(cs.cs_quantity, 0)) ) AS return_ratio, ( sum(coalesce(cr.cr_return_amount, 0)) / sum(coalesce(cs.cs_net_paid, 0)) ) AS currency_ratio FROM tpcds.catalog_sales cs LEFT OUTER JOIN tpcds.catalog_returns cr ON ( cs.cs_order_number = cr.cr_order_number AND cs.cs_item_sk = cr.cr_item_sk ), date_dim WHERE cr.cr_return_amount > 10000 AND cs.cs_net_profit > 1 AND cs.cs_net_paid > 0 AND cs.cs_quantity > 0 AND cs_sold_date_sk = d_date_sk AND d_year = 1999 AND d_moy = 12 GROUP BY cs.cs_item_sk) in_cat) catalog WHERE ( catalog.return_rank <= 10 OR catalog.currency_rank <= 10 ) UNION ALL SELECT 'store' AS channel, store.item, store.return_ratio, store.return_rank, store.currency_rank FROM (SELECT item, return_ratio, currency_ratio, rank() OVER ( ORDER BY return_ratio) AS return_rank, rank() OVER ( ORDER BY currency_ratio) AS currency_rank FROM (SELECT sts.ss_item_sk AS item, ( sum(coalesce(sr.sr_return_quantity, 0)) / sum(coalesce(sts.ss_quantity, 0)) ) AS return_ratio, ( sum(coalesce(sr.sr_return_amt, 0)) / sum(coalesce(sts.ss_net_paid, 0)) ) AS currency_ratio FROM tpcds.store_sales sts LEFT OUTER JOIN tpcds.store_returns sr ON ( sts.ss_ticket_number = sr.sr_ticket_number AND sts.ss_item_sk = sr.sr_item_sk ), date_dim WHERE sr.sr_return_amt > 10000 AND sts.ss_net_profit > 1 AND sts.ss_net_paid > 0 AND sts.ss_quantity > 0 AND ss_sold_date_sk = d_date_sk AND d_year = 1999 AND d_moy = 12 GROUP BY sts.ss_item_sk) in_store) store WHERE ( store.return_rank <= 10 OR store.currency_rank <= 10 ) ) ORDER BY 1, 4, 5 LIMIT 100
SELECT s_store_name, s_company_id, s_street_number, s_street_name, s_street_type, s_suite_number, s_city, s_county, s_state, s_zip, sum(CASE WHEN ( sr_returned_date_sk - ss_sold_date_sk <= 30 ) THEN 1 ELSE 0 END) AS days_30, sum(CASE WHEN ( sr_returned_date_sk - ss_sold_date_sk > 30 ) AND ( sr_returned_date_sk - ss_sold_date_sk <= 60 ) THEN 1 ELSE 0 END) AS days_31_60, sum(CASE WHEN ( sr_returned_date_sk - ss_sold_date_sk > 60 ) AND ( sr_returned_date_sk - ss_sold_date_sk <= 90 ) THEN 1 ELSE 0 END) AS days_61_90, sum(CASE WHEN ( sr_returned_date_sk - ss_sold_date_sk > 90 ) AND ( sr_returned_date_sk - ss_sold_date_sk <= 120 ) THEN 1 ELSE 0 END) AS days_91_120, sum(CASE WHEN ( sr_returned_date_sk - ss_sold_date_sk > 120 ) THEN 1 ELSE 0 END) AS days_over_120 FROM tpcds.store_sales, tpcds.store_returns, tpcds.store, tpcds.date_dim d1, tpcds.date_dim d2 WHERE d2.d_year = 2002 AND d2.d_moy = 9 AND ss_ticket_number = sr_ticket_number AND ss_item_sk = sr_item_sk AND ss_sold_date_sk = d1.d_date_sk AND sr_returned_date_sk = d2.d_date_sk AND ss_customer_sk = sr_customer_sk AND ss_store_sk = s_store_sk GROUP BY s_store_name, s_company_id, s_street_number, s_street_name, s_street_type, s_suite_number, s_city, s_county, s_state, s_zip ORDER BY s_store_name, s_company_id, s_street_number, s_street_name, s_street_type, s_suite_number, s_city, s_county, s_state, s_zip LIMIT 100
WITH web_v1 AS ( SELECT ws_item_sk item_sk, d_date, sum(sum(ws_sales_price)) OVER (partition BY ws_item_sk ORDER BY d_date rows BETWEEN UNBOUNDED PRECEDING AND CURRENT row) cume_sales FROM tpcds.web_sales , tpcds.date_dim WHERE ws_sold_date_sk=d_date_sk AND d_month_seq BETWEEN 1192 AND 1192+11 AND ws_item_sk IS NOT NULL GROUP BY ws_item_sk, d_date), store_v1 AS ( SELECT ss_item_sk item_sk, d_date, sum(sum(ss_sales_price)) OVER (partition BY ss_item_sk ORDER BY d_date rows BETWEEN UNBOUNDED PRECEDING AND CURRENT row) cume_sales FROM tpcds.store_sales , tpcds.date_dim WHERE ss_sold_date_sk=d_date_sk AND d_month_seq BETWEEN 1192 AND 1192+11 AND ss_item_sk IS NOT NULL GROUP BY ss_item_sk, d_date) SELECT * FROM ( SELECT item_sk , d_date , web_sales , store_sales , max(web_sales) OVER (partition BY item_sk ORDER BY d_date rows BETWEEN UNBOUNDED PRECEDING AND CURRENT row) web_cumulative , max(store_sales) OVER (partition BY item_sk ORDER BY d_date rows BETWEEN UNBOUNDED PRECEDING AND CURRENT row) store_cumulative FROM ( SELECT CASE WHEN web.item_sk IS NOT NULL THEN web.item_sk ELSE store.item_sk END item_sk , CASE WHEN web.d_date IS NOT NULL THEN web.d_date ELSE store.d_date END d_date , web.cume_sales web_sales , store.cume_sales store_sales FROM web_v1 web FULL OUTER JOIN store_v1 store ON ( web.item_sk = store.item_sk AND web.d_date = store.d_date) )x )y WHERE web_cumulative > store_cumulative ORDER BY item_sk , d_date LIMIT 100
SELECT dt.d_year, item.i_brand_id brand_id, item.i_brand brand, sum(ss_ext_sales_price) ext_price FROM tpcds.date_dim dt, tpcds.store_sales, tpcds.item WHERE dt.d_date_sk = store_sales.ss_sold_date_sk AND store_sales.ss_item_sk = item.i_item_sk AND item.i_manager_id = 1 AND dt.d_moy = 11 AND dt.d_year = 1999 GROUP BY dt.d_year, item.i_brand, item.i_brand_id ORDER BY dt.d_year, ext_price DESC, brand_id LIMIT 100
SELECT * FROM (SELECT i_manufact_id, sum(ss_sales_price) sum_sales, avg(sum(ss_sales_price)) OVER ( partition BY i_manufact_id) avg_quarterly_sales FROM tpcds.item, tpcds.store_sales, tpcds.date_dim, tpcds.store WHERE ss_item_sk = i_item_sk AND ss_sold_date_sk = d_date_sk AND ss_store_sk = s_store_sk AND d_month_seq IN ( 1199, 1199 + 1, 1199 + 2, 1199 + 3, 1199 + 4, 1199 + 5, 1199 + 6, 1199 + 7, 1199 + 8, 1199 + 9, 1199 + 10, 1199 + 11 ) AND ( ( i_category IN ( 'Books', 'Children', 'Electronics' ) AND i_class IN ( 'personal', 'portable', 'reference', 'self-help' ) AND i_brand IN ( 'scholaramalgamalg #14', 'scholaramalgamalg #7' , 'exportiunivamalg #9', 'scholaramalgamalg #9' ) ) OR ( i_category IN ( 'Women', 'Music', 'Men' ) AND i_class IN ( 'accessories', 'classical', 'fragrances', 'pants' ) AND i_brand IN ( 'amalgimporto #1', 'edu packscholar #1', 'exportiimporto #1', 'importoamalg #1' ) ) ) GROUP BY i_manufact_id, d_qoy) tmp1 WHERE CASE WHEN avg_quarterly_sales > 0 THEN abs (sum_sales - avg_quarterly_sales) / avg_quarterly_sales ELSE NULL END > 0.1 ORDER BY avg_quarterly_sales, sum_sales, i_manufact_id LIMIT 100
WITH my_customers AS (SELECT DISTINCT c_customer_sk, c_current_addr_sk FROM (SELECT cs_sold_date_sk sold_date_sk, cs_bill_customer_sk customer_sk, cs_item_sk item_sk FROM tpcds.catalog_sales UNION ALL SELECT ws_sold_date_sk sold_date_sk, ws_bill_customer_sk customer_sk, ws_item_sk item_sk FROM tpcds.web_sales) cs_or_ws_sales, item, date_dim, customer WHERE sold_date_sk = d_date_sk AND item_sk = i_item_sk AND i_category = 'Sports' AND i_class = 'fitness' AND c_customer_sk = cs_or_ws_sales.customer_sk AND d_moy = 5 AND d_year = 2000), my_revenue AS (SELECT c_customer_sk, sum(ss_ext_sales_price) AS revenue FROM my_customers, tpcds.store_sales, tpcds.customer_address, tpcds.store, tpcds.date_dim WHERE c_current_addr_sk = ca_address_sk AND ca_county = s_county AND ca_state = s_state AND ss_sold_date_sk = d_date_sk AND c_customer_sk = ss_customer_sk AND d_month_seq BETWEEN (SELECT DISTINCT d_month_seq + 1 FROM tpcds.date_dim WHERE d_year = 2000 AND d_moy = 5) AND (SELECT DISTINCT d_month_seq + 3 FROM tpcds.date_dim WHERE d_year = 2000 AND d_moy = 5) GROUP BY c_customer_sk), segments AS (SELECT Floor(revenue / 50) AS segment FROM my_revenue) SELECT segment, count(*) AS num_customers, segment * 50 AS segment_base FROM segments GROUP BY segment ORDER BY segment, num_customers LIMIT 100
SELECT i_brand_id brand_id, i_brand brand, sum(ss_ext_sales_price) ext_price FROM tpcds.date_dim, tpcds.store_sales, tpcds.item WHERE d_date_sk = ss_sold_date_sk AND ss_item_sk = i_item_sk AND i_manager_id = 33 AND d_moy = 12 AND d_year = 1998 GROUP BY i_brand, i_brand_id ORDER BY ext_price DESC, i_brand_id LIMIT 100
WITH ss AS (SELECT i_item_id, sum(ss_ext_sales_price) total_sales FROM tpcds.store_sales, tpcds.date_dim, tpcds.customer_address, tpcds.item WHERE i_item_id IN (SELECT i_item_id FROM tpcds.item WHERE i_color IN ( 'firebrick', 'rosy', 'white' ) ) AND ss_item_sk = i_item_sk AND ss_sold_date_sk = d_date_sk AND d_year = 1998 AND d_moy = 3 AND ss_addr_sk = ca_address_sk AND ca_gmt_offset = -6 GROUP BY i_item_id), cs AS (SELECT i_item_id, sum(cs_ext_sales_price) total_sales FROM tpcds.catalog_sales, tpcds.date_dim, tpcds.customer_address, tpcds.item WHERE i_item_id IN (SELECT i_item_id FROM tpcds.item WHERE i_color IN ( 'firebrick', 'rosy', 'white' ) ) AND cs_item_sk = i_item_sk AND cs_sold_date_sk = d_date_sk AND d_year = 1998 AND d_moy = 3 AND cs_bill_addr_sk = ca_address_sk AND ca_gmt_offset = -6 GROUP BY i_item_id), ws AS (SELECT i_item_id, sum(ws_ext_sales_price) total_sales FROM tpcds.web_sales, tpcds.date_dim, tpcds.customer_address, tpcds.item WHERE i_item_id IN (SELECT i_item_id FROM tpcds.item WHERE i_color IN ( 'firebrick', 'rosy', 'white' ) ) AND ws_item_sk = i_item_sk AND ws_sold_date_sk = d_date_sk AND d_year = 1998 AND d_moy = 3 AND ws_bill_addr_sk = ca_address_sk AND ca_gmt_offset = -6 GROUP BY i_item_id) SELECT i_item_id, sum(total_sales) total_sales FROM (SELECT * FROM ss UNION ALL SELECT * FROM cs UNION ALL SELECT * FROM ws) tmp1 GROUP BY i_item_id ORDER BY total_sales LIMIT 100
with v1 as( select i_category, i_brand, cc_name, d_year, d_moy, sum(cs_sales_price) sum_sales, avg(sum(cs_sales_price)) over (partition by i_category, i_brand, cc_name, d_year) avg_monthly_sales, rank() over (partition by i_category, i_brand, cc_name order by d_year, d_moy) rn from item, catalog_sales, date_dim, call_center where cs_item_sk = i_item_sk and cs_sold_date_sk = d_date_sk and cc_call_center_sk= cs_call_center_sk and ( d_year = 2000 or ( d_year = 2000-1 and d_moy =12) or ( d_year = 2000+1 and d_moy =1) ) group by i_category, i_brand, cc_name , d_year, d_moy), v2 as( select v1.cc_name ,v1.d_year, v1.d_moy ,v1.avg_monthly_sales ,v1.sum_sales, v1_lag.sum_sales psum, v1_lead.sum_sales nsum from v1, v1 v1_lag, v1 v1_lead where v1.i_category = v1_lag.i_category and v1.i_category = v1_lead.i_category and v1.i_brand = v1_lag.i_brand and v1.i_brand = v1_lead.i_brand and v1. cc_name = v1_lag. cc_name and v1. cc_name = v1_lead. cc_name and v1.rn = v1_lag.rn + 1 and v1.rn = v1_lead.rn - 1) select * from v2 where d_year = 2000 and avg_monthly_sales > 0 and case when avg_monthly_sales > 0 then abs(sum_sales - avg_monthly_sales) / avg_monthly_sales else null end > 0.1 order by sum_sales - avg_monthly_sales, nsum LIMIT 100
with wss as (select d_week_seq, ss_store_sk, sum(case when (d_day_name='Sunday') then ss_sales_price else null end) sun_sales, sum(case when (d_day_name='Monday') then ss_sales_price else null end) mon_sales, sum(case when (d_day_name='Tuesday') then ss_sales_price else null end) tue_sales, sum(case when (d_day_name='Wednesday') then ss_sales_price else null end) wed_sales, sum(case when (d_day_name='Thursday') then ss_sales_price else null end) thu_sales, sum(case when (d_day_name='Friday') then ss_sales_price else null end) fri_sales, sum(case when (d_day_name='Saturday') then ss_sales_price else null end) sat_sales from store_sales,date_dim where d_date_sk = ss_sold_date_sk group by d_week_seq,ss_store_sk ) select s_store_name1,s_store_id1,d_week_seq1 ,sun_sales1/sun_sales2,mon_sales1/mon_sales2 ,tue_sales1/tue_sales2,wed_sales1/wed_sales2,thu_sales1/thu_sales2 ,fri_sales1/fri_sales2,sat_sales1/sat_sales2 from (select s_store_name s_store_name1,wss.d_week_seq d_week_seq1 ,s_store_id s_store_id1,sun_sales sun_sales1 ,mon_sales mon_sales1,tue_sales tue_sales1 ,wed_sales wed_sales1,thu_sales thu_sales1 ,fri_sales fri_sales1,sat_sales sat_sales1 from wss,store,date_dim d where d.d_week_seq = wss.d_week_seq and ss_store_sk = s_store_sk and d_month_seq between 1185 and 1185 + 11) y, (select s_store_name s_store_name2,wss.d_week_seq d_week_seq2 ,s_store_id s_store_id2,sun_sales sun_sales2 ,mon_sales mon_sales2,tue_sales tue_sales2 ,wed_sales wed_sales2,thu_sales thu_sales2 ,fri_sales fri_sales2,sat_sales sat_sales2 from wss,store,date_dim d where d.d_week_seq = wss.d_week_seq and ss_store_sk = s_store_sk and d_month_seq between 1185+ 12 and 1185 + 23) x where s_store_id1=s_store_id2 and d_week_seq1=d_week_seq2-52 order by s_store_name1,s_store_id1,d_week_seq1 LIMIT 100
WITH wss AS (SELECT d_week_seq, ss_store_sk, sum(CASE WHEN ( d_day_name = 'Sunday' ) THEN ss_sales_price ELSE NULL END) sun_sales, sum(CASE WHEN ( d_day_name = 'Monday' ) THEN ss_sales_price ELSE NULL END) mon_sales, sum(CASE WHEN ( d_day_name = 'Tuesday' ) THEN ss_sales_price ELSE NULL END) tue_sales, sum(CASE WHEN ( d_day_name = 'Wednesday' ) THEN ss_sales_price ELSE NULL END) wed_sales, sum(CASE WHEN ( d_day_name = 'Thursday' ) THEN ss_sales_price ELSE NULL END) thu_sales, sum(CASE WHEN ( d_day_name = 'Friday' ) THEN ss_sales_price ELSE NULL END) fri_sales, sum(CASE WHEN ( d_day_name = 'Saturday' ) THEN ss_sales_price ELSE NULL END) sat_sales FROM tpcds.store_sales, tpcds.date_dim WHERE d_date_sk = ss_sold_date_sk GROUP BY d_week_seq, ss_store_sk) SELECT s_store_name1, s_store_id1, d_week_seq1, sun_sales1 / sun_sales2, mon_sales1 / mon_sales2, tue_sales1 / tue_sales2, wed_sales1 / wed_sales2, thu_sales1 / thu_sales2, fri_sales1 / fri_sales2, sat_sales1 / sat_sales2 FROM (SELECT s_store_name s_store_name1, wss.d_week_seq d_week_seq1, s_store_id s_store_id1, sun_sales sun_sales1, mon_sales mon_sales1, tue_sales tue_sales1, wed_sales wed_sales1, thu_sales thu_sales1, fri_sales fri_sales1, sat_sales sat_sales1 FROM wss, tpcds.store, tpcds.date_dim d WHERE d.d_week_seq = wss.d_week_seq AND ss_store_sk = s_store_sk AND d_month_seq BETWEEN 1196 AND 1196 + 11) y, (SELECT s_store_name s_store_name2, wss.d_week_seq d_week_seq2, s_store_id s_store_id2, sun_sales sun_sales2, mon_sales mon_sales2, tue_sales tue_sales2, wed_sales wed_sales2, thu_sales thu_sales2, fri_sales fri_sales2, sat_sales sat_sales2 FROM wss, tpcds.store, tpcds.date_dim d WHERE d.d_week_seq = wss.d_week_seq AND ss_store_sk = s_store_sk AND d_month_seq BETWEEN 1196 + 12 AND 1196 + 23) x WHERE s_store_id1 = s_store_id2 AND d_week_seq1 = d_week_seq2 - 52 ORDER BY s_store_name1, s_store_id1, d_week_seq1 LIMIT 100
WITH ss AS (SELECT i_item_id, sum(ss_ext_sales_price) total_sales FROM tpcds.store_sales, tpcds.date_dim, tpcds.customer_address, tpcds.item WHERE i_item_id IN (SELECT i_item_id FROM tpcds.item WHERE i_category IN ( 'Jewelry' )) AND ss_item_sk = i_item_sk AND ss_sold_date_sk = d_date_sk AND d_year = 1999 AND d_moy = 8 AND ss_addr_sk = ca_address_sk AND ca_gmt_offset = -6 GROUP BY i_item_id), cs AS (SELECT i_item_id, sum(cs_ext_sales_price) total_sales FROM tpcds.catalog_sales, tpcds.date_dim, tpcds.customer_address, tpcds.item WHERE i_item_id IN (SELECT i_item_id FROM tpcds.item WHERE i_category IN ( 'Jewelry' )) AND cs_item_sk = i_item_sk AND cs_sold_date_sk = d_date_sk AND d_year = 1999 AND d_moy = 8 AND cs_bill_addr_sk = ca_address_sk AND ca_gmt_offset = -6 GROUP BY i_item_id), ws AS (SELECT i_item_id, sum(ws_ext_sales_price) total_sales FROM tpcds.web_sales, tpcds.date_dim, tpcds.customer_address, tpcds.item WHERE i_item_id IN (SELECT i_item_id FROM tpcds.item WHERE i_category IN ( 'Jewelry' )) AND ws_item_sk = i_item_sk AND ws_sold_date_sk = d_date_sk AND d_year = 1999 AND d_moy = 8 AND ws_bill_addr_sk = ca_address_sk AND ca_gmt_offset = -6 GROUP BY i_item_id) SELECT i_item_id, sum(total_sales) total_sales FROM (SELECT * FROM ss UNION ALL SELECT * FROM cs UNION ALL SELECT * FROM ws) tmp1 GROUP BY i_item_id ORDER BY i_item_id, total_sales LIMIT 100
SELECT promotions, total, promotions / total * 100 FROM (SELECT sum(ss_ext_sales_price) promotions FROM tpcds.store_sales, tpcds.store, tpcds.promotion, tpcds.date_dim, tpcds.customer, tpcds.customer_address, tpcds.item WHERE ss_sold_date_sk = d_date_sk AND ss_store_sk = s_store_sk AND ss_promo_sk = p_promo_sk AND ss_customer_sk = c_customer_sk AND ca_address_sk = c_current_addr_sk AND ss_item_sk = i_item_sk AND ca_gmt_offset = -7 AND i_category = 'Books' AND ( p_channel_dmail = 'Y' OR p_channel_email = 'Y' OR p_channel_tv = 'Y' ) AND s_gmt_offset = -7 AND d_year = 2001 AND d_moy = 12) promotional_sales, (SELECT sum(ss_ext_sales_price) total FROM tpcds.store_sales, tpcds.store, tpcds.date_dim, tpcds.customer, tpcds.customer_address, tpcds.item WHERE ss_sold_date_sk = d_date_sk AND ss_store_sk = s_store_sk AND ss_customer_sk = c_customer_sk AND ca_address_sk = c_current_addr_sk AND ss_item_sk = i_item_sk AND ca_gmt_offset = -7 AND i_category = 'Books' AND s_gmt_offset = -7 AND d_year = 2001 AND d_moy = 12) all_sales ORDER BY promotions, total LIMIT 100
SELECT substr(w_warehouse_name, 1, 20), sm_type, web_name, sum(CASE WHEN ( ws_ship_date_sk - ws_sold_date_sk <= 30 ) THEN 1 ELSE 0 END) AS days_30, sum(CASE WHEN ( ws_ship_date_sk - ws_sold_date_sk > 30 ) AND ( ws_ship_date_sk - ws_sold_date_sk <= 60 ) THEN 1 ELSE 0 END) AS days_31_60, sum(CASE WHEN ( ws_ship_date_sk - ws_sold_date_sk > 60 ) AND ( ws_ship_date_sk - ws_sold_date_sk <= 90 ) THEN 1 ELSE 0 END) AS days_61_90, sum(CASE WHEN ( ws_ship_date_sk - ws_sold_date_sk > 90 ) AND ( ws_ship_date_sk - ws_sold_date_sk <= 120 ) THEN 1 ELSE 0 END) AS days_91_120, sum(CASE WHEN ( ws_ship_date_sk - ws_sold_date_sk > 120 ) THEN 1 ELSE 0 END) AS days_over_120 FROM tpcds.web_sales, tpcds.warehouse, tpcds.ship_mode, tpcds.web_site, tpcds.date_dim WHERE d_month_seq BETWEEN 1222 AND 1222 + 11 AND ws_ship_date_sk = d_date_sk AND ws_warehouse_sk = w_warehouse_sk AND ws_ship_mode_sk = sm_ship_mode_sk AND ws_web_site_sk = web_site_sk GROUP BY w_warehouse_name, sm_type, web_name ORDER BY 1, sm_type, web_name LIMIT 100
SELECT * FROM (SELECT i_manager_id, sum(ss_sales_price) sum_sales, avg(sum(ss_sales_price)) OVER ( partition BY i_manager_id) avg_monthly_sales FROM tpcds.item, tpcds.store_sales, tpcds.date_dim, tpcds.store WHERE ss_item_sk = i_item_sk AND ss_sold_date_sk = d_date_sk AND ss_store_sk = s_store_sk AND d_month_seq IN ( 1200, 1200 + 1, 1200 + 2, 1200 + 3, 1200 + 4, 1200 + 5, 1200 + 6, 1200 + 7, 1200 + 8, 1200 + 9, 1200 + 10, 1200 + 11 ) AND ( ( i_category IN ( 'Books', 'Children', 'Electronics' ) AND i_class IN ( 'personal', 'portable', 'reference', 'self-help' ) AND i_brand IN ( 'scholaramalgamalg #14', 'scholaramalgamalg #7' , 'exportiunivamalg #9', 'scholaramalgamalg #9' ) ) OR ( i_category IN ( 'Women', 'Music', 'Men' ) AND i_class IN ( 'accessories', 'classical', 'fragrances', 'pants' ) AND i_brand IN ( 'amalgimporto #1', 'edu packscholar #1', 'exportiimporto #1', 'importoamalg #1' ) ) ) GROUP BY i_manager_id, d_moy) tmp1 WHERE CASE WHEN avg_monthly_sales > 0 THEN abs (sum_sales - avg_monthly_sales) / avg_monthly_sales ELSE NULL END > 0.1 ORDER BY i_manager_id, avg_monthly_sales, sum_sales LIMIT 100
WITH cs_ui AS (SELECT cs_item_sk, sum(cs_ext_list_price) AS sale, sum(cr_refunded_cash + cr_reversed_charge + cr_store_credit) AS refund FROM tpcds.catalog_sales, tpcds.catalog_returns WHERE cs_item_sk = cr_item_sk AND cs_order_number = cr_order_number GROUP BY cs_item_sk HAVING sum(cs_ext_list_price) > 2 * sum( cr_refunded_cash + cr_reversed_charge + cr_store_credit)), cross_sales AS (SELECT i_product_name product_name, i_item_sk item_sk, s_store_name store_name, s_zip store_zip, ad1.ca_street_number b_street_number, ad1.ca_street_name b_streen_name, ad1.ca_city b_city, ad1.ca_zip b_zip, ad2.ca_street_number c_street_number, ad2.ca_street_name c_street_name, ad2.ca_city c_city, ad2.ca_zip c_zip, d1.d_year AS syear, d2.d_year AS fsyear, d3.d_year s2year, count(*) cnt, sum(ss_wholesale_cost) s1, sum(ss_list_price) s2, sum(ss_coupon_amt) s3 FROM tpcds.store_sales, tpcds.store_returns, cs_ui, tpcds.date_dim d1, tpcds.date_dim d2, tpcds.date_dim d3, tpcds.store, tpcds.customer, tpcds.customer_demographics cd1, tpcds.customer_demographics cd2, tpcds.promotion, tpcds.household_demographics hd1, tpcds.household_demographics hd2, tpcds.customer_address ad1, tpcds.customer_address ad2, tpcds.income_band ib1, tpcds.income_band ib2, tpcds.item WHERE ss_store_sk = s_store_sk AND ss_sold_date_sk = d1.d_date_sk AND ss_customer_sk = c_customer_sk AND ss_cdemo_sk = cd1.cd_demo_sk AND ss_hdemo_sk = hd1.hd_demo_sk AND ss_addr_sk = ad1.ca_address_sk AND ss_item_sk = i_item_sk AND ss_item_sk = sr_item_sk AND ss_ticket_number = sr_ticket_number AND ss_item_sk = cs_ui.cs_item_sk AND c_current_cdemo_sk = cd2.cd_demo_sk AND c_current_hdemo_sk = hd2.hd_demo_sk AND c_current_addr_sk = ad2.ca_address_sk AND c_first_sales_date_sk = d2.d_date_sk AND c_first_shipto_date_sk = d3.d_date_sk AND ss_promo_sk = p_promo_sk AND hd1.hd_income_band_sk = ib1.ib_income_band_sk AND hd2.hd_income_band_sk = ib2.ib_income_band_sk AND cd1.cd_marital_status <> cd2.cd_marital_status AND i_color IN ( 'cyan', 'peach', 'blush', 'frosted', 'powder', 'orange' ) AND i_current_price BETWEEN 58 AND 58 + 10 AND i_current_price BETWEEN 58 + 1 AND 58 + 15 GROUP BY i_product_name, i_item_sk, s_store_name, s_zip, ad1.ca_street_number, ad1.ca_street_name, ad1.ca_city, ad1.ca_zip, ad2.ca_street_number, ad2.ca_street_name, ad2.ca_city, ad2.ca_zip, d1.d_year, d2.d_year, d3.d_year) SELECT cs1.product_name, cs1.store_name, cs1.store_zip, cs1.b_street_number, cs1.b_streen_name, cs1.b_city, cs1.b_zip, cs1.c_street_number, cs1.c_street_name, cs1.c_city, cs1.c_zip, cs1.syear, cs1.cnt, cs1.s1, cs1.s2, cs1.s3, cs2.s1 AS s1_, cs2.s2 AS s2_, cs2.s3 AS s3_, cs2.syear AS syear_, cs2.cnt AS cnt_ FROM cross_sales cs1, cross_sales cs2 WHERE cs1.item_sk = cs2.item_sk AND cs1.syear = 2001 AND cs2.syear = 2001 + 1 AND cs2.cnt <= cs1.cnt AND cs1.store_name = cs2.store_name AND cs1.store_zip = cs2.store_zip ORDER BY cs1.product_name, cs1.store_name, cs2.cnt
SELECT s_store_name, i_item_desc, sc.revenue, i_current_price, i_wholesale_cost, i_brand FROM tpcds.store, tpcds.item, (SELECT ss_store_sk, avg(revenue) AS ave FROM (SELECT ss_store_sk, ss_item_sk, sum(ss_sales_price) AS revenue FROM tpcds.store_sales, tpcds.date_dim WHERE ss_sold_date_sk = d_date_sk AND d_month_seq BETWEEN 1199 AND 1199 + 11 GROUP BY ss_store_sk, ss_item_sk) sa GROUP BY ss_store_sk) sb, (SELECT ss_store_sk, ss_item_sk, sum(ss_sales_price) AS revenue FROM tpcds.store_sales, tpcds.date_dim WHERE ss_sold_date_sk = d_date_sk AND d_month_seq BETWEEN 1199 AND 1199 + 11 GROUP BY ss_store_sk, ss_item_sk) sc WHERE sb.ss_store_sk = sc.ss_store_sk AND sc.revenue <= 0.1 * sb.ave AND s_store_sk = sc.ss_store_sk AND i_item_sk = sc.ss_item_sk ORDER BY s_store_name, i_item_desc LIMIT 100
SELECT w_warehouse_name, w_warehouse_sq_ft, w_city, w_county, w_state, w_country, ship_carriers, year1, sum(jan_sales) AS jan_sales, sum(feb_sales) AS feb_sales, sum(mar_sales) AS mar_sales, sum(apr_sales) AS apr_sales, sum(may_sales) AS may_sales, sum(jun_sales) AS jun_sales, sum(jul_sales) AS jul_sales, sum(aug_sales) AS aug_sales, sum(sep_sales) AS sep_sales, sum(oct_sales) AS oct_sales, sum(nov_sales) AS nov_sales, sum(dec_sales) AS dec_sales, sum(jan_sales / w_warehouse_sq_ft) AS jan_sales_per_sq_foot, sum(feb_sales / w_warehouse_sq_ft) AS feb_sales_per_sq_foot, sum(mar_sales / w_warehouse_sq_ft) AS mar_sales_per_sq_foot, sum(apr_sales / w_warehouse_sq_ft) AS apr_sales_per_sq_foot, sum(may_sales / w_warehouse_sq_ft) AS may_sales_per_sq_foot, sum(jun_sales / w_warehouse_sq_ft) AS jun_sales_per_sq_foot, sum(jul_sales / w_warehouse_sq_ft) AS jul_sales_per_sq_foot, sum(aug_sales / w_warehouse_sq_ft) AS aug_sales_per_sq_foot, sum(sep_sales / w_warehouse_sq_ft) AS sep_sales_per_sq_foot, sum(oct_sales / w_warehouse_sq_ft) AS oct_sales_per_sq_foot, sum(nov_sales / w_warehouse_sq_ft) AS nov_sales_per_sq_foot, sum(dec_sales / w_warehouse_sq_ft) AS dec_sales_per_sq_foot, sum(jan_net) AS jan_net, sum(feb_net) AS feb_net, sum(mar_net) AS mar_net, sum(apr_net) AS apr_net, sum(may_net) AS may_net, sum(jun_net) AS jun_net, sum(jul_net) AS jul_net, sum(aug_net) AS aug_net, sum(sep_net) AS sep_net, sum(oct_net) AS oct_net, sum(nov_net) AS nov_net, sum(dec_net) AS dec_net FROM (SELECT w_warehouse_name, w_warehouse_sq_ft, w_city, w_county, w_state, w_country, 'ZOUROS,ZHOU' AS ship_carriers, d_year AS year1, sum(CASE WHEN d_moy = 1 THEN ws_ext_sales_price * ws_quantity ELSE 0 END) AS jan_sales, sum(CASE WHEN d_moy = 2 THEN ws_ext_sales_price * ws_quantity ELSE 0 END) AS feb_sales, sum(CASE WHEN d_moy = 3 THEN ws_ext_sales_price * ws_quantity ELSE 0 END) AS mar_sales, sum(CASE WHEN d_moy = 4 THEN ws_ext_sales_price * ws_quantity ELSE 0 END) AS apr_sales, sum(CASE WHEN d_moy = 5 THEN ws_ext_sales_price * ws_quantity ELSE 0 END) AS may_sales, sum(CASE WHEN d_moy = 6 THEN ws_ext_sales_price * ws_quantity ELSE 0 END) AS jun_sales, sum(CASE WHEN d_moy = 7 THEN ws_ext_sales_price * ws_quantity ELSE 0 END) AS jul_sales, sum(CASE WHEN d_moy = 8 THEN ws_ext_sales_price * ws_quantity ELSE 0 END) AS aug_sales, sum(CASE WHEN d_moy = 9 THEN ws_ext_sales_price * ws_quantity ELSE 0 END) AS sep_sales, sum(CASE WHEN d_moy = 10 THEN ws_ext_sales_price * ws_quantity ELSE 0 END) AS oct_sales, sum(CASE WHEN d_moy = 11 THEN ws_ext_sales_price * ws_quantity ELSE 0 END) AS nov_sales, sum(CASE WHEN d_moy = 12 THEN ws_ext_sales_price * ws_quantity ELSE 0 END) AS dec_sales, sum(CASE WHEN d_moy = 1 THEN ws_net_paid_inc_ship * ws_quantity ELSE 0 END) AS jan_net, sum(CASE WHEN d_moy = 2 THEN ws_net_paid_inc_ship * ws_quantity ELSE 0 END) AS feb_net, sum(CASE WHEN d_moy = 3 THEN ws_net_paid_inc_ship * ws_quantity ELSE 0 END) AS mar_net, sum(CASE WHEN d_moy = 4 THEN ws_net_paid_inc_ship * ws_quantity ELSE 0 END) AS apr_net, sum(CASE WHEN d_moy = 5 THEN ws_net_paid_inc_ship * ws_quantity ELSE 0 END) AS may_net, sum(CASE WHEN d_moy = 6 THEN ws_net_paid_inc_ship * ws_quantity ELSE 0 END) AS jun_net, sum(CASE WHEN d_moy = 7 THEN ws_net_paid_inc_ship * ws_quantity ELSE 0 END) AS jul_net, sum(CASE WHEN d_moy = 8 THEN ws_net_paid_inc_ship * ws_quantity ELSE 0 END) AS aug_net, sum(CASE WHEN d_moy = 9 THEN ws_net_paid_inc_ship * ws_quantity ELSE 0 END) AS sep_net, sum(CASE WHEN d_moy = 10 THEN ws_net_paid_inc_ship * ws_quantity ELSE 0 END) AS oct_net, sum(CASE WHEN d_moy = 11 THEN ws_net_paid_inc_ship * ws_quantity ELSE 0 END) AS nov_net, sum(CASE WHEN d_moy = 12 THEN ws_net_paid_inc_ship * ws_quantity ELSE 0 END) AS dec_net FROM tpcds.web_sales, tpcds.warehouse, tpcds.date_dim, tpcds.time_dim, tpcds.ship_mode WHERE ws_warehouse_sk = w_warehouse_sk AND ws_sold_date_sk = d_date_sk AND ws_sold_time_sk = t_time_sk AND ws_ship_mode_sk = sm_ship_mode_sk AND d_year = 1998 AND t_time BETWEEN 7249 AND 7249 + 28800 AND sm_carrier IN ( 'ZOUROS', 'ZHOU' ) GROUP BY w_warehouse_name, w_warehouse_sq_ft, w_city, w_county, w_state, w_country, d_year UNION ALL SELECT w_warehouse_name, w_warehouse_sq_ft, w_city, w_county, w_state, w_country, 'ZOUROS,ZHOU' AS ship_carriers, d_year AS year1, sum(CASE WHEN d_moy = 1 THEN cs_ext_sales_price * cs_quantity ELSE 0 END) AS jan_sales, sum(CASE WHEN d_moy = 2 THEN cs_ext_sales_price * cs_quantity ELSE 0 END) AS feb_sales, sum(CASE WHEN d_moy = 3 THEN cs_ext_sales_price * cs_quantity ELSE 0 END) AS mar_sales, sum(CASE WHEN d_moy = 4 THEN cs_ext_sales_price * cs_quantity ELSE 0 END) AS apr_sales, sum(CASE WHEN d_moy = 5 THEN cs_ext_sales_price * cs_quantity ELSE 0 END) AS may_sales, sum(CASE WHEN d_moy = 6 THEN cs_ext_sales_price * cs_quantity ELSE 0 END) AS jun_sales, sum(CASE WHEN d_moy = 7 THEN cs_ext_sales_price * cs_quantity ELSE 0 END) AS jul_sales, sum(CASE WHEN d_moy = 8 THEN cs_ext_sales_price * cs_quantity ELSE 0 END) AS aug_sales, sum(CASE WHEN d_moy = 9 THEN cs_ext_sales_price * cs_quantity ELSE 0 END) AS sep_sales, sum(CASE WHEN d_moy = 10 THEN cs_ext_sales_price * cs_quantity ELSE 0 END) AS oct_sales, sum(CASE WHEN d_moy = 11 THEN cs_ext_sales_price * cs_quantity ELSE 0 END) AS nov_sales, sum(CASE WHEN d_moy = 12 THEN cs_ext_sales_price * cs_quantity ELSE 0 END) AS dec_sales, sum(CASE WHEN d_moy = 1 THEN cs_net_paid * cs_quantity ELSE 0 END) AS jan_net, sum(CASE WHEN d_moy = 2 THEN cs_net_paid * cs_quantity ELSE 0 END) AS feb_net, sum(CASE WHEN d_moy = 3 THEN cs_net_paid * cs_quantity ELSE 0 END) AS mar_net, sum(CASE WHEN d_moy = 4 THEN cs_net_paid * cs_quantity ELSE 0 END) AS apr_net, sum(CASE WHEN d_moy = 5 THEN cs_net_paid * cs_quantity ELSE 0 END) AS may_net, sum(CASE WHEN d_moy = 6 THEN cs_net_paid * cs_quantity ELSE 0 END) AS jun_net, sum(CASE WHEN d_moy = 7 THEN cs_net_paid * cs_quantity ELSE 0 END) AS jul_net, sum(CASE WHEN d_moy = 8 THEN cs_net_paid * cs_quantity ELSE 0 END) AS aug_net, sum(CASE WHEN d_moy = 9 THEN cs_net_paid * cs_quantity ELSE 0 END) AS sep_net, sum(CASE WHEN d_moy = 10 THEN cs_net_paid * cs_quantity ELSE 0 END) AS oct_net, sum(CASE WHEN d_moy = 11 THEN cs_net_paid * cs_quantity ELSE 0 END) AS nov_net, sum(CASE WHEN d_moy = 12 THEN cs_net_paid * cs_quantity ELSE 0 END) AS dec_net FROM tpcds.catalog_sales, tpcds.warehouse, tpcds.date_dim, tpcds.time_dim, tpcds.ship_mode WHERE cs_warehouse_sk = w_warehouse_sk AND cs_sold_date_sk = d_date_sk AND cs_sold_time_sk = t_time_sk AND cs_ship_mode_sk = sm_ship_mode_sk AND d_year = 1998 AND t_time BETWEEN 7249 AND 7249 + 28800 AND sm_carrier IN ( 'ZOUROS', 'ZHOU' ) GROUP BY w_warehouse_name, w_warehouse_sq_ft, w_city, w_county, w_state, w_country, d_year) x GROUP BY w_warehouse_name, w_warehouse_sq_ft, w_city, w_county, w_state, w_country, ship_carriers, year1 ORDER BY w_warehouse_name 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, ca_city, bought_city, ss_ticket_number, extended_price, extended_tax, list_price FROM (SELECT ss_ticket_number, ss_customer_sk, ca_city bought_city, sum(ss_ext_sales_price) extended_price, sum(ss_ext_list_price) list_price, sum(ss_ext_tax) extended_tax FROM tpcds.store_sales, tpcds.date_dim, tpcds.store, tpcds.household_demographics, tpcds.customer_address 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 store_sales.ss_addr_sk = customer_address.ca_address_sk AND date_dim.d_dom BETWEEN 1 AND 2 AND ( household_demographics.hd_dep_count = 8 OR household_demographics.hd_vehicle_count = 3 ) AND date_dim.d_year IN ( 1998, 1998 + 1, 1998 + 2 ) AND store.s_city IN ( 'Fairview', 'Midway' ) GROUP BY ss_ticket_number, ss_customer_sk, ss_addr_sk, ca_city) dn, customer, customer_address current_addr WHERE ss_customer_sk = c_customer_sk AND customer.c_current_addr_sk = current_addr.ca_address_sk AND current_addr.ca_city <> bought_city ORDER BY c_last_name, ss_ticket_number LIMIT 100
SELECT cd_gender, cd_marital_status, cd_education_status, count(*) cnt1, cd_purchase_estimate, count(*) cnt2, cd_credit_rating, count(*) cnt3 FROM tpcds.customer c, tpcds.customer_address ca, tpcds.customer_demographics WHERE c.c_current_addr_sk = ca.ca_address_sk AND ca_state IN ( 'KS', 'AZ', 'NE' ) AND cd_demo_sk = c.c_current_cdemo_sk AND EXISTS (SELECT * FROM tpcds.store_sales, tpcds.date_dim WHERE c.c_customer_sk = ss_customer_sk AND ss_sold_date_sk = d_date_sk AND d_year = 2004 AND d_moy BETWEEN 3 AND 3 + 2) AND ( NOT EXISTS (SELECT * FROM tpcds.web_sales, tpcds.date_dim WHERE c.c_customer_sk = ws_bill_customer_sk AND ws_sold_date_sk = d_date_sk AND d_year = 2004 AND d_moy BETWEEN 3 AND 3 + 2) AND NOT EXISTS (SELECT * FROM tpcds.catalog_sales, tpcds.date_dim WHERE c.c_customer_sk = cs_ship_customer_sk AND cs_sold_date_sk = d_date_sk AND d_year = 2004 AND d_moy BETWEEN 3 AND 3 + 2) ) GROUP BY cd_gender, cd_marital_status, cd_education_status, cd_purchase_estimate, cd_credit_rating ORDER BY cd_gender, cd_marital_status, cd_education_status, cd_purchase_estimate, cd_credit_rating LIMIT 100
SELECT sum(ss_net_profit) AS total_sum, s_state, s_county, rank() OVER ( PARTITION BY s_state, s_county ORDER BY sum(ss_net_profit) DESC) AS rank_within_parent FROM tpcds.store_sales, tpcds.date_dim d1, tpcds.store WHERE d1.d_month_seq BETWEEN 1200 AND 1200 + 11 AND d1.d_date_sk = ss_sold_date_sk AND s_store_sk = ss_store_sk AND s_state IN (SELECT s_state FROM (SELECT s_state AS s_state, rank() OVER ( partition BY s_state ORDER BY sum(ss_net_profit) DESC) AS ranking FROM tpcds.store_sales, tpcds.store, tpcds.date_dim WHERE d_month_seq BETWEEN 1200 AND 1200 + 11 AND d_date_sk = ss_sold_date_sk AND s_store_sk = ss_store_sk GROUP BY s_state) tmp1 WHERE ranking <= 5) GROUP BY s_state, s_county ORDER BY s_state, rank_within_parent LIMIT 100
SELECT i_brand_id brand_id, i_brand brand, t_hour, t_minute, sum(ext_price) ext_price FROM tpcds.item, (SELECT ws_ext_sales_price AS ext_price, ws_sold_date_sk AS sold_date_sk, ws_item_sk AS sold_item_sk, ws_sold_time_sk AS time_sk FROM tpcds.web_sales, tpcds.date_dim WHERE d_date_sk = ws_sold_date_sk AND d_moy = 11 AND d_year = 2001 UNION ALL SELECT cs_ext_sales_price AS ext_price, cs_sold_date_sk AS sold_date_sk, cs_item_sk AS sold_item_sk, cs_sold_time_sk AS time_sk FROM tpcds.catalog_sales, tpcds.date_dim WHERE d_date_sk = cs_sold_date_sk AND d_moy = 11 AND d_year = 2001 UNION ALL SELECT ss_ext_sales_price AS ext_price, ss_sold_date_sk AS sold_date_sk, ss_item_sk AS sold_item_sk, ss_sold_time_sk AS time_sk FROM tpcds.store_sales, tpcds.date_dim WHERE d_date_sk = ss_sold_date_sk AND d_moy = 11 AND d_year = 2001) AS tmp, time_dim WHERE sold_item_sk = i_item_sk AND i_manager_id = 1 AND time_sk = t_time_sk AND ( t_meal_time = 'breakfast' OR t_meal_time = 'dinner' ) GROUP BY i_brand, i_brand_id, t_hour, t_minute ORDER BY ext_price DESC, i_brand_id
WITH top_items AS ( SELECT cs_item_sk, cs_warehouse_sk, d_week_seq, sum(CASE WHEN p_promo_sk IS NULL THEN 1 ELSE 0 END) no_promo, sum(CASE WHEN p_promo_sk IS NOT NULL THEN 1 ELSE 0 END) promo, count(*) total_cnt FROM tpcds.catalog_sales JOIN tpcds.inventory ON ( cs_item_sk = inv_item_sk and cs_sold_date_sk = inv_date_sk and cs_warehouse_sk = inv_warehouse_sk ) JOIN tpcds.customer_demographics ON ( cs_bill_cdemo_sk = cd_demo_sk ) JOIN tpcds.household_demographics ON ( cs_bill_hdemo_sk = hd_demo_sk ) JOIN tpcds.date_dim d1 ON ( cs_sold_date_sk = d1.d_date_sk ) LEFT OUTER JOIN tpcds.promotion ON ( cs_promo_sk = p_promo_sk ) LEFT OUTER JOIN tpcds.catalog_returns ON ( cr_item_sk = cs_item_sk AND cr_order_number = cs_order_number ) WHERE inv_quantity_on_hand < cs_quantity AND hd_buy_potential = '501-1000' AND d1.d_year = 2002 AND cd_marital_status = 'M' GROUP BY cs_item_sk, cs_warehouse_sk, d1.d_week_seq ) SELECT i_item_desc, w_warehouse_name, top_items.d_week_seq, no_promo, promo, total_cnt FROM top_items JOIN tpcds.warehouse ON ( w_warehouse_sk = cs_warehouse_sk ) JOIN tpcds.item ON ( i_item_sk = cs_item_sk ) ORDER BY total_cnt DESC, 1, 2, 3 LIMIT 100
SELECT c_last_name, c_first_name, c_salutation, c_preferred_cust_flag, ss_ticket_number, cnt FROM (SELECT ss_ticket_number, ss_customer_sk, count(*) cnt 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 date_dim.d_dom BETWEEN 1 AND 2 AND ( household_demographics.hd_buy_potential = '>10000' OR household_demographics.hd_buy_potential = '0-500' ) AND household_demographics.hd_vehicle_count > 0 AND CASE WHEN household_demographics.hd_vehicle_count > 0 THEN household_demographics.hd_dep_count / household_demographics.hd_vehicle_count ELSE NULL END > 1 AND date_dim.d_year IN ( 2000, 2000 + 1, 2000 + 2 ) AND store.s_county IN ( 'Williamson County', 'Williamson County', 'Williamson County', 'Williamson County' ) GROUP BY ss_ticket_number, ss_customer_sk) dj, customer WHERE ss_customer_sk = c_customer_sk AND cnt BETWEEN 1 AND 5 ORDER BY cnt DESC, c_last_name ASC
WITH year_total AS (SELECT c_customer_id customer_id, c_first_name customer_first_name, c_last_name customer_last_name, d_year AS year1, sum(ss_net_paid) 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 AND d_year IN ( 1999, 1999 + 1 ) GROUP BY c_customer_id, c_first_name, c_last_name, d_year UNION ALL SELECT c_customer_id customer_id, c_first_name customer_first_name, c_last_name customer_last_name, d_year AS year1, sum(ws_net_paid) 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 AND d_year IN ( 1999, 1999 + 1 ) GROUP BY c_customer_id, c_first_name, c_last_name, d_year) SELECT t_s_secyear.customer_id, t_s_secyear.customer_first_name, t_s_secyear.customer_last_name FROM year_total t_s_firstyear, year_total t_s_secyear, year_total t_w_firstyear, year_total t_w_secyear WHERE t_s_secyear.customer_id = t_s_firstyear.customer_id AND t_s_firstyear.customer_id = t_w_secyear.customer_id AND t_s_firstyear.customer_id = t_w_firstyear.customer_id AND t_s_firstyear.sale_type = 's' AND t_w_firstyear.sale_type = 'w' AND t_s_secyear.sale_type = 's' AND t_w_secyear.sale_type = 'w' AND t_s_firstyear.year1 = 1999 AND t_s_secyear.year1 = 1999 + 1 AND t_w_firstyear.year1 = 1999 AND t_w_secyear.year1 = 1999 + 1 AND t_s_firstyear.year_total > 0 AND t_w_firstyear.year_total > 0 AND CASE WHEN t_w_firstyear.year_total > 0 THEN t_w_secyear.year_total / t_w_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 ORDER BY 1, 2, 3 LIMIT 100
WITH all_sales AS (SELECT d_year, i_brand_id, i_class_id, i_category_id, i_manufact_id, sum(sales_cnt) AS sales_cnt, sum(sales_amt) AS sales_amt FROM (SELECT d_year, i_brand_id, i_class_id, i_category_id, i_manufact_id, cs_quantity - coalesce(cr_return_quantity, 0) AS sales_cnt, cs_ext_sales_price - coalesce(cr_return_amount, 0.0) AS sales_amt FROM tpcds.catalog_sales JOIN tpcds.item ON i_item_sk = cs_item_sk JOIN tpcds.date_dim ON d_date_sk = cs_sold_date_sk LEFT JOIN tpcds.catalog_returns ON ( cs_order_number = cr_order_number AND cs_item_sk = cr_item_sk ) WHERE i_category = 'Men' UNION ALL SELECT d_year, i_brand_id, i_class_id, i_category_id, i_manufact_id, ss_quantity - coalesce(sr_return_quantity, 0) AS sales_cnt, ss_ext_sales_price - coalesce(sr_return_amt, 0.0) AS sales_amt FROM tpcds.store_sales JOIN tpcds.item ON i_item_sk = ss_item_sk JOIN tpcds.date_dim ON d_date_sk = ss_sold_date_sk LEFT JOIN tpcds.store_returns ON ( ss_ticket_number = sr_ticket_number AND ss_item_sk = sr_item_sk ) WHERE i_category = 'Men' UNION ALL SELECT d_year, i_brand_id, i_class_id, i_category_id, i_manufact_id, ws_quantity - coalesce(wr_return_quantity, 0) AS sales_cnt, ws_ext_sales_price - coalesce(wr_return_amt, 0.0) AS sales_amt FROM tpcds.web_sales JOIN tpcds.item ON i_item_sk = ws_item_sk JOIN tpcds.date_dim ON d_date_sk = ws_sold_date_sk LEFT JOIN tpcds.web_returns ON ( ws_order_number = wr_order_number AND ws_item_sk = wr_item_sk ) WHERE i_category = 'Men') sales_detail GROUP BY d_year, i_brand_id, i_class_id, i_category_id, i_manufact_id) SELECT prev_yr.d_year AS prev_year, curr_yr.d_year AS year1, curr_yr.i_brand_id, curr_yr.i_class_id, curr_yr.i_category_id, curr_yr.i_manufact_id, prev_yr.sales_cnt AS prev_yr_cnt, curr_yr.sales_cnt AS curr_yr_cnt, curr_yr.sales_cnt - prev_yr.sales_cnt AS sales_cnt_diff, curr_yr.sales_amt - prev_yr.sales_amt AS sales_amt_diff FROM all_sales curr_yr, all_sales prev_yr WHERE curr_yr.i_brand_id = prev_yr.i_brand_id AND curr_yr.i_class_id = prev_yr.i_class_id AND curr_yr.i_category_id = prev_yr.i_category_id AND curr_yr.i_manufact_id = prev_yr.i_manufact_id AND curr_yr.d_year = 2002 AND prev_yr.d_year = 2002 - 1 AND curr_yr.sales_cnt / prev_yr.sales_cnt < 0.9 ORDER BY sales_cnt_diff LIMIT 100
SELECT channel, col_name, d_year, d_qoy, i_category, count(*) sales_cnt, sum(ext_sales_price) sales_amt FROM (SELECT 'store' AS channel, 'ss_hdemo_sk' col_name, d_year, d_qoy, i_category, ss_ext_sales_price ext_sales_price FROM tpcds.store_sales, tpcds.item, tpcds.date_dim WHERE ss_hdemo_sk IS NULL AND ss_sold_date_sk = d_date_sk AND ss_item_sk = i_item_sk UNION ALL SELECT 'web' AS channel, 'ws_ship_hdemo_sk' col_name, d_year, d_qoy, i_category, ws_ext_sales_price ext_sales_price FROM tpcds.web_sales, tpcds.item, tpcds.date_dim WHERE ws_ship_hdemo_sk IS NULL AND ws_sold_date_sk = d_date_sk AND ws_item_sk = i_item_sk UNION ALL SELECT 'catalog' AS channel, 'cs_warehouse_sk' col_name, d_year, d_qoy, i_category, cs_ext_sales_price ext_sales_price FROM tpcds.catalog_sales, tpcds.item, tpcds.date_dim WHERE cs_warehouse_sk IS NULL AND cs_sold_date_sk = d_date_sk AND cs_item_sk = i_item_sk) foo GROUP BY channel, col_name, d_year, d_qoy, i_category ORDER BY channel, col_name, d_year, d_qoy, i_category LIMIT 100
WITH ss AS ( SELECT s_store_sk, sum(ss_ext_sales_price) AS sales, sum(ss_net_profit) AS profit FROM tpcds.store_sales, tpcds.date_dim, tpcds.store WHERE ss_sold_date_sk = d_date_sk AND Cast(d_date AS DATE) BETWEEN Cast('2001-08-16' AS DATE) AND ( Cast('2001-09-15' AS DATE)) AND ss_store_sk = s_store_sk GROUP BY s_store_sk) , sr AS ( SELECT s_store_sk, sum(sr_return_amt) AS returns1, sum(sr_net_loss) AS profit_loss FROM tpcds.store_returns, tpcds.date_dim, tpcds.store WHERE sr_returned_date_sk = d_date_sk AND Cast(d_date AS DATE) BETWEEN cast('2001-08-16' AS date) AND ( Cast('2001-09-15' AS DATE)) AND sr_store_sk = s_store_sk GROUP BY s_store_sk), cs AS ( SELECT cs_call_center_sk, sum(cs_ext_sales_price) AS sales, sum(cs_net_profit) AS profit FROM tpcds.catalog_sales, tpcds.date_dim WHERE cs_sold_date_sk = d_date_sk AND Cast(d_date AS DATE) BETWEEN cast('2001-08-16' AS date) AND ( Cast('2001-09-15' AS DATE)) GROUP BY cs_call_center_sk ), cr AS ( SELECT cr_call_center_sk, sum(cr_return_amount) AS returns1, sum(cr_net_loss) AS profit_loss FROM tpcds.catalog_returns, tpcds.date_dim WHERE cr_returned_date_sk = d_date_sk AND Cast(d_date AS DATE) BETWEEN cast('2001-08-16' AS date) AND ( Cast('2001-09-15' AS DATE)) GROUP BY cr_call_center_sk ), ws AS ( SELECT wp_web_page_sk, sum(ws_ext_sales_price) AS sales, sum(ws_net_profit) AS profit FROM tpcds.web_sales, tpcds.date_dim, tpcds.web_page WHERE ws_sold_date_sk = d_date_sk AND Cast(d_date AS DATE) BETWEEN cast('2001-08-16' AS date) AND ( Cast('2001-09-15' AS DATE)) AND ws_web_page_sk = wp_web_page_sk GROUP BY wp_web_page_sk), wr AS ( SELECT wp_web_page_sk, sum(wr_return_amt) AS returns1, sum(wr_net_loss) AS profit_loss FROM tpcds.web_returns, tpcds.date_dim, tpcds.web_page WHERE wr_returned_date_sk = d_date_sk AND Cast(d_date AS DATE) BETWEEN cast('2001-08-16' AS date) AND ( Cast('2001-09-15' AS DATE)) AND wr_web_page_sk = wp_web_page_sk GROUP BY wp_web_page_sk) SELECT channel , id , sum(sales) AS sales , sum(returns1) AS returns1 , sum(profit) AS profit FROM ( SELECT 'store channel' AS channel , ss.s_store_sk AS id , sales , coalesce(returns1, 0) AS returns1 , (profit - coalesce(profit_loss,0)) AS profit FROM ss LEFT JOIN sr ON ss.s_store_sk = sr.s_store_sk UNION ALL SELECT 'catalog channel' AS channel , cs_call_center_sk AS id , sales , returns1 , (profit - profit_loss) AS profit FROM cs , cr UNION ALL SELECT 'web channel' AS channel , ws.wp_web_page_sk AS id , sales , coalesce(returns1, 0) returns1 , (profit - coalesce(profit_loss,0)) AS profit FROM ws LEFT JOIN wr ON ws.wp_web_page_sk = wr.wp_web_page_sk ) x GROUP BY channel, id ORDER BY channel , id 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
WITH ssr AS ( SELECT s_store_id AS store_id, sum(ss_ext_sales_price) AS sales, sum(coalesce(sr_return_amt, 0)) AS returns1, sum(ss_net_profit - coalesce(sr_net_loss, 0)) AS profit FROM tpcds.store_sales LEFT OUTER JOIN tpcds.store_returns ON ( ss_item_sk = sr_item_sk AND ss_ticket_number = sr_ticket_number), date_dim, store, item, promotion WHERE ss_sold_date_sk = d_date_sk AND Cast(d_date AS DATE) BETWEEN Cast('2000-08-26' AS DATE) AND ( Cast('2001-09-25' AS DATE)) AND ss_store_sk = s_store_sk AND ss_item_sk = i_item_sk AND i_current_price > 50 AND ss_promo_sk = p_promo_sk AND p_channel_tv = 'N' GROUP BY s_store_id) , csr AS ( SELECT cp_catalog_page_id AS catalog_page_id, sum(cs_ext_sales_price) AS sales, sum(coalesce(cr_return_amount, 0)) AS returns1, sum(cs_net_profit - coalesce(cr_net_loss, 0)) AS profit FROM tpcds.catalog_sales LEFT OUTER JOIN tpcds.catalog_returns ON ( cs_item_sk = cr_item_sk AND cs_order_number = cr_order_number), date_dim, catalog_page, item, promotion WHERE cs_sold_date_sk = d_date_sk AND Cast(d_date AS DATE) BETWEEN cast('2000-08-26' AS date) AND ( Cast('2001-09-25' AS DATE)) AND cs_catalog_page_sk = cp_catalog_page_sk AND cs_item_sk = i_item_sk AND i_current_price > 50 AND cs_promo_sk = p_promo_sk AND p_channel_tv = 'N' GROUP BY cp_catalog_page_id) , wsr AS ( SELECT web_site_id, sum(ws_ext_sales_price) AS sales, sum(coalesce(wr_return_amt, 0)) AS returns1, sum(ws_net_profit - coalesce(wr_net_loss, 0)) AS profit FROM tpcds.web_sales LEFT OUTER JOIN tpcds.web_returns ON ( ws_item_sk = wr_item_sk AND ws_order_number = wr_order_number), date_dim, web_site, item, promotion WHERE ws_sold_date_sk = d_date_sk AND Cast(d_date AS DATE) BETWEEN cast('2000-08-26' AS date) AND ( Cast('2001-09-25' AS DATE)) AND ws_web_site_sk = web_site_sk AND ws_item_sk = i_item_sk AND i_current_price > 50 AND ws_promo_sk = p_promo_sk AND p_channel_tv = 'N' GROUP BY web_site_id) SELECT channel , id , sum(sales) AS sales , sum(returns1) AS returns1 , sum(profit) AS profit FROM ( SELECT 'store channel' AS channel , concat('store', store_id) AS id , sales , returns1 , profit FROM ssr UNION ALL SELECT 'catalog channel' AS channel , concat('catalog_page', catalog_page_id) AS id , sales , returns1 , profit FROM csr UNION ALL SELECT 'web channel' AS channel , concat('web_site', web_site_id) AS id , sales , returns1 , profit FROM wsr ) x GROUP BY channel, id ORDER BY channel , id LIMIT 100
WITH customer_total_return AS (SELECT cr_returning_customer_sk AS ctr_customer_sk, ca_state AS ctr_state, sum(cr_return_amt_inc_tax) AS ctr_total_return FROM tpcds.catalog_returns, tpcds.date_dim, tpcds.customer_address WHERE cr_returned_date_sk = d_date_sk AND d_year = 1999 AND cr_returning_addr_sk = ca_address_sk GROUP BY cr_returning_customer_sk, ca_state), high_return AS ( SELECT ctr_state AS hr_state, avg(ctr_total_return) * 1.2 AS hr_limit FROM customer_total_return GROUP BY ctr_state ) SELECT c_customer_id, c_salutation, c_first_name, c_last_name, ca_street_number, ca_street_name, ca_street_type, ca_suite_number, ca_city, ca_county, ca_state, ca_zip, ca_country, ca_gmt_offset, ca_location_type, ctr_total_return FROM customer_total_return, high_return, tpcds.customer_address, tpcds.customer WHERE ctr_state = hr_state AND ctr_customer_sk = c_customer_sk AND ca_address_sk = c_current_addr_sk AND ca_state = 'TX' AND ctr_total_return > hr_limit ORDER BY c_customer_id, c_salutation, c_first_name, c_last_name, ca_street_number, ca_street_name, ca_street_type, ca_suite_number, ca_city, ca_county, ca_state, ca_zip, ca_country, ca_gmt_offset, ca_location_type, ctr_total_return LIMIT 100
SELECT i_item_id , i_item_desc , i_current_price FROM tpcds.item, tpcds.inventory, tpcds.date_dim, tpcds.store_sales WHERE i_current_price BETWEEN 63 AND 63+30 AND inv_item_sk = i_item_sk AND d_date_sk=inv_date_sk AND Cast(d_date AS DATE) BETWEEN Cast('1998-04-27' AS DATE) AND ( Cast('1998-06-27' AS DATE)) AND i_manufact_id IN (57,293,427,320) AND inv_quantity_on_hand BETWEEN 100 AND 500 AND ss_item_sk = i_item_sk GROUP BY i_item_id, i_item_desc, i_current_price ORDER BY i_item_id LIMIT 100
WITH sr_items AS (SELECT i_item_id item_id, sum(sr_return_quantity) sr_item_qty FROM tpcds.store_returns, tpcds.item, tpcds.date_dim WHERE sr_item_sk = i_item_sk AND d_date IN (SELECT d_date FROM tpcds.date_dim WHERE d_week_seq IN (SELECT d_week_seq FROM tpcds.date_dim WHERE d_date IN ( '1999-06-30', '1999-08-28', '1999-11-18' ))) AND sr_returned_date_sk = d_date_sk GROUP BY i_item_id), cr_items AS (SELECT i_item_id item_id, sum(cr_return_quantity) cr_item_qty FROM tpcds.catalog_returns, tpcds.item, tpcds.date_dim WHERE cr_item_sk = i_item_sk AND d_date IN (SELECT d_date FROM tpcds.date_dim WHERE d_week_seq IN (SELECT d_week_seq FROM tpcds.date_dim WHERE d_date IN ( '1999-06-30', '1999-08-28', '1999-11-18' ))) AND cr_returned_date_sk = d_date_sk GROUP BY i_item_id), wr_items AS (SELECT i_item_id item_id, sum(wr_return_quantity) wr_item_qty FROM tpcds.web_returns, tpcds.item, tpcds.date_dim WHERE wr_item_sk = i_item_sk AND d_date IN (SELECT d_date FROM tpcds.date_dim WHERE d_week_seq IN (SELECT d_week_seq FROM tpcds.date_dim WHERE d_date IN ( '1999-06-30', '1999-08-28', '1999-11-18' ))) AND wr_returned_date_sk = d_date_sk GROUP BY i_item_id) SELECT sr_items.item_id, sr_item_qty, sr_item_qty / ( sr_item_qty + cr_item_qty + wr_item_qty ) / 3.0 * 100 sr_dev, cr_item_qty, cr_item_qty / ( sr_item_qty + cr_item_qty + wr_item_qty ) / 3.0 * 100 cr_dev, wr_item_qty, wr_item_qty / ( sr_item_qty + cr_item_qty + wr_item_qty ) / 3.0 * 100 wr_dev, ( sr_item_qty + cr_item_qty + wr_item_qty ) / 3.0 average FROM sr_items, cr_items, wr_items WHERE sr_items.item_id = cr_items.item_id AND sr_items.item_id = wr_items.item_id ORDER BY sr_items.item_id, sr_item_qty LIMIT 100
SELECT c_customer_id AS customer_id, concat(c_last_name, concat(', ', c_first_name)) AS customername FROM tpcds.customer, tpcds.customer_address, tpcds.customer_demographics, tpcds.household_demographics, tpcds.income_band, tpcds.store_returns WHERE ca_city = 'Green Acres' AND c_current_addr_sk = ca_address_sk AND ib_lower_bound >= 54986 AND ib_upper_bound <= 54986 + 50000 AND ib_income_band_sk = hd_income_band_sk AND cd_demo_sk = c_current_cdemo_sk AND hd_demo_sk = c_current_hdemo_sk AND sr_cdemo_sk = cd_demo_sk ORDER BY c_customer_id LIMIT 100
SELECT substr(r_reason_desc, 1, 20), avg(ws_quantity), avg(wr_refunded_cash), avg(wr_fee) FROM tpcds.web_sales, tpcds.web_returns, tpcds.web_page, tpcds.customer_demographics cd1, tpcds.customer_demographics cd2, tpcds.customer_address, tpcds.date_dim, tpcds.reason WHERE ws_web_page_sk = wp_web_page_sk AND ws_item_sk = wr_item_sk AND ws_order_number = wr_order_number AND ws_sold_date_sk = d_date_sk AND d_year = 2001 AND cd1.cd_demo_sk = wr_refunded_cdemo_sk AND cd2.cd_demo_sk = wr_returning_cdemo_sk AND ca_address_sk = wr_refunded_addr_sk AND r_reason_sk = wr_reason_sk AND ( ( cd1.cd_marital_status = 'W' AND cd1.cd_marital_status = cd2.cd_marital_status AND cd1.cd_education_status = 'Primary' AND cd1.cd_education_status = cd2.cd_education_status AND ws_sales_price BETWEEN 100.00 AND 150.00 ) OR ( cd1.cd_marital_status = 'D' AND cd1.cd_marital_status = cd2.cd_marital_status AND cd1.cd_education_status = 'Secondary' AND cd1.cd_education_status = cd2.cd_education_status AND ws_sales_price BETWEEN 50.00 AND 100.00 ) OR ( cd1.cd_marital_status = 'M' AND cd1.cd_marital_status = cd2.cd_marital_status AND cd1.cd_education_status = 'Advanced Degree' AND cd1.cd_education_status = cd2.cd_education_status AND ws_sales_price BETWEEN 150.00 AND 200.00 ) ) AND ( ( ca_country = 'United States' AND ca_state IN ( 'KY', 'ME', 'IL' ) AND ws_net_profit BETWEEN 100 AND 200 ) OR ( ca_country = 'United States' AND ca_state IN ( 'OK', 'NE', 'MN' ) AND ws_net_profit BETWEEN 150 AND 300 ) OR ( ca_country = 'United States' AND ca_state IN ( 'FL', 'WI', 'KS' ) AND ws_net_profit BETWEEN 50 AND 250 ) ) GROUP BY r_reason_desc ORDER BY substr(r_reason_desc, 1, 20), avg(ws_quantity), avg(wr_refunded_cash), avg(wr_fee) LIMIT 100
SELECT sum(ws_net_paid) AS total_sum, i_category, i_class, rank() OVER ( PARTITION BY i_category, i_class ORDER BY sum(ws_net_paid) DESC) AS rank_within_parent FROM tpcds.web_sales, tpcds.date_dim d1, tpcds.item WHERE d1.d_month_seq BETWEEN 1183 AND 1183 + 11 AND d1.d_date_sk = ws_sold_date_sk AND i_item_sk = ws_item_sk GROUP BY i_category, i_class ORDER BY i_category, rank_within_parent LIMIT 100
select count(*) from ((select distinct c_last_name, c_first_name, d_date from store_sales, date_dim, customer where store_sales.ss_sold_date_sk = date_dim.d_date_sk and store_sales.ss_customer_sk = customer.c_customer_sk and d_month_seq between 1212 and 1212+11) except (select distinct c_last_name, c_first_name, d_date from catalog_sales, date_dim, customer where catalog_sales.cs_sold_date_sk = date_dim.d_date_sk and catalog_sales.cs_bill_customer_sk = customer.c_customer_sk and d_month_seq between 1212 and 1212+11) except (select distinct c_last_name, c_first_name, d_date from web_sales, date_dim, customer where web_sales.ws_sold_date_sk = date_dim.d_date_sk and web_sales.ws_bill_customer_sk = customer.c_customer_sk and d_month_seq between 1212 and 1212+11) ) cool_cust
select * FROM (select count(*) h8_30_to_9 FROM tpcds.store_sales, tpcds.household_demographics , tpcds.time_dim, tpcds.store where ss_sold_time_sk = time_dim.t_time_sk and ss_hdemo_sk = household_demographics.hd_demo_sk and ss_store_sk = s_store_sk and time_dim.t_hour = 8 and time_dim.t_minute >= 30 and ((household_demographics.hd_dep_count = -1 and household_demographics.hd_vehicle_count<=-1+2) or (household_demographics.hd_dep_count = 2 and household_demographics.hd_vehicle_count<=2+2) or (household_demographics.hd_dep_count = 3 and household_demographics.hd_vehicle_count<=3+2)) and store.s_store_name = 'ese') s1, (select count(*) h9_to_9_30 FROM tpcds.store_sales, tpcds.household_demographics , tpcds.time_dim, tpcds.store where ss_sold_time_sk = time_dim.t_time_sk and ss_hdemo_sk = household_demographics.hd_demo_sk and ss_store_sk = s_store_sk and time_dim.t_hour = 9 and time_dim.t_minute < 30 and ((household_demographics.hd_dep_count = -1 and household_demographics.hd_vehicle_count<=-1+2) or (household_demographics.hd_dep_count = 2 and household_demographics.hd_vehicle_count<=2+2) or (household_demographics.hd_dep_count = 3 and household_demographics.hd_vehicle_count<=3+2)) and store.s_store_name = 'ese') s2, (select count(*) h9_30_to_10 FROM tpcds.store_sales, tpcds.household_demographics , tpcds.time_dim, tpcds.store where ss_sold_time_sk = time_dim.t_time_sk and ss_hdemo_sk = household_demographics.hd_demo_sk and ss_store_sk = s_store_sk and time_dim.t_hour = 9 and time_dim.t_minute >= 30 and ((household_demographics.hd_dep_count = -1 and household_demographics.hd_vehicle_count<=-1+2) or (household_demographics.hd_dep_count = 2 and household_demographics.hd_vehicle_count<=2+2) or (household_demographics.hd_dep_count = 3 and household_demographics.hd_vehicle_count<=3+2)) and store.s_store_name = 'ese') s3, (select count(*) h10_to_10_30 FROM tpcds.store_sales, tpcds.household_demographics , tpcds.time_dim, tpcds.store where ss_sold_time_sk = time_dim.t_time_sk and ss_hdemo_sk = household_demographics.hd_demo_sk and ss_store_sk = s_store_sk and time_dim.t_hour = 10 and time_dim.t_minute < 30 and ((household_demographics.hd_dep_count = -1 and household_demographics.hd_vehicle_count<=-1+2) or (household_demographics.hd_dep_count = 2 and household_demographics.hd_vehicle_count<=2+2) or (household_demographics.hd_dep_count = 3 and household_demographics.hd_vehicle_count<=3+2)) and store.s_store_name = 'ese') s4, (select count(*) h10_30_to_11 FROM tpcds.store_sales, tpcds.household_demographics , tpcds.time_dim, tpcds.store where ss_sold_time_sk = time_dim.t_time_sk and ss_hdemo_sk = household_demographics.hd_demo_sk and ss_store_sk = s_store_sk and time_dim.t_hour = 10 and time_dim.t_minute >= 30 and ((household_demographics.hd_dep_count = -1 and household_demographics.hd_vehicle_count<=-1+2) or (household_demographics.hd_dep_count = 2 and household_demographics.hd_vehicle_count<=2+2) or (household_demographics.hd_dep_count = 3 and household_demographics.hd_vehicle_count<=3+2)) and store.s_store_name = 'ese') s5, (select count(*) h11_to_11_30 FROM tpcds.store_sales, tpcds.household_demographics , tpcds.time_dim, tpcds.store where ss_sold_time_sk = time_dim.t_time_sk and ss_hdemo_sk = household_demographics.hd_demo_sk and ss_store_sk = s_store_sk and time_dim.t_hour = 11 and time_dim.t_minute < 30 and ((household_demographics.hd_dep_count = -1 and household_demographics.hd_vehicle_count<=-1+2) or (household_demographics.hd_dep_count = 2 and household_demographics.hd_vehicle_count<=2+2) or (household_demographics.hd_dep_count = 3 and household_demographics.hd_vehicle_count<=3+2)) and store.s_store_name = 'ese') s6, (select count(*) h11_30_to_12 FROM tpcds.store_sales, tpcds.household_demographics , tpcds.time_dim, tpcds.store where ss_sold_time_sk = time_dim.t_time_sk and ss_hdemo_sk = household_demographics.hd_demo_sk and ss_store_sk = s_store_sk and time_dim.t_hour = 11 and time_dim.t_minute >= 30 and ((household_demographics.hd_dep_count = -1 and household_demographics.hd_vehicle_count<=-1+2) or (household_demographics.hd_dep_count = 2 and household_demographics.hd_vehicle_count<=2+2) or (household_demographics.hd_dep_count = 3 and household_demographics.hd_vehicle_count<=3+2)) and store.s_store_name = 'ese') s7, (select count(*) h12_to_12_30 FROM tpcds.store_sales, tpcds.household_demographics , tpcds.time_dim, tpcds.store where ss_sold_time_sk = time_dim.t_time_sk and ss_hdemo_sk = household_demographics.hd_demo_sk and ss_store_sk = s_store_sk and time_dim.t_hour = 12 and time_dim.t_minute < 30 and ((household_demographics.hd_dep_count = -1 and household_demographics.hd_vehicle_count<=-1+2) or (household_demographics.hd_dep_count = 2 and household_demographics.hd_vehicle_count<=2+2) or (household_demographics.hd_dep_count = 3 and household_demographics.hd_vehicle_count<=3+2)) and store.s_store_name = 'ese') s8
SELECT * FROM (SELECT i_category, i_class, i_brand, s_store_name, s_company_name, d_moy, sum(ss_sales_price) sum_sales, avg(sum(ss_sales_price)) OVER ( partition BY i_category, i_brand, s_store_name, s_company_name ) avg_monthly_sales FROM tpcds.item, tpcds.store_sales, tpcds.date_dim, tpcds.store WHERE ss_item_sk = i_item_sk AND ss_sold_date_sk = d_date_sk AND ss_store_sk = s_store_sk AND d_year IN ( 2002 ) AND ( ( i_category IN ( 'Home', 'Men', 'Sports' ) AND i_class IN ( 'paint', 'accessories', 'fitness' ) ) OR ( i_category IN ( 'Shoes', 'Jewelry', 'Women' ) AND i_class IN ( 'mens', 'pendants', 'swimwear' ) ) ) GROUP BY i_category, i_class, i_brand, s_store_name, s_company_name, d_moy) tmp1 WHERE CASE WHEN ( avg_monthly_sales <> 0 ) THEN ( abs(sum_sales - avg_monthly_sales) / avg_monthly_sales ) ELSE NULL END > 0.1 ORDER BY sum_sales - avg_monthly_sales, s_store_name LIMIT 100
SELECT amc / pmc AS am_pm_ratio FROM (SELECT count(*) amc FROM tpcds.web_sales, tpcds.household_demographics, tpcds.time_dim, tpcds.web_page WHERE ws_sold_time_sk = time_dim.t_time_sk AND ws_ship_hdemo_sk = household_demographics.hd_demo_sk AND ws_web_page_sk = web_page.wp_web_page_sk AND time_dim.t_hour BETWEEN 12 AND 12 + 1 AND household_demographics.hd_dep_count = 8 AND web_page.wp_char_count BETWEEN 5000 AND 5200) at1, (SELECT count(*) pmc FROM tpcds.web_sales, tpcds.household_demographics, tpcds.time_dim, tpcds.web_page WHERE ws_sold_time_sk = time_dim.t_time_sk AND ws_ship_hdemo_sk = household_demographics.hd_demo_sk AND ws_web_page_sk = web_page.wp_web_page_sk AND time_dim.t_hour BETWEEN 20 AND 20 + 1 AND household_demographics.hd_dep_count = 8 AND web_page.wp_char_count BETWEEN 5000 AND 5200) pt ORDER BY am_pm_ratio LIMIT 100
SELECT cc_call_center_id Call_Center, cc_name Call_Center_Name, cc_manager Manager, sum(cr_net_loss) Returns_Loss FROM tpcds.call_center, tpcds.catalog_returns, tpcds.date_dim, tpcds.customer, tpcds.customer_address, tpcds.customer_demographics, tpcds.household_demographics WHERE cr_call_center_sk = cc_call_center_sk AND cr_returned_date_sk = d_date_sk AND cr_returning_customer_sk = c_customer_sk AND cd_demo_sk = c_current_cdemo_sk AND hd_demo_sk = c_current_hdemo_sk AND ca_address_sk = c_current_addr_sk AND d_year = 1999 AND d_moy = 12 AND ( ( cd_marital_status = 'M' AND cd_education_status = 'Unknown' ) OR ( cd_marital_status = 'W' AND cd_education_status = 'Advanced Degree' ) ) AND hd_buy_potential LIKE 'Unknown%' AND ca_gmt_offset = -7 GROUP BY cc_call_center_id, cc_name, cc_manager, cd_marital_status, cd_education_status ORDER BY sum(cr_net_loss) DESC
SELECT sum(ws_ext_discount_amt) AS excess_discount_amount FROM tpcds.web_sales , tpcds.item , tpcds.date_dim WHERE i_manufact_id = 718 AND i_item_sk = ws_item_sk AND Cast(d_date AS DATE) BETWEEN Cast('2002-03-29' AS DATE) AND ( Cast('2002-06-28' AS DATE)) AND d_date_sk = ws_sold_date_sk AND ws_ext_discount_amt > ( SELECT 1.3 * avg(ws_ext_discount_amt) FROM tpcds.web_sales , tpcds.date_dim WHERE ws_item_sk = i_item_sk AND Cast(d_date AS DATE) BETWEEN Cast('2002-03-29' AS DATE) AND ( cast('2002-06-28' AS date)) AND d_date_sk = ws_sold_date_sk ) ORDER BY sum(ws_ext_discount_amt) LIMIT 100
SELECT ss_customer_sk, sum(act_sales) sumsales FROM (SELECT ss_item_sk, ss_ticket_number, ss_customer_sk, CASE WHEN sr_return_quantity IS NOT NULL THEN ( ss_quantity - sr_return_quantity ) * ss_sales_price ELSE ( ss_quantity * ss_sales_price ) END act_sales FROM tpcds.store_sales LEFT OUTER JOIN tpcds.store_returns ON ( sr_item_sk = ss_item_sk AND sr_ticket_number = ss_ticket_number ), reason WHERE sr_reason_sk = r_reason_sk AND r_reason_desc = 'reason 38') t GROUP BY ss_customer_sk ORDER BY sumsales, ss_customer_sk LIMIT 100
SELECT count(DISTINCT ws_order_number) AS order_count, sum(ws_ext_ship_cost) AS total_shipping_cost , sum(ws_net_profit) AS total_net_profit FROM tpcds.web_sales ws1 , tpcds.date_dim , tpcds.customer_address , tpcds.web_site WHERE Cast(d_date AS DATE) BETWEEN Cast('2000-3-01' AS DATE) AND ( Cast('2000-5-01' AS DATE)) AND ws1.ws_ship_date_sk = d_date_sk AND ws1.ws_ship_addr_sk = ca_address_sk AND ca_state = 'MT' AND ws1.ws_web_site_sk = web_site_sk AND web_company_name = 'pri' AND EXISTS ( SELECT * FROM tpcds.web_sales ws2 WHERE ws1.ws_order_number = ws2.ws_order_number AND ws1.ws_warehouse_sk <> ws2.ws_warehouse_sk) AND NOT EXISTS ( SELECT * FROM tpcds.web_returns wr1 WHERE ws1.ws_order_number = wr1.wr_order_number) ORDER BY count(DISTINCT ws_order_number) LIMIT 100
WITH ws_wh AS ( SELECT ws1.ws_order_number, ws1.ws_warehouse_sk wh1, ws2.ws_warehouse_sk wh2 FROM tpcds.web_sales ws1, tpcds.web_sales ws2 WHERE ws1.ws_order_number = ws2.ws_order_number AND ws1.ws_warehouse_sk <> ws2.ws_warehouse_sk) SELECT count(DISTINCT ws_order_number) AS order_count, sum(ws_ext_ship_cost) AS total_shipping_cost, sum(ws_net_profit) AS total_net_profit FROM tpcds.web_sales ws1 , tpcds.date_dim , tpcds.customer_address , tpcds.web_site WHERE Cast(d_date AS DATE) BETWEEN Cast('2000-4-01' AS DATE) AND ( Cast('2000-6-01' AS DATE)) AND ws1.ws_ship_date_sk = d_date_sk AND ws1.ws_ship_addr_sk = ca_address_sk AND ca_state = 'IN' AND ws1.ws_web_site_sk = web_site_sk AND web_company_name = 'pri' AND ws1.ws_order_number IN ( SELECT ws_order_number FROM ws_wh) AND ws1.ws_order_number IN ( SELECT wr_order_number FROM tpcds.web_returns, ws_wh WHERE wr_order_number = ws_wh.ws_order_number) ORDER BY count(DISTINCT ws_order_number) LIMIT 100
SELECT count(*) FROM tpcds.store_sales, tpcds.household_demographics, tpcds.time_dim, tpcds.store WHERE ss_sold_time_sk = time_dim.t_time_sk AND ss_hdemo_sk = household_demographics.hd_demo_sk AND ss_store_sk = s_store_sk AND time_dim.t_hour = 15 AND time_dim.t_minute >= 30 AND household_demographics.hd_dep_count = 7 AND store.s_store_name = 'ese' ORDER BY count(*) LIMIT 100
WITH ssci AS (SELECT ss_customer_sk customer_sk, ss_item_sk item_sk FROM tpcds.store_sales, tpcds.date_dim WHERE ss_sold_date_sk = d_date_sk AND d_month_seq BETWEEN 1196 AND 1196 + 11 GROUP BY ss_customer_sk, ss_item_sk), csci AS (SELECT cs_bill_customer_sk customer_sk, cs_item_sk item_sk FROM tpcds.catalog_sales, tpcds.date_dim WHERE cs_sold_date_sk = d_date_sk AND d_month_seq BETWEEN 1196 AND 1196 + 11 GROUP BY cs_bill_customer_sk, cs_item_sk) SELECT sum(CASE WHEN ssci.customer_sk IS NOT NULL AND csci.customer_sk IS NULL THEN 1 ELSE 0 END) store_only, sum(CASE WHEN ssci.customer_sk IS NULL AND csci.customer_sk IS NOT NULL THEN 1 ELSE 0 END) catalog_only, sum(CASE WHEN ssci.customer_sk IS NOT NULL AND csci.customer_sk IS NOT NULL THEN 1 ELSE 0 END) store_and_catalog FROM ssci FULL OUTER JOIN csci ON ( ssci.customer_sk = csci.customer_sk AND ssci.item_sk = csci.item_sk ) LIMIT 100
SELECT i_item_id, i_item_desc, i_category, i_class, i_current_price, sum(ss_ext_sales_price) AS itemrevenue, sum(ss_ext_sales_price) * 100 / sum(sum(ss_ext_sales_price)) OVER ( PARTITION BY i_class) AS revenueratio FROM tpcds.store_sales, tpcds.item, tpcds.date_dim WHERE ss_item_sk = i_item_sk AND i_category IN ( 'Men', 'Home', 'Electronics' ) AND ss_sold_date_sk = d_date_sk AND Cast(d_date AS DATE) BETWEEN CAST('2000-05-18' AS DATE) AND ( CAST('2000-06-18' AS DATE) ) GROUP BY i_item_id, i_item_desc, i_category, i_class, i_current_price ORDER BY i_category, i_class, i_item_id, i_item_desc, revenueratio
SELECT substr(w_warehouse_name, 1, 20), sm_type, cc_name, sum(CASE WHEN ( cs_ship_date_sk - cs_sold_date_sk <= 30 ) THEN 1 ELSE 0 END) AS days_30, sum(CASE WHEN ( cs_ship_date_sk - cs_sold_date_sk > 30 ) AND ( cs_ship_date_sk - cs_sold_date_sk <= 60 ) THEN 1 ELSE 0 END) AS days_31_60, sum(CASE WHEN ( cs_ship_date_sk - cs_sold_date_sk > 60 ) AND ( cs_ship_date_sk - cs_sold_date_sk <= 90 ) THEN 1 ELSE 0 END) AS days_61_90, sum(CASE WHEN ( cs_ship_date_sk - cs_sold_date_sk > 90 ) AND ( cs_ship_date_sk - cs_sold_date_sk <= 120 ) THEN 1 ELSE 0 END) AS days_91_120, sum(CASE WHEN ( cs_ship_date_sk - cs_sold_date_sk > 120 ) THEN 1 ELSE 0 END) AS days_over_120 FROM tpcds.catalog_sales, tpcds.warehouse, tpcds.ship_mode, tpcds.call_center, tpcds.date_dim WHERE d_month_seq BETWEEN 1200 AND 1200 + 11 AND cs_ship_date_sk = d_date_sk AND cs_warehouse_sk = w_warehouse_sk AND cs_ship_mode_sk = sm_ship_mode_sk AND cs_call_center_sk = cc_call_center_sk GROUP BY w_warehouse_name, sm_type, cc_name ORDER BY 1, 2, 3 LIMIT 100