You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@carbondata.apache.org by ma...@apache.org on 2018/05/17 14:30:40 UTC
[27/50] [abbrv] carbondata git commit: [CARBONDATA-2474] Support
Modular Plan for Materialized View DataMap
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>