http://git-wip-us.apache.org/repos/asf/carbondata/blob/ffddba70/datamap/mv/plan/src/main/scala/org/apache/carbondata/mv/testutil/Tpcds_1_4_QueryBatch.scala ---------------------------------------------------------------------- diff --git a/datamap/mv/plan/src/main/scala/org/apache/carbondata/mv/testutil/Tpcds_1_4_QueryBatch.scala b/datamap/mv/plan/src/main/scala/org/apache/carbondata/mv/testutil/Tpcds_1_4_QueryBatch.scala new file mode 100644 index 0000000..8262dfa --- /dev/null +++ b/datamap/mv/plan/src/main/scala/org/apache/carbondata/mv/testutil/Tpcds_1_4_QueryBatch.scala @@ -0,0 +1,4293 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one or more + * contributor license agreements. See the NOTICE file distributed with + * this work for additional information regarding copyright ownership. + * The ASF licenses this file to You under the Apache License, Version 2.0 + * (the "License"); you may not use this file except in compliance with + * the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ + +package org.apache.carbondata.mv.testutil + +object Tpcds_1_4_QueryBatch { + + // should be random generated based on scale + // RC=ulist(random(1, rowcount("store_sales")/5,uniform),5); + val rc = Array(1000000, 1000000, 1000000, 1000000, 1000000) + + // Queries the TPCDS 1.4 queries using the qualifcations values in the templates. + + val tpcds1_4Queries = Seq( + ("q1", + """ + | 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 store_returns, date_dim + | WHERE sr_returned_date_sk = d_date_sk AND d_year = 2000 + | GROUP BY sr_customer_sk, sr_store_sk) + | SELECT c_customer_id + | FROM customer_total_return ctr1, store, customer + | WHERE ctr1.ctr_total_return > + | (SELECT avg(ctr_total_return)*1.2 + | FROM customer_total_return ctr2 + | WHERE ctr1.ctr_store_sk = ctr2.ctr_store_sk) + | AND s_store_sk = ctr1.ctr_store_sk + | AND s_state = 'TN' + | AND ctr1.ctr_customer_sk = c_customer_sk + | ORDER BY c_customer_id LIMIT 100 + """.stripMargin), + ("q2", + """ + | 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) x + | 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 = 2001) 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 = 2001 + 1) z + | WHERE d_week_seq1=d_week_seq2-53 + | ORDER BY d_week_seq1 + """.stripMargin), + ("q3", + """ + | SELECT dt.d_year, item.i_brand_id brand_id, item.i_brand brand,SUM(ss_ext_sales_price) + | sum_agg + | FROM date_dim dt, store_sales, 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 = 128 + | 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 + """.stripMargin), + ("q4", + """ + |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_wholesale_cost-ss_ext_discount_amt) + | +ss_ext_sales_price)/2) year_total, + | 's' sale_type + | FROM customer, store_sales, 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((((cs_ext_list_price-cs_ext_wholesale_cost-cs_ext_discount_amt) + | +cs_ext_sales_price)/2) ) year_total, + | 'c' sale_type + | FROM customer, catalog_sales, date_dim + | WHERE c_customer_sk = cs_bill_customer_sk AND cs_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_wholesale_cost-ws_ext_discount_amt) + | +ws_ext_sales_price)/2) ) year_total + | ,'w' sale_type + | FROM customer, web_sales, 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_preferred_cust_flag, + | t_s_secyear.customer_birth_country, + | t_s_secyear.customer_login, + | t_s_secyear.customer_email_address + | 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 + | 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_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, + | t_s_secyear.customer_first_name, + | t_s_secyear.customer_last_name, + | t_s_secyear.customer_preferred_cust_flag, + | t_s_secyear.customer_birth_country, + | t_s_secyear.customer_login, + | t_s_secyear.customer_email_address + | LIMIT 100 + """.stripMargin), + // Modifications: "+ days" -> date_add + // Modifications: "||" -> concat + ("q5", + """ + | WITH ssr AS + | (SELECT s_store_id, + | sum(sales_price) as sales, + | sum(profit) as profit, + | sum(return_amt) as returns, + | 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, + | cast(0 as decimal(7,2)) as return_amt, + | cast(0 as decimal(7,2)) as net_loss + | FROM store_sales + | UNION ALL + | SELECT sr_store_sk as store_sk, + | sr_returned_date_sk as date_sk, + | cast(0 as decimal(7,2)) as sales_price, + | cast(0 as decimal(7,2)) as profit, + | sr_return_amt as return_amt, + | sr_net_loss as net_loss + | FROM store_returns) + | salesreturns, date_dim, store + | WHERE date_sk = d_date_sk + | and d_date between cast('2000-08-23' as date) + | and ((cast('2000-08-23' as date) + interval 14 days)) + | 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 returns, + | 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, + | cast(0 as decimal(7,2)) as return_amt, + | cast(0 as decimal(7,2)) as net_loss + | FROM catalog_sales + | UNION ALL + | SELECT cr_catalog_page_sk as page_sk, + | cr_returned_date_sk as date_sk, + | cast(0 as decimal(7,2)) as sales_price, + | cast(0 as decimal(7,2)) as profit, + | cr_return_amount as return_amt, + | cr_net_loss as net_loss + | from catalog_returns + | ) salesreturns, date_dim, catalog_page + | WHERE date_sk = d_date_sk + | and d_date between cast('2000-08-23' as date) + | and ((cast('2000-08-23' as date) + interval 14 days)) + | 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 returns, + | 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, + | cast(0 as decimal(7,2)) as return_amt, + | cast(0 as decimal(7,2)) as net_loss + | from web_sales + | union all + | select ws_web_site_sk as wsr_web_site_sk, + | wr_returned_date_sk as date_sk, + | cast(0 as decimal(7,2)) as sales_price, + | cast(0 as decimal(7,2)) as profit, + | wr_return_amt as return_amt, + | wr_net_loss as net_loss + | FROM web_returns LEFT OUTER JOIN 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 d_date between cast('2000-08-23' as date) + | and ((cast('2000-08-23' as date) + interval 14 days)) + | and wsr_web_site_sk = web_site_sk + | GROUP BY web_site_id) + | SELECT channel, + | id, + | sum(sales) as sales, + | sum(returns) as returns, + | sum(profit) as profit + | from + | (select 'store channel' as channel, + | concat('store', s_store_id) as id, + | sales, + | returns, + | (profit - profit_loss) as profit + | FROM ssr + | UNION ALL + | select 'catalog channel' as channel, + | concat('catalog_page', cp_catalog_page_id) as id, + | sales, + | returns, + | (profit - profit_loss) as profit + | FROM csr + | UNION ALL + | SELECT 'web channel' as channel, + | concat('web_site', web_site_id) as id, + | sales, + | returns, + | (profit - profit_loss) as profit + | FROM wsr + | ) x + | GROUP BY ROLLUP (channel, id) + | ORDER BY channel, id + | LIMIT 100 + """.stripMargin), + ("q6", + """ + | SELECT a.ca_state state, count(*) cnt + | FROM + | customer_address a, customer c, store_sales s, date_dim d, 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 date_dim + | WHERE d_year = 2000 AND d_moy = 1) + | AND i.i_current_price > 1.2 * + | (SELECT avg(j.i_current_price) FROM item j + | WHERE j.i_category = i.i_category) + | GROUP BY a.ca_state + | HAVING count(*) >= 10 + | ORDER BY cnt LIMIT 100 + """.stripMargin), + ("q7", + """ + | SELECT i_item_id, + | avg(ss_quantity) agg1, + | avg(ss_list_price) agg2, + | avg(ss_coupon_amt) agg3, + | avg(ss_sales_price) agg4 + | FROM store_sales, customer_demographics, date_dim, item, 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 = 'M' AND + | cd_marital_status = 'S' AND + | cd_education_status = 'College' 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 + """.stripMargin), + ("q8", + """ + | select s_store_name, sum(ss_net_profit) + | from store_sales, date_dim, store, + | (SELECT ca_zip + | from ( + | (SELECT substr(ca_zip,1,5) ca_zip FROM customer_address + | WHERE substr(ca_zip,1,5) IN ( + | '24128','76232','65084','87816','83926','77556','20548', + | '26231','43848','15126','91137','61265','98294','25782', + | '17920','18426','98235','40081','84093','28577','55565', + | '17183','54601','67897','22752','86284','18376','38607', + | '45200','21756','29741','96765','23932','89360','29839', + | '25989','28898','91068','72550','10390','18845','47770', + | '82636','41367','76638','86198','81312','37126','39192', + | '88424','72175','81426','53672','10445','42666','66864', + | '66708','41248','48583','82276','18842','78890','49448', + | '14089','38122','34425','79077','19849','43285','39861', + | '66162','77610','13695','99543','83444','83041','12305', + | '57665','68341','25003','57834','62878','49130','81096', + | '18840','27700','23470','50412','21195','16021','76107', + | '71954','68309','18119','98359','64544','10336','86379', + | '27068','39736','98569','28915','24206','56529','57647', + | '54917','42961','91110','63981','14922','36420','23006', + | '67467','32754','30903','20260','31671','51798','72325', + | '85816','68621','13955','36446','41766','68806','16725', + | '15146','22744','35850','88086','51649','18270','52867', + | '39972','96976','63792','11376','94898','13595','10516', + | '90225','58943','39371','94945','28587','96576','57855', + | '28488','26105','83933','25858','34322','44438','73171', + | '30122','34102','22685','71256','78451','54364','13354', + | '45375','40558','56458','28286','45266','47305','69399', + | '83921','26233','11101','15371','69913','35942','15882', + | '25631','24610','44165','99076','33786','70738','26653', + | '14328','72305','62496','22152','10144','64147','48425', + | '14663','21076','18799','30450','63089','81019','68893', + | '24996','51200','51211','45692','92712','70466','79994', + | '22437','25280','38935','71791','73134','56571','14060', + | '19505','72425','56575','74351','68786','51650','20004', + | '18383','76614','11634','18906','15765','41368','73241', + | '76698','78567','97189','28545','76231','75691','22246', + | '51061','90578','56691','68014','51103','94167','57047', + | '14867','73520','15734','63435','25733','35474','24676', + | '94627','53535','17879','15559','53268','59166','11928', + | '59402','33282','45721','43933','68101','33515','36634', + | '71286','19736','58058','55253','67473','41918','19515', + | '36495','19430','22351','77191','91393','49156','50298', + | '87501','18652','53179','18767','63193','23968','65164', + | '68880','21286','72823','58470','67301','13394','31016', + | '70372','67030','40604','24317','45748','39127','26065', + | '77721','31029','31880','60576','24671','45549','13376', + | '50016','33123','19769','22927','97789','46081','72151', + | '15723','46136','51949','68100','96888','64528','14171', + | '79777','28709','11489','25103','32213','78668','22245', + | '15798','27156','37930','62971','21337','51622','67853', + | '10567','38415','15455','58263','42029','60279','37125', + | '56240','88190','50308','26859','64457','89091','82136', + | '62377','36233','63837','58078','17043','30010','60099', + | '28810','98025','29178','87343','73273','30469','64034', + | '39516','86057','21309','90257','67875','40162','11356', + | '73650','61810','72013','30431','22461','19512','13375', + | '55307','30625','83849','68908','26689','96451','38193', + | '46820','88885','84935','69035','83144','47537','56616', + | '94983','48033','69952','25486','61547','27385','61860', + | '58048','56910','16807','17871','35258','31387','35458', + | '35576')) + | INTERSECT + | (select ca_zip + | FROM + | (SELECT substr(ca_zip,1,5) ca_zip,count(*) cnt + | FROM customer_address, customer + | WHERE ca_address_sk = c_current_addr_sk and + | c_preferred_cust_flag='Y' + | group by ca_zip + | having count(*) > 10) A1) + | ) A2 + | ) V1 + | where ss_store_sk = s_store_sk + | and ss_sold_date_sk = d_date_sk + | and d_qoy = 2 and d_year = 1998 + | and (substr(s_zip,1,2) = substr(V1.ca_zip,1,2)) + | group by s_store_name + | order by s_store_name LIMIT 100 + """.stripMargin), + ("q9", + s""" + |select case when (select count(*) from store_sales + | where ss_quantity between 1 and 20) > ${ rc(0) } + | then (select avg(ss_ext_discount_amt) from store_sales + | where ss_quantity between 1 and 20) + | else (select avg(ss_net_paid) from store_sales + | where ss_quantity between 1 and 20) end bucket1 , + | case when (select count(*) from store_sales + | where ss_quantity between 21 and 40) > ${ rc(1) } + | then (select avg(ss_ext_discount_amt) from store_sales + | where ss_quantity between 21 and 40) + | else (select avg(ss_net_paid) from store_sales + | where ss_quantity between 21 and 40) end bucket2, + | case when (select count(*) from store_sales + | where ss_quantity between 41 and 60) > ${ rc(2) } + | then (select avg(ss_ext_discount_amt) from store_sales + | where ss_quantity between 41 and 60) + | else (select avg(ss_net_paid) from store_sales + | where ss_quantity between 41 and 60) end bucket3, + | case when (select count(*) from store_sales + | where ss_quantity between 61 and 80) > ${ rc(3) } + | then (select avg(ss_ext_discount_amt) from store_sales + | where ss_quantity between 61 and 80) + | else (select avg(ss_net_paid) from store_sales + | where ss_quantity between 61 and 80) end bucket4, + | case when (select count(*) from store_sales + | where ss_quantity between 81 and 100) > ${ rc(4) } + | then (select avg(ss_ext_discount_amt) from store_sales + | where ss_quantity between 81 and 100) + | else (select avg(ss_net_paid) from store_sales + | where ss_quantity between 81 and 100) end bucket5 + |from reason + |where r_reason_sk = 1 + """.stripMargin), + ("q10", + """ + | 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 + | customer c, customer_address ca, customer_demographics + | where + | c.c_current_addr_sk = ca.ca_address_sk and + | ca_county in ('Rush County','Toole County','Jefferson County', + | 'Dona Ana County','La Porte County') and + | cd_demo_sk = c.c_current_cdemo_sk AND + | exists (select * from store_sales, 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 1 AND 1+3) AND + | (exists (select * from web_sales, 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 1 AND 1+3) or + | exists (select * from catalog_sales, 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 1 AND 1+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 + """.stripMargin), + ("q11", + """ + | 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 customer, store_sales, 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 + | ,d_year + | ,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 customer, web_sales, 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_preferred_cust_flag + | 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 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 t_s_secyear.customer_preferred_cust_flag + | LIMIT 100 + """.stripMargin), + // Modifications: "+ days" -> date_add + ("q12", + """ + | select + | 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 + | web_sales, item, date_dim + | where + | ws_item_sk = i_item_sk + | and i_category in ('Sports', 'Books', 'Home') + | and ws_sold_date_sk = d_date_sk + | and d_date between cast('1999-02-22' as date) + | and (cast('1999-02-22' as date) + interval 30 days) + | 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 + """.stripMargin), + ("q13", + """ + | select avg(ss_quantity) + | ,avg(ss_ext_sales_price) + | ,avg(ss_ext_wholesale_cost) + | ,sum(ss_ext_wholesale_cost) + | from store_sales + | ,store + | ,customer_demographics + | ,household_demographics + | ,customer_address + | ,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 = 'M' + | 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 = 'S' + | and cd_education_status = 'College' + | 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 = 'W' + | and cd_education_status = '2 yr Degree' + | 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 ('TX', 'OH', 'TX') + | and ss_net_profit between 100 and 200 + | ) or + | (ss_addr_sk = ca_address_sk + | and ca_country = 'United States' + | and ca_state in ('OR', 'NM', 'KY') + | and ss_net_profit between 150 and 300 + | ) or + | (ss_addr_sk = ca_address_sk + | and ca_country = 'United States' + | and ca_state in ('VA', 'TX', 'MS') + | and ss_net_profit between 50 and 250 + | )) + """.stripMargin), + ("q14a", + """ + |with cross_items as + | (select i_item_sk ss_item_sk + | from item, + | (select iss.i_brand_id brand_id, iss.i_class_id class_id, iss.i_category_id + | category_id + | from store_sales, item iss, date_dim d1 + | where ss_item_sk = iss.i_item_sk + and ss_sold_date_sk = d1.d_date_sk + | and d1.d_year between 1999 AND 1999 + 2 + | intersect + | select ics.i_brand_id, ics.i_class_id, ics.i_category_id + | from catalog_sales, item ics, date_dim d2 + | where cs_item_sk = ics.i_item_sk + | and cs_sold_date_sk = d2.d_date_sk + | and d2.d_year between 1999 AND 1999 + 2 + | intersect + | select iws.i_brand_id, iws.i_class_id, iws.i_category_id + | from web_sales, item iws, date_dim d3 + | where ws_item_sk = iws.i_item_sk + | and ws_sold_date_sk = d3.d_date_sk + | and d3.d_year between 1999 AND 1999 + 2) x + | where i_brand_id = brand_id + | and i_class_id = class_id + | and i_category_id = category_id + |), + | avg_sales as + | (select avg(quantity*list_price) average_sales + | from ( + | select ss_quantity quantity, ss_list_price list_price + | from store_sales, date_dim + | where ss_sold_date_sk = d_date_sk + | and d_year between 1999 and 2001 + | union all + | select cs_quantity quantity, cs_list_price list_price + | from catalog_sales, date_dim + | where cs_sold_date_sk = d_date_sk + | and d_year between 1999 and 1999 + 2 + | union all + | select ws_quantity quantity, ws_list_price list_price + | from web_sales, date_dim + | where ws_sold_date_sk = d_date_sk + | and d_year between 1999 and 1999 + 2) x) + | select channel, i_brand_id,i_class_id,i_category_id,sum(sales), sum(number_sales) + | from( + | select 'store' channel, i_brand_id,i_class_id + | ,i_category_id,sum(ss_quantity*ss_list_price) sales + | , count(*) number_sales + | from store_sales, item, date_dim + | where ss_item_sk in (select ss_item_sk from cross_items) + | and ss_item_sk = i_item_sk + | and ss_sold_date_sk = d_date_sk + | and d_year = 1999+2 + | and d_moy = 11 + | group by i_brand_id,i_class_id,i_category_id + | having sum(ss_quantity*ss_list_price) > (select average_sales from avg_sales) + | union all + | select 'catalog' channel, i_brand_id,i_class_id,i_category_id, sum + | (cs_quantity*cs_list_price) sales, count(*) number_sales + | from catalog_sales, item, date_dim + | where cs_item_sk in (select ss_item_sk from cross_items) + | and cs_item_sk = i_item_sk + | and cs_sold_date_sk = d_date_sk + | and d_year = 1999+2 + | and d_moy = 11 + | group by i_brand_id,i_class_id,i_category_id + | having sum(cs_quantity*cs_list_price) > (select average_sales from avg_sales) + | union all + | select 'web' channel, i_brand_id,i_class_id,i_category_id, sum + | (ws_quantity*ws_list_price) sales , count(*) number_sales + | from web_sales, item, date_dim + | where ws_item_sk in (select ss_item_sk from cross_items) + | and ws_item_sk = i_item_sk + | and ws_sold_date_sk = d_date_sk + | and d_year = 1999+2 + | and d_moy = 11 + | group by i_brand_id,i_class_id,i_category_id + | having sum(ws_quantity*ws_list_price) > (select average_sales from avg_sales) + | ) y + | group by rollup (channel, i_brand_id,i_class_id,i_category_id) + | order by channel,i_brand_id,i_class_id,i_category_id + | limit 100 + """.stripMargin), + ("q14b", + """ + | with cross_items as + | (select i_item_sk ss_item_sk + | from item, + | (select iss.i_brand_id brand_id, iss.i_class_id class_id, iss.i_category_id + | category_id + | from store_sales, item iss, date_dim d1 + | where ss_item_sk = iss.i_item_sk + | and ss_sold_date_sk = d1.d_date_sk + | and d1.d_year between 1999 AND 1999 + 2 + | intersect + | select ics.i_brand_id, ics.i_class_id, ics.i_category_id + | from catalog_sales, item ics, date_dim d2 + | where cs_item_sk = ics.i_item_sk + | and cs_sold_date_sk = d2.d_date_sk + | and d2.d_year between 1999 AND 1999 + 2 + | intersect + | select iws.i_brand_id, iws.i_class_id, iws.i_category_id + | from web_sales, item iws, date_dim d3 + | where ws_item_sk = iws.i_item_sk + | and ws_sold_date_sk = d3.d_date_sk + | and d3.d_year between 1999 AND 1999 + 2) x + | where i_brand_id = brand_id + | and i_class_id = class_id + | and i_category_id = category_id + | ), + | avg_sales as + | (select avg(quantity*list_price) average_sales + | from (select ss_quantity quantity, ss_list_price list_price + | from store_sales, date_dim + | where ss_sold_date_sk = d_date_sk and d_year between 1999 and 1999 + 2 + | union all + | select cs_quantity quantity, cs_list_price list_price + | from catalog_sales, date_dim + | where cs_sold_date_sk = d_date_sk and d_year between 1999 and 1999 + 2 + | union all + | select ws_quantity quantity, ws_list_price list_price + | from web_sales, date_dim + | where ws_sold_date_sk = d_date_sk and d_year between 1999 and 1999 + 2) x) + | select * from + | (select 'store' channel, i_brand_id,i_class_id,i_category_id + | ,sum(ss_quantity*ss_list_price) sales, count(*) number_sales + | from store_sales, item, date_dim + | where ss_item_sk in (select ss_item_sk from cross_items) + | and ss_item_sk = i_item_sk + | and ss_sold_date_sk = d_date_sk + | and d_week_seq = (select d_week_seq from date_dim + | where d_year = 1999 + 1 and d_moy = 12 and d_dom = 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)) this_year, + | (select 'store' channel, i_brand_id,i_class_id + | ,i_category_id, sum(ss_quantity*ss_list_price) sales, count(*) number_sales + | from store_sales, item, date_dim + | where ss_item_sk in (select ss_item_sk from cross_items) + | and ss_item_sk = i_item_sk + | and ss_sold_date_sk = d_date_sk + | and d_week_seq = (select d_week_seq from date_dim + | where d_year = 1999 and d_moy = 12 and d_dom = 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)) last_year + | where this_year.i_brand_id= last_year.i_brand_id + | and this_year.i_class_id = last_year.i_class_id + | and this_year.i_category_id = last_year.i_category_id + | order by this_year.channel, this_year.i_brand_id, this_year.i_class_id, this_year + | .i_category_id + | limit 100 + """.stripMargin), + ("q15", + """ + | select ca_zip, sum(cs_sales_price) + | from catalog_sales, customer, customer_address, 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 = 2 and d_year = 2001 + | group by ca_zip + | order by ca_zip + | limit 100 + """.stripMargin), + // Modifications: " -> ` + ("q16", + """ + | 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 + | catalog_sales cs1, date_dim, customer_address, call_center + | where + | d_date between '2002-02-01' and (cast('2002-02-01' as date) + interval 60 days) + | and cs1.cs_ship_date_sk = d_date_sk + | and cs1.cs_ship_addr_sk = ca_address_sk + | and ca_state = 'GA' + | 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 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 catalog_returns cr1 + | where cs1.cs_order_number = cr1.cr_order_number) + | order by count(distinct cs_order_number) + | limit 100 + """.stripMargin), + ("q17", + """ + | 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 store_sales, store_returns, catalog_sales, date_dim d1, date_dim d2, date_dim d3, + | store, item + | where d1.d_quarter_name = '2001Q1' + | 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 ('2001Q1','2001Q2','2001Q3') + | 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 ('2001Q1','2001Q2','2001Q3') + | group by i_item_id, i_item_desc, s_state + | order by i_item_id, i_item_desc, s_state + | limit 100 + """.stripMargin), + // Modifications: "numeric" -> "decimal" + ("q18", + """ + | select i_item_id, + | ca_country, + | ca_state, + | ca_county, + | avg( cast(cs_quantity as decimal(12,2))) agg1, + | avg( cast(cs_list_price as decimal(12,2))) agg2, + | avg( cast(cs_coupon_amt as decimal(12,2))) agg3, + | avg( cast(cs_sales_price as decimal(12,2))) agg4, + | avg( cast(cs_net_profit as decimal(12,2))) agg5, + | avg( cast(c_birth_year as decimal(12,2))) agg6, + | avg( cast(cd1.cd_dep_count as decimal(12,2))) agg7 + | from catalog_sales, customer_demographics cd1, + | customer_demographics cd2, customer, customer_address, date_dim, 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 = 'Unknown' and + | c_current_cdemo_sk = cd2.cd_demo_sk and + | c_current_addr_sk = ca_address_sk and + | c_birth_month in (1,6,8,9,12,2) and + | d_year = 1998 and + | ca_state in ('MS','IN','ND','OK','NM','VA','MS') + | group by rollup (i_item_id, ca_country, ca_state, ca_county) + | order by ca_country, ca_state, ca_county, i_item_id + | LIMIT 100 + """.stripMargin), + ("q19", + """ + | select i_brand_id brand_id, i_brand brand, i_manufact_id, i_manufact, + | sum(ss_ext_sales_price) ext_price + | from date_dim, store_sales, item,customer,customer_address,store + | where d_date_sk = ss_sold_date_sk + | and ss_item_sk = i_item_sk + | and i_manager_id = 8 + | and d_moy = 11 + | 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, brand, brand_id, i_manufact_id, i_manufact + | limit 100 + """.stripMargin), + ("q20", + """ + |select 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 catalog_sales, item, date_dim + | where cs_item_sk = i_item_sk + | and i_category in ('Sports', 'Books', 'Home') + | and cs_sold_date_sk = d_date_sk + | and d_date between cast('1999-02-22' as date) + | and (cast('1999-02-22' as date) + interval 30 days) + | 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 + """.stripMargin), + // Modifications: "+ days" -> date_add + ("q21", + """ + | select * from( + | select w_warehouse_name, i_item_id, + | sum(case when (cast(d_date as date) < cast ('2000-03-11' as date)) + | then inv_quantity_on_hand + | else 0 end) as inv_before, + | sum(case when (cast(d_date as date) >= cast ('2000-03-11' as date)) + | then inv_quantity_on_hand + | else 0 end) as inv_after + | from inventory, warehouse, item, 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 d_date between (cast('2000-03-11' as date) - interval 30 days) + | and (cast('2000-03-11' as date) + interval 30 days) + | 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 + """.stripMargin), + ("q22", + """ + | select i_product_name, i_brand, i_class, i_category, avg(inv_quantity_on_hand) qoh + | from inventory, date_dim, item, 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 1200 and 1200 + 11 + | group by rollup(i_product_name, i_brand, i_class, i_category) + | order by qoh, i_product_name, i_brand, i_class, i_category + | limit 100 + """.stripMargin), + ("q23a", + """ + | with frequent_ss_items as + | (select substr(i_item_desc,1,30) itemdesc,i_item_sk item_sk,d_date solddate,count(*) cnt + | from store_sales, date_dim, item + | where ss_sold_date_sk = d_date_sk + | and ss_item_sk = i_item_sk + | and d_year in (2000, 2000+1, 2000+2,2000+3) + | group by substr(i_item_desc,1,30),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 store_sales, customer, date_dim + | where ss_customer_sk = c_customer_sk + | and ss_sold_date_sk = d_date_sk + | and d_year in (2000, 2000+1, 2000+2,2000+3) + | group by c_customer_sk) x), + | best_ss_customer as + | (select c_customer_sk,sum(ss_quantity*ss_sales_price) ssales + | from store_sales, customer + | where ss_customer_sk = c_customer_sk + | group by c_customer_sk + | having sum(ss_quantity*ss_sales_price) > (50/100.0) * + | (select * from max_store_sales)) + | select sum(sales) + | from ((select cs_quantity*cs_list_price sales + | from catalog_sales, date_dim + | where d_year = 2000 + | and d_moy = 2 + | 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 web_sales, date_dim + | where d_year = 2000 + | and d_moy = 2 + | 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))) y + | limit 100 + """.stripMargin), + ("q23b", + """ + | + | with frequent_ss_items as + | (select substr(i_item_desc,1,30) itemdesc,i_item_sk item_sk,d_date solddate,count(*) cnt + | from store_sales, date_dim, item + | where ss_sold_date_sk = d_date_sk + | and ss_item_sk = i_item_sk + | and d_year in (2000, 2000+1, 2000+2,2000+3) + | group by substr(i_item_desc,1,30),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 store_sales, customer, date_dim + | where ss_customer_sk = c_customer_sk + | and ss_sold_date_sk = d_date_sk + | and d_year in (2000, 2000+1, 2000+2,2000+3) + | group by c_customer_sk) x), + | best_ss_customer as + | (select c_customer_sk,sum(ss_quantity*ss_sales_price) ssales + | from store_sales + | ,customer + | where ss_customer_sk = c_customer_sk + | group by c_customer_sk + | having sum(ss_quantity*ss_sales_price) > (50/100.0) * + | (select * from max_store_sales)) + | select c_last_name,c_first_name,sales + | from ((select c_last_name,c_first_name,sum(cs_quantity*cs_list_price) sales + | from catalog_sales, customer, date_dim + | where d_year = 2000 + | and d_moy = 2 + | 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) + | and cs_bill_customer_sk = c_customer_sk + | group by c_last_name,c_first_name) + | union all + | (select c_last_name,c_first_name,sum(ws_quantity*ws_list_price) sales + | from web_sales, customer, date_dim + | where d_year = 2000 + | and d_moy = 2 + | 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) + | and ws_bill_customer_sk = c_customer_sk + | group by c_last_name,c_first_name)) y + | order by c_last_name,c_first_name,sales + | limit 100 + """.stripMargin), + ("q24a", + """ + | 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_paid) netpaid + | from store_sales, store_returns, store, item, customer, 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 = 8 + | 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 = 'pale' + | group by c_last_name, c_first_name, s_store_name + | having sum(netpaid) > (select 0.05*avg(netpaid) from ssales) + """.stripMargin), + ("q24b", + """ + | 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_paid) netpaid + | from store_sales, store_returns, store, item, customer, 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 = 8 + | 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 = 'chiffon' + | group by c_last_name, c_first_name, s_store_name + | having sum(netpaid) > (select 0.05*avg(netpaid) from ssales) + """.stripMargin), + ("q25", + """ + | select i_item_id, i_item_desc, s_store_id, s_store_name, + | sum(ss_net_profit) as store_sales_profit, + | sum(sr_net_loss) as store_returns_loss, + | sum(cs_net_profit) as catalog_sales_profit + | from + | store_sales, store_returns, catalog_sales, date_dim d1, date_dim d2, date_dim d3, + | store, 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 + """.stripMargin), + ("q26", + """ + | select i_item_id, + | avg(cs_quantity) agg1, + | avg(cs_list_price) agg2, + | avg(cs_coupon_amt) agg3, + | avg(cs_sales_price) agg4 + | from catalog_sales, customer_demographics, date_dim, item, 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 = 'M' and + | cd_marital_status = 'S' and + | cd_education_status = 'College' 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 + """.stripMargin), + ("q27", + """ + | select i_item_id, + | s_state, grouping(s_state) g_state, + | avg(ss_quantity) agg1, + | avg(ss_list_price) agg2, + | avg(ss_coupon_amt) agg3, + | avg(ss_sales_price) agg4 + | from store_sales, customer_demographics, 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 + | ss_cdemo_sk = cd_demo_sk and + | cd_gender = 'M' and + | cd_marital_status = 'S' and + | cd_education_status = 'College' and + | d_year = 2002 and + | s_state in ('TN','TN', 'TN', 'TN', 'TN', 'TN') + | group by rollup (i_item_id, s_state) + | order by i_item_id, s_state + | limit 100 + """.stripMargin), + ("q28", + """ + | select * + | from (select avg(ss_list_price) B1_LP + | ,count(ss_list_price) B1_CNT + | ,count(distinct ss_list_price) B1_CNTD + | from store_sales + | where ss_quantity between 0 and 5 + | and (ss_list_price between 8 and 8+10 + | or ss_coupon_amt between 459 and 459+1000 + | or ss_wholesale_cost between 57 and 57+20)) B1, + | (select avg(ss_list_price) B2_LP + | ,count(ss_list_price) B2_CNT + | ,count(distinct ss_list_price) B2_CNTD + | from store_sales + | where ss_quantity between 6 and 10 + | and (ss_list_price between 90 and 90+10 + | or ss_coupon_amt between 2323 and 2323+1000 + | or ss_wholesale_cost between 31 and 31+20)) B2, + | (select avg(ss_list_price) B3_LP + | ,count(ss_list_price) B3_CNT + | ,count(distinct ss_list_price) B3_CNTD + | from store_sales + | where ss_quantity between 11 and 15 + | and (ss_list_price between 142 and 142+10 + | or ss_coupon_amt between 12214 and 12214+1000 + | or ss_wholesale_cost between 79 and 79+20)) B3, + | (select avg(ss_list_price) B4_LP + | ,count(ss_list_price) B4_CNT + | ,count(distinct ss_list_price) B4_CNTD + | from store_sales + | where ss_quantity between 16 and 20 + | and (ss_list_price between 135 and 135+10 + | or ss_coupon_amt between 6071 and 6071+1000 + | or ss_wholesale_cost between 38 and 38+20)) B4, + | (select avg(ss_list_price) B5_LP + | ,count(ss_list_price) B5_CNT + | ,count(distinct ss_list_price) B5_CNTD + | from store_sales + | where ss_quantity between 21 and 25 + | and (ss_list_price between 122 and 122+10 + | or ss_coupon_amt between 836 and 836+1000 + | or ss_wholesale_cost between 17 and 17+20)) B5, + | (select avg(ss_list_price) B6_LP + | ,count(ss_list_price) B6_CNT + | ,count(distinct ss_list_price) B6_CNTD + | from store_sales + | where ss_quantity between 26 and 30 + | and (ss_list_price between 154 and 154+10 + | or ss_coupon_amt between 7326 and 7326+1000 + | or ss_wholesale_cost between 7 and 7+20)) B6 + | limit 100 + """.stripMargin), + ("q29", + """ + | select + | i_item_id + | ,i_item_desc + | ,s_store_id + | ,s_store_name + | ,sum(ss_quantity) as store_sales_quantity + | ,sum(sr_return_quantity) as store_returns_quantity + | ,sum(cs_quantity) as catalog_sales_quantity + | from + | store_sales, store_returns, catalog_sales, date_dim d1, date_dim d2, + | date_dim d3, store, item + | where + | d1.d_moy = 9 + | and d1.d_year = 1999 + | 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 9 and 9 + 3 + | and d2.d_year = 1999 + | 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 (1999,1999+1,1999+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 + """.stripMargin), + ("q30", + """ + | 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 web_returns, date_dim, customer_address + | where wr_returned_date_sk = d_date_sk + | and d_year = 2002 + | 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, customer_address, 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 = 'GA' + | 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 + """.stripMargin), + ("q31", + """ + | with ss as + | (select ca_county,d_qoy, d_year,sum(ss_ext_sales_price) as store_sales + | from store_sales,date_dim,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 web_sales,date_dim,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 = 2000 + | and ss1.ca_county = ss2.ca_county + | and ss2.d_qoy = 2 + | and ss2.d_year = 2000 + | and ss2.ca_county = ss3.ca_county + | and ss3.d_qoy = 3 + | and ss3.d_year = 2000 + | and ss1.ca_county = ws1.ca_county + | and ws1.d_qoy = 1 + | and ws1.d_year = 2000 + | and ws1.ca_county = ws2.ca_county + | and ws2.d_qoy = 2 + | and ws2.d_year = 2000 + | and ws1.ca_county = ws3.ca_county + | and ws3.d_qoy = 3 + | and ws3.d_year = 2000 + | 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.ca_county + """.stripMargin), + // Modifications: " -> ` + ("q32", + """ + | select sum(cs_ext_discount_amt) as `excess discount amount` + | from + | catalog_sales, item, date_dim + | where + | i_manufact_id = 977 + | and i_item_sk = cs_item_sk + | and d_date between '2000-01-27' and (cast('2000-01-27' as date) + interval 90 days) + | and d_date_sk = cs_sold_date_sk + | and cs_ext_discount_amt > ( + | select 1.3 * avg(cs_ext_discount_amt) + | from catalog_sales, date_dim + | where cs_item_sk = i_item_sk + | and d_date between '2000-01-27]' and (cast('2000-01-27' as date) + interval + | 90 days) + | and d_date_sk = cs_sold_date_sk) + |limit 100 + """.stripMargin), + ("q33", + """ + | with ss as ( + | select + | i_manufact_id,sum(ss_ext_sales_price) total_sales + | from + | store_sales, date_dim, customer_address, item + | where + | i_manufact_id in (select i_manufact_id + | from item + | where i_category in ('Electronics')) + | and ss_item_sk = i_item_sk + | and ss_sold_date_sk = d_date_sk + | and d_year = 1998 + | and d_moy = 5 + | 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 catalog_sales, date_dim, customer_address, item + | where + | i_manufact_id in ( + | select i_manufact_id from item + | where + | i_category in ('Electronics')) + | and cs_item_sk = i_item_sk + | and cs_sold_date_sk = d_date_sk + | and d_year = 1998 + | and d_moy = 5 + | 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 + | web_sales, date_dim, customer_address, item + | where + | i_manufact_id in (select i_manufact_id from item + | where i_category in ('Electronics')) + | and ws_item_sk = i_item_sk + | and ws_sold_date_sk = d_date_sk + | and d_year = 1998 + | and d_moy = 5 + | 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 + """.stripMargin), + ("q34", + """ + | 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 store_sales,date_dim,store,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 + """.stripMargin), + ("q35", + """ + | select + | ca_state, + | cd_gender, + | cd_marital_status, + | count(*) cnt1, + | min(cd_dep_count), + | max(cd_dep_count), + | avg(cd_dep_count), + | cd_dep_employed_count, + | count(*) cnt2, + | min(cd_dep_employed_count), + | max(cd_dep_employed_count), + | avg(cd_dep_employed_count), + | cd_dep_college_count, + | count(*) cnt3, + | min(cd_dep_college_count), + | max(cd_dep_college_count), + | avg(cd_dep_college_count) + | from + | customer c,customer_address ca,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 store_sales, date_dim + | where c.c_customer_sk = ss_customer_sk and + | ss_sold_date_sk = d_date_sk and + | d_year = 2002 and + | d_qoy < 4) and + | (exists (select * from web_sales, 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_qoy < 4) or + | exists (select * from catalog_sales, 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_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 + """.stripMargin), + ("q36", + """ + | select + | sum(ss_net_profit)/sum(ss_ext_sales_price) as gross_margin + | ,i_category + | ,i_class + | ,grouping(i_category)+grouping(i_class) as lochierarchy + | ,rank() over ( + | partition by grouping(i_category)+grouping(i_class), + | case when grouping(i_class) = 0 then i_category end + | order by sum(ss_net_profit)/sum(ss_ext_sales_price) asc) as rank_within_parent + | from + | store_sales, date_dim d1, item, store + | where + | 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 s_state in ('TN','TN','TN','TN','TN','TN','TN','TN') + | group by rollup(i_category,i_class) + | order by + | lochierarchy desc + | ,case when lochierarchy = 0 then i_category end + | ,rank_within_parent + | limit 100 + """.stripMargin), + // Modifications: "+ days" -> date_add + ("q37", + """ + | select i_item_id, i_item_desc, i_current_price + | from item, inventory, date_dim, catalog_sales + | where i_current_price between 68 and 68 + 30 + | and inv_item_sk = i_item_sk + | and d_date_sk=inv_date_sk + | and d_date between cast('2000-02-01' as date) and (cast('2000-02-01' as date) + + | interval 60 days) + | and i_manufact_id in (677,940,694,808) + | 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 + """.stripMargin), + ("q38", + """ + | 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 1200 and 1200 + 11 + | intersect + | 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 1200 and 1200 + 11 + | intersect + | 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 1200 and 1200 + 11 + | ) hot_cust + | limit 100 + """.stripMargin), + ("q39a", + """ + | 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 inventory, item, warehouse, 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 = 2001 + | 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,inv2.i_item_sk,inv2.d_moy,inv2.mean, inv2.cov + | 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 + """.stripMargin), + ("q39b", + """ + | 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 inventory, item, warehouse, 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 = 2001 + | 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,inv2.i_item_sk,inv2.d_moy,inv2.mean, inv2.cov + | 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 + | and inv1.cov > 1.5 + | order by inv1.w_warehouse_sk,inv1.i_item_sk,inv1.d_moy,inv1.mean,inv1.cov + | ,inv2.d_moy,inv2.mean, inv2.cov + """.stripMargin), + // Modifications: "+ days" -> date_add + ("q40", + """ + | select + | w_state + | ,i_item_id + | ,sum(case when (cast(d_date as date) < cast('2000-03-11' 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('2000-03-11' as date)) + | then cs_sales_price - coalesce(cr_refunded_cash,0) else 0 end) as sales_after + | from + | catalog_sales left outer join 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 d_date between (cast('2000-03-11' as date) - interval 30 days) + | and (cast('2000-03-11' as date) + interval 30 days) + | group by w_state,i_item_id + | order by w_state,i_item_id + | limit 100 + """.stripMargin), + ("q41", + """ + | select distinct(i_product_name) + | from item i1 + | where i_manufact_id between 738 and 738+40 + | and (select count(*) as item_cnt + | from item + | where (i_manufact = i1.i_manufact and + | ((i_category = 'Women' and + | (i_color = 'powder' or i_color = 'khaki') and + | (i_units = 'Ounce' or i_units = 'Oz') and + | (i_size = 'medium' or i_size = 'extra large') + | ) or + | (i_category = 'Women' and + | (i_color = 'brown' or i_color = 'honeydew') and + | (i_units = 'Bunch' or i_units = 'Ton') and + | (i_size = 'N/A' or i_size = 'small') + | ) or + | (i_category = 'Men' and + | (i_color = 'floral' or i_color = 'deep') and + | (i_units = 'N/A' or i_units = 'Dozen') and + | (i_size = 'petite' or i_size = 'large') + | ) or + | (i_category = 'Men' and + | (i_color = 'light' or i_color = 'cornflower') and + | (i_units = 'Box' or i_units = 'Pound') and + | (i_size = 'medium' or i_size = 'extra large') + | ))) or + | (i_manufact = i1.i_manufact and + | ((i_category = 'Women' and + | (i_color = 'midnight' or i_color = 'snow') and + | (i_units = 'Pallet' or i_units = 'Gross') and + | (i_size = 'medium' or i_size = 'extra large') + | ) or + | (i_category = 'Women' and + | (i_color = 'cyan' or i_color = 'papaya') and + | (i_units = 'Cup' or i_units = 'Dram') and + | (i_size = 'N/A' or i_size = 'small') + | ) or + | (i_category = 'Men' and + | (i_color = 'orange' or i_color = 'frosted') and + | (i_units = 'Each' or i_units = 'Tbl') and + | (i_size = 'petite' or i_size = 'large') + | ) or + | (i_category = 'Men' and + | (i_color = 'forest' or i_color = 'ghost') and + | (i_units = 'Lb' or i_units = 'Bundle') and + | (i_size = 'medium' or i_size = 'extra large') + | )))) > 0 + | order by i_product_name + | limit 100 + """.stripMargin), + ("q42", + """ + | select dt.d_year, item.i_category_id, item.i_category, sum(ss_ext_sales_price) + | from date_dim dt, store_sales, 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=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 + """.stripMargin), + ("q43", + """ + | 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 date_dim, store_sales, store + | where d_date_sk = ss_sold_date_sk and + | s_store_sk = ss_store_sk and + | s_gmt_offset = -5 and + | d_year = 2000 + | 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 + """.stripMargin), + ("q44", + """ + | 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 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 store_sales + | where ss_store_sk = 4 + | and ss_addr_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 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 store_sales + | where ss_store_sk = 4 + | and ss_addr_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 + |
<TRUNCATED>