http://git-wip-us.apache.org/repos/asf/carbondata/blob/bf73e9fe/datamap/mv/core/src/test/scala/org/apache/carbondata/mv/rewrite/matching/TestTPCDS_1_4_Batch.scala ---------------------------------------------------------------------- diff --git a/datamap/mv/core/src/test/scala/org/apache/carbondata/mv/rewrite/matching/TestTPCDS_1_4_Batch.scala b/datamap/mv/core/src/test/scala/org/apache/carbondata/mv/rewrite/matching/TestTPCDS_1_4_Batch.scala new file mode 100644 index 0000000..074bf00 --- /dev/null +++ b/datamap/mv/core/src/test/scala/org/apache/carbondata/mv/rewrite/matching/TestTPCDS_1_4_Batch.scala @@ -0,0 +1,2496 @@ +/* + * 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.rewrite.matching + +object TestTPCDS_1_4_Batch { + val tpcds_1_4_testCases = Seq( + // sequence of triples. each triple denotes (MV, user query, rewritten query) + // test case 1: test SELECT-SELECT-EXACT_MATCH with simple SELECT (extract from q45) + ("case_1", + """ + |SELECT i_item_id, i_item_sk + |FROM item + |WHERE i_item_sk IN (2, 3, 5, 7, 11, 13, 17, 19, 23, 29) + """.stripMargin.trim, + """ + |SELECT i_item_id + |FROM item + |WHERE i_item_sk IN (2, 3, 5, 7, 11, 13, 17, 19) + """.stripMargin.trim, + """ + |SELECT + |FROM + |WHERE + """.stripMargin.trim), + // test case 2: test SELECT-SELECT-EXACT_MATCH with SELECT containing join (derive from q64) + ("case_2", + """ + |SELECT cs1.product_name, cs1.store_name, cs1.store_zip, cs1.b_street_number, + | cs1.b_streen_name, cs1.b_city, cs1.b_zip, cs1.c_street_number, cs1.c_street_name, + | cs1.c_city, cs1.c_zip, cs1.syear, cs1.cnt, cs1.s1, cs1.s2, cs1.s3, cs2.s1, + | cs2.s2, cs2.s3, cs2.syear, cs2.cnt + |FROM cross_sales cs1,cross_sales cs2 + |WHERE cs1.item_sk=cs2.item_sk AND + | cs1.syear = 1999 AND + | cs2.syear = 1999 + 1 AND + | cs2.cnt <= cs1.cnt AND + | cs1.store_name = cs2.store_name AND + | cs1.store_zip = cs2.store_zip + """.stripMargin.trim, + """ + |SELECT cs1.product_name, cs1.store_name, cs1.store_zip, cs1.b_street_number, + | cs1.b_streen_name, cs1.b_city, cs1.b_zip, cs1.c_street_number, cs1.c_street_name, + | cs1.c_city, cs1.c_zip, cs1.syear, cs1.cnt, cs1.s1, cs1.s2, cs1.s3, cs2.s1, + | cs2.s2, cs2.s3 + |FROM cross_sales cs1,cross_sales cs2 + |WHERE cs1.item_sk=cs2.item_sk AND + | cs1.syear = 1999 AND + | cs2.syear = 1999 + 1 AND + | cs2.cnt <= cs1.cnt AND + | cs1.store_name = cs2.store_name AND + | cs1.store_zip = cs2.store_zip + |ORDER BY cs1.product_name, cs1.store_name, cs2.cnt + """.stripMargin.trim, + """ + |SELECT + |FROM + |WHERE + """.stripMargin.trim), + // test case 3: test simple SELECT with GROUPBY (from q99) + ("case_3", + """ + |SELECT count(ss_sold_date_sk) as not_null_total, + | max(ss_sold_date_sk) as max_ss_sold_date_sk, + | max(ss_sold_time_sk) as max_ss_sold_time_sk, + | ss_item_sk, + | ss_store_sk + |FROM store_sales + |GROUP BY ss_item_sk, ss_store_sk + """.stripMargin.trim, + """ + |SELECT count(ss_sold_date_sk) as not_null_total, + | max(ss_sold_date_sk) as max_ss_sold_date_sk, + | ss_item_sk, + | ss_store_sk + |FROM store_sales + |GROUP BY ss_item_sk, ss_store_sk + """.stripMargin.trim, + """ + |SELECT gen_subsumer_0.`not_null_total`, + | gen_subsumer_0.`max_ss_sold_date_sk`, + | gen_subsumer_0.`ss_item_sk`, + | gen_subsumer_0.`ss_store_sk` + |FROM + | (SELECT count(`ss_sold_date_sk`) AS `not_null_total`, max(`ss_sold_date_sk`) AS `max_ss_sold_date_sk`, max(`ss_sold_time_sk`) AS `max_ss_sold_time_sk`, `ss_item_sk`, `ss_store_sk` + | FROM store_sales + | GROUP BY `ss_item_sk`, `ss_store_sk`) gen_subsumer_0 + """.stripMargin.trim), + // test case 4 test SELECT containing join with GROUPBY (from q65) + ("case_4", + """ + |SELECT ss_store_sk, ss_item_sk, sum(ss_sales_price) as revenue + |FROM store_sales, date_dim + |WHERE ss_sold_date_sk = d_date_sk and d_month_seq between 1176 and 1176+11 + |GROUP BY ss_store_sk, ss_item_sk + """.stripMargin.trim, + """ + |SELECT ss_store_sk, ss_item_sk, sum(ss_sales_price) as revenue + |FROM store_sales, date_dim + |WHERE ss_sold_date_sk = d_date_sk and d_month_seq between 1176 and 1176+11 + |GROUP BY ss_store_sk, ss_item_sk + """.stripMargin.trim, + """ + |SELECT `ss_store_sk`, `ss_item_sk`, sum(`ss_sales_price`) AS `revenue` + |FROM + | store_sales + | INNER JOIN date_dim ON (`d_month_seq` >= 1176) AND (`d_month_seq` <= 1187) AND (`ss_sold_date_sk` = `d_date_sk`) + |GROUP BY `ss_store_sk`, `ss_item_sk` + """.stripMargin.trim), + // the following 6 cases involve an MV of store_sales, item, date_dim + // q3 + ("case_5", + """ + |SELECT dt.d_date, dt.d_moy, dt.d_year, item.i_brand, item.i_brand_id, item.i_item_id, item.i_item_desc, + | item.i_manufact_id, substr(item.i_item_desc, 1, 30) itemdesc, item.i_category, item.i_class, + | item.i_current_price, item.i_item_sk, store_sales.ss_store_sk, + | SUM(store_sales.ss_ext_sales_price) sum_agg, + | SUM(store_sales.ss_quantity*store_sales.ss_list_price) sales, count(*) number_sales + |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 + |GROUP BY dt.d_date, dt.d_moy, dt.d_year, item.i_brand, item.i_brand_id, item.i_item_id, item.i_item_desc, + | item.i_manufact_id, substr(item.i_item_desc, 1, 30), item.i_category, item.i_category_id, + | item.i_class, item.i_class_id, item.i_current_price, item.i_manager_id, + | item.i_item_sk, store_sales.ss_store_sk + """.stripMargin.trim, + """ + | 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.trim, + """ + |SELECT gen_subsumer_0.`d_year`, gen_subsumer_0.`i_brand_id` AS `brand_id`, gen_subsumer_0.`i_brand` AS `brand`, sum(gen_subsumer_0.`sum_agg`) AS `sum_agg` + |FROM + | (SELECT `d_date`, `d_moy`, `d_year`, `i_brand`, `i_brand_id`, `i_item_id`, `i_item_desc`, `i_manufact_id`, substring(`i_item_desc`, 1, 30) AS `itemdesc`, `i_category`, `i_class`, `i_current_price`, `i_item_sk`, `ss_store_sk`, sum(`ss_ext_sales_price`) AS `sum_agg`, sum((CAST(CAST(`ss_quantity` AS DECIMAL(10,0)) AS DECIMAL(12,2)) * CAST(`ss_list_price` AS DECIMAL(12,2)))) AS `sales`, count(1) AS `number_sales` + | FROM + | date_dim dt + | INNER JOIN store_sales ON (`d_date_sk` = `ss_sold_date_sk`) + | INNER JOIN item ON (`ss_item_sk` = `i_item_sk`) + | GROUP BY `d_date`, `d_moy`, `d_year`, `i_brand`, `i_brand_id`, `i_item_id`, `i_item_desc`, `i_manufact_id`, substring(`i_item_desc`, 1, 30), `i_category`, `i_category_id`, `i_class`, `i_class_id`, `i_current_price`, `i_manager_id`, `i_item_sk`, `ss_store_sk`) gen_subsumer_0 + |WHERE + | (gen_subsumer_0.`d_moy` = 11) AND (gen_subsumer_0.`i_manufact_id` = 128) + |GROUP BY gen_subsumer_0.`d_year`, gen_subsumer_0.`i_brand`, gen_subsumer_0.`i_brand_id` + |ORDER BY gen_subsumer_0.`d_year` ASC NULLS FIRST, `sum_agg` DESC NULLS LAST, `brand_id` ASC NULLS FIRST + |LIMIT 100 + """.stripMargin.trim), + // q23a + ("case_6", + """ + |SELECT dt.d_date, dt.d_moy, dt.d_year, item.i_brand, item.i_brand_id, item.i_item_id, item.i_item_desc, + | item.i_manufact_id, substr(item.i_item_desc, 1, 30) itemdesc, item.i_category, item.i_class, + | item.i_current_price, item.i_item_sk, store_sales.ss_store_sk, + | SUM(store_sales.ss_ext_sales_price) sum_agg, + | SUM(store_sales.ss_quantity*store_sales.ss_list_price) sales, count(*) number_sales + |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 + |GROUP BY dt.d_date, dt.d_moy, dt.d_year, item.i_brand, item.i_brand_id, item.i_item_id, item.i_item_desc, + | item.i_manufact_id, substr(item.i_item_desc, 1, 30), item.i_category, item.i_category_id, + | item.i_class, item.i_class_id, item.i_current_price, item.i_manager_id, + | item.i_item_sk, store_sales.ss_store_sk + """.stripMargin.trim, + """ + | 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.trim, + """ + |SELECT sum(gen_subquery_4.`sales`) AS `sum(sales)` + |FROM + | (SELECT (CAST(CAST(catalog_sales.`cs_quantity` AS DECIMAL(10,0)) AS DECIMAL(12,2)) * CAST(catalog_sales.`cs_list_price` AS DECIMAL(12,2))) AS `sales` + | FROM + | catalog_sales + | LEFT SEMI JOIN (SELECT gen_subsumer_0.`i_item_sk` AS `item_sk`, sum(gen_subsumer_0.`number_sales`) AS `count(1)` + | FROM + | (SELECT dt.`d_date`, dt.`d_moy`, dt.`d_year`, item.`i_brand`, item.`i_brand_id`, item.`i_item_id`, item.`i_item_desc`, item.`i_manufact_id`, substring(item.`i_item_desc`, 1, 30) AS `itemdesc`, item.`i_category`, item.`i_class`, item.`i_current_price`, item.`i_item_sk`, store_sales.`ss_store_sk`, sum(store_sales.`ss_ext_sales_price`) AS `sum_agg`, sum((CAST(CAST(store_sales.`ss_quantity` AS DECIMAL(10,0)) AS DECIMAL(12,2)) * CAST(store_sales.`ss_list_price` AS DECIMAL(12,2)))) AS `sales`, count(1) AS `number_sales` + | FROM + | date_dim dt + | INNER JOIN store_sales ON (dt.`d_date_sk` = store_sales.`ss_sold_date_sk`) + | INNER JOIN item ON (store_sales.`ss_item_sk` = item.`i_item_sk`) + | GROUP BY dt.`d_date`, dt.`d_moy`, dt.`d_year`, item.`i_brand`, item.`i_brand_id`, item.`i_item_id`, item.`i_item_desc`, item.`i_manufact_id`, substring(item.`i_item_desc`, 1, 30), item.`i_category`, item.`i_category_id`, item.`i_class`, item.`i_class_id`, item.`i_current_price`, item.`i_manager_id`, item.`i_item_sk`, store_sales.`ss_store_sk`) gen_subsumer_0 + | WHERE + | (gen_subsumer_0.`d_year` IN (2000, 2001, 2002, 2003)) + | GROUP BY gen_subsumer_0.`itemdesc`, gen_subsumer_0.`i_item_sk`, gen_subsumer_0.`d_date`) gen_subquery_0 ON (gen_subquery_0.`count(1)` > 4L) AND (catalog_sales.`cs_item_sk` = gen_subquery_0.`item_sk`) + | LEFT SEMI JOIN (SELECT customer.`c_customer_sk`, sum((CAST(CAST(store_sales.`ss_quantity` AS DECIMAL(10,0)) AS DECIMAL(12,2)) * CAST(store_sales.`ss_sales_price` AS DECIMAL(12,2)))) AS `sum(CheckOverflow((promote_precision(cast(cast(ss_quantity#271 as decimal(10,0)) as decimal(12,2))) * promote_precision(cast(ss_sales_price#274 as decimal(12,2)))), DecimalType(18,2)))` + | FROM + | store_sales + | INNER JOIN customer ON (store_sales.`ss_customer_sk` = customer.`c_customer_sk`) + | GROUP BY customer.`c_customer_sk`) gen_subquery_1 ON (CAST(gen_subquery_1.`sum(CheckOverflow((promote_precision(cast(cast(ss_quantity#271 as decimal(10,0)) as decimal(12,2))) * promote_precision(cast(ss_sales_price#274 as decimal(12,2)))), DecimalType(18,2)))` AS DECIMAL(38,8)) > (0.500000BD * CAST((SELECT max(gen_scalar_subquery_0_0.`csales`) AS `tpcds_cmax` FROM (SELECT sum((CAST(CAST(store_sales.`ss_quantity` AS DECIMAL(10,0)) AS DECIMAL(12,2)) * CAST(store_sales.`ss_sales_price` AS DECIMAL(12,2)))) AS `csales` FROM store_sales INNER JOIN customer ON (store_sales.`ss_customer_sk` = customer.`c_customer_sk`) INNER JOIN date_dim ON (date_dim.`d_year` IN (2000, 2001, 2002, 2003)) AND (store_sales.`ss_sold_date_sk` = date_dim.`d_date_sk`) GROUP BY customer.`c_customer_sk`) gen_scalar_subquery_0_0 ) AS DECIMAL(32,6)))) AND (catalog_sales.`cs_bill_customer_sk` = gen_subquery_1.`c_customer_sk`) + | INNER JOIN date_dim ON (date_dim.`d_year` = 2000) AND (date_dim.`d_moy` = 2) AND (catalog_sales.`cs_sold_date_sk` = date_dim.`d_date_sk`) + | UNION ALL + | SELECT (CAST(CAST(web_sales.`ws_quantity` AS DECIMAL(10,0)) AS DECIMAL(12,2)) * CAST(web_sales.`ws_list_price` AS DECIMAL(12,2))) AS `sales` + | FROM + | web_sales + | LEFT SEMI JOIN (SELECT gen_subsumer_1.`i_item_sk` AS `item_sk`, sum(gen_subsumer_1.`number_sales`) AS `count(1)` + | FROM + | (SELECT dt.`d_date`, dt.`d_moy`, dt.`d_year`, item.`i_brand`, item.`i_brand_id`, item.`i_item_id`, item.`i_item_desc`, item.`i_manufact_id`, substring(item.`i_item_desc`, 1, 30) AS `itemdesc`, item.`i_category`, item.`i_class`, item.`i_current_price`, item.`i_item_sk`, store_sales.`ss_store_sk`, sum(store_sales.`ss_ext_sales_price`) AS `sum_agg`, sum((CAST(CAST(store_sales.`ss_quantity` AS DECIMAL(10,0)) AS DECIMAL(12,2)) * CAST(store_sales.`ss_list_price` AS DECIMAL(12,2)))) AS `sales`, count(1) AS `number_sales` + | FROM + | date_dim dt + | INNER JOIN store_sales ON (dt.`d_date_sk` = store_sales.`ss_sold_date_sk`) + | INNER JOIN item ON (store_sales.`ss_item_sk` = item.`i_item_sk`) + | GROUP BY dt.`d_date`, dt.`d_moy`, dt.`d_year`, item.`i_brand`, item.`i_brand_id`, item.`i_item_id`, item.`i_item_desc`, item.`i_manufact_id`, substring(item.`i_item_desc`, 1, 30), item.`i_category`, item.`i_category_id`, item.`i_class`, item.`i_class_id`, item.`i_current_price`, item.`i_manager_id`, item.`i_item_sk`, store_sales.`ss_store_sk`) gen_subsumer_1 + | WHERE + | (gen_subsumer_1.`d_year` IN (2000, 2001, 2002, 2003)) + | GROUP BY gen_subsumer_1.`itemdesc`, gen_subsumer_1.`i_item_sk`, gen_subsumer_1.`d_date`) gen_subquery_2 ON (gen_subquery_2.`count(1)` > 4L) AND (web_sales.`ws_item_sk` = gen_subquery_2.`item_sk`) + | LEFT SEMI JOIN (SELECT customer.`c_customer_sk`, sum((CAST(CAST(store_sales.`ss_quantity` AS DECIMAL(10,0)) AS DECIMAL(12,2)) * CAST(store_sales.`ss_sales_price` AS DECIMAL(12,2)))) AS `sum(CheckOverflow((promote_precision(cast(cast(ss_quantity#271 as decimal(10,0)) as decimal(12,2))) * promote_precision(cast(ss_sales_price#274 as decimal(12,2)))), DecimalType(18,2)))` + | FROM + | store_sales + | INNER JOIN customer ON (store_sales.`ss_customer_sk` = customer.`c_customer_sk`) + | GROUP BY customer.`c_customer_sk`) gen_subquery_3 ON (CAST(gen_subquery_3.`sum(CheckOverflow((promote_precision(cast(cast(ss_quantity#271 as decimal(10,0)) as decimal(12,2))) * promote_precision(cast(ss_sales_price#274 as decimal(12,2)))), DecimalType(18,2)))` AS DECIMAL(38,8)) > (0.500000BD * CAST((SELECT max(gen_scalar_subquery_1_0.`csales`) AS `tpcds_cmax` FROM (SELECT sum((CAST(CAST(store_sales.`ss_quantity` AS DECIMAL(10,0)) AS DECIMAL(12,2)) * CAST(store_sales.`ss_sales_price` AS DECIMAL(12,2)))) AS `csales` FROM store_sales INNER JOIN customer ON (store_sales.`ss_customer_sk` = customer.`c_customer_sk`) INNER JOIN date_dim ON (date_dim.`d_year` IN (2000, 2001, 2002, 2003)) AND (store_sales.`ss_sold_date_sk` = date_dim.`d_date_sk`) GROUP BY customer.`c_customer_sk`) gen_scalar_subquery_1_0 ) AS DECIMAL(32,6)))) AND (web_sales.`ws_bill_customer_sk` = gen_subquery_3.`c_customer_sk`) + | INNER JOIN date_dim ON (date_dim.`d_year` = 2000) AND (date_dim.`d_moy` = 2) AND (web_sales.`ws_sold_date_sk` = date_dim.`d_date_sk`)) gen_subquery_4 + |LIMIT 100 + """.stripMargin.trim), + // q14a + ("case_7", + """ + |SELECT dt.d_date, dt.d_moy, dt.d_year, item.i_brand, item.i_brand_id, item.i_item_id, item.i_item_desc, + | substr(item.i_item_desc, 1, 30) itemdesc, item.i_category, item.i_class, + | item.i_current_price, item.i_item_sk, store_sales.ss_store_sk, + | SUM(store_sales.ss_ext_sales_price) sum_agg, + | SUM(store_sales.ss_quantity*store_sales.ss_list_price) sales, count(*) number_sales + |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 + |GROUP BY dt.d_date, dt.d_moy, dt.d_year, item.i_brand, item.i_brand_id, item.i_item_id, item.i_item_desc, + | substr(item.i_item_desc, 1, 30), item.i_category, item.i_category_id, + | item.i_class, item.i_class_id, item.i_current_price, item.i_manager_id, + | item.i_item_sk, store_sales.ss_store_sk + """.stripMargin.trim, + """ + |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.trim, + """ + | + | + | + """.stripMargin.trim), + // q55 + ("case_8", + """ + |SELECT dt.d_date, dt.d_moy, dt.d_year, item.i_brand, item.i_brand_id, item.i_item_id, item.i_item_desc, + | substr(item.i_item_desc, 1, 30) itemdesc, item.i_category, item.i_class, + | item.i_manager_id, item.i_current_price, item.i_item_sk, store_sales.ss_store_sk, + | SUM(store_sales.ss_ext_sales_price) sum_agg, + | SUM(store_sales.ss_quantity*store_sales.ss_list_price) sales, count(*) number_sales + |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 + |GROUP BY dt.d_date, dt.d_moy, dt.d_year, item.i_brand, item.i_brand_id, item.i_item_id, item.i_item_desc, + | substr(item.i_item_desc, 1, 30), item.i_category, item.i_category_id, + | item.i_class, item.i_class_id, item.i_current_price, item.i_manager_id, + | item.i_item_sk, store_sales.ss_store_sk + """.stripMargin.trim, + """ + |select i_brand_id brand_id, i_brand brand, + | sum(ss_ext_sales_price) ext_price + | from date_dim, store_sales, item + | where d_date_sk = ss_sold_date_sk + | and ss_item_sk = i_item_sk + | and i_manager_id=28 + | and d_moy=11 + | and d_year=1999 + | group by i_brand, i_brand_id + | order by ext_price desc, brand_id + | limit 100 + """.stripMargin.trim, + """ + |SELECT gen_subsumer_0.`i_brand_id` AS `brand_id`, gen_subsumer_0.`i_brand` AS `brand`, sum(gen_subsumer_0.`sum_agg`) AS `ext_price` + |FROM + | (SELECT `d_date`, `d_moy`, `d_year`, `i_brand`, `i_brand_id`, `i_item_id`, `i_item_desc`, substring(`i_item_desc`, 1, 30) AS `itemdesc`, `i_category`, `i_class`, `i_manager_id`, `i_current_price`, `i_item_sk`, `ss_store_sk`, sum(`ss_ext_sales_price`) AS `sum_agg`, sum((CAST(CAST(`ss_quantity` AS DECIMAL(10,0)) AS DECIMAL(12,2)) * CAST(`ss_list_price` AS DECIMAL(12,2)))) AS `sales`, count(1) AS `number_sales` + | FROM + | date_dim dt + | INNER JOIN store_sales ON (`d_date_sk` = `ss_sold_date_sk`) + | INNER JOIN item ON (`ss_item_sk` = `i_item_sk`) + | GROUP BY `d_date`, `d_moy`, `d_year`, `i_brand`, `i_brand_id`, `i_item_id`, `i_item_desc`, substring(`i_item_desc`, 1, 30), `i_category`, `i_category_id`, `i_class`, `i_class_id`, `i_current_price`, `i_manager_id`, `i_item_sk`, `ss_store_sk`) gen_subsumer_0 + |WHERE + | (gen_subsumer_0.`d_moy` = 11) AND (gen_subsumer_0.`d_year` = 1999) AND (gen_subsumer_0.`i_manager_id` = 28) + |GROUP BY gen_subsumer_0.`i_brand`, gen_subsumer_0.`i_brand_id` + |ORDER BY `ext_price` DESC NULLS LAST, `brand_id` ASC NULLS FIRST + |LIMIT 100 + """.stripMargin.trim), + // q98 + ("case_9", + """ + |SELECT dt.d_date, dt.d_moy, dt.d_year, item.i_brand, item.i_brand_id, item.i_item_id, item.i_item_desc, + | substr(item.i_item_desc, 1, 30) itemdesc, item.i_category, item.i_class, + | item.i_manager_id, item.i_current_price, item.i_item_sk, store_sales.ss_store_sk, + | SUM(store_sales.ss_ext_sales_price) sum_agg, + | SUM(store_sales.ss_quantity*store_sales.ss_list_price) sales, count(*) number_sales + |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 + |GROUP BY dt.d_date, dt.d_moy, dt.d_year, item.i_brand, item.i_brand_id, item.i_item_id, item.i_item_desc, + | substr(item.i_item_desc, 1, 30), item.i_category, item.i_category_id, + | item.i_class, item.i_class_id, item.i_current_price, item.i_manager_id, + | item.i_item_sk, store_sales.ss_store_sk + """.stripMargin.trim, + """ + |select i_item_desc, i_category, i_class, i_current_price + | ,sum(ss_ext_sales_price) as itemrevenue + | ,sum(ss_ext_sales_price)*100/sum(sum(ss_ext_sales_price)) over + | (partition by i_class) as revenueratio + |from + | store_sales, item, date_dim + |where + | ss_item_sk = i_item_sk + | and i_category in ('Sports', 'Books', 'Home') + | and ss_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 + """.stripMargin.trim, + """ + | + | + | + """.stripMargin.trim), + // q76 + ("case_10", + """ + |SELECT dt.d_date, dt.d_moy, dt.d_year, item.i_brand, item.i_brand_id, item.i_item_id, item.i_item_desc, + | substr(item.i_item_desc, 1, 30) itemdesc, item.i_category, item.i_class, + | item.i_manager_id, item.i_current_price, item.i_item_sk, store_sales.ss_store_sk, + | SUM(store_sales.ss_ext_sales_price) sum_agg, + | SUM(store_sales.ss_quantity*store_sales.ss_list_price) sales, count(*) number_sales + |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 + |GROUP BY dt.d_date, dt.d_moy, dt.d_year, item.i_brand, item.i_brand_id, item.i_item_id, item.i_item_desc, + | substr(item.i_item_desc, 1, 30), item.i_category, item.i_category_id, + | item.i_class, item.i_class_id, item.i_current_price, item.i_manager_id, + | item.i_item_sk, store_sales.ss_store_sk + """.stripMargin.trim, + """ + | SELECT + | channel, col_name, d_year, d_qoy, i_category, COUNT(*) sales_cnt, + | SUM(ext_sales_price) sales_amt + | FROM( + | SELECT + | 'store' as channel, ss_store_sk col_name, d_year, d_qoy, i_category, + | ss_ext_sales_price ext_sales_price + | FROM store_sales, item, date_dim + | WHERE ss_store_sk IS NULL + | AND ss_sold_date_sk=d_date_sk + | AND ss_item_sk=i_item_sk + | UNION ALL + | SELECT + | 'web' as channel, ws_ship_customer_sk col_name, d_year, d_qoy, i_category, + | ws_ext_sales_price ext_sales_price + | FROM web_sales, item, date_dim + | WHERE ws_ship_customer_sk IS NULL + | AND ws_sold_date_sk=d_date_sk + | AND ws_item_sk=i_item_sk + | UNION ALL + | SELECT + | 'catalog' as channel, cs_ship_addr_sk col_name, d_year, d_qoy, i_category, + | cs_ext_sales_price ext_sales_price + | FROM catalog_sales, item, date_dim + | WHERE cs_ship_addr_sk IS NULL + | AND cs_sold_date_sk=d_date_sk + | AND cs_item_sk=i_item_sk) foo + | GROUP BY channel, col_name, d_year, d_qoy, i_category + | ORDER BY channel, col_name, d_year, d_qoy, i_category + | limit 100 + """.stripMargin.trim, + """ + | + | + | + """.stripMargin.trim), + // the following four cases involve a MV of catalog_sales, item, date_dim + // q20 + ("case_11", + """ + |SELECT cs_ship_addr_sk , d_date, d_year, d_qoy, d_moy, i_category,i_item_sk, i_item_id, + | i_item_desc, i_class, i_current_price, i_brand_id, i_class_id, i_category_id, i_manufact_id, + | SUM(cs_ext_sales_price) sales_amt, + | SUM(cs_quantity*cs_list_price) sales, + | SUM(cs_ext_discount_amt) as `excess discount amount`, + | count(*) number_sales + |FROM catalog_sales, item, date_dim + |WHERE cs_item_sk = i_item_sk + | AND cs_sold_date_sk = d_date_sk + |GROUP BY i_brand_id, i_class_id, i_category_id, i_item_id, i_item_desc, i_category, i_class, + | i_current_price, i_manufact_id, d_date, d_moy, d_qoy, d_year, cs_ship_addr_sk, i_item_sk + """.stripMargin.trim, + """ + |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.trim, + """ + |SELECT gen_subquery_1.`i_item_desc`, gen_subquery_1.`i_category`, gen_subquery_1.`i_class`, gen_subquery_1.`i_current_price`, gen_subquery_1.`itemrevenue`, ((gen_subquery_1.`_w0` * 100.00BD) / CAST(gen_subquery_1.`_we0` AS DECIMAL(28,2))) AS `revenueratio` + |FROM + | (SELECT gen_subquery_0.`i_item_desc`, gen_subquery_0.`i_category`, gen_subquery_0.`i_class`, gen_subquery_0.`i_current_price`, gen_subquery_0.`itemrevenue`, gen_subquery_0.`_w0`, gen_subquery_0.`_w1`, gen_subquery_0.`i_item_id`, sum(gen_subquery_0.`_w1`) OVER (PARTITION BY gen_subquery_0.`i_class` ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS `_we0` + | FROM + | (SELECT gen_subsumer_0.`i_item_desc`, gen_subsumer_0.`i_category`, gen_subsumer_0.`i_class`, gen_subsumer_0.`i_current_price`, sum(gen_subsumer_0.`sales_amt`) AS `itemrevenue`, sum(gen_subsumer_0.`sales_amt`) AS `_w0`, sum(gen_subsumer_0.`sales_amt`) AS `_w1`, gen_subsumer_0.`i_item_id` + | FROM + | (SELECT `cs_ship_addr_sk`, `d_date`, `d_year`, `d_qoy`, `d_moy`, `i_category`, `cs_ship_addr_sk`, `i_item_sk`, `i_item_id`, `i_item_desc`, `i_class`, `i_current_price`, `i_brand_id`, `i_class_id`, `i_category_id`, `i_manufact_id`, sum(`cs_ext_sales_price`) AS `sales_amt`, sum((CAST(CAST(`cs_quantity` AS DECIMAL(10,0)) AS DECIMAL(12,2)) * CAST(`cs_list_price` AS DECIMAL(12,2)))) AS `sales`, sum(`cs_ext_discount_amt`) AS `excess discount amount`, count(1) AS `number_sales` + | FROM + | catalog_sales + | INNER JOIN item ON (`cs_item_sk` = `i_item_sk`) + | INNER JOIN date_dim ON (`cs_sold_date_sk` = `d_date_sk`) + | GROUP BY `i_brand_id`, `i_class_id`, `i_category_id`, `i_item_id`, `i_item_desc`, `i_category`, `i_class`, `i_current_price`, `i_manufact_id`, `d_date`, `d_moy`, `d_qoy`, `d_year`, `cs_ship_addr_sk`, `i_item_sk`) gen_subsumer_0 + | WHERE + | (gen_subsumer_0.`i_category` IN ('Sports', 'Books', 'Home')) AND (gen_subsumer_0.`d_date` >= DATE '1999-02-22') AND (gen_subsumer_0.`d_date` <= DATE '1999-03-24') + | GROUP BY gen_subsumer_0.`i_item_id`, gen_subsumer_0.`i_item_desc`, gen_subsumer_0.`i_category`, gen_subsumer_0.`i_class`, gen_subsumer_0.`i_current_price`) gen_subquery_0 ) gen_subquery_1 + |ORDER BY gen_subquery_1.`i_category` ASC NULLS FIRST, gen_subquery_1.`i_class` ASC NULLS FIRST, gen_subquery_1.`i_item_id` ASC NULLS FIRST, gen_subquery_1.`i_item_desc` ASC NULLS FIRST, `revenueratio` ASC NULLS FIRST + |LIMIT 100 + """.stripMargin.trim), + // q32 + ("case_12", + """ + |SELECT cs_ship_addr_sk , d_date, d_year, d_qoy, d_moy, i_category, cs_ship_addr_sk,i_item_sk, i_item_id, + | i_item_desc, i_class, i_current_price, i_brand_id, i_class_id, i_category_id, i_manufact_id, + | SUM(cs_ext_sales_price) sales_amt, + | SUM(cs_quantity*cs_list_price) sales, + | SUM(cs_ext_discount_amt) as `excess discount amount`, + | count(*) number_sales + |FROM catalog_sales, item, date_dim + |WHERE cs_item_sk = i_item_sk + | AND cs_sold_date_sk = d_date_sk + |GROUP BY i_brand_id, i_class_id, i_category_id, i_item_id, i_item_desc, i_category, i_class, + | i_current_price, i_manufact_id, d_date, d_moy, d_qoy, d_year, cs_ship_addr_sk, i_item_sk + """.stripMargin.trim, + """ + | 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.trim, + """ + | + | + | + """.stripMargin.trim), + // q58 debug + ("case_13", + """ + |SELECT cs_ship_addr_sk , d_date, d_year, d_qoy, d_moy, i_category, cs_ext_sales_price, cs_ship_addr_sk, i_item_sk, i_item_id, + | i_item_desc, i_class, i_current_price, i_brand_id, i_class_id, i_category_id, i_manufact_id, + | SUM(cs_ext_sales_price) sales_amt, + | SUM(cs_ext_sales_price)*100/sum(sum(cs_ext_sales_price)) over + | (partition by i_class) as revenueratio + | SUM(cs_quantity*cs_list_price) sales, + | SUM(cs_ext_discount_amt) as `excess discount amount`, + | count(*) number_sales + |FROM catalog_sales, item, date_dim + |WHERE cs_item_sk = i_item_sk + | AND cs_sold_date_sk = d_date_sk + |GROUP BY i_brand_id, i_class_id, i_category_id, i_item_id, i_item_desc, i_category, i_class, + | i_current_price, i_manufact_id, d_date, d_moy, d_qoy, d_year, cs_ship_addr_sk, i_item_sk + """.stripMargin.trim, + """ + | with ss_items as + | (select i_item_id item_id, sum(ss_ext_sales_price) ss_item_rev + | from store_sales, item, date_dim + | where ss_item_sk = i_item_sk + | and d_date in (select d_date + | from date_dim + | where d_week_seq = (select d_week_seq + | from date_dim + | where d_date = '2000-01-03')) + | and ss_sold_date_sk = d_date_sk + | group by i_item_id), + | cs_items as + | (select i_item_id item_id + | ,sum(cs_ext_sales_price) cs_item_rev + | from catalog_sales, item, date_dim + | where cs_item_sk = i_item_sk + | and d_date in (select d_date + | from date_dim + | where d_week_seq = (select d_week_seq + | from date_dim + | where d_date = '2000-01-03')) + | and cs_sold_date_sk = d_date_sk + | group by i_item_id), + | ws_items as + | (select i_item_id item_id, sum(ws_ext_sales_price) ws_item_rev + | from web_sales, item, date_dim + | where ws_item_sk = i_item_sk + | and d_date in (select d_date + | from date_dim + | where d_week_seq =(select d_week_seq + | from date_dim + | where d_date = '2000-01-03')) + | and ws_sold_date_sk = d_date_sk + | group by i_item_id) + | select ss_items.item_id + | ,ss_item_rev + | ,ss_item_rev/(ss_item_rev+cs_item_rev+ws_item_rev)/3 * 100 ss_dev + | ,cs_item_rev + | ,cs_item_rev/(ss_item_rev+cs_item_rev+ws_item_rev)/3 * 100 cs_dev + | ,ws_item_rev + | ,ws_item_rev/(ss_item_rev+cs_item_rev+ws_item_rev)/3 * 100 ws_dev + | ,(ss_item_rev+cs_item_rev+ws_item_rev)/3 average + | from ss_items,cs_items,ws_items + | where ss_items.item_id=cs_items.item_id + | and ss_items.item_id=ws_items.item_id + | and ss_item_rev between 0.9 * cs_item_rev and 1.1 * cs_item_rev + | and ss_item_rev between 0.9 * ws_item_rev and 1.1 * ws_item_rev + | and cs_item_rev between 0.9 * ss_item_rev and 1.1 * ss_item_rev + | and cs_item_rev between 0.9 * ws_item_rev and 1.1 * ws_item_rev + | and ws_item_rev between 0.9 * ss_item_rev and 1.1 * ss_item_rev + | and ws_item_rev between 0.9 * cs_item_rev and 1.1 * cs_item_rev + | order by item_id, ss_item_rev + | limit 100 + """.stripMargin.trim, + """ + | + | + | + """.stripMargin.trim), + // q76 + ("case_14", + """ + |SELECT cs_ship_addr_sk , d_date, d_year, d_qoy, d_moy, i_category, cs_ext_sales_price, cs_ship_addr_sk, i_item_sk, i_item_id, + | i_item_desc, i_class, i_current_price, i_brand_id, i_class_id, i_category_id, i_manufact_id, + | SUM(cs_ext_sales_price) sales_amt, + | SUM(cs_quantity*cs_list_price) sales, + | SUM(cs_ext_discount_amt) as `excess discount amount`, + | count(*) number_sales + |FROM catalog_sales, item, date_dim + |WHERE cs_item_sk = i_item_sk + | AND cs_sold_date_sk = d_date_sk + |GROUP BY i_brand_id, i_class_id, i_category_id, i_item_id, i_item_desc, i_category, i_class, + | i_current_price, i_manufact_id, d_date, d_moy, d_qoy, d_year, cs_ship_addr_sk, i_item_sk + """.stripMargin.trim, + """ + | SELECT + | channel, col_name, d_year, d_qoy, i_category, COUNT(*) sales_cnt, + | SUM(ext_sales_price) sales_amt + | FROM( + | SELECT + | 'store' as channel, ss_store_sk col_name, d_year, d_qoy, i_category, + | ss_ext_sales_price ext_sales_price + | FROM store_sales, item, date_dim + | WHERE ss_store_sk IS NULL + | AND ss_sold_date_sk=d_date_sk + | AND ss_item_sk=i_item_sk + | UNION ALL + | SELECT + | 'web' as channel, ws_ship_customer_sk col_name, d_year, d_qoy, i_category, + | ws_ext_sales_price ext_sales_price + | FROM web_sales, item, date_dim + | WHERE ws_ship_customer_sk IS NULL + | AND ws_sold_date_sk=d_date_sk + | AND ws_item_sk=i_item_sk + | UNION ALL + | SELECT + | 'catalog' as channel, cs_ship_addr_sk col_name, d_year, d_qoy, i_category, + | cs_ext_sales_price ext_sales_price + | FROM catalog_sales, item, date_dim + | WHERE cs_ship_addr_sk IS NULL + | AND cs_sold_date_sk=d_date_sk + | AND cs_item_sk=i_item_sk) foo + | GROUP BY channel, col_name, d_year, d_qoy, i_category + | ORDER BY channel, col_name, d_year, d_qoy, i_category + | limit 100 + """.stripMargin.trim, + """ + | + | + | + """.stripMargin.trim), + // the following two cases involve a MV of store_sales and customer + // q23a + ("case_15", + """ + | SELECT c_customer_sk, + | sum(ss_quantity*ss_sales_price) csales + | FROM customer, store_sales + | WHERE c_customer_sk = ss_customer_sk + | GROUP BY c_customer_sk + """.stripMargin.trim, + """ + | 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.trim, + """ + | + | + | + """.stripMargin.trim), + // q23b + ("case_16", + """ + | SELECT c_customer_sk, + | sum(ss_quantity*ss_sales_price) csales + | FROM customer, store_sales + | WHERE c_customer_sk = ss_customer_sk + | GROUP BY c_customer_sk + """.stripMargin.trim, + """ + | + | 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.trim, + """ + |SELECT gen_subquery_6.`c_last_name`, gen_subquery_6.`c_first_name`, gen_subquery_6.`sales` + |FROM + | (SELECT gen_subquery_2.`c_last_name`, gen_subquery_2.`c_first_name`, sum((CAST(CAST(gen_subquery_2.`cs_quantity` AS DECIMAL(10,0)) AS DECIMAL(12,2)) * CAST(gen_subquery_2.`cs_list_price` AS DECIMAL(12,2)))) AS `sales` + | FROM + | (SELECT `cs_quantity`, `cs_list_price`, `c_first_name`, `c_last_name` + | FROM + | catalog_sales + | LEFT SEMI JOIN (SELECT item.`i_item_sk` AS `item_sk`, count(1) AS `count(1)` + | FROM + | store_sales + | INNER JOIN date_dim ON (date_dim.`d_year` IN (2000, 2001, 2002, 2003)) AND (store_sales.`ss_sold_date_sk` = date_dim.`d_date_sk`) + | INNER JOIN item ON (store_sales.`ss_item_sk` = item.`i_item_sk`) + | GROUP BY substring(item.`i_item_desc`, 1, 30), item.`i_item_sk`, date_dim.`d_date`) gen_subquery_0 ON (gen_subquery_0.`count(1)` > 4L) AND (catalog_sales.`cs_item_sk` = gen_subquery_0.`item_sk`) + | LEFT SEMI JOIN (SELECT customer.`c_customer_sk`, sum((CAST(CAST(store_sales.`ss_quantity` AS DECIMAL(10,0)) AS DECIMAL(12,2)) * CAST(store_sales.`ss_sales_price` AS DECIMAL(12,2)))) AS `csales` + | FROM + | customer + | INNER JOIN store_sales ON (customer.`c_customer_sk` = store_sales.`ss_customer_sk`) + | GROUP BY customer.`c_customer_sk`) gen_subquery_1 ON (catalog_sales.`cs_bill_customer_sk` = gen_subquery_1.`c_customer_sk`) + | INNER JOIN customer ON (catalog_sales.`cs_bill_customer_sk` = customer.`c_customer_sk`) + | INNER JOIN date_dim ON (date_dim.`d_year` = 2000) AND (date_dim.`d_moy` = 2) AND (catalog_sales.`cs_sold_date_sk` = date_dim.`d_date_sk`) + | WHERE + | (CAST(`sum(CheckOverflow((promote_precision(cast(cast(ss_quantity#219 as decimal(10,0)) as decimal(12,2))) * promote_precision(cast(ss_sales_price#222 as decimal(12,2)))), DecimalType(18,2)))` AS DECIMAL(38,8)) > (0.500000BD * CAST((SELECT max(gen_expression_0_0.`csales`) AS `tpcds_cmax` FROM (SELECT sum((CAST(CAST(store_sales.`ss_quantity` AS DECIMAL(10,0)) AS DECIMAL(12,2)) * CAST(store_sales.`ss_sales_price` AS DECIMAL(12,2)))) AS `csales` FROM store_sales INNER JOIN customer ON (store_sales.`ss_customer_sk` = customer.`c_customer_sk`) INNER JOIN date_dim ON (date_dim.`d_year` IN (2000, 2001, 2002, 2003)) AND (store_sales.`ss_sold_date_sk` = date_dim.`d_date_sk`) GROUP BY customer.`c_customer_sk`) gen_expression_0_0 ) AS DECIMAL(32,6))))) gen_subquery_2 + | GROUP BY gen_subquery_2.`c_last_name`, gen_subquery_2.`c_first_name` + | UNION ALL + | SELECT gen_subquery_5.`c_last_name`, gen_subquery_5.`c_first_name`, sum((CAST(CAST(gen_subquery_5.`ws_quantity` AS DECIMAL(10,0)) AS DECIMAL(12,2)) * CAST(gen_subquery_5.`ws_list_price` AS DECIMAL(12,2)))) AS `sales` + | FROM + | (SELECT `ws_quantity`, `ws_list_price`, `c_first_name`, `c_last_name` + | FROM + | web_sales + | LEFT SEMI JOIN (SELECT item.`i_item_sk` AS `item_sk`, count(1) AS `count(1)` + | FROM + | store_sales + | INNER JOIN date_dim ON (date_dim.`d_year` IN (2000, 2001, 2002, 2003)) AND (store_sales.`ss_sold_date_sk` = date_dim.`d_date_sk`) + | INNER JOIN item ON (store_sales.`ss_item_sk` = item.`i_item_sk`) + | GROUP BY substring(item.`i_item_desc`, 1, 30), item.`i_item_sk`, date_dim.`d_date`) gen_subquery_3 ON (gen_subquery_3.`count(1)` > 4L) AND (web_sales.`ws_item_sk` = gen_subquery_3.`item_sk`) + | LEFT SEMI JOIN (SELECT customer.`c_customer_sk`, sum((CAST(CAST(store_sales.`ss_quantity` AS DECIMAL(10,0)) AS DECIMAL(12,2)) * CAST(store_sales.`ss_sales_price` AS DECIMAL(12,2)))) AS `csales` + | FROM + | customer + | INNER JOIN store_sales ON (customer.`c_customer_sk` = store_sales.`ss_customer_sk`) + | GROUP BY customer.`c_customer_sk`) gen_subquery_4 ON (web_sales.`ws_bill_customer_sk` = gen_subquery_4.`c_customer_sk`) + | INNER JOIN customer ON (web_sales.`ws_bill_customer_sk` = customer.`c_customer_sk`) + | INNER JOIN date_dim ON (date_dim.`d_year` = 2000) AND (date_dim.`d_moy` = 2) AND (web_sales.`ws_sold_date_sk` = date_dim.`d_date_sk`) + | WHERE + | (CAST(`sum(CheckOverflow((promote_precision(cast(cast(ss_quantity#219 as decimal(10,0)) as decimal(12,2))) * promote_precision(cast(ss_sales_price#222 as decimal(12,2)))), DecimalType(18,2)))` AS DECIMAL(38,8)) > (0.500000BD * CAST((SELECT max(gen_expression_1_0.`csales`) AS `tpcds_cmax` FROM (SELECT sum((CAST(CAST(store_sales.`ss_quantity` AS DECIMAL(10,0)) AS DECIMAL(12,2)) * CAST(store_sales.`ss_sales_price` AS DECIMAL(12,2)))) AS `csales` FROM store_sales INNER JOIN customer ON (store_sales.`ss_customer_sk` = customer.`c_customer_sk`) INNER JOIN date_dim ON (date_dim.`d_year` IN (2000, 2001, 2002, 2003)) AND (store_sales.`ss_sold_date_sk` = date_dim.`d_date_sk`) GROUP BY customer.`c_customer_sk`) gen_expression_1_0 ) AS DECIMAL(32,6))))) gen_subquery_5 + | GROUP BY gen_subquery_5.`c_last_name`, gen_subquery_5.`c_first_name`) gen_subquery_6 + |ORDER BY gen_subquery_6.`c_last_name` ASC NULLS FIRST, gen_subquery_6.`c_first_name` ASC NULLS FIRST, gen_subquery_6.`sales` ASC NULLS FIRST + |LIMIT 100 + """.stripMargin.trim), + // the following cases involve a MV of store_sales, customer and date + // q4 + ("case_17", + """ + | 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, + | d_date ddate, + | sum(((ss_ext_list_price-ss_ext_wholesale_cost-ss_ext_discount_amt)+ss_ext_sales_price)/2) year_total, + | sum(ss_ext_list_price-ss_ext_discount_amt) year_total1, + | sum(ss_net_paid) year_total_74, + | '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, + | d_date + """.stripMargin.trim, + """ + |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.trim, + """ + | + | + | + """.stripMargin.trim), + //q11 + ("case_18", + """ + | 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, + | d_date ddate, + | sum(((ss_ext_list_price-ss_ext_wholesale_cost-ss_ext_discount_amt)+ss_ext_sales_price)/2) year_total, + | sum(ss_ext_list_price-ss_ext_discount_amt) year_total1, + | sum(ss_net_paid) year_total_74, + | '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, + | d_date + """.stripMargin.trim, + """ + | 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.trim, + """ + |SELECT gen_subquery_1.`customer_preferred_cust_flag` + |FROM + | (SELECT gen_subsumer_0.`customer_id` AS `customer_id`, sum(gen_subsumer_0.`year_total1`) AS `year_total` + | FROM + | (SELECT `c_customer_id` AS `customer_id`, `c_first_name` AS `customer_first_name`, `c_last_name` AS `customer_last_name`, `c_preferred_cust_flag` AS `customer_preferred_cust_flag`, `c_birth_country` AS `customer_birth_country`, `c_login` AS `customer_login`, `c_email_address` AS `customer_email_address`, `d_year` AS `dyear`, `d_date` AS `ddate`, sum((CAST((((CAST(`ss_ext_list_price` AS DECIMAL(8,2)) - CAST(`ss_ext_wholesale_cost` AS DECIMAL(8,2))) - CAST(`ss_ext_discount_amt` AS DECIMAL(8,2))) + CAST(`ss_ext_sales_price` AS DECIMAL(8,2))) AS DECIMAL(12,2)) / 2.00BD)) AS `year_total`, sum((CAST(`ss_ext_list_price` AS DECIMAL(8,2)) - CAST(`ss_ext_discount_amt` AS DECIMAL(8,2)))) AS `year_total1`, sum(`ss_net_paid`) AS `year_total_74`, 's' AS `sale_type` + | FROM + | customer + | INNER JOIN store_sales ON (`c_customer_sk` = `ss_customer_sk`) + | INNER JOIN date_dim ON (`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`, `d_date`) gen_subsumer_0 + | WHERE + | (gen_subsumer_0.`dyear` = 2001) + | GROUP BY gen_subsumer_0.`customer_id`, gen_subsumer_0.`customer_first_name`, gen_subsumer_0.`customer_last_name`, gen_subsumer_0.`dyear`, gen_subsumer_0.`customer_preferred_cust_flag`, gen_subsumer_0.`customer_birth_country`, gen_subsumer_0.`customer_login`, gen_subsumer_0.`customer_email_address` + | HAVING (sum(gen_subsumer_0.`year_total1`) > 0.00BD) + | UNION ALL + | SELECT customer.`c_customer_id` AS `customer_id`, sum((CAST(web_sales.`ws_ext_list_price` AS DECIMAL(8,2)) - CAST(web_sales.`ws_ext_discount_amt` AS DECIMAL(8,2)))) AS `year_total` + | FROM + | customer + | INNER JOIN web_sales ON (customer.`c_customer_sk` = web_sales.`ws_bill_customer_sk`) + | INNER JOIN date_dim ON (web_sales.`ws_sold_date_sk` = date_dim.`d_date_sk`) + | WHERE + | false + | GROUP BY customer.`c_customer_id`, customer.`c_first_name`, customer.`c_last_name`, customer.`c_preferred_cust_flag`, customer.`c_birth_country`, customer.`c_login`, customer.`c_email_address`, date_dim.`d_year` + | HAVING (`year_total` > 0.00BD)) gen_subquery_0 + | INNER JOIN (SELECT gen_subsumer_1.`customer_id` AS `customer_id`, gen_subsumer_1.`customer_preferred_cust_flag` AS `customer_preferred_cust_flag`, sum(gen_subsumer_1.`year_total1`) AS `year_total` + | FROM + | (SELECT `c_customer_id` AS `customer_id`, `c_first_name` AS `customer_first_name`, `c_last_name` AS `customer_last_name`, `c_preferred_cust_flag` AS `customer_preferred_cust_flag`, `c_birth_country` AS `customer_birth_country`, `c_login` AS `customer_login`, `c_email_address` AS `customer_email_address`, `d_year` AS `dyear`, `d_date` AS `ddate`, sum((CAST((((CAST(`ss_ext_list_price` AS DECIMAL(8,2)) - CAST(`ss_ext_wholesale_cost` AS DECIMAL(8,2))) - CAST(`ss_ext_discount_amt` AS DECIMAL(8,2))) + CAST(`ss_ext_sales_price` AS DECIMAL(8,2))) AS DECIMAL(12,2)) / 2.00BD)) AS `year_total`, sum((CAST(`ss_ext_list_price` AS DECIMAL(8,2)) - CAST(`ss_ext_discount_amt` AS DECIMAL(8,2)))) AS `year_total1`, sum(`ss_net_paid`) AS `year_total_74`, 's' AS `sale_type` + | FROM + | customer + | INNER JOIN store_sales ON (`c_customer_sk` = `ss_customer_sk`) + | INNER JOIN date_dim ON (`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`, `d_date`) gen_subsumer_1 + | WHERE + | (gen_subsumer_1.`dyear` = 2002) + | GROUP BY gen_subsumer_1.`customer_id`, gen_subsumer_1.`customer_first_name`, gen_subsumer_1.`customer_last_name`, gen_subsumer_1.`dyear`, gen_subsumer_1.`customer_preferred_cust_flag`, gen_subsumer_1.`customer_birth_country`, gen_subsumer_1.`customer_login`, gen_subsumer_1.`customer_email_address` + | UNION ALL + | SELECT customer.`c_customer_id` AS `customer_id`, customer.`c_preferred_cust_flag` AS `customer_preferred_cust_flag`, sum((CAST(web_sales.`ws_ext_list_price` AS DECIMAL(8,2)) - CAST(web_sales.`ws_ext_discount_amt` AS DECIMAL(8,2)))) AS `year_total` + | FROM + | customer + | INNER JOIN web_sales ON (customer.`c_customer_sk` = web_sales.`ws_bill_customer_sk`) + | INNER JOIN date_dim ON (web_sales.`ws_sold_date_sk` = date_dim.`d_date_sk`) + | WHERE + | false + | GROUP BY customer.`c_customer_id`, customer.`c_first_name`, customer.`c_last_name`, customer.`c_preferred_cust_flag`, customer.`c_birth_country`, customer.`c_login`, customer.`c_email_address`, date_dim.`d_year`) gen_subquery_1 ON (gen_subquery_1.`customer_id` = gen_subquery_0.`customer_id`) + | INNER JOIN (SELECT gen_subsumer_2.`customer_id` AS `customer_id`, sum(gen_subsumer_2.`year_total1`) AS `year_total` + | FROM + | (SELECT `c_customer_id` AS `customer_id`, `c_first_name` AS `customer_first_name`, `c_last_name` AS `customer_last_name`, `c_preferred_cust_flag` AS `customer_preferred_cust_flag`, `c_birth_country` AS `customer_birth_country`, `c_login` AS `customer_login`, `c_email_address` AS `customer_email_address`, `d_year` AS `dyear`, `d_date` AS `ddate`, sum((CAST((((CAST(`ss_ext_list_price` AS DECIMAL(8,2)) - CAST(`ss_ext_wholesale_cost` AS DECIMAL(8,2))) - CAST(`ss_ext_discount_amt` AS DECIMAL(8,2))) + CAST(`ss_ext_sales_price` AS DECIMAL(8,2))) AS DECIMAL(12,2)) / 2.00BD)) AS `year_total`, sum((CAST(`ss_ext_list_price` AS DECIMAL(8,2)) - CAST(`ss_ext_discount_amt` AS DECIMAL(8,2)))) AS `year_total1`, sum(`ss_net_paid`) AS `year_total_74`, 's' AS `sale_type` + | FROM + | customer + | INNER JOIN store_sales ON (`c_customer_sk` = `ss_customer_sk`) + | INNER JOIN date_dim ON (`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`, `d_date`) gen_subsumer_2 + | WHERE + | false + | GROUP BY gen_subsumer_2.`customer_id`, gen_subsumer_2.`customer_first_name`, gen_subsumer_2.`customer_last_name`, gen_subsumer_2.`dyear`, gen_subsumer_2.`customer_preferred_cust_flag`, gen_subsumer_2.`customer_birth_country`, gen_subsumer_2.`customer_login`, gen_subsumer_2.`customer_email_address` + | HAVING (sum(gen_subsumer_2.`year_total1`) > 0.00BD) + | UNION ALL + | SELECT customer.`c_customer_id` AS `customer_id`, sum((CAST(web_sales.`ws_ext_list_price` AS DECIMAL(8,2)) - CAST(web_sales.`ws_ext_discount_amt` AS DECIMAL(8,2)))) AS `year_total` + | FROM + | customer + | INNER JOIN web_sales ON (customer.`c_customer_sk` = web_sales.`ws_bill_customer_sk`) + | INNER JOIN date_dim ON (date_dim.`d_year` = 2001) AND (web_sales.`ws_sold_date_sk` = date_dim.`d_date_sk`) + | GROUP BY customer.`c_customer_id`, customer.`c_first_name`, customer.`c_last_name`, customer.`c_preferred_cust_flag`, customer.`c_birth_country`, customer.`c_login`, customer.`c_email_address`, date_dim.`d_year` + | HAVING (`year_total` > 0.00BD)) gen_subquery_2 ON (gen_subquery_0.`customer_id` = gen_subquery_2.`customer_id`) + | INNER JOIN (SELECT gen_subsumer_3.`customer_id` AS `customer_id`, sum(gen_subsumer_3.`year_total1`) AS `year_total` + | FROM + | (SELECT `c_customer_id` AS `customer_id`, `c_first_name` AS `customer_first_name`, `c_last_name` AS `customer_last_name`, `c_preferred_cust_flag` AS `customer_preferred_cust_flag`, `c_birth_country` AS `customer_birth_country`, `c_login` AS `customer_login`, `c_email_address` AS `customer_email_address`, `d_year` AS `dyear`, `d_date` AS `ddate`, sum((CAST((((CAST(`ss_ext_list_price` AS DECIMAL(8,2)) - CAST(`ss_ext_wholesale_cost` AS DECIMAL(8,2))) - CAST(`ss_ext_discount_amt` AS DECIMAL(8,2))) + CAST(`ss_ext_sales_price` AS DECIMAL(8,2))) AS DECIMAL(12,2)) / 2.00BD)) AS `year_total`, sum((CAST(`ss_ext_list_price` AS DECIMAL(8,2)) - CAST(`ss_ext_discount_amt` AS DECIMAL(8,2)))) AS `year_total1`, sum(`ss_net_paid`) AS `year_total_74`, 's' AS `sale_type` + | FROM + | customer + | INNER JOIN store_sales ON (`c_customer_sk` = `ss_customer_sk`) + | INNER JOIN date_dim ON (`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`, `d_date`) gen_subsumer_3 + | WHERE + | false + | GROUP BY gen_subsumer_3.`customer_id`, gen_subsumer_3.`customer_first_name`, gen_subsumer_3.`customer_last_name`, gen_subsumer_3.`dyear`, gen_subsumer_3.`customer_preferred_cust_flag`, gen_subsumer_3.`customer_birth_country`, gen_subsumer_3.`customer_login`, gen_subsumer_3.`customer_email_address` + | UNION ALL + | SELECT customer.`c_customer_id` AS `customer_id`, sum((CAST(web_sales.`ws_ext_list_price` AS DECIMAL(8,2)) - CAST(web_sales.`ws_ext_discount_amt` AS DECIMAL(8,2)))) AS `year_total` + | FROM + | customer + | INNER JOIN web_sales ON (customer.`c_customer_sk` = web_sales.`ws_bill_customer_sk`) + | INNER JOIN date_dim ON (date_dim.`d_year` = 2002) AND (web_sales.`ws_sold_date_sk` = date_dim.`d_date_sk`) + | GROUP BY customer.`c_customer_id`, customer.`c_first_name`, customer.`c_last_name`, customer.`c_preferred_cust_flag`, customer.`c_birth_country`, customer.`c_login`, customer.`c_email_address`, date_dim.`d_year`) gen_subquery_3 + |WHERE + | (gen_subquery_0.`customer_id` = gen_subquery_3.`customer_id`) AND (CASE WHEN (gen_subquery_2.`year_total` > 0.00BD) THEN (gen_subquery_3.`year_total` / gen_subquery_2.`year_total`) ELSE CAST(NULL AS DECIMAL(38,20)) END > CASE WHEN (gen_subquery_0.`year_total` > 0.00BD) THEN (gen_subquery_1.`year_total` / gen_subquery_0.`year_total`) ELSE CAST(NULL AS DECIMAL(38,20)) END) + |ORDER BY gen_subquery_1.`customer_preferred_cust_flag` ASC NULLS FIRST + |LIMIT 100 + """.stripMargin.trim), + //q38 + ("case_19", + """ + | 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, + | d_date ddate, + | sum(((ss_ext_list_price-ss_ext_wholesale_cost-ss_ext_discount_amt)+ss_ext_sales_price)/2) year_total, + | sum(ss_ext_list_price-ss_ext_discount_amt) year_total1, + | sum(ss_net_paid) year_total_74, + | '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, + | d_date + """.stripMargin.trim, + """ + | 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.trim, + """ + | + | + | + """.stripMargin.trim), + //q74 + ("case_20", + """ + | 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, + | d_date ddate, + | d_month_seq, + | sum(((ss_ext_list_price-ss_ext_wholesale_cost-ss_ext_discount_amt)+ss_ext_sales_price)/2) year_total, + | sum(ss_net_paid) year_total_74, + | 's' sale_type + | FROM customer, store_sales, date_dim + | WHERE ss_customer_sk = c_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, + | d_date, + | d_month_seq + """.stripMargin.trim, + """ + | with year_total as ( + | select + | c_customer_id customer_id, c_first_name customer_first_name, + | c_last_name customer_last_name, d_year as year, + | sum(ss_net_paid) 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 + | and d_year in (2001,2001+1) + | group by + | c_customer_id, c_first_name, c_last_name, d_year + | union all + | select + | c_customer_id customer_id, c_first_name customer_first_name, + | c_last_name customer_last_name, d_year as year, + | sum(ws_net_paid) 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 + | and d_year in (2001,2001+1) + | group by + | c_customer_id, c_first_name, c_last_name, d_year) + | select + | t_s_secyear.customer_id, t_s_secyear.customer_first_name, t_s_secyear.customer_last_name + | from + | year_total t_s_firstyear, year_total t_s_secyear, + | year_total t_w_firstyear, year_total t_w_secyear + | where t_s_secyear.customer_id = t_s_firstyear.customer_id + | and t_s_firstyear.customer_id = t_w_secyear.customer_id + | and t_s_firstyear.customer_id = t_w_firstyear.customer_id + | and t_s_firstyear.sale_type = 's' + | and t_w_firstyear.sale_type = 'w' + | and t_s_secyear.sale_type = 's' + | and t_w_secyear.sale_type = 'w' + | and t_s_firstyear.year = 2001 + | and t_s_secyear.year = 2001+1 + | and t_w_firstyear.year = 2001 + | and t_w_secyear.year = 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 1, 1, 1 + | limit 100 + """.stripMargin.trim, + """ + |SELECT gen_subquery_1.`customer_id`, gen_subquery_1.`customer_first_name`, gen_subquery_1.`customer_last_name` + |FROM + | (SELECT gen_subsumer_0.`customer_id` AS `customer_id`, gen_subsumer_0.`year_total_74` AS `year_total` + | FROM + | (SELECT customer.`c_customer_id` AS `customer_id`, customer.`c_first_name` AS `customer_first_name`, customer.`c_last_name` AS `customer_last_name`, customer.`c_preferred_cust_flag` AS `customer_preferred_cust_flag`, customer.`c_birth_country` AS `customer_birth_country`, customer.`c_login` AS `customer_login`, customer.`c_email_address` AS `customer_email_address`, date_dim.`d_year` AS `dyear`, date_dim.`d_date` AS `ddate`, date_dim.`d_month_seq`, sum((CAST((((CAST(store_sales.`ss_ext_list_price` AS DECIMAL(8,2)) - CAST(store_sales.`ss_ext_wholesale_cost` AS DECIMAL(8,2))) - CAST(store_sales.`ss_ext_discount_amt` AS DECIMAL(8,2))) + CAST(store_sales.`ss_ext_sales_price` AS DECIMAL(8,2))) AS DECIMAL(12,2)) / 2.00BD)) AS `year_total`, makedecimal(sum(unscaledvalue(store_sales.`ss_net_paid`))) AS `year_total_74`, 's' AS `sale_type` + | FROM + | customer + | INNER JOIN store_sales ON (store_sales.`ss_customer_sk` = customer.`c_customer_sk`) + | INNER JOIN date_dim ON (store_sales.`ss_sold_date_sk` = date_dim.`d_date_sk`) + | GROUP BY customer.`c_customer_id`, customer.`c_first_name`, customer.`c_last_name`, customer.`c_preferred_cust_flag`, customer.`c_birth_country`, customer.`c_login`, customer.`c_email_address`, date_dim.`d_year`, date_dim.`d_date`, date_dim.`d_month_seq`) gen_subsumer_0 + | WHERE + | (gen_subsumer_0.`dyear` IN (2001, 2002)) AND (gen_subsumer_0.`dyear` = 2001) + | GROUP BY gen_subsumer_0.`customer_id`, gen_subsumer_0.`customer_first_name`, gen_subsumer_0.`customer_last_name`, gen_subsumer_0.`dyear` + | HAVING (gen_subsumer_0.`year_total_74` > 0.00BD) + | UNION ALL + | SELECT customer.`c_customer_id` AS `customer_id`, makedecimal(sum(unscaledvalue(web_sales.`ws_net_paid`))) AS `year_total` + | FROM + | customer + | INNER JOIN web_sales ON (customer.`c_customer_sk` = web_sales.`ws_bill_customer_sk`) + | INNER JOIN date_dim ON (date_dim.`d_year` IN (2001, 2002)) AND (web_sales.`ws_sold_date_sk` = date_dim.`d_date_sk`) + | WHERE + | false + | GROUP BY customer.`c_customer_id`, customer.`c_first_name`, customer.`c_last_name`, date_dim.`d_year` + | HAVING (`year_total` > 0.00BD)) gen_subquery_0 + | INNER JOIN (SELECT gen_subsumer_1.`customer_id` AS `customer_id`, gen_subsumer_1.`customer_first_name` AS `customer_first_name`, gen_subsumer_1.`customer_last_name` AS `customer_last_name`, gen_subsumer_1.`year_total_74` AS `year_total` + | FROM + | (SELECT customer.`customer_id` AS `customer_id`, customer.`customer_first_name` AS `customer_first_name`, customer.`customer_last_name` AS `customer_last_name`, customer.`customer_preferred_cust_flag` AS `customer_preferred_cust_flag`, customer.`customer_birth_country` AS `customer_birth_country`, customer.`customer_login` AS `customer_login`, customer.`customer_email_address` AS `customer_email_address`, date_dim.`dyear` AS `dyear`, date_dim.`ddate` AS `ddate`, date_dim.`d_month_seq`, sum((CAST((((CAST(store_sales.`ss_ext_list_price` AS DECIMAL(8,2)) - CAST(store_sales.`ss_ext_wholesale_cost` AS DECIMAL(8,2))) - CAST(store_sales.`ss_ext_discount_amt` AS DECIMAL(8,2))) + CAST(store_sales.`ss_ext_sales_price` AS DECIMAL(8,2))) AS DECIMAL(12,2)) / 2.00BD)) AS `year_total`, makedecimal(sum(unscaledvalue(store_sales.`ss_net_paid`))) AS `year_total_74`, 's' AS `sale_type` + | FROM + | customer + | INNER JOIN store_sales ON (store_sales.`ss_customer_sk` = customer.`c_customer_sk`) + | INNER JOIN date_dim ON (store_sales.`ss_sold_date_sk` = date_dim.`d_date_sk`) + | GROUP BY customer.`customer_id`, customer.`customer_first_name`, customer.`customer_last_name`, customer.`customer_preferred_cust_flag`, customer.`customer_birth_country`, customer.`customer_login`, customer.`customer_email_address`, date_dim.`dyear`, date_dim.`ddate`, date_dim.`d_month_seq`) gen_subsumer_1 + | WHERE + | (gen_subsumer_1.`dyear` IN (2001, 2002)) AND (gen_subsumer_1.`dyear` = 2002) + | GROUP BY gen_subsumer_1.`customer_id`, gen_subsumer_1.`customer_first_name`, gen_subsumer_1.`customer_last_name`, gen_subsumer_1.`dyear` + | UNION ALL + | SELECT customer.`c_customer_id` AS `customer_id`, customer.`c_first_name` AS `customer_first_name`, customer.`c_last_name` AS `customer_last_name`, makedecimal(sum(unscaledvalue(web_sales.`ws_net_paid`))) AS `year_total` + | FROM + | customer + | INNER JOIN web_sales ON (customer.`c_customer_sk` = web_sales.`ws_bill_customer_sk`) + | INNER JOIN date_dim ON (date_dim.`d_year` IN (2001, 2002)) AND (web_sales.`ws_sold_date_sk` = date_dim.`d_date_sk`) + | WHERE + | false + | GROUP BY customer.`c_customer_id`, customer.`c_first_name`, customer.`c_last_name`, date_dim.`d_year`) gen_subquery_1 ON (gen_subquery_1.`customer_id` = gen_subquery_0.`customer_id`) + | INNER JOIN (SELECT gen_subsumer_2.`customer_id` AS `customer_id`, gen_subsumer_2.`year_total_74` AS `year_total` + | FROM + | (SELECT customer.`c_customer_id` AS `customer_id`, customer.`c_first_name` AS `customer_first_name`, customer.`c_last_name` AS `customer_last_name`, customer.`c_preferred_cust_flag` AS `customer_preferred_cust_flag`, customer.`c_birth_country` AS `customer_birth_country`, customer.`c_login` AS `customer_login`, customer.`c_email_address` AS `customer_email_address`, date_dim.`d_year` AS `dyear`, date_dim.`d_date` AS `ddate`, date_dim.`d_month_seq`, sum((CAST((((CAST(store_sales.`ss_ext_list_price` AS DECIMAL(8,2)) - CAST(store_sales.`ss_ext_wholesale_cost` AS DECIMAL(8,2))) - CAST(store_sales.`ss_ext_discount_amt` AS DECIMAL(8,2))) + CAST(store_sales.`ss_ext_sales_price` AS DECIMAL(8,2))) AS DECIMAL(12,2)) / 2.00BD)) AS `year_total`, makedecimal(sum(unscaledvalue(store_sales.`ss_net_paid`))) AS `y
<TRUNCATED>
