carbondata-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From jack...@apache.org
Subject [09/12] carbondata git commit: [CARBONDATA-2242] Add Materialized View modules
Date Fri, 09 Mar 2018 08:44:27 GMT
http://git-wip-us.apache.org/repos/asf/carbondata/blob/02fd7873/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..b4d6f5b
--- /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, 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 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_n

<TRUNCATED>

Mime
View raw message