Adding upstream version 16.2.1.upstream/16.2.1

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

6 files changed, 1302 insertions, 969 deletions

diff --git a/tests/fixtures/optimizer/isolate_table_selects.sql b/tests/fixtures/optimizer/isolate_table_selects.sql
index 43540e8..36f2d8e 100644
--- a/tests/fixtures/optimizer/isolate_table_selects.sql
+++ b/tests/fixtures/optimizer/isolate_table_selects.sql
@@ -1,5 +1,5 @@
 SELECT * FROM x AS x, y AS y2;
-SELECT * FROM (SELECT * FROM x AS x) AS x, (SELECT * FROM y AS y) AS y2;
+SELECT * FROM (SELECT * FROM x AS x) AS x, (SELECT * FROM y AS y2) AS y2;
 
 SELECT * FROM x AS x WHERE x = 1;
 SELECT * FROM x AS x WHERE x = 1;
@@ -17,7 +17,7 @@ WITH y AS (SELECT *) SELECT * FROM x AS x;
 WITH y AS (SELECT *) SELECT * FROM x AS x;
 
 WITH y AS (SELECT * FROM y AS y2 CROSS JOIN x AS z2) SELECT * FROM x AS x CROSS JOIN y as y;
-WITH y AS (SELECT * FROM (SELECT * FROM y AS y) AS y2 CROSS JOIN (SELECT * FROM x AS x) AS z2) SELECT * FROM (SELECT * FROM x AS x) AS x CROSS JOIN y AS y;
+WITH y AS (SELECT * FROM (SELECT * FROM y AS y2) AS y2 CROSS JOIN (SELECT * FROM x AS z2) AS z2) SELECT * FROM (SELECT * FROM x AS x) AS x CROSS JOIN y AS y;
 
 SELECT * FROM x AS x CROSS JOIN xx AS y;
 SELECT * FROM (SELECT * FROM x AS x) AS x CROSS JOIN xx AS y;
diff --git a/tests/fixtures/optimizer/optimizer.sql b/tests/fixtures/optimizer/optimizer.sql
index e0567d7..0cb1a58 100644
--- a/tests/fixtures/optimizer/optimizer.sql
+++ b/tests/fixtures/optimizer/optimizer.sql
@@ -101,10 +101,10 @@ SELECT
   "x"."a" AS "a",
   SUM("y"."b") AS "sum_b"
 FROM "x" AS "x"
-JOIN "y" AS "y"
-  ON "x"."b" = "y"."b"
 LEFT JOIN "_u_0" AS "_u_0"
   ON "x"."b" = "_u_0"."_u_1"
+JOIN "y" AS "y"
+  ON "x"."b" = "y"."b"
 WHERE
   "_u_0"."_col_0" >= 0 AND "x"."a" > 1
 GROUP BY
@@ -210,10 +210,10 @@ SELECT
   "n"."b" AS "b",
   "o"."b" AS "b"
 FROM "n"
-FULL JOIN "o"
-  ON "n"."a" = "o"."a"
 JOIN "n" AS "n2"
   ON "n"."a" = "n2"."a"
+FULL JOIN "o"
+  ON "n"."a" = "o"."a"
 WHERE
   "o"."b" > 0;
 
@@ -619,3 +619,30 @@ WITH "foO" AS (
 SELECT
   "foO"."x" AS "x"
 FROM "foO" AS "foO";
+
+# title: lateral subquery
+# execute: false
+# dialect: postgres
+SELECT u.user_id, l.log_date
+FROM   users u
+CROSS JOIN LATERAL (
+    SELECT l.log_date
+    FROM   logs l
+    WHERE  l.user_id = u.user_id AND l.log_date <= 100
+    ORDER  BY l.log_date DESC NULLS LAST
+    LIMIT  1
+) l;
+SELECT
+  "u"."user_id" AS "user_id",
+  "l"."log_date" AS "log_date"
+FROM "users" AS "u"
+CROSS JOIN LATERAL (
+  SELECT
+    "l"."log_date"
+  FROM "logs" AS "l"
+  WHERE
+    "l"."log_date" <= 100 AND "l"."user_id" = "u"."user_id"
+  ORDER BY
+    "l"."log_date" DESC NULLS LAST
+  LIMIT 1
+) AS "l";
diff --git a/tests/fixtures/optimizer/pushdown_predicates.sql b/tests/fixtures/optimizer/pushdown_predicates.sql
index 83a353d..79ce353 100644
--- a/tests/fixtures/optimizer/pushdown_predicates.sql
+++ b/tests/fixtures/optimizer/pushdown_predicates.sql
@@ -25,8 +25,8 @@ SELECT x.a AS a FROM (SELECT x.a FROM x AS x WHERE x.a = 1 AND x.b = 1) AS x JOI
 SELECT x.a FROM x AS x JOIN (SELECT y.a FROM y AS y) AS y ON y.a = 1 AND x.a = y.a;
 SELECT x.a FROM x AS x JOIN (SELECT y.a FROM y AS y WHERE y.a = 1) AS y ON x.a = y.a AND TRUE;
 
-SELECT x.a AS a FROM x AS x JOIN (SELECT * FROM y AS y) AS y ON y.a = 1 WHERE x.a = 1 AND x.b = 1 AND y.a = x;
-SELECT x.a AS a FROM x AS x JOIN (SELECT * FROM y AS y WHERE y.a = 1) AS y ON y.a = x AND TRUE WHERE x.a = 1 AND x.b = 1 AND TRUE;
+SELECT x.a AS a FROM x AS x JOIN (SELECT * FROM y AS y) AS y ON y.a = 1 WHERE x.a = 1 AND x.b = 1 AND y.a = x.a;
+SELECT x.a AS a FROM x AS x JOIN (SELECT * FROM y AS y WHERE y.a = 1) AS y ON y.a = x.a AND TRUE WHERE x.a = 1 AND x.b = 1 AND TRUE;
 
 SELECT x.a AS a FROM x AS x CROSS JOIN (SELECT * FROM y AS y) AS y WHERE x.a = 1 AND x.b = 1 AND y.a = x.a AND y.a = 1;
 SELECT x.a AS a FROM x AS x JOIN (SELECT * FROM y AS y WHERE y.a = 1) AS y ON y.a = x.a AND TRUE WHERE x.a = 1 AND x.b = 1 AND TRUE AND TRUE;
diff --git a/tests/fixtures/optimizer/qualify_columns.sql b/tests/fixtures/optimizer/qualify_columns.sql
index 7be2c7f..81c0b5e 100644
--- a/tests/fixtures/optimizer/qualify_columns.sql
+++ b/tests/fixtures/optimizer/qualify_columns.sql
@@ -296,6 +296,10 @@ SELECT x.b AS b FROM x AS x;
 SELECT x.b FROM x JOIN y USING (b);
 SELECT x.b AS b FROM x AS x JOIN y AS y ON x.b = y.b;
 
+# execute: false
+WITH cte AS (SELECT a.b.c.d.f.g FROM tbl1) SELECT g FROM (SELECT g FROM tbl2) tbl2 JOIN cte USING(g);
+WITH cte AS (SELECT tbl1.a.b.c.d.f.g AS g FROM tbl1 AS tbl1) SELECT COALESCE(tbl2.g, cte.g) AS g FROM (SELECT tbl2.g AS g FROM tbl2 AS tbl2) AS tbl2 JOIN cte ON tbl2.g = cte.g;
+
 SELECT x.b FROM x JOIN y USING (b) JOIN z USING (b);
 SELECT x.b AS b FROM x AS x JOIN y AS y ON x.b = y.b JOIN z AS z ON x.b = z.b;
 
diff --git a/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql b/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql
index a6ee325..7ef7a6d 100644
--- a/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql
+++ b/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql
@@ -46,12 +46,12 @@ WITH "customer_total_return" AS (
 SELECT
   "customer"."c_customer_id" AS "c_customer_id"
 FROM "customer_total_return" AS "ctr1"
-JOIN "store" AS "store"
-  ON "store"."s_state" = 'TN' AND "store"."s_store_sk" = "ctr1"."ctr_store_sk"
-JOIN "customer" AS "customer"
-  ON "ctr1"."ctr_customer_sk" = "customer"."c_customer_sk"
 LEFT JOIN "_u_0" AS "_u_0"
   ON "ctr1"."ctr_store_sk" = "_u_0"."_u_1"
+JOIN "customer" AS "customer"
+  ON "ctr1"."ctr_customer_sk" = "customer"."c_customer_sk"
+JOIN "store" AS "store"
+  ON "store"."s_state" = 'TN' AND "store"."s_store_sk" = "ctr1"."ctr_store_sk"
 WHERE
   "ctr1"."ctr_total_return" > "_u_0"."_col_0"
 ORDER BY
@@ -238,23 +238,23 @@ ORDER  BY dt.d_year,
           brand_id
 LIMIT 100;
 SELECT
-  "date_dim"."d_year" AS "d_year",
+  "dt"."d_year" AS "d_year",
   "item"."i_brand_id" AS "brand_id",
   "item"."i_brand" AS "brand",
   SUM("store_sales"."ss_ext_discount_amt") AS "sum_agg"
-FROM "date_dim" AS "date_dim"
+FROM "date_dim" AS "dt"
 JOIN "store_sales" AS "store_sales"
-  ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk"
+  ON "dt"."d_date_sk" = "store_sales"."ss_sold_date_sk"
 JOIN "item" AS "item"
   ON "item"."i_manufact_id" = 427 AND "store_sales"."ss_item_sk" = "item"."i_item_sk"
 WHERE
-  "date_dim"."d_moy" = 11
+  "dt"."d_moy" = 11
 GROUP BY
-  "date_dim"."d_year",
+  "dt"."d_year",
   "item"."i_brand",
   "item"."i_brand_id"
 ORDER BY
-  "date_dim"."d_year",
+  "dt"."d_year",
   "sum_agg" DESC,
   "brand_id"
 LIMIT 100;
@@ -567,14 +567,14 @@ SELECT
   "t_s_secyear"."customer_last_name" AS "customer_last_name",
   "t_s_secyear"."customer_preferred_cust_flag" AS "customer_preferred_cust_flag"
 FROM "year_total" AS "t_s_firstyear"
-JOIN "year_total" AS "t_s_secyear"
-  ON "t_s_secyear"."customer_id" = "t_s_firstyear"."customer_id"
-  AND "t_s_secyear"."dyear" = 2002
-  AND "t_s_secyear"."sale_type" = 's'
 JOIN "year_total" AS "t_c_secyear"
   ON "t_c_secyear"."dyear" = 2002
   AND "t_c_secyear"."sale_type" = 'c'
   AND "t_s_firstyear"."customer_id" = "t_c_secyear"."customer_id"
+JOIN "year_total" AS "t_s_secyear"
+  ON "t_s_secyear"."customer_id" = "t_s_firstyear"."customer_id"
+  AND "t_s_secyear"."dyear" = 2002
+  AND "t_s_secyear"."sale_type" = 's'
 JOIN "year_total" AS "t_w_firstyear"
   ON "t_s_firstyear"."customer_id" = "t_w_firstyear"."customer_id"
   AND "t_w_firstyear"."dyear" = 2001
@@ -813,10 +813,10 @@ WITH "salesreturns" AS (
     SUM("salesreturns"."return_amt") AS "returns1",
     SUM("salesreturns"."net_loss") AS "profit_loss"
   FROM "salesreturns_2" AS "salesreturns"
-  JOIN "date_dim_2" AS "date_dim"
-    ON "salesreturns"."date_sk" = "date_dim"."d_date_sk"
   JOIN "catalog_page" AS "catalog_page"
     ON "salesreturns"."page_sk" = "catalog_page"."cp_catalog_page_sk"
+  JOIN "date_dim_2" AS "date_dim"
+    ON "salesreturns"."date_sk" = "date_dim"."d_date_sk"
   GROUP BY
     "catalog_page"."cp_catalog_page_id"
 ), "salesreturns_3" AS (
@@ -931,7 +931,7 @@ ORDER  BY cnt
 LIMIT 100;
 WITH "_u_0" AS (
   SELECT DISTINCT
-    "date_dim"."d_month_seq" AS "_col_0"
+    "date_dim"."d_month_seq" AS "d_month_seq"
   FROM "date_dim" AS "date_dim"
   WHERE
     "date_dim"."d_moy" = 7 AND "date_dim"."d_year" = 1998
@@ -944,25 +944,25 @@ WITH "_u_0" AS (
     "j"."i_category"
 )
 SELECT
-  "customer_address"."ca_state" AS "state",
+  "a"."ca_state" AS "state",
   COUNT(*) AS "cnt"
-FROM "customer_address" AS "customer_address"
-CROSS JOIN "_u_0" AS "_u_0"
-JOIN "date_dim" AS "date_dim"
-  ON "date_dim"."d_month_seq" = "_u_0"."_col_0"
-JOIN "store_sales" AS "store_sales"
-  ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
-JOIN "customer" AS "customer"
-  ON "customer"."c_customer_sk" = "store_sales"."ss_customer_sk"
-  AND "customer_address"."ca_address_sk" = "customer"."c_current_addr_sk"
-JOIN "item" AS "item"
-  ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
+FROM "customer_address" AS "a"
+JOIN "customer" AS "c"
+  ON "a"."ca_address_sk" = "c"."c_current_addr_sk"
+JOIN "store_sales" AS "s"
+  ON "c"."c_customer_sk" = "s"."ss_customer_sk"
+JOIN "date_dim" AS "d"
+  ON "s"."ss_sold_date_sk" = "d"."d_date_sk"
+JOIN "item" AS "i"
+  ON "s"."ss_item_sk" = "i"."i_item_sk"
+JOIN "_u_0" AS "_u_0"
+  ON "d"."d_month_seq" = "_u_0"."d_month_seq"
 LEFT JOIN "_u_1" AS "_u_1"
-  ON "_u_1"."_u_2" = "item"."i_category"
+  ON "_u_1"."_u_2" = "i"."i_category"
 WHERE
-  "item"."i_current_price" > 1.2 * "_u_1"."_col_0"
+  "i"."i_current_price" > 1.2 * "_u_1"."_col_0"
 GROUP BY
-  "customer_address"."ca_state"
+  "a"."ca_state"
 HAVING
   COUNT(*) >= 10
 ORDER BY
@@ -1369,6 +1369,36 @@ WITH "_u_0" AS (
   FROM "store_sales" AS "store_sales"
   WHERE
     "store_sales"."ss_quantity" <= 20 AND "store_sales"."ss_quantity" >= 1
+), "_u_10" AS (
+  SELECT
+    AVG("store_sales"."ss_ext_list_price") AS "_col_0"
+  FROM "store_sales" AS "store_sales"
+  WHERE
+    "store_sales"."ss_quantity" <= 80 AND "store_sales"."ss_quantity" >= 61
+), "_u_11" AS (
+  SELECT
+    AVG("store_sales"."ss_net_profit") AS "_col_0"
+  FROM "store_sales" AS "store_sales"
+  WHERE
+    "store_sales"."ss_quantity" <= 80 AND "store_sales"."ss_quantity" >= 61
+), "_u_12" AS (
+  SELECT
+    COUNT(*) AS "_col_0"
+  FROM "store_sales" AS "store_sales"
+  WHERE
+    "store_sales"."ss_quantity" <= 100 AND "store_sales"."ss_quantity" >= 81
+), "_u_13" AS (
+  SELECT
+    AVG("store_sales"."ss_ext_list_price") AS "_col_0"
+  FROM "store_sales" AS "store_sales"
+  WHERE
+    "store_sales"."ss_quantity" <= 100 AND "store_sales"."ss_quantity" >= 81
+), "_u_14" AS (
+  SELECT
+    AVG("store_sales"."ss_net_profit") AS "_col_0"
+  FROM "store_sales" AS "store_sales"
+  WHERE
+    "store_sales"."ss_quantity" <= 100 AND "store_sales"."ss_quantity" >= 81
 ), "_u_2" AS (
   SELECT
     AVG("store_sales"."ss_net_profit") AS "_col_0"
@@ -1417,36 +1447,6 @@ WITH "_u_0" AS (
   FROM "store_sales" AS "store_sales"
   WHERE
     "store_sales"."ss_quantity" <= 80 AND "store_sales"."ss_quantity" >= 61
-), "_u_10" AS (
-  SELECT
-    AVG("store_sales"."ss_ext_list_price") AS "_col_0"
-  FROM "store_sales" AS "store_sales"
-  WHERE
-    "store_sales"."ss_quantity" <= 80 AND "store_sales"."ss_quantity" >= 61
-), "_u_11" AS (
-  SELECT
-    AVG("store_sales"."ss_net_profit") AS "_col_0"
-  FROM "store_sales" AS "store_sales"
-  WHERE
-    "store_sales"."ss_quantity" <= 80 AND "store_sales"."ss_quantity" >= 61
-), "_u_12" AS (
-  SELECT
-    COUNT(*) AS "_col_0"
-  FROM "store_sales" AS "store_sales"
-  WHERE
-    "store_sales"."ss_quantity" <= 100 AND "store_sales"."ss_quantity" >= 81
-), "_u_13" AS (
-  SELECT
-    AVG("store_sales"."ss_ext_list_price") AS "_col_0"
-  FROM "store_sales" AS "store_sales"
-  WHERE
-    "store_sales"."ss_quantity" <= 100 AND "store_sales"."ss_quantity" >= 81
-), "_u_14" AS (
-  SELECT
-    AVG("store_sales"."ss_net_profit") AS "_col_0"
-  FROM "store_sales" AS "store_sales"
-  WHERE
-    "store_sales"."ss_quantity" <= 100 AND "store_sales"."ss_quantity" >= 81
 )
 SELECT
   CASE WHEN "_u_0"."_col_0" > 3672 THEN "_u_1"."_col_0" ELSE "_u_2"."_col_0" END AS "bucket1",
@@ -1457,6 +1457,11 @@ SELECT
 FROM "reason" AS "reason"
 CROSS JOIN "_u_0" AS "_u_0"
 CROSS JOIN "_u_1" AS "_u_1"
+CROSS JOIN "_u_10" AS "_u_10"
+CROSS JOIN "_u_11" AS "_u_11"
+CROSS JOIN "_u_12" AS "_u_12"
+CROSS JOIN "_u_13" AS "_u_13"
+CROSS JOIN "_u_14" AS "_u_14"
 CROSS JOIN "_u_2" AS "_u_2"
 CROSS JOIN "_u_3" AS "_u_3"
 CROSS JOIN "_u_4" AS "_u_4"
@@ -1465,11 +1470,6 @@ CROSS JOIN "_u_6" AS "_u_6"
 CROSS JOIN "_u_7" AS "_u_7"
 CROSS JOIN "_u_8" AS "_u_8"
 CROSS JOIN "_u_9" AS "_u_9"
-CROSS JOIN "_u_10" AS "_u_10"
-CROSS JOIN "_u_11" AS "_u_11"
-CROSS JOIN "_u_12" AS "_u_12"
-CROSS JOIN "_u_13" AS "_u_13"
-CROSS JOIN "_u_14" AS "_u_14"
 WHERE
   "reason"."r_reason_sk" = 1;
 
@@ -1610,18 +1610,18 @@ SELECT
   COUNT(*) AS "cnt5",
   "customer_demographics"."cd_dep_college_count" AS "cd_dep_college_count",
   COUNT(*) AS "cnt6"
-FROM "customer" AS "customer"
-JOIN "customer_address" AS "customer_address"
-  ON "customer"."c_current_addr_sk" = "customer_address"."ca_address_sk"
-  AND "customer_address"."ca_county" IN ('Lycoming County', 'Sheridan County', 'Kandiyohi County', 'Pike County', 'Greene County')
-JOIN "customer_demographics" AS "customer_demographics"
-  ON "customer_demographics"."cd_demo_sk" = "customer"."c_current_cdemo_sk"
+FROM "customer" AS "c"
 LEFT JOIN "_u_0" AS "_u_0"
-  ON "customer"."c_customer_sk" = "_u_0"."_u_1"
+  ON "c"."c_customer_sk" = "_u_0"."_u_1"
 LEFT JOIN "_u_2" AS "_u_2"
-  ON "customer"."c_customer_sk" = "_u_2"."_u_3"
+  ON "c"."c_customer_sk" = "_u_2"."_u_3"
 LEFT JOIN "_u_4" AS "_u_4"
-  ON "customer"."c_customer_sk" = "_u_4"."_u_5"
+  ON "c"."c_customer_sk" = "_u_4"."_u_5"
+JOIN "customer_address" AS "ca"
+  ON "c"."c_current_addr_sk" = "ca"."ca_address_sk"
+  AND "ca"."ca_county" IN ('Lycoming County', 'Sheridan County', 'Kandiyohi County', 'Pike County', 'Greene County')
+JOIN "customer_demographics" AS "customer_demographics"
+  ON "customer_demographics"."cd_demo_sk" = "c"."c_current_cdemo_sk"
 WHERE
   NOT "_u_0"."_u_1" IS NULL
   AND (
@@ -1835,19 +1835,19 @@ SELECT
   "t_s_secyear"."customer_last_name" AS "customer_last_name",
   "t_s_secyear"."customer_birth_country" AS "customer_birth_country"
 FROM "year_total" AS "t_s_firstyear"
-JOIN "year_total" AS "t_s_secyear"
-  ON "t_s_secyear"."customer_id" = "t_s_firstyear"."customer_id"
-  AND "t_s_secyear"."dyear" = 2002
-  AND "t_s_secyear"."sale_type" = 's'
-JOIN "year_total" AS "t_w_secyear"
-  ON "t_s_firstyear"."customer_id" = "t_w_secyear"."customer_id"
-  AND "t_w_secyear"."dyear" = 2002
-  AND "t_w_secyear"."sale_type" = 'w'
 JOIN "year_total" AS "t_w_firstyear"
   ON "t_s_firstyear"."customer_id" = "t_w_firstyear"."customer_id"
   AND "t_w_firstyear"."dyear" = 2001
   AND "t_w_firstyear"."sale_type" = 'w'
   AND "t_w_firstyear"."year_total" > 0
+JOIN "year_total" AS "t_w_secyear"
+  ON "t_s_firstyear"."customer_id" = "t_w_secyear"."customer_id"
+  AND "t_w_secyear"."dyear" = 2002
+  AND "t_w_secyear"."sale_type" = 'w'
+JOIN "year_total" AS "t_s_secyear"
+  ON "t_s_secyear"."customer_id" = "t_s_firstyear"."customer_id"
+  AND "t_s_secyear"."dyear" = 2002
+  AND "t_s_secyear"."sale_type" = 's'
   AND CASE
     WHEN "t_w_firstyear"."year_total" > 0
     THEN "t_w_secyear"."year_total" / "t_w_firstyear"."year_total"
@@ -1909,13 +1909,13 @@ SELECT
   SUM("web_sales"."ws_ext_sales_price") AS "itemrevenue",
   SUM("web_sales"."ws_ext_sales_price") * 100 / SUM(SUM("web_sales"."ws_ext_sales_price")) OVER (PARTITION BY "item"."i_class") AS "revenueratio"
 FROM "web_sales" AS "web_sales"
-JOIN "item" AS "item"
-  ON "item"."i_category" IN ('Home', 'Men', 'Women')
-  AND "web_sales"."ws_item_sk" = "item"."i_item_sk"
 JOIN "date_dim" AS "date_dim"
   ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk"
   AND CAST("date_dim"."d_date" AS DATE) <= CAST('2000-06-10' AS DATE)
   AND CAST("date_dim"."d_date" AS DATE) >= CAST('2000-05-11' AS DATE)
+JOIN "item" AS "item"
+  ON "item"."i_category" IN ('Home', 'Men', 'Women')
+  AND "web_sales"."ws_item_sk" = "item"."i_item_sk"
 GROUP BY
   "item"."i_item_id",
   "item"."i_item_desc",
@@ -1982,22 +1982,7 @@ SELECT
   AVG("store_sales"."ss_ext_wholesale_cost") AS "_col_2",
   SUM("store_sales"."ss_ext_wholesale_cost") AS "_col_3"
 FROM "store_sales" AS "store_sales"
-JOIN "store" AS "store"
-  ON "store"."s_store_sk" = "store_sales"."ss_store_sk"
-CROSS JOIN "household_demographics" AS "household_demographics"
-JOIN "customer_demographics" AS "customer_demographics"
-  ON "customer_demographics"."cd_demo_sk" = "store_sales"."ss_cdemo_sk"
-  AND "customer_demographics"."cd_education_status" = 'Advanced Degree'
-  AND "customer_demographics"."cd_education_status" = 'Primary'
-  AND "customer_demographics"."cd_education_status" = 'Secondary'
-  AND "customer_demographics"."cd_marital_status" = 'D'
-  AND "customer_demographics"."cd_marital_status" = 'M'
-  AND "customer_demographics"."cd_marital_status" = 'U'
-  AND "household_demographics"."hd_dep_count" = 1
-  AND "household_demographics"."hd_dep_count" = 3
-  AND "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk"
-  AND "store_sales"."ss_sales_price" <= 100.00
-  AND "store_sales"."ss_sales_price" >= 150.00
+CROSS JOIN "customer_demographics" AS "customer_demographics"
 JOIN "customer_address" AS "customer_address"
   ON (
     "customer_address"."ca_country" = 'United States'
@@ -2022,7 +2007,22 @@ JOIN "customer_address" AS "customer_address"
   )
 JOIN "date_dim" AS "date_dim"
   ON "date_dim"."d_year" = 2001
-  AND "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk";
+  AND "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
+JOIN "household_demographics" AS "household_demographics"
+  ON "customer_demographics"."cd_demo_sk" = "store_sales"."ss_cdemo_sk"
+  AND "customer_demographics"."cd_education_status" = 'Advanced Degree'
+  AND "customer_demographics"."cd_education_status" = 'Primary'
+  AND "customer_demographics"."cd_education_status" = 'Secondary'
+  AND "customer_demographics"."cd_marital_status" = 'D'
+  AND "customer_demographics"."cd_marital_status" = 'M'
+  AND "customer_demographics"."cd_marital_status" = 'U'
+  AND "household_demographics"."hd_dep_count" = 1
+  AND "household_demographics"."hd_dep_count" = 3
+  AND "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk"
+  AND "store_sales"."ss_sales_price" <= 100.00
+  AND "store_sales"."ss_sales_price" >= 150.00
+JOIN "store" AS "store"
+  ON "store"."s_store_sk" = "store_sales"."ss_store_sk";
 
 --------------------------------------
 -- TPC-DS 14
@@ -2165,69 +2165,75 @@ WITH "item_2" AS (
     "item"."i_class_id" AS "i_class_id",
     "item"."i_category_id" AS "i_category_id"
   FROM "item" AS "item"
-), "d1" AS (
-  SELECT
-    "date_dim"."d_date_sk" AS "d_date_sk",
-    "date_dim"."d_year" AS "d_year"
-  FROM "date_dim" AS "date_dim"
-  WHERE
-    "date_dim"."d_year" <= 2001 AND "date_dim"."d_year" >= 1999
 ), "cte_4" AS (
   SELECT
     "ics"."i_brand_id" AS "i_brand_id",
     "ics"."i_class_id" AS "i_class_id",
     "ics"."i_category_id" AS "i_category_id"
   FROM "catalog_sales" AS "catalog_sales"
-  JOIN "item_2" AS "ics"
-    ON "catalog_sales"."cs_item_sk" = "ics"."i_item_sk"
-  JOIN "d1" AS "d2"
+  JOIN "date_dim" AS "d2"
     ON "catalog_sales"."cs_sold_date_sk" = "d2"."d_date_sk"
+    AND "d2"."d_year" <= 2001
+    AND "d2"."d_year" >= 1999
+  JOIN "item" AS "ics"
+    ON "catalog_sales"."cs_item_sk" = "ics"."i_item_sk"
   INTERSECT
   SELECT
     "iws"."i_brand_id" AS "i_brand_id",
     "iws"."i_class_id" AS "i_class_id",
     "iws"."i_category_id" AS "i_category_id"
   FROM "web_sales" AS "web_sales"
-  JOIN "item_2" AS "iws"
+  JOIN "date_dim" AS "d3"
+    ON "d3"."d_year" <= 2001
+    AND "d3"."d_year" >= 1999
+    AND "web_sales"."ws_sold_date_sk" = "d3"."d_date_sk"
+  JOIN "item" AS "iws"
     ON "web_sales"."ws_item_sk" = "iws"."i_item_sk"
-  JOIN "d1" AS "d3"
-    ON "web_sales"."ws_sold_date_sk" = "d3"."d_date_sk"
 ), "_q_0" AS (
   SELECT
     "iss"."i_brand_id" AS "brand_id",
     "iss"."i_class_id" AS "class_id",
     "iss"."i_category_id" AS "category_id"
   FROM "store_sales" AS "store_sales"
-  JOIN "item_2" AS "iss"
+  JOIN "date_dim" AS "d1"
+    ON "d1"."d_year" <= 2001
+    AND "d1"."d_year" >= 1999
+    AND "store_sales"."ss_sold_date_sk" = "d1"."d_date_sk"
+  JOIN "item" AS "iss"
     ON "store_sales"."ss_item_sk" = "iss"."i_item_sk"
-  JOIN "d1" AS "d1"
-    ON "store_sales"."ss_sold_date_sk" = "d1"."d_date_sk"
   INTERSECT
   SELECT
     "cte_4"."i_brand_id" AS "i_brand_id",
     "cte_4"."i_class_id" AS "i_class_id",
     "cte_4"."i_category_id" AS "i_category_id"
   FROM "cte_4" AS "cte_4"
+), "date_dim_2" AS (
+  SELECT
+    "date_dim"."d_date_sk" AS "d_date_sk",
+    "date_dim"."d_year" AS "d_year"
+  FROM "date_dim" AS "date_dim"
+  WHERE
+    "date_dim"."d_year" <= 2001 AND "date_dim"."d_year" >= 1999
 ), "cte_8" AS (
   SELECT
     "catalog_sales"."cs_quantity" AS "quantity",
     "catalog_sales"."cs_list_price" AS "list_price"
   FROM "catalog_sales" AS "catalog_sales"
-  JOIN "d1" AS "date_dim"
+  JOIN "date_dim_2" AS "date_dim"
     ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk"
   UNION ALL
   SELECT
     "web_sales"."ws_quantity" AS "quantity",
     "web_sales"."ws_list_price" AS "list_price"
   FROM "web_sales" AS "web_sales"
-  JOIN "d1" AS "date_dim"
+  JOIN "date_dim_2" AS "date_dim"
     ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk"
 ), "x" AS (
   SELECT
     "store_sales"."ss_quantity" AS "quantity",
     "store_sales"."ss_list_price" AS "list_price"
   FROM "store_sales" AS "store_sales"
-  JOIN "d1" AS "date_dim"
+  JOIN "date_dim_2" AS "date_dim"
     ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
   UNION ALL
   SELECT
@@ -2238,14 +2244,10 @@ WITH "item_2" AS (
   SELECT
     AVG("x"."quantity" * "x"."list_price") AS "average_sales"
   FROM "x" AS "x"
-), "date_dim_2" AS (
+), "_u_1" AS (
   SELECT
-    "date_dim"."d_date_sk" AS "d_date_sk",
-    "date_dim"."d_year" AS "d_year",
-    "date_dim"."d_moy" AS "d_moy"
-  FROM "date_dim" AS "date_dim"
-  WHERE
-    "date_dim"."d_moy" = 11 AND "date_dim"."d_year" = 2001
+    "avg_sales"."average_sales" AS "average_sales"
+  FROM "avg_sales"
 ), "_u_0" AS (
   SELECT
     "item"."i_item_sk" AS "ss_item_sk"
@@ -2256,10 +2258,14 @@ WITH "item_2" AS (
     AND "item"."i_class_id" = "_q_0"."class_id"
   GROUP BY
     "item"."i_item_sk"
-), "_u_1" AS (
+), "date_dim_3" AS (
   SELECT
-    "avg_sales"."average_sales" AS "average_sales"
-  FROM "avg_sales"
+    "date_dim"."d_date_sk" AS "d_date_sk",
+    "date_dim"."d_year" AS "d_year",
+    "date_dim"."d_moy" AS "d_moy"
+  FROM "date_dim" AS "date_dim"
+  WHERE
+    "date_dim"."d_moy" = 11 AND "date_dim"."d_year" = 2001
 ), "cte_9" AS (
   SELECT
     'store' AS "channel",
@@ -2269,13 +2275,13 @@ WITH "item_2" AS (
     SUM("store_sales"."ss_quantity" * "store_sales"."ss_list_price") AS "sales",
     COUNT(*) AS "number_sales"
   FROM "store_sales" AS "store_sales"
-  JOIN "item_2" AS "item"
-    ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
-  JOIN "date_dim_2" AS "date_dim"
-    ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
+  CROSS JOIN "_u_1" AS "_u_1"
   LEFT JOIN "_u_0" AS "_u_0"
     ON "store_sales"."ss_item_sk" = "_u_0"."ss_item_sk"
-  CROSS JOIN "_u_1" AS "_u_1"
+  JOIN "date_dim_3" AS "date_dim"
+    ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
+  JOIN "item_2" AS "item"
+    ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
   WHERE
     NOT "_u_0"."ss_item_sk" IS NULL
   GROUP BY
@@ -2293,13 +2299,13 @@ WITH "item_2" AS (
     SUM("catalog_sales"."cs_quantity" * "catalog_sales"."cs_list_price") AS "sales",
     COUNT(*) AS "number_sales"
   FROM "catalog_sales" AS "catalog_sales"
-  JOIN "item_2" AS "item"
-    ON "catalog_sales"."cs_item_sk" = "item"."i_item_sk"
-  JOIN "date_dim_2" AS "date_dim"
-    ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk"
+  CROSS JOIN "_u_1" AS "_u_3"
   LEFT JOIN "_u_0" AS "_u_2"
     ON "catalog_sales"."cs_item_sk" = "_u_2"."ss_item_sk"
-  CROSS JOIN "_u_1" AS "_u_3"
+  JOIN "date_dim_3" AS "date_dim"
+    ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk"
+  JOIN "item_2" AS "item"
+    ON "catalog_sales"."cs_item_sk" = "item"."i_item_sk"
   WHERE
     NOT "_u_2"."ss_item_sk" IS NULL
   GROUP BY
@@ -2317,13 +2323,13 @@ WITH "item_2" AS (
     SUM("web_sales"."ws_quantity" * "web_sales"."ws_list_price") AS "sales",
     COUNT(*) AS "number_sales"
   FROM "web_sales" AS "web_sales"
-  JOIN "item_2" AS "item"
-    ON "web_sales"."ws_item_sk" = "item"."i_item_sk"
-  JOIN "date_dim_2" AS "date_dim"
-    ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk"
+  CROSS JOIN "_u_1" AS "_u_5"
   LEFT JOIN "_u_0" AS "_u_4"
     ON "web_sales"."ws_item_sk" = "_u_4"."ss_item_sk"
-  CROSS JOIN "_u_1" AS "_u_5"
+  JOIN "date_dim_3" AS "date_dim"
+    ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk"
+  JOIN "item_2" AS "item"
+    ON "web_sales"."ws_item_sk" = "item"."i_item_sk"
   WHERE
     NOT "_u_4"."ss_item_sk" IS NULL
   GROUP BY
@@ -2421,13 +2427,13 @@ JOIN "customer_address" AS "customer_address"
   ON "catalog_sales"."cs_sales_price" > 500
   OR "customer_address"."ca_state" IN ('CA', 'WA', 'GA')
   OR SUBSTR("customer_address"."ca_zip", 1, 5) IN ('85669', '86197', '88274', '83405', '86475', '85392', '85460', '80348', '81792')
-JOIN "customer" AS "customer"
-  ON "catalog_sales"."cs_bill_customer_sk" = "customer"."c_customer_sk"
-  AND "customer"."c_current_addr_sk" = "customer_address"."ca_address_sk"
 JOIN "date_dim" AS "date_dim"
   ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk"
   AND "date_dim"."d_qoy" = 1
   AND "date_dim"."d_year" = 1998
+JOIN "customer" AS "customer"
+  ON "catalog_sales"."cs_bill_customer_sk" = "customer"."c_customer_sk"
+  AND "customer"."c_current_addr_sk" = "customer_address"."ca_address_sk"
 GROUP BY
   "customer_address"."ca_zip"
 ORDER BY
@@ -2484,32 +2490,32 @@ WITH "_u_0" AS (
     "cr1"."cr_order_number"
 )
 SELECT
-  COUNT(DISTINCT "catalog_sales"."cs_order_number") AS "order count",
-  SUM("catalog_sales"."cs_ext_ship_cost") AS "total shipping cost",
-  SUM("catalog_sales"."cs_net_profit") AS "total net profit"
-FROM "catalog_sales" AS "catalog_sales"
+  COUNT(DISTINCT "cs1"."cs_order_number") AS "order count",
+  SUM("cs1"."cs_ext_ship_cost") AS "total shipping cost",
+  SUM("cs1"."cs_net_profit") AS "total net profit"
+FROM "catalog_sales" AS "cs1"
+LEFT JOIN "_u_0" AS "_u_0"
+  ON "cs1"."cs_order_number" = "_u_0"."_u_1"
+LEFT JOIN "_u_3" AS "_u_3"
+  ON "cs1"."cs_order_number" = "_u_3"."_u_4"
+JOIN "call_center" AS "call_center"
+  ON "call_center"."cc_county" IN ('Williamson County', 'Williamson County', 'Williamson County', 'Williamson County', 'Williamson County')
+  AND "cs1"."cs_call_center_sk" = "call_center"."cc_call_center_sk"
+JOIN "customer_address" AS "customer_address"
+  ON "cs1"."cs_ship_addr_sk" = "customer_address"."ca_address_sk"
+  AND "customer_address"."ca_state" = 'IA'
 JOIN "date_dim" AS "date_dim"
-  ON "catalog_sales"."cs_ship_date_sk" = "date_dim"."d_date_sk"
+  ON "cs1"."cs_ship_date_sk" = "date_dim"."d_date_sk"
   AND "date_dim"."d_date" >= '2002-3-01'
   AND CAST("date_dim"."d_date" AS DATE) <= (
     CAST('2002-3-01' AS DATE) + INTERVAL '60' day
   )
-JOIN "customer_address" AS "customer_address"
-  ON "catalog_sales"."cs_ship_addr_sk" = "customer_address"."ca_address_sk"
-  AND "customer_address"."ca_state" = 'IA'
-JOIN "call_center" AS "call_center"
-  ON "call_center"."cc_county" IN ('Williamson County', 'Williamson County', 'Williamson County', 'Williamson County', 'Williamson County')
-  AND "catalog_sales"."cs_call_center_sk" = "call_center"."cc_call_center_sk"
-LEFT JOIN "_u_0" AS "_u_0"
-  ON "catalog_sales"."cs_order_number" = "_u_0"."_u_1"
-LEFT JOIN "_u_3" AS "_u_3"
-  ON "catalog_sales"."cs_order_number" = "_u_3"."_u_4"
 WHERE
   "_u_3"."_u_4" IS NULL
-  AND ARRAY_ANY("_u_0"."_u_2", "_x" -> "catalog_sales"."cs_warehouse_sk" <> "_x")
+  AND ARRAY_ANY("_u_0"."_u_2", "_x" -> "cs1"."cs_warehouse_sk" <> "_x")
   AND NOT "_u_0"."_u_1" IS NULL
 ORDER BY
-  COUNT(DISTINCT "catalog_sales"."cs_order_number")
+  COUNT(DISTINCT "cs1"."cs_order_number")
 LIMIT 100;
 
 --------------------------------------
@@ -2570,14 +2576,6 @@ ORDER  BY i_item_id,
           i_item_desc,
           s_state
 LIMIT 100;
-WITH "d3" AS (
-  SELECT
-    "date_dim"."d_date_sk" AS "d_date_sk",
-    "date_dim"."d_quarter_name" AS "d_quarter_name"
-  FROM "date_dim" AS "date_dim"
-  WHERE
-    "date_dim"."d_quarter_name" IN ('1999Q1', '1999Q2', '1999Q3')
-)
 SELECT
   "item"."i_item_id" AS "i_item_id",
   "item"."i_item_desc" AS "i_item_desc",
@@ -2595,24 +2593,26 @@ SELECT
   STDDEV_SAMP("catalog_sales"."cs_quantity") / AVG("catalog_sales"."cs_quantity") AS "catalog_sales_quantitystdev",
   STDDEV_SAMP("catalog_sales"."cs_quantity") / AVG("catalog_sales"."cs_quantity") AS "catalog_sales_quantitycov"
 FROM "store_sales" AS "store_sales"
-CROSS JOIN "d3" AS "d3"
-JOIN "catalog_sales" AS "catalog_sales"
-  ON "catalog_sales"."cs_sold_date_sk" = "d3"."d_date_sk"
+JOIN "date_dim" AS "d1"
+  ON "d1"."d_date_sk" = "store_sales"."ss_sold_date_sk"
+  AND "d1"."d_quarter_name" = '1999Q1'
+JOIN "item" AS "item"
+  ON "item"."i_item_sk" = "store_sales"."ss_item_sk"
+JOIN "store" AS "store"
+  ON "store"."s_store_sk" = "store_sales"."ss_store_sk"
 JOIN "store_returns" AS "store_returns"
-  ON "store_returns"."sr_customer_sk" = "catalog_sales"."cs_bill_customer_sk"
-  AND "store_returns"."sr_item_sk" = "catalog_sales"."cs_item_sk"
-  AND "store_sales"."ss_customer_sk" = "store_returns"."sr_customer_sk"
+  ON "store_sales"."ss_customer_sk" = "store_returns"."sr_customer_sk"
   AND "store_sales"."ss_item_sk" = "store_returns"."sr_item_sk"
   AND "store_sales"."ss_ticket_number" = "store_returns"."sr_ticket_number"
-JOIN "date_dim" AS "date_dim"
-  ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk"
-  AND "date_dim"."d_quarter_name" = '1999Q1'
-JOIN "d3" AS "d2"
-  ON "store_returns"."sr_returned_date_sk" = "d2"."d_date_sk"
-JOIN "store" AS "store"
-  ON "store"."s_store_sk" = "store_sales"."ss_store_sk"
-JOIN "item" AS "item"
-  ON "item"."i_item_sk" = "store_sales"."ss_item_sk"
+JOIN "catalog_sales" AS "catalog_sales"
+  ON "store_returns"."sr_customer_sk" = "catalog_sales"."cs_bill_customer_sk"
+  AND "store_returns"."sr_item_sk" = "catalog_sales"."cs_item_sk"
+JOIN "date_dim" AS "d2"
+  ON "d2"."d_quarter_name" IN ('1999Q1', '1999Q2', '1999Q3')
+  AND "store_returns"."sr_returned_date_sk" = "d2"."d_date_sk"
+JOIN "date_dim" AS "d3"
+  ON "catalog_sales"."cs_sold_date_sk" = "d3"."d_date_sk"
+  AND "d3"."d_quarter_name" IN ('1999Q1', '1999Q2', '1999Q3')
 GROUP BY
   "item"."i_item_id",
   "item"."i_item_desc",
@@ -2674,25 +2674,25 @@ SELECT
   AVG(CAST("catalog_sales"."cs_sales_price" AS DECIMAL(12, 2))) AS "agg4",
   AVG(CAST("catalog_sales"."cs_net_profit" AS DECIMAL(12, 2))) AS "agg5",
   AVG(CAST("customer"."c_birth_year" AS DECIMAL(12, 2))) AS "agg6",
-  AVG(CAST("customer_demographics"."cd_dep_count" AS DECIMAL(12, 2))) AS "agg7"
+  AVG(CAST("cd1"."cd_dep_count" AS DECIMAL(12, 2))) AS "agg7"
 FROM "catalog_sales" AS "catalog_sales"
-JOIN "customer_demographics" AS "customer_demographics"
-  ON "catalog_sales"."cs_bill_cdemo_sk" = "customer_demographics"."cd_demo_sk"
-  AND "customer_demographics"."cd_education_status" = 'Secondary'
-  AND "customer_demographics"."cd_gender" = 'F'
+JOIN "customer_demographics" AS "cd1"
+  ON "catalog_sales"."cs_bill_cdemo_sk" = "cd1"."cd_demo_sk"
+  AND "cd1"."cd_education_status" = 'Secondary'
+  AND "cd1"."cd_gender" = 'F'
 JOIN "customer" AS "customer"
   ON "catalog_sales"."cs_bill_customer_sk" = "customer"."c_customer_sk"
   AND "customer"."c_birth_month" IN (8, 4, 2, 5, 11, 9)
-JOIN "customer_demographics" AS "customer_demographics_2"
-  ON "customer"."c_current_cdemo_sk" = "customer_demographics_2"."cd_demo_sk"
-JOIN "customer_address" AS "customer_address"
-  ON "customer"."c_current_addr_sk" = "customer_address"."ca_address_sk"
-  AND "customer_address"."ca_state" IN ('KS', 'IA', 'AL', 'UT', 'VA', 'NC', 'TX')
 JOIN "date_dim" AS "date_dim"
   ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk"
   AND "date_dim"."d_year" = 2001
 JOIN "item" AS "item"
   ON "catalog_sales"."cs_item_sk" = "item"."i_item_sk"
+JOIN "customer_demographics" AS "cd2"
+  ON "customer"."c_current_cdemo_sk" = "cd2"."cd_demo_sk"
+JOIN "customer_address" AS "customer_address"
+  ON "customer"."c_current_addr_sk" = "customer_address"."ca_address_sk"
+  AND "customer_address"."ca_state" IN ('KS', 'IA', 'AL', 'UT', 'VA', 'NC', 'TX')
 GROUP BY
 ROLLUP (
   "item"."i_item_id",
@@ -2708,6 +2708,72 @@ ORDER BY
 LIMIT 100;
 
 --------------------------------------
+-- TPC-DS 19
+--------------------------------------
+SELECT i_brand_id              brand_id,
+               i_brand                 brand,
+               i_manufact_id,
+               i_manufact,
+               Sum(ss_ext_sales_price) ext_price
+FROM   date_dim,
+       store_sales,
+       item,
+       customer,
+       customer_address,
+       store
+WHERE  d_date_sk = ss_sold_date_sk
+       AND ss_item_sk = i_item_sk
+       AND i_manager_id = 38
+       AND d_moy = 12
+       AND d_year = 1998
+       AND ss_customer_sk = c_customer_sk
+       AND c_current_addr_sk = ca_address_sk
+       AND Substr(ca_zip, 1, 5) <> Substr(s_zip, 1, 5)
+       AND ss_store_sk = s_store_sk
+GROUP  BY i_brand,
+          i_brand_id,
+          i_manufact_id,
+          i_manufact
+ORDER  BY ext_price DESC,
+          i_brand,
+          i_brand_id,
+          i_manufact_id,
+          i_manufact
+LIMIT 100;
+SELECT
+  "item"."i_brand_id" AS "brand_id",
+  "item"."i_brand" AS "brand",
+  "item"."i_manufact_id" AS "i_manufact_id",
+  "item"."i_manufact" AS "i_manufact",
+  SUM("store_sales"."ss_ext_sales_price") AS "ext_price"
+FROM "date_dim" AS "date_dim"
+JOIN "store_sales" AS "store_sales"
+  ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk"
+JOIN "item" AS "item"
+  ON "item"."i_manager_id" = 38 AND "store_sales"."ss_item_sk" = "item"."i_item_sk"
+JOIN "store" AS "store"
+  ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
+JOIN "customer_address" AS "customer_address"
+  ON SUBSTR("customer_address"."ca_zip", 1, 5) <> SUBSTR("store"."s_zip", 1, 5)
+JOIN "customer" AS "customer"
+  ON "customer"."c_current_addr_sk" = "customer_address"."ca_address_sk"
+  AND "store_sales"."ss_customer_sk" = "customer"."c_customer_sk"
+WHERE
+  "date_dim"."d_moy" = 12 AND "date_dim"."d_year" = 1998
+GROUP BY
+  "item"."i_brand",
+  "item"."i_brand_id",
+  "item"."i_manufact_id",
+  "item"."i_manufact"
+ORDER BY
+  "ext_price" DESC,
+  "item"."i_brand",
+  "item"."i_brand_id",
+  "i_manufact_id",
+  "i_manufact"
+LIMIT 100;
+
+--------------------------------------
 -- TPC-DS 20
 --------------------------------------
 SELECT
@@ -2748,13 +2814,13 @@ SELECT
   SUM("catalog_sales"."cs_ext_sales_price") AS "itemrevenue",
   SUM("catalog_sales"."cs_ext_sales_price") * 100 / SUM(SUM("catalog_sales"."cs_ext_sales_price")) OVER (PARTITION BY "item"."i_class") AS "revenueratio"
 FROM "catalog_sales" AS "catalog_sales"
-JOIN "item" AS "item"
-  ON "catalog_sales"."cs_item_sk" = "item"."i_item_sk"
-  AND "item"."i_category" IN ('Children', 'Women', 'Electronics')
 JOIN "date_dim" AS "date_dim"
   ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk"
   AND CAST("date_dim"."d_date" AS DATE) <= CAST('2001-03-05' AS DATE)
   AND CAST("date_dim"."d_date" AS DATE) >= CAST('2001-02-03' AS DATE)
+JOIN "item" AS "item"
+  ON "catalog_sales"."cs_item_sk" = "item"."i_item_sk"
+  AND "item"."i_category" IN ('Children', 'Women', 'Electronics')
 GROUP BY
   "item"."i_item_id",
   "item"."i_item_desc",
@@ -2828,16 +2894,16 @@ WITH "x" AS (
       END
     ) AS "inv_after"
   FROM "inventory" AS "inventory"
-  JOIN "warehouse" AS "warehouse"
-    ON "inventory"."inv_warehouse_sk" = "warehouse"."w_warehouse_sk"
-  JOIN "item" AS "item"
-    ON "item"."i_current_price" <= 1.49
-    AND "item"."i_current_price" >= 0.99
-    AND "item"."i_item_sk" = "inventory"."inv_item_sk"
   JOIN "date_dim" AS "date_dim"
     ON "inventory"."inv_date_sk" = "date_dim"."d_date_sk"
     AND CAST("date_dim"."d_date" AS DATE) <= CAST('2000-06-12' AS DATE)
     AND CAST("date_dim"."d_date" AS DATE) >= CAST('2000-04-13' AS DATE)
+  JOIN "item" AS "item"
+    ON "item"."i_current_price" <= 1.49
+    AND "item"."i_current_price" >= 0.99
+    AND "item"."i_item_sk" = "inventory"."inv_item_sk"
+  JOIN "warehouse" AS "warehouse"
+    ON "inventory"."inv_warehouse_sk" = "warehouse"."w_warehouse_sk"
   GROUP BY
     "warehouse"."w_warehouse_name",
     "item"."i_item_id"
@@ -3008,21 +3074,13 @@ WITH "frequent_ss_items" AS (
   SELECT
     "customer"."c_customer_sk" AS "c_customer_sk"
   FROM "store_sales" AS "store_sales"
+  CROSS JOIN "max_store_sales"
   JOIN "customer_2" AS "customer"
     ON "store_sales"."ss_customer_sk" = "customer"."c_customer_sk"
-  CROSS JOIN "max_store_sales"
   GROUP BY
     "customer"."c_customer_sk"
   HAVING
     SUM("store_sales"."ss_quantity" * "store_sales"."ss_sales_price") > 0.95 * MAX("max_store_sales"."tpcds_cmax")
-), "date_dim_4" AS (
-  SELECT
-    "date_dim"."d_date_sk" AS "d_date_sk",
-    "date_dim"."d_year" AS "d_year",
-    "date_dim"."d_moy" AS "d_moy"
-  FROM "date_dim" AS "date_dim"
-  WHERE
-    "date_dim"."d_moy" = 6 AND "date_dim"."d_year" = 1998
 ), "_u_1" AS (
   SELECT
     "frequent_ss_items"."item_sk" AS "item_sk"
@@ -3035,28 +3093,36 @@ WITH "frequent_ss_items" AS (
   FROM "best_ss_customer"
   GROUP BY
     "best_ss_customer"."c_customer_sk"
+), "date_dim_4" AS (
+  SELECT
+    "date_dim"."d_date_sk" AS "d_date_sk",
+    "date_dim"."d_year" AS "d_year",
+    "date_dim"."d_moy" AS "d_moy"
+  FROM "date_dim" AS "date_dim"
+  WHERE
+    "date_dim"."d_moy" = 6 AND "date_dim"."d_year" = 1998
 ), "_q_1" AS (
   SELECT
     "catalog_sales"."cs_quantity" * "catalog_sales"."cs_list_price" AS "sales"
   FROM "catalog_sales" AS "catalog_sales"
-  JOIN "date_dim_4" AS "date_dim"
-    ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk"
   LEFT JOIN "_u_1" AS "_u_1"
     ON "catalog_sales"."cs_item_sk" = "_u_1"."item_sk"
   LEFT JOIN "_u_2" AS "_u_2"
     ON "catalog_sales"."cs_bill_customer_sk" = "_u_2"."c_customer_sk"
+  JOIN "date_dim_4" AS "date_dim"
+    ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk"
   WHERE
     NOT "_u_1"."item_sk" IS NULL AND NOT "_u_2"."c_customer_sk" IS NULL
   UNION ALL
   SELECT
     "web_sales"."ws_quantity" * "web_sales"."ws_list_price" AS "sales"
   FROM "web_sales" AS "web_sales"
-  JOIN "date_dim_4" AS "date_dim"
-    ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk"
   LEFT JOIN "_u_1" AS "_u_3"
     ON "web_sales"."ws_item_sk" = "_u_3"."item_sk"
   LEFT JOIN "_u_2" AS "_u_4"
     ON "web_sales"."ws_bill_customer_sk" = "_u_4"."c_customer_sk"
+  JOIN "date_dim_4" AS "date_dim"
+    ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk"
   WHERE
     NOT "_u_3"."item_sk" IS NULL AND NOT "_u_4"."c_customer_sk" IS NULL
 )
@@ -3123,18 +3189,18 @@ WITH "ssales" AS (
     "item"."i_color" AS "i_color",
     SUM("store_sales"."ss_net_profit") AS "netpaid"
   FROM "store_sales" AS "store_sales"
+  JOIN "item" AS "item"
+    ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
+  JOIN "store" AS "store"
+    ON "store"."s_market_id" = 6 AND "store_sales"."ss_store_sk" = "store"."s_store_sk"
   JOIN "store_returns" AS "store_returns"
     ON "store_sales"."ss_item_sk" = "store_returns"."sr_item_sk"
     AND "store_sales"."ss_ticket_number" = "store_returns"."sr_ticket_number"
-  JOIN "store" AS "store"
-    ON "store"."s_market_id" = 6 AND "store_sales"."ss_store_sk" = "store"."s_store_sk"
-  JOIN "item" AS "item"
-    ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
-  JOIN "customer" AS "customer"
-    ON "store_sales"."ss_customer_sk" = "customer"."c_customer_sk"
   JOIN "customer_address" AS "customer_address"
+    ON "store"."s_zip" = "customer_address"."ca_zip"
+  JOIN "customer" AS "customer"
     ON "customer"."c_birth_country" = UPPER("customer_address"."ca_country")
-    AND "store"."s_zip" = "customer_address"."ca_zip"
+    AND "store_sales"."ss_customer_sk" = "customer"."c_customer_sk"
   GROUP BY
     "customer"."c_last_name",
     "customer"."c_first_name",
@@ -3210,15 +3276,6 @@ ORDER  BY i_item_id,
           s_store_id,
           s_store_name
 LIMIT 100;
-WITH "d3" AS (
-  SELECT
-    "date_dim"."d_date_sk" AS "d_date_sk",
-    "date_dim"."d_year" AS "d_year",
-    "date_dim"."d_moy" AS "d_moy"
-  FROM "date_dim" AS "date_dim"
-  WHERE
-    "date_dim"."d_moy" <= 10 AND "date_dim"."d_moy" >= 4 AND "date_dim"."d_year" = 2001
-)
 SELECT
   "item"."i_item_id" AS "i_item_id",
   "item"."i_item_desc" AS "i_item_desc",
@@ -3228,25 +3285,31 @@ SELECT
   MAX("store_returns"."sr_net_loss") AS "store_returns_loss",
   MAX("catalog_sales"."cs_net_profit") AS "catalog_sales_profit"
 FROM "store_sales" AS "store_sales"
-CROSS JOIN "d3" AS "d3"
-JOIN "catalog_sales" AS "catalog_sales"
-  ON "catalog_sales"."cs_sold_date_sk" = "d3"."d_date_sk"
+JOIN "date_dim" AS "d1"
+  ON "d1"."d_date_sk" = "store_sales"."ss_sold_date_sk"
+  AND "d1"."d_moy" = 4
+  AND "d1"."d_year" = 2001
+JOIN "item" AS "item"
+  ON "item"."i_item_sk" = "store_sales"."ss_item_sk"
+JOIN "store" AS "store"
+  ON "store"."s_store_sk" = "store_sales"."ss_store_sk"
 JOIN "store_returns" AS "store_returns"
-  ON "store_returns"."sr_customer_sk" = "catalog_sales"."cs_bill_customer_sk"
-  AND "store_returns"."sr_item_sk" = "catalog_sales"."cs_item_sk"
-  AND "store_sales"."ss_customer_sk" = "store_returns"."sr_customer_sk"
+  ON "store_sales"."ss_customer_sk" = "store_returns"."sr_customer_sk"
   AND "store_sales"."ss_item_sk" = "store_returns"."sr_item_sk"
   AND "store_sales"."ss_ticket_number" = "store_returns"."sr_ticket_number"
-JOIN "date_dim" AS "date_dim"
-  ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk"
-  AND "date_dim"."d_moy" = 4
-  AND "date_dim"."d_year" = 2001
-JOIN "d3" AS "d2"
-  ON "store_returns"."sr_returned_date_sk" = "d2"."d_date_sk"
-JOIN "store" AS "store"
-  ON "store"."s_store_sk" = "store_sales"."ss_store_sk"
-JOIN "item" AS "item"
-  ON "item"."i_item_sk" = "store_sales"."ss_item_sk"
+JOIN "catalog_sales" AS "catalog_sales"
+  ON "store_returns"."sr_customer_sk" = "catalog_sales"."cs_bill_customer_sk"
+  AND "store_returns"."sr_item_sk" = "catalog_sales"."cs_item_sk"
+JOIN "date_dim" AS "d2"
+  ON "d2"."d_moy" <= 10
+  AND "d2"."d_moy" >= 4
+  AND "d2"."d_year" = 2001
+  AND "store_returns"."sr_returned_date_sk" = "d2"."d_date_sk"
+JOIN "date_dim" AS "d3"
+  ON "catalog_sales"."cs_sold_date_sk" = "d3"."d_date_sk"
+  AND "d3"."d_moy" <= 10
+  AND "d3"."d_moy" >= 4
+  AND "d3"."d_year" = 2001
 GROUP BY
   "item"."i_item_id",
   "item"."i_item_desc",
@@ -3359,11 +3422,11 @@ JOIN "customer_demographics" AS "customer_demographics"
 JOIN "date_dim" AS "date_dim"
   ON "date_dim"."d_year" = 2000
   AND "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
+JOIN "item" AS "item"
+  ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
 JOIN "store" AS "store"
   ON "store"."s_state" IN ('TN', 'TN', 'TN', 'TN', 'TN', 'TN')
   AND "store_sales"."ss_store_sk" = "store"."s_store_sk"
-JOIN "item" AS "item"
-  ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
 GROUP BY
 ROLLUP (
   "item"."i_item_id",
@@ -3590,29 +3653,29 @@ SELECT
   AVG("store_returns"."sr_return_quantity") AS "store_returns_quantity",
   AVG("catalog_sales"."cs_quantity") AS "catalog_sales_quantity"
 FROM "store_sales" AS "store_sales"
-JOIN "date_dim" AS "date_dim"
-  ON "date_dim"."d_year" IN (1998, 1999, 2000)
-JOIN "catalog_sales" AS "catalog_sales"
-  ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk"
+JOIN "date_dim" AS "d1"
+  ON "d1"."d_date_sk" = "store_sales"."ss_sold_date_sk"
+  AND "d1"."d_moy" = 4
+  AND "d1"."d_year" = 1998
+JOIN "item" AS "item"
+  ON "item"."i_item_sk" = "store_sales"."ss_item_sk"
+JOIN "store" AS "store"
+  ON "store"."s_store_sk" = "store_sales"."ss_store_sk"
 JOIN "store_returns" AS "store_returns"
-  ON "store_returns"."sr_customer_sk" = "catalog_sales"."cs_bill_customer_sk"
-  AND "store_returns"."sr_item_sk" = "catalog_sales"."cs_item_sk"
-  AND "store_sales"."ss_customer_sk" = "store_returns"."sr_customer_sk"
+  ON "store_sales"."ss_customer_sk" = "store_returns"."sr_customer_sk"
   AND "store_sales"."ss_item_sk" = "store_returns"."sr_item_sk"
   AND "store_sales"."ss_ticket_number" = "store_returns"."sr_ticket_number"
-JOIN "date_dim" AS "date_dim_2"
-  ON "date_dim_2"."d_date_sk" = "store_sales"."ss_sold_date_sk"
-  AND "date_dim_2"."d_moy" = 4
-  AND "date_dim_2"."d_year" = 1998
-JOIN "date_dim" AS "date_dim_3"
-  ON "date_dim_3"."d_moy" <= 7
-  AND "date_dim_3"."d_moy" >= 4
-  AND "date_dim_3"."d_year" = 1998
-  AND "store_returns"."sr_returned_date_sk" = "date_dim_3"."d_date_sk"
-JOIN "store" AS "store"
-  ON "store"."s_store_sk" = "store_sales"."ss_store_sk"
-JOIN "item" AS "item"
-  ON "item"."i_item_sk" = "store_sales"."ss_item_sk"
+JOIN "catalog_sales" AS "catalog_sales"
+  ON "store_returns"."sr_customer_sk" = "catalog_sales"."cs_bill_customer_sk"
+  AND "store_returns"."sr_item_sk" = "catalog_sales"."cs_item_sk"
+JOIN "date_dim" AS "d2"
+  ON "d2"."d_moy" <= 7
+  AND "d2"."d_moy" >= 4
+  AND "d2"."d_year" = 1998
+  AND "store_returns"."sr_returned_date_sk" = "d2"."d_date_sk"
+JOIN "date_dim" AS "d3"
+  ON "catalog_sales"."cs_sold_date_sk" = "d3"."d_date_sk"
+  AND "d3"."d_year" IN (1998, 1999, 2000)
 GROUP BY
   "item"."i_item_id",
   "item"."i_item_desc",
@@ -3682,11 +3745,11 @@ WITH "customer_total_return" AS (
     "customer_address"."ca_state" AS "ctr_state",
     SUM("web_returns"."wr_return_amt") AS "ctr_total_return"
   FROM "web_returns" AS "web_returns"
+  JOIN "customer_address" AS "customer_address"
+    ON "web_returns"."wr_returning_addr_sk" = "customer_address"."ca_address_sk"
   JOIN "date_dim" AS "date_dim"
     ON "date_dim"."d_year" = 2000
     AND "web_returns"."wr_returned_date_sk" = "date_dim"."d_date_sk"
-  JOIN "customer_address" AS "customer_address"
-    ON "web_returns"."wr_returning_addr_sk" = "customer_address"."ca_address_sk"
   GROUP BY
     "web_returns"."wr_returning_customer_sk",
     "customer_address"."ca_state"
@@ -3713,13 +3776,13 @@ SELECT
   "customer"."c_last_review_date" AS "c_last_review_date",
   "ctr1"."ctr_total_return" AS "ctr_total_return"
 FROM "customer_total_return" AS "ctr1"
+LEFT JOIN "_u_0" AS "_u_0"
+  ON "ctr1"."ctr_state" = "_u_0"."_u_1"
 JOIN "customer" AS "customer"
   ON "ctr1"."ctr_customer_sk" = "customer"."c_customer_sk"
 JOIN "customer_address" AS "customer_address"
   ON "customer_address"."ca_address_sk" = "customer"."c_current_addr_sk"
   AND "customer_address"."ca_state" = 'IN'
-LEFT JOIN "_u_0" AS "_u_0"
-  ON "ctr1"."ctr_state" = "_u_0"."_u_1"
 WHERE
   "ctr1"."ctr_total_return" > "_u_0"."_col_0"
 ORDER BY
@@ -3813,17 +3876,17 @@ WHERE  ss1.d_qoy = 1
                    ELSE NULL
                  END
 ORDER  BY ss1.d_year;
-WITH "date_dim_2" AS (
+WITH "customer_address_2" AS (
+  SELECT
+    "customer_address"."ca_address_sk" AS "ca_address_sk",
+    "customer_address"."ca_county" AS "ca_county"
+  FROM "customer_address" AS "customer_address"
+), "date_dim_2" AS (
   SELECT
     "date_dim"."d_date_sk" AS "d_date_sk",
     "date_dim"."d_year" AS "d_year",
     "date_dim"."d_qoy" AS "d_qoy"
   FROM "date_dim" AS "date_dim"
-), "customer_address_2" AS (
-  SELECT
-    "customer_address"."ca_address_sk" AS "ca_address_sk",
-    "customer_address"."ca_county" AS "ca_county"
-  FROM "customer_address" AS "customer_address"
 ), "ss" AS (
   SELECT
     "customer_address"."ca_county" AS "ca_county",
@@ -3831,10 +3894,10 @@ WITH "date_dim_2" AS (
     "date_dim"."d_year" AS "d_year",
     SUM("store_sales"."ss_ext_sales_price") AS "store_sales"
   FROM "store_sales" AS "store_sales"
-  JOIN "date_dim_2" AS "date_dim"
-    ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
   JOIN "customer_address_2" AS "customer_address"
     ON "store_sales"."ss_addr_sk" = "customer_address"."ca_address_sk"
+  JOIN "date_dim_2" AS "date_dim"
+    ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
   GROUP BY
     "customer_address"."ca_county",
     "date_dim"."d_qoy",
@@ -3846,10 +3909,10 @@ WITH "date_dim_2" AS (
     "date_dim"."d_year" AS "d_year",
     SUM("web_sales"."ws_ext_sales_price") AS "web_sales"
   FROM "web_sales" AS "web_sales"
-  JOIN "date_dim_2" AS "date_dim"
-    ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk"
   JOIN "customer_address_2" AS "customer_address"
     ON "web_sales"."ws_bill_addr_sk" = "customer_address"."ca_address_sk"
+  JOIN "date_dim_2" AS "date_dim"
+    ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk"
   GROUP BY
     "customer_address"."ca_county",
     "date_dim"."d_qoy",
@@ -3865,13 +3928,12 @@ SELECT
 FROM "ss" AS "ss1"
 JOIN "ss" AS "ss2"
   ON "ss1"."ca_county" = "ss2"."ca_county" AND "ss2"."d_qoy" = 2 AND "ss2"."d_year" = 2001
-JOIN "ws" AS "ws2"
-  ON "ws2"."d_qoy" = 2 AND "ws2"."d_year" = 2001
 JOIN "ws" AS "ws1"
-  ON "ss1"."ca_county" = "ws1"."ca_county"
-  AND "ws1"."ca_county" = "ws2"."ca_county"
-  AND "ws1"."d_qoy" = 1
-  AND "ws1"."d_year" = 2001
+  ON "ss1"."ca_county" = "ws1"."ca_county" AND "ws1"."d_qoy" = 1 AND "ws1"."d_year" = 2001
+JOIN "ws" AS "ws2"
+  ON "ws1"."ca_county" = "ws2"."ca_county"
+  AND "ws2"."d_qoy" = 2
+  AND "ws2"."d_year" = 2001
   AND CASE
     WHEN "ws1"."web_sales" > 0
     THEN "ws2"."web_sales" / "ws1"."web_sales"
@@ -3951,10 +4013,10 @@ WITH "catalog_sales_2" AS (
 SELECT
   SUM("catalog_sales"."cs_ext_discount_amt") AS "excess discount amount"
 FROM "catalog_sales_2" AS "catalog_sales"
-JOIN "item" AS "item"
-  ON "item"."i_item_sk" = "catalog_sales"."cs_item_sk" AND "item"."i_manufact_id" = 610
 JOIN "date_dim_2" AS "date_dim"
   ON "date_dim"."d_date_sk" = "catalog_sales"."cs_sold_date_sk"
+JOIN "item" AS "item"
+  ON "item"."i_item_sk" = "catalog_sales"."cs_item_sk" AND "item"."i_manufact_id" = 610
 LEFT JOIN "_u_0" AS "_u_0"
   ON "_u_0"."_u_1" = "item"."i_item_sk"
 WHERE
@@ -4028,7 +4090,14 @@ FROM   (SELECT *
 GROUP  BY i_manufact_id
 ORDER  BY total_sales
 LIMIT 100;
-WITH "date_dim_2" AS (
+WITH "customer_address_2" AS (
+  SELECT
+    "customer_address"."ca_address_sk" AS "ca_address_sk",
+    "customer_address"."ca_gmt_offset" AS "ca_gmt_offset"
+  FROM "customer_address" AS "customer_address"
+  WHERE
+    "customer_address"."ca_gmt_offset" = -5
+), "date_dim_2" AS (
   SELECT
     "date_dim"."d_date_sk" AS "d_date_sk",
     "date_dim"."d_year" AS "d_year",
@@ -4036,13 +4105,6 @@ WITH "date_dim_2" AS (
   FROM "date_dim" AS "date_dim"
   WHERE
     "date_dim"."d_moy" = 3 AND "date_dim"."d_year" = 1999
-), "customer_address_2" AS (
-  SELECT
-    "customer_address"."ca_address_sk" AS "ca_address_sk",
-    "customer_address"."ca_gmt_offset" AS "ca_gmt_offset"
-  FROM "customer_address" AS "customer_address"
-  WHERE
-    "customer_address"."ca_gmt_offset" = -5
 ), "item_2" AS (
   SELECT
     "item"."i_item_sk" AS "i_item_sk",
@@ -4061,10 +4123,10 @@ WITH "date_dim_2" AS (
     "item"."i_manufact_id" AS "i_manufact_id",
     SUM("store_sales"."ss_ext_sales_price") AS "total_sales"
   FROM "store_sales" AS "store_sales"
-  JOIN "date_dim_2" AS "date_dim"
-    ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
   JOIN "customer_address_2" AS "customer_address"
     ON "store_sales"."ss_addr_sk" = "customer_address"."ca_address_sk"
+  JOIN "date_dim_2" AS "date_dim"
+    ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
   JOIN "item_2" AS "item"
     ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
   LEFT JOIN "_u_0" AS "_u_0"
@@ -4078,10 +4140,10 @@ WITH "date_dim_2" AS (
     "item"."i_manufact_id" AS "i_manufact_id",
     SUM("catalog_sales"."cs_ext_sales_price") AS "total_sales"
   FROM "catalog_sales" AS "catalog_sales"
-  JOIN "date_dim_2" AS "date_dim"
-    ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk"
   JOIN "customer_address_2" AS "customer_address"
     ON "catalog_sales"."cs_bill_addr_sk" = "customer_address"."ca_address_sk"
+  JOIN "date_dim_2" AS "date_dim"
+    ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk"
   JOIN "item_2" AS "item"
     ON "catalog_sales"."cs_item_sk" = "item"."i_item_sk"
   LEFT JOIN "_u_0" AS "_u_1"
@@ -4095,10 +4157,10 @@ WITH "date_dim_2" AS (
     "item"."i_manufact_id" AS "i_manufact_id",
     SUM("web_sales"."ws_ext_sales_price") AS "total_sales"
   FROM "web_sales" AS "web_sales"
-  JOIN "date_dim_2" AS "date_dim"
-    ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk"
   JOIN "customer_address_2" AS "customer_address"
     ON "web_sales"."ws_bill_addr_sk" = "customer_address"."ca_address_sk"
+  JOIN "date_dim_2" AS "date_dim"
+    ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk"
   JOIN "item_2" AS "item"
     ON "web_sales"."ws_item_sk" = "item"."i_item_sk"
   LEFT JOIN "_u_0" AS "_u_2"
@@ -4203,9 +4265,6 @@ WITH "dn" AS (
         "date_dim"."d_dom" <= 3 AND "date_dim"."d_dom" >= 1
       )
     )
-  JOIN "store" AS "store"
-    ON "store"."s_county" IN ('Williamson County', 'Williamson County', 'Williamson County', 'Williamson County', 'Williamson County', 'Williamson County', 'Williamson County', 'Williamson County')
-    AND "store_sales"."ss_store_sk" = "store"."s_store_sk"
   JOIN "household_demographics" AS "household_demographics"
     ON (
       "household_demographics"."hd_buy_potential" = '>10000'
@@ -4218,6 +4277,9 @@ WITH "dn" AS (
       THEN "household_demographics"."hd_dep_count" / "household_demographics"."hd_vehicle_count"
       ELSE NULL
     END > 1.2
+  JOIN "store" AS "store"
+    ON "store"."s_county" IN ('Williamson County', 'Williamson County', 'Williamson County', 'Williamson County', 'Williamson County', 'Williamson County', 'Williamson County', 'Williamson County')
+    AND "store_sales"."ss_store_sk" = "store"."s_store_sk"
   GROUP BY
     "store_sales"."ss_ticket_number",
     "store_sales"."ss_customer_sk"
@@ -4359,7 +4421,7 @@ WITH "date_dim_2" AS (
     "catalog_sales"."cs_ship_customer_sk"
 )
 SELECT
-  "customer_address"."ca_state" AS "ca_state",
+  "ca"."ca_state" AS "ca_state",
   "customer_demographics"."cd_gender" AS "cd_gender",
   "customer_demographics"."cd_marital_status" AS "cd_marital_status",
   "customer_demographics"."cd_dep_count" AS "cd_dep_count",
@@ -4377,24 +4439,24 @@ SELECT
   STDDEV_SAMP("customer_demographics"."cd_dep_college_count") AS "_col_15",
   AVG("customer_demographics"."cd_dep_college_count") AS "_col_16",
   MAX("customer_demographics"."cd_dep_college_count") AS "_col_17"
-FROM "customer" AS "customer"
-JOIN "customer_address" AS "customer_address"
-  ON "customer"."c_current_addr_sk" = "customer_address"."ca_address_sk"
-JOIN "customer_demographics" AS "customer_demographics"
-  ON "customer_demographics"."cd_demo_sk" = "customer"."c_current_cdemo_sk"
+FROM "customer" AS "c"
 LEFT JOIN "_u_0" AS "_u_0"
-  ON "customer"."c_customer_sk" = "_u_0"."_u_1"
+  ON "c"."c_customer_sk" = "_u_0"."_u_1"
 LEFT JOIN "_u_2" AS "_u_2"
-  ON "customer"."c_customer_sk" = "_u_2"."_u_3"
+  ON "c"."c_customer_sk" = "_u_2"."_u_3"
 LEFT JOIN "_u_4" AS "_u_4"
-  ON "customer"."c_customer_sk" = "_u_4"."_u_5"
+  ON "c"."c_customer_sk" = "_u_4"."_u_5"
+JOIN "customer_address" AS "ca"
+  ON "c"."c_current_addr_sk" = "ca"."ca_address_sk"
+JOIN "customer_demographics" AS "customer_demographics"
+  ON "customer_demographics"."cd_demo_sk" = "c"."c_current_cdemo_sk"
 WHERE
   NOT "_u_0"."_u_1" IS NULL
   AND (
     NOT "_u_2"."_u_3" IS NULL OR NOT "_u_4"."_u_5" IS NULL
   )
 GROUP BY
-  "customer_address"."ca_state",
+  "ca"."ca_state",
   "customer_demographics"."cd_gender",
   "customer_demographics"."cd_marital_status",
   "customer_demographics"."cd_dep_count",
@@ -4449,9 +4511,8 @@ SELECT
   GROUPING("item"."i_category") + GROUPING("item"."i_class") AS "lochierarchy",
   RANK() OVER (PARTITION BY GROUPING("item"."i_category") + GROUPING("item"."i_class"), CASE WHEN GROUPING("item"."i_class") = 0 THEN "item"."i_category" END ORDER BY SUM("store_sales"."ss_net_profit") / SUM("store_sales"."ss_ext_sales_price")) AS "rank_within_parent"
 FROM "store_sales" AS "store_sales"
-JOIN "date_dim" AS "date_dim"
-  ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk"
-  AND "date_dim"."d_year" = 2000
+JOIN "date_dim" AS "d1"
+  ON "d1"."d_date_sk" = "store_sales"."ss_sold_date_sk" AND "d1"."d_year" = 2000
 JOIN "item" AS "item"
   ON "item"."i_item_sk" = "store_sales"."ss_item_sk"
 JOIN "store" AS "store"
@@ -4497,6 +4558,8 @@ SELECT
   "item"."i_item_desc" AS "i_item_desc",
   "item"."i_current_price" AS "i_current_price"
 FROM "item" AS "item"
+JOIN "catalog_sales" AS "catalog_sales"
+  ON "catalog_sales"."cs_item_sk" = "item"."i_item_sk"
 JOIN "inventory" AS "inventory"
   ON "inventory"."inv_item_sk" = "item"."i_item_sk"
   AND "inventory"."inv_quantity_on_hand" <= 500
@@ -4505,8 +4568,6 @@ JOIN "date_dim" AS "date_dim"
   ON "date_dim"."d_date_sk" = "inventory"."inv_date_sk"
   AND CAST("date_dim"."d_date" AS DATE) <= CAST('1999-05-05' AS DATE)
   AND CAST("date_dim"."d_date" AS DATE) >= CAST('1999-03-06' AS DATE)
-JOIN "catalog_sales" AS "catalog_sales"
-  ON "catalog_sales"."cs_item_sk" = "item"."i_item_sk"
 WHERE
   "item"."i_current_price" <= 50
   AND "item"."i_current_price" >= 20
@@ -4553,7 +4614,13 @@ FROM   (SELECT DISTINCT c_last_name,
                AND web_sales.ws_bill_customer_sk = customer.c_customer_sk
                AND d_month_seq BETWEEN 1188 AND 1188 + 11) hot_cust
 LIMIT 100;
-WITH "date_dim_2" AS (
+WITH "customer_2" AS (
+  SELECT
+    "customer"."c_customer_sk" AS "c_customer_sk",
+    "customer"."c_first_name" AS "c_first_name",
+    "customer"."c_last_name" AS "c_last_name"
+  FROM "customer" AS "customer"
+), "date_dim_2" AS (
   SELECT
     "date_dim"."d_date_sk" AS "d_date_sk",
     "date_dim"."d_date" AS "d_date",
@@ -4561,42 +4628,36 @@ WITH "date_dim_2" AS (
   FROM "date_dim" AS "date_dim"
   WHERE
     "date_dim"."d_month_seq" <= 1199 AND "date_dim"."d_month_seq" >= 1188
-), "customer_2" AS (
-  SELECT
-    "customer"."c_customer_sk" AS "c_customer_sk",
-    "customer"."c_first_name" AS "c_first_name",
-    "customer"."c_last_name" AS "c_last_name"
-  FROM "customer" AS "customer"
 ), "cte" AS (
   SELECT DISTINCT
     "customer"."c_last_name" AS "c_last_name",
     "customer"."c_first_name" AS "c_first_name",
     "date_dim"."d_date" AS "d_date"
   FROM "store_sales" AS "store_sales"
-  JOIN "date_dim_2" AS "date_dim"
-    ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
   JOIN "customer_2" AS "customer"
     ON "store_sales"."ss_customer_sk" = "customer"."c_customer_sk"
+  JOIN "date_dim_2" AS "date_dim"
+    ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
 ), "cte_2" AS (
   SELECT DISTINCT
     "customer"."c_last_name" AS "c_last_name",
     "customer"."c_first_name" AS "c_first_name",
     "date_dim"."d_date" AS "d_date"
   FROM "catalog_sales" AS "catalog_sales"
-  JOIN "date_dim_2" AS "date_dim"
-    ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk"
   JOIN "customer_2" AS "customer"
     ON "catalog_sales"."cs_bill_customer_sk" = "customer"."c_customer_sk"
+  JOIN "date_dim_2" AS "date_dim"
+    ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk"
 ), "cte_3" AS (
   SELECT DISTINCT
     "customer"."c_last_name" AS "c_last_name",
     "customer"."c_first_name" AS "c_first_name",
     "date_dim"."d_date" AS "d_date"
   FROM "web_sales" AS "web_sales"
-  JOIN "date_dim_2" AS "date_dim"
-    ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk"
   JOIN "customer_2" AS "customer"
     ON "web_sales"."ws_bill_customer_sk" = "customer"."c_customer_sk"
+  JOIN "date_dim_2" AS "date_dim"
+    ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk"
 ), "cte_4" AS (
   SELECT
     "cte_2"."c_last_name" AS "c_last_name",
@@ -4695,12 +4756,12 @@ WITH "foo" AS (
     STDDEV_SAMP("inventory"."inv_quantity_on_hand") AS "stdev",
     AVG("inventory"."inv_quantity_on_hand") AS "mean"
   FROM "inventory" AS "inventory"
+  JOIN "date_dim" AS "date_dim"
+    ON "date_dim"."d_year" = 2002 AND "inventory"."inv_date_sk" = "date_dim"."d_date_sk"
   JOIN "item" AS "item"
     ON "inventory"."inv_item_sk" = "item"."i_item_sk"
   JOIN "warehouse" AS "warehouse"
     ON "inventory"."inv_warehouse_sk" = "warehouse"."w_warehouse_sk"
-  JOIN "date_dim" AS "date_dim"
-    ON "date_dim"."d_year" = 2002 AND "inventory"."inv_date_sk" = "date_dim"."d_date_sk"
   GROUP BY
     "warehouse"."w_warehouse_name",
     "warehouse"."w_warehouse_sk",
@@ -4803,16 +4864,16 @@ FROM "catalog_sales" AS "catalog_sales"
 LEFT JOIN "catalog_returns" AS "catalog_returns"
   ON "catalog_sales"."cs_item_sk" = "catalog_returns"."cr_item_sk"
   AND "catalog_sales"."cs_order_number" = "catalog_returns"."cr_order_number"
-JOIN "warehouse" AS "warehouse"
-  ON "catalog_sales"."cs_warehouse_sk" = "warehouse"."w_warehouse_sk"
-JOIN "item" AS "item"
-  ON "item"."i_current_price" <= 1.49
-  AND "item"."i_current_price" >= 0.99
-  AND "item"."i_item_sk" = "catalog_sales"."cs_item_sk"
 JOIN "date_dim" AS "date_dim"
   ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk"
   AND CAST("date_dim"."d_date" AS DATE) <= CAST('2002-07-01' AS DATE)
   AND CAST("date_dim"."d_date" AS DATE) >= CAST('2002-05-02' AS DATE)
+JOIN "item" AS "item"
+  ON "item"."i_current_price" <= 1.49
+  AND "item"."i_current_price" >= 0.99
+  AND "item"."i_item_sk" = "catalog_sales"."cs_item_sk"
+JOIN "warehouse" AS "warehouse"
+  ON "catalog_sales"."cs_warehouse_sk" = "warehouse"."w_warehouse_sk"
 GROUP BY
   "warehouse"."w_state",
   "item"."i_item_id"
@@ -4890,7 +4951,7 @@ WHERE  i_manufact_id BETWEEN 765 AND 765 + 40
 ORDER  BY i_product_name
 LIMIT 100;
 SELECT DISTINCT
-  "i1"."i_product_name" AS "_col_0"
+  "i1"."i_product_name" AS "i_product_name"
 FROM "item" AS "i1"
 WHERE
   "i1"."i_manufact_id" <= 805
@@ -5035,24 +5096,24 @@ ORDER  BY Sum(ss_ext_sales_price) DESC,
           item.i_category
 LIMIT 100;
 SELECT
-  "date_dim"."d_year" AS "d_year",
+  "dt"."d_year" AS "d_year",
   "item"."i_category_id" AS "i_category_id",
   "item"."i_category" AS "i_category",
   SUM("store_sales"."ss_ext_sales_price") AS "_col_3"
-FROM "date_dim" AS "date_dim"
+FROM "date_dim" AS "dt"
 JOIN "store_sales" AS "store_sales"
-  ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk"
+  ON "dt"."d_date_sk" = "store_sales"."ss_sold_date_sk"
 JOIN "item" AS "item"
   ON "item"."i_manager_id" = 1 AND "store_sales"."ss_item_sk" = "item"."i_item_sk"
 WHERE
-  "date_dim"."d_moy" = 12 AND "date_dim"."d_year" = 2000
+  "dt"."d_moy" = 12 AND "dt"."d_year" = 2000
 GROUP BY
-  "date_dim"."d_year",
+  "dt"."d_year",
   "item"."i_category_id",
   "item"."i_category"
 ORDER BY
   SUM("store_sales"."ss_ext_sales_price") DESC,
-  "date_dim"."d_year",
+  "dt"."d_year",
   "item"."i_category_id",
   "item"."i_category"
 LIMIT 100;
@@ -5278,11 +5339,6 @@ WITH "_u_0" AS (
     "v2"."item_sk" AS "item_sk",
     RANK() OVER (ORDER BY "v2"."rank_col" DESC) AS "rnk"
   FROM "v2" AS "v2"
-), "i1" AS (
-  SELECT
-    "item"."i_item_sk" AS "i_item_sk",
-    "item"."i_product_name" AS "i_product_name"
-  FROM "item" AS "item"
 )
 SELECT
   "v11"."rnk" AS "rnk",
@@ -5291,9 +5347,9 @@ SELECT
 FROM "v11" AS "v11"
 JOIN "v21" AS "v21"
   ON "v11"."rnk" = "v21"."rnk" AND "v21"."rnk" < 11
-JOIN "i1" AS "i1"
+JOIN "item" AS "i1"
   ON "i1"."i_item_sk" = "v11"."item_sk"
-JOIN "i1" AS "i2"
+JOIN "item" AS "i2"
   ON "i2"."i_item_sk" = "v21"."item_sk"
 WHERE
   "v11"."rnk" < 11
@@ -5347,8 +5403,6 @@ SELECT
 FROM "web_sales" AS "web_sales"
 JOIN "customer" AS "customer"
   ON "web_sales"."ws_bill_customer_sk" = "customer"."c_customer_sk"
-JOIN "customer_address" AS "customer_address"
-  ON "customer"."c_current_addr_sk" = "customer_address"."ca_address_sk"
 JOIN "date_dim" AS "date_dim"
   ON "date_dim"."d_qoy" = 1
   AND "date_dim"."d_year" = 2000
@@ -5357,6 +5411,8 @@ JOIN "item" AS "item"
   ON "web_sales"."ws_item_sk" = "item"."i_item_sk"
 LEFT JOIN "_u_0" AS "_u_0"
   ON "item"."i_item_id" = "_u_0"."i_item_id"
+JOIN "customer_address" AS "customer_address"
+  ON "customer"."c_current_addr_sk" = "customer_address"."ca_address_sk"
 WHERE
   NOT "_u_0"."i_item_id" IS NULL
   OR SUBSTR("customer_address"."ca_zip", 1, 5) IN ('85669', '86197', '88274', '83405', '86475', '85392', '85460', '80348', '81792')
@@ -5414,12 +5470,7 @@ ORDER  BY c_last_name,
           bought_city,
           ss_ticket_number
 LIMIT 100;
-WITH "customer_address_2" AS (
-  SELECT
-    "customer_address"."ca_address_sk" AS "ca_address_sk",
-    "customer_address"."ca_city" AS "ca_city"
-  FROM "customer_address" AS "customer_address"
-), "dn" AS (
+WITH "dn" AS (
   SELECT
     "store_sales"."ss_ticket_number" AS "ss_ticket_number",
     "store_sales"."ss_customer_sk" AS "ss_customer_sk",
@@ -5427,21 +5478,21 @@ WITH "customer_address_2" AS (
     SUM("store_sales"."ss_coupon_amt") AS "amt",
     SUM("store_sales"."ss_net_profit") AS "profit"
   FROM "store_sales" AS "store_sales"
+  JOIN "customer_address" AS "customer_address"
+    ON "store_sales"."ss_addr_sk" = "customer_address"."ca_address_sk"
   JOIN "date_dim" AS "date_dim"
     ON "date_dim"."d_dow" IN (6, 0)
     AND "date_dim"."d_year" IN (2000, 2001, 2002)
     AND "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
-  JOIN "store" AS "store"
-    ON "store"."s_city" IN ('Midway', 'Fairview', 'Fairview', 'Fairview', 'Fairview')
-    AND "store_sales"."ss_store_sk" = "store"."s_store_sk"
   JOIN "household_demographics" AS "household_demographics"
     ON (
       "household_demographics"."hd_dep_count" = 6
       OR "household_demographics"."hd_vehicle_count" = 0
     )
     AND "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk"
-  JOIN "customer_address_2" AS "customer_address"
-    ON "store_sales"."ss_addr_sk" = "customer_address"."ca_address_sk"
+  JOIN "store" AS "store"
+    ON "store"."s_city" IN ('Midway', 'Fairview', 'Fairview', 'Fairview', 'Fairview')
+    AND "store_sales"."ss_store_sk" = "store"."s_store_sk"
   GROUP BY
     "store_sales"."ss_ticket_number",
     "store_sales"."ss_customer_sk",
@@ -5457,11 +5508,11 @@ SELECT
   "dn"."amt" AS "amt",
   "dn"."profit" AS "profit"
 FROM "dn" AS "dn"
-JOIN "customer_address_2" AS "current_addr"
-  ON "current_addr"."ca_city" <> "dn"."bought_city"
 JOIN "customer" AS "customer"
-  ON "customer"."c_current_addr_sk" = "current_addr"."ca_address_sk"
-  AND "dn"."ss_customer_sk" = "customer"."c_customer_sk"
+  ON "dn"."ss_customer_sk" = "customer"."c_customer_sk"
+JOIN "customer_address" AS "current_addr"
+  ON "current_addr"."ca_city" <> "dn"."bought_city"
+  AND "customer"."c_current_addr_sk" = "current_addr"."ca_address_sk"
 ORDER BY
   "c_last_name",
   "c_first_name",
@@ -5655,30 +5706,6 @@ WHERE  s_store_sk = ss_store_sk
 SELECT
   SUM("store_sales"."ss_quantity") AS "_col_0"
 FROM "store_sales" AS "store_sales"
-JOIN "store" AS "store"
-  ON "store"."s_store_sk" = "store_sales"."ss_store_sk"
-JOIN "customer_demographics" AS "customer_demographics"
-  ON (
-    "customer_demographics"."cd_demo_sk" = "store_sales"."ss_cdemo_sk"
-    AND "customer_demographics"."cd_education_status" = '2 yr Degree'
-    AND "customer_demographics"."cd_marital_status" = 'D'
-    AND "store_sales"."ss_sales_price" <= 200.00
-    AND "store_sales"."ss_sales_price" >= 150.00
-  )
-  OR (
-    "customer_demographics"."cd_demo_sk" = "store_sales"."ss_cdemo_sk"
-    AND "customer_demographics"."cd_education_status" = 'Advanced Degree'
-    AND "customer_demographics"."cd_marital_status" = 'M'
-    AND "store_sales"."ss_sales_price" <= 100.00
-    AND "store_sales"."ss_sales_price" >= 50.00
-  )
-  OR (
-    "customer_demographics"."cd_demo_sk" = "store_sales"."ss_cdemo_sk"
-    AND "customer_demographics"."cd_education_status" = 'Secondary'
-    AND "customer_demographics"."cd_marital_status" = 'W'
-    AND "store_sales"."ss_sales_price" <= 150.00
-    AND "store_sales"."ss_sales_price" >= 100.00
-  )
 JOIN "customer_address" AS "customer_address"
   ON (
     "customer_address"."ca_country" = 'United States'
@@ -5701,9 +5728,33 @@ JOIN "customer_address" AS "customer_address"
     AND "store_sales"."ss_net_profit" <= 2000
     AND "store_sales"."ss_net_profit" >= 0
   )
+JOIN "customer_demographics" AS "customer_demographics"
+  ON (
+    "customer_demographics"."cd_demo_sk" = "store_sales"."ss_cdemo_sk"
+    AND "customer_demographics"."cd_education_status" = '2 yr Degree'
+    AND "customer_demographics"."cd_marital_status" = 'D'
+    AND "store_sales"."ss_sales_price" <= 200.00
+    AND "store_sales"."ss_sales_price" >= 150.00
+  )
+  OR (
+    "customer_demographics"."cd_demo_sk" = "store_sales"."ss_cdemo_sk"
+    AND "customer_demographics"."cd_education_status" = 'Advanced Degree'
+    AND "customer_demographics"."cd_marital_status" = 'M'
+    AND "store_sales"."ss_sales_price" <= 100.00
+    AND "store_sales"."ss_sales_price" >= 50.00
+  )
+  OR (
+    "customer_demographics"."cd_demo_sk" = "store_sales"."ss_cdemo_sk"
+    AND "customer_demographics"."cd_education_status" = 'Secondary'
+    AND "customer_demographics"."cd_marital_status" = 'W'
+    AND "store_sales"."ss_sales_price" <= 150.00
+    AND "store_sales"."ss_sales_price" >= 100.00
+  )
 JOIN "date_dim" AS "date_dim"
   ON "date_dim"."d_year" = 1999
-  AND "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk";
+  AND "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
+JOIN "store" AS "store"
+  ON "store"."s_store_sk" = "store_sales"."ss_store_sk";
 
 --------------------------------------
 -- TPC-DS 49
@@ -5850,22 +5901,22 @@ WITH "date_dim_2" AS (
     "date_dim"."d_moy" = 12 AND "date_dim"."d_year" = 1999
 ), "in_web" AS (
   SELECT
-    "web_sales"."ws_item_sk" AS "item",
-    CAST(SUM(COALESCE("web_returns"."wr_return_quantity", 0)) AS DECIMAL(15, 4)) / CAST(SUM(COALESCE("web_sales"."ws_quantity", 0)) AS DECIMAL(15, 4)) AS "return_ratio",
-    CAST(SUM(COALESCE("web_returns"."wr_return_amt", 0)) AS DECIMAL(15, 4)) / CAST(SUM(COALESCE("web_sales"."ws_net_paid", 0)) AS DECIMAL(15, 4)) AS "currency_ratio"
-  FROM "web_sales" AS "web_sales"
-  LEFT JOIN "web_returns" AS "web_returns"
-    ON "web_sales"."ws_item_sk" = "web_returns"."wr_item_sk"
-    AND "web_sales"."ws_order_number" = "web_returns"."wr_order_number"
+    "ws"."ws_item_sk" AS "item",
+    CAST(SUM(COALESCE("wr"."wr_return_quantity", 0)) AS DECIMAL(15, 4)) / CAST(SUM(COALESCE("ws"."ws_quantity", 0)) AS DECIMAL(15, 4)) AS "return_ratio",
+    CAST(SUM(COALESCE("wr"."wr_return_amt", 0)) AS DECIMAL(15, 4)) / CAST(SUM(COALESCE("ws"."ws_net_paid", 0)) AS DECIMAL(15, 4)) AS "currency_ratio"
+  FROM "web_sales" AS "ws"
   JOIN "date_dim_2" AS "date_dim"
-    ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk"
+    ON "ws"."ws_sold_date_sk" = "date_dim"."d_date_sk"
+  LEFT JOIN "web_returns" AS "wr"
+    ON "ws"."ws_item_sk" = "wr"."wr_item_sk"
+    AND "ws"."ws_order_number" = "wr"."wr_order_number"
   WHERE
-    "web_returns"."wr_return_amt" > 10000
-    AND "web_sales"."ws_net_paid" > 0
-    AND "web_sales"."ws_net_profit" > 1
-    AND "web_sales"."ws_quantity" > 0
+    "wr"."wr_return_amt" > 10000
+    AND "ws"."ws_net_paid" > 0
+    AND "ws"."ws_net_profit" > 1
+    AND "ws"."ws_quantity" > 0
   GROUP BY
-    "web_sales"."ws_item_sk"
+    "ws"."ws_item_sk"
 ), "web" AS (
   SELECT
     "in_web"."item" AS "item",
@@ -5875,22 +5926,22 @@ WITH "date_dim_2" AS (
   FROM "in_web" AS "in_web"
 ), "in_cat" AS (
   SELECT
-    "catalog_sales"."cs_item_sk" AS "item",
-    CAST(SUM(COALESCE("catalog_returns"."cr_return_quantity", 0)) AS DECIMAL(15, 4)) / CAST(SUM(COALESCE("catalog_sales"."cs_quantity", 0)) AS DECIMAL(15, 4)) AS "return_ratio",
-    CAST(SUM(COALESCE("catalog_returns"."cr_return_amount", 0)) AS DECIMAL(15, 4)) / CAST(SUM(COALESCE("catalog_sales"."cs_net_paid", 0)) AS DECIMAL(15, 4)) AS "currency_ratio"
-  FROM "catalog_sales" AS "catalog_sales"
-  LEFT JOIN "catalog_returns" AS "catalog_returns"
-    ON "catalog_sales"."cs_item_sk" = "catalog_returns"."cr_item_sk"
-    AND "catalog_sales"."cs_order_number" = "catalog_returns"."cr_order_number"
+    "cs"."cs_item_sk" AS "item",
+    CAST(SUM(COALESCE("cr"."cr_return_quantity", 0)) AS DECIMAL(15, 4)) / CAST(SUM(COALESCE("cs"."cs_quantity", 0)) AS DECIMAL(15, 4)) AS "return_ratio",
+    CAST(SUM(COALESCE("cr"."cr_return_amount", 0)) AS DECIMAL(15, 4)) / CAST(SUM(COALESCE("cs"."cs_net_paid", 0)) AS DECIMAL(15, 4)) AS "currency_ratio"
+  FROM "catalog_sales" AS "cs"
+  LEFT JOIN "catalog_returns" AS "cr"
+    ON "cs"."cs_item_sk" = "cr"."cr_item_sk"
+    AND "cs"."cs_order_number" = "cr"."cr_order_number"
   JOIN "date_dim_2" AS "date_dim"
-    ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk"
+    ON "cs"."cs_sold_date_sk" = "date_dim"."d_date_sk"
   WHERE
-    "catalog_returns"."cr_return_amount" > 10000
-    AND "catalog_sales"."cs_net_paid" > 0
-    AND "catalog_sales"."cs_net_profit" > 1
-    AND "catalog_sales"."cs_quantity" > 0
+    "cr"."cr_return_amount" > 10000
+    AND "cs"."cs_net_paid" > 0
+    AND "cs"."cs_net_profit" > 1
+    AND "cs"."cs_quantity" > 0
   GROUP BY
-    "catalog_sales"."cs_item_sk"
+    "cs"."cs_item_sk"
 ), "catalog" AS (
   SELECT
     "in_cat"."item" AS "item",
@@ -5900,22 +5951,22 @@ WITH "date_dim_2" AS (
   FROM "in_cat" AS "in_cat"
 ), "in_store" AS (
   SELECT
-    "store_sales"."ss_item_sk" AS "item",
-    CAST(SUM(COALESCE("store_returns"."sr_return_quantity", 0)) AS DECIMAL(15, 4)) / CAST(SUM(COALESCE("store_sales"."ss_quantity", 0)) AS DECIMAL(15, 4)) AS "return_ratio",
-    CAST(SUM(COALESCE("store_returns"."sr_return_amt", 0)) AS DECIMAL(15, 4)) / CAST(SUM(COALESCE("store_sales"."ss_net_paid", 0)) AS DECIMAL(15, 4)) AS "currency_ratio"
-  FROM "store_sales" AS "store_sales"
-  LEFT JOIN "store_returns" AS "store_returns"
-    ON "store_sales"."ss_item_sk" = "store_returns"."sr_item_sk"
-    AND "store_sales"."ss_ticket_number" = "store_returns"."sr_ticket_number"
+    "sts"."ss_item_sk" AS "item",
+    CAST(SUM(COALESCE("sr"."sr_return_quantity", 0)) AS DECIMAL(15, 4)) / CAST(SUM(COALESCE("sts"."ss_quantity", 0)) AS DECIMAL(15, 4)) AS "return_ratio",
+    CAST(SUM(COALESCE("sr"."sr_return_amt", 0)) AS DECIMAL(15, 4)) / CAST(SUM(COALESCE("sts"."ss_net_paid", 0)) AS DECIMAL(15, 4)) AS "currency_ratio"
+  FROM "store_sales" AS "sts"
   JOIN "date_dim_2" AS "date_dim"
-    ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
+    ON "sts"."ss_sold_date_sk" = "date_dim"."d_date_sk"
+  LEFT JOIN "store_returns" AS "sr"
+    ON "sts"."ss_item_sk" = "sr"."sr_item_sk"
+    AND "sts"."ss_ticket_number" = "sr"."sr_ticket_number"
   WHERE
-    "store_returns"."sr_return_amt" > 10000
-    AND "store_sales"."ss_net_paid" > 0
-    AND "store_sales"."ss_net_profit" > 1
-    AND "store_sales"."ss_quantity" > 0
+    "sr"."sr_return_amt" > 10000
+    AND "sts"."ss_net_paid" > 0
+    AND "sts"."ss_net_profit" > 1
+    AND "sts"."ss_quantity" > 0
   GROUP BY
-    "store_sales"."ss_item_sk"
+    "sts"."ss_item_sk"
 ), "store" AS (
   SELECT
     "in_store"."item" AS "item",
@@ -6098,18 +6149,18 @@ SELECT
     END
   ) AS ">120 days"
 FROM "store_sales" AS "store_sales"
+JOIN "date_dim" AS "d1"
+  ON "store_sales"."ss_sold_date_sk" = "d1"."d_date_sk"
+JOIN "store" AS "store"
+  ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
 JOIN "store_returns" AS "store_returns"
   ON "store_sales"."ss_customer_sk" = "store_returns"."sr_customer_sk"
   AND "store_sales"."ss_item_sk" = "store_returns"."sr_item_sk"
   AND "store_sales"."ss_ticket_number" = "store_returns"."sr_ticket_number"
-JOIN "store" AS "store"
-  ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
-JOIN "date_dim" AS "date_dim"
-  ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
-JOIN "date_dim" AS "date_dim_2"
-  ON "date_dim_2"."d_moy" = 9
-  AND "date_dim_2"."d_year" = 2002
-  AND "store_returns"."sr_returned_date_sk" = "date_dim_2"."d_date_sk"
+JOIN "date_dim" AS "d2"
+  ON "d2"."d_moy" = 9
+  AND "d2"."d_year" = 2002
+  AND "store_returns"."sr_returned_date_sk" = "d2"."d_date_sk"
 GROUP BY
   "store"."s_store_name",
   "store"."s_company_id",
@@ -6286,23 +6337,23 @@ ORDER  BY dt.d_year,
           brand_id
 LIMIT 100;
 SELECT
-  "date_dim"."d_year" AS "d_year",
+  "dt"."d_year" AS "d_year",
   "item"."i_brand_id" AS "brand_id",
   "item"."i_brand" AS "brand",
   SUM("store_sales"."ss_ext_sales_price") AS "ext_price"
-FROM "date_dim" AS "date_dim"
+FROM "date_dim" AS "dt"
 JOIN "store_sales" AS "store_sales"
-  ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk"
+  ON "dt"."d_date_sk" = "store_sales"."ss_sold_date_sk"
 JOIN "item" AS "item"
   ON "item"."i_manager_id" = 1 AND "store_sales"."ss_item_sk" = "item"."i_item_sk"
 WHERE
-  "date_dim"."d_moy" = 11 AND "date_dim"."d_year" = 1999
+  "dt"."d_moy" = 11 AND "dt"."d_year" = 1999
 GROUP BY
-  "date_dim"."d_year",
+  "dt"."d_year",
   "item"."i_brand",
   "item"."i_brand_id"
 ORDER BY
-  "date_dim"."d_year",
+  "dt"."d_year",
   "ext_price" DESC,
   "brand_id"
 LIMIT 100;
@@ -6502,16 +6553,16 @@ WITH "cs_or_ws_sales" AS (
     "customer"."c_customer_sk" AS "c_customer_sk",
     "customer"."c_current_addr_sk" AS "c_current_addr_sk"
   FROM "cs_or_ws_sales" AS "cs_or_ws_sales"
-  JOIN "item" AS "item"
-    ON "cs_or_ws_sales"."item_sk" = "item"."i_item_sk"
-    AND "item"."i_category" = 'Sports'
-    AND "item"."i_class" = 'fitness'
+  JOIN "customer" AS "customer"
+    ON "customer"."c_customer_sk" = "cs_or_ws_sales"."customer_sk"
   JOIN "date_dim" AS "date_dim"
     ON "cs_or_ws_sales"."sold_date_sk" = "date_dim"."d_date_sk"
     AND "date_dim"."d_moy" = 5
     AND "date_dim"."d_year" = 2000
-  JOIN "customer" AS "customer"
-    ON "customer"."c_customer_sk" = "cs_or_ws_sales"."customer_sk"
+  JOIN "item" AS "item"
+    ON "cs_or_ws_sales"."item_sk" = "item"."i_item_sk"
+    AND "item"."i_category" = 'Sports'
+    AND "item"."i_class" = 'fitness'
 ), "_u_0" AS (
   SELECT DISTINCT
     "date_dim"."d_month_seq" + 1 AS "_col_0"
@@ -6528,12 +6579,12 @@ WITH "cs_or_ws_sales" AS (
   SELECT
     SUM("store_sales"."ss_ext_sales_price") AS "revenue"
   FROM "my_customers"
-  CROSS JOIN "date_dim" AS "date_dim"
-  JOIN "store_sales" AS "store_sales"
-    ON "my_customers"."c_customer_sk" = "store_sales"."ss_customer_sk"
-    AND "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
   JOIN "customer_address" AS "customer_address"
     ON "my_customers"."c_current_addr_sk" = "customer_address"."ca_address_sk"
+  JOIN "store_sales" AS "store_sales"
+    ON "my_customers"."c_customer_sk" = "store_sales"."ss_customer_sk"
+  JOIN "date_dim" AS "date_dim"
+    ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
   JOIN "store" AS "store"
     ON "customer_address"."ca_county" = "store"."s_county"
     AND "customer_address"."ca_state" = "store"."s_state"
@@ -6670,7 +6721,14 @@ FROM   (SELECT *
 GROUP  BY i_item_id
 ORDER  BY total_sales
 LIMIT 100;
-WITH "date_dim_2" AS (
+WITH "customer_address_2" AS (
+  SELECT
+    "customer_address"."ca_address_sk" AS "ca_address_sk",
+    "customer_address"."ca_gmt_offset" AS "ca_gmt_offset"
+  FROM "customer_address" AS "customer_address"
+  WHERE
+    "customer_address"."ca_gmt_offset" = -6
+), "date_dim_2" AS (
   SELECT
     "date_dim"."d_date_sk" AS "d_date_sk",
     "date_dim"."d_year" AS "d_year",
@@ -6678,13 +6736,6 @@ WITH "date_dim_2" AS (
   FROM "date_dim" AS "date_dim"
   WHERE
     "date_dim"."d_moy" = 3 AND "date_dim"."d_year" = 1998
-), "customer_address_2" AS (
-  SELECT
-    "customer_address"."ca_address_sk" AS "ca_address_sk",
-    "customer_address"."ca_gmt_offset" AS "ca_gmt_offset"
-  FROM "customer_address" AS "customer_address"
-  WHERE
-    "customer_address"."ca_gmt_offset" = -6
 ), "item_2" AS (
   SELECT
     "item"."i_item_sk" AS "i_item_sk",
@@ -6703,10 +6754,10 @@ WITH "date_dim_2" AS (
     "item"."i_item_id" AS "i_item_id",
     SUM("store_sales"."ss_ext_sales_price") AS "total_sales"
   FROM "store_sales" AS "store_sales"
-  JOIN "date_dim_2" AS "date_dim"
-    ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
   JOIN "customer_address_2" AS "customer_address"
     ON "store_sales"."ss_addr_sk" = "customer_address"."ca_address_sk"
+  JOIN "date_dim_2" AS "date_dim"
+    ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
   JOIN "item_2" AS "item"
     ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
   LEFT JOIN "_u_0" AS "_u_0"
@@ -6720,10 +6771,10 @@ WITH "date_dim_2" AS (
     "item"."i_item_id" AS "i_item_id",
     SUM("catalog_sales"."cs_ext_sales_price") AS "total_sales"
   FROM "catalog_sales" AS "catalog_sales"
-  JOIN "date_dim_2" AS "date_dim"
-    ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk"
   JOIN "customer_address_2" AS "customer_address"
     ON "catalog_sales"."cs_bill_addr_sk" = "customer_address"."ca_address_sk"
+  JOIN "date_dim_2" AS "date_dim"
+    ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk"
   JOIN "item_2" AS "item"
     ON "catalog_sales"."cs_item_sk" = "item"."i_item_sk"
   LEFT JOIN "_u_0" AS "_u_1"
@@ -6737,10 +6788,10 @@ WITH "date_dim_2" AS (
     "item"."i_item_id" AS "i_item_id",
     SUM("web_sales"."ws_ext_sales_price") AS "total_sales"
   FROM "web_sales" AS "web_sales"
-  JOIN "date_dim_2" AS "date_dim"
-    ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk"
   JOIN "customer_address_2" AS "customer_address"
     ON "web_sales"."ws_bill_addr_sk" = "customer_address"."ca_address_sk"
+  JOIN "date_dim_2" AS "date_dim"
+    ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk"
   JOIN "item_2" AS "item"
     ON "web_sales"."ws_item_sk" = "item"."i_item_sk"
   LEFT JOIN "_u_0" AS "_u_2"
@@ -6860,6 +6911,8 @@ WITH "v1" AS (
   FROM "item" AS "item"
   JOIN "catalog_sales" AS "catalog_sales"
     ON "catalog_sales"."cs_item_sk" = "item"."i_item_sk"
+  JOIN "call_center" AS "call_center"
+    ON "call_center"."cc_call_center_sk" = "catalog_sales"."cs_call_center_sk"
   JOIN "date_dim" AS "date_dim"
     ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk"
     AND (
@@ -6874,8 +6927,6 @@ WITH "v1" AS (
     AND (
       "date_dim"."d_year" = 1999 OR "date_dim"."d_year" = 2000 OR "date_dim"."d_year" = 2001
     )
-  JOIN "call_center" AS "call_center"
-    ON "call_center"."cc_call_center_sk" = "catalog_sales"."cs_call_center_sk"
   GROUP BY
     "item"."i_category",
     "item"."i_brand",
@@ -6915,6 +6966,205 @@ ORDER BY
 LIMIT 100;
 
 --------------------------------------
+-- TPC-DS 58
+--------------------------------------
+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 = '2002-02-25'
+                                                   ))
+                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 = '2002-02-25'
+                                                   ))
+                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 = '2002-02-25'
+                                                   ))
+                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;
+WITH "date_dim_2" AS (
+  SELECT
+    "date_dim"."d_date_sk" AS "d_date_sk",
+    "date_dim"."d_date" AS "d_date"
+  FROM "date_dim" AS "date_dim"
+), "item_2" AS (
+  SELECT
+    "item"."i_item_sk" AS "i_item_sk",
+    "item"."i_item_id" AS "i_item_id"
+  FROM "item" AS "item"
+), "_u_0" AS (
+  SELECT
+    "date_dim"."d_week_seq" AS "d_week_seq"
+  FROM "date_dim" AS "date_dim"
+  WHERE
+    "date_dim"."d_date" = '2002-02-25'
+), "_u_1" AS (
+  SELECT
+    "date_dim"."d_date" AS "d_date"
+  FROM "date_dim" AS "date_dim"
+  JOIN "_u_0" AS "_u_0"
+    ON "date_dim"."d_week_seq" = "_u_0"."d_week_seq"
+  GROUP BY
+    "date_dim"."d_date"
+), "ss_items" AS (
+  SELECT
+    "item"."i_item_id" AS "item_id",
+    SUM("store_sales"."ss_ext_sales_price") AS "ss_item_rev"
+  FROM "store_sales" AS "store_sales"
+  JOIN "date_dim_2" AS "date_dim"
+    ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
+  JOIN "item_2" AS "item"
+    ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
+  LEFT JOIN "_u_1" AS "_u_1"
+    ON "date_dim"."d_date" = "_u_1"."d_date"
+  WHERE
+    NOT "_u_1"."d_date" IS NULL
+  GROUP BY
+    "item"."i_item_id"
+), "_u_3" AS (
+  SELECT
+    "date_dim"."d_date" AS "d_date"
+  FROM "date_dim" AS "date_dim"
+  JOIN "_u_0" AS "_u_2"
+    ON "date_dim"."d_week_seq" = "_u_2"."d_week_seq"
+  GROUP BY
+    "date_dim"."d_date"
+), "cs_items" AS (
+  SELECT
+    "item"."i_item_id" AS "item_id",
+    SUM("catalog_sales"."cs_ext_sales_price") AS "cs_item_rev"
+  FROM "catalog_sales" AS "catalog_sales"
+  JOIN "date_dim_2" AS "date_dim"
+    ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk"
+  JOIN "item_2" AS "item"
+    ON "catalog_sales"."cs_item_sk" = "item"."i_item_sk"
+  LEFT JOIN "_u_3" AS "_u_3"
+    ON "date_dim"."d_date" = "_u_3"."d_date"
+  WHERE
+    NOT "_u_3"."d_date" IS NULL
+  GROUP BY
+    "item"."i_item_id"
+), "_u_5" AS (
+  SELECT
+    "date_dim"."d_date" AS "d_date"
+  FROM "date_dim" AS "date_dim"
+  JOIN "_u_0" AS "_u_4"
+    ON "date_dim"."d_week_seq" = "_u_4"."d_week_seq"
+  GROUP BY
+    "date_dim"."d_date"
+), "ws_items" AS (
+  SELECT
+    "item"."i_item_id" AS "item_id",
+    SUM("web_sales"."ws_ext_sales_price") AS "ws_item_rev"
+  FROM "web_sales" AS "web_sales"
+  JOIN "date_dim_2" AS "date_dim"
+    ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk"
+  JOIN "item_2" AS "item"
+    ON "web_sales"."ws_item_sk" = "item"."i_item_sk"
+  LEFT JOIN "_u_5" AS "_u_5"
+    ON "date_dim"."d_date" = "_u_5"."d_date"
+  WHERE
+    NOT "_u_5"."d_date" IS NULL
+  GROUP BY
+    "item"."i_item_id"
+)
+SELECT
+  "ss_items"."item_id" AS "item_id",
+  "ss_items"."ss_item_rev" AS "ss_item_rev",
+  "ss_items"."ss_item_rev" / (
+    "ss_items"."ss_item_rev" + "cs_items"."cs_item_rev" + "ws_items"."ws_item_rev"
+  ) / 3 * 100 AS "ss_dev",
+  "cs_items"."cs_item_rev" AS "cs_item_rev",
+  "cs_items"."cs_item_rev" / (
+    "ss_items"."ss_item_rev" + "cs_items"."cs_item_rev" + "ws_items"."ws_item_rev"
+  ) / 3 * 100 AS "cs_dev",
+  "ws_items"."ws_item_rev" AS "ws_item_rev",
+  "ws_items"."ws_item_rev" / (
+    "ss_items"."ss_item_rev" + "cs_items"."cs_item_rev" + "ws_items"."ws_item_rev"
+  ) / 3 * 100 AS "ws_dev",
+  (
+    "ss_items"."ss_item_rev" + "cs_items"."cs_item_rev" + "ws_items"."ws_item_rev"
+  ) / 3 AS "average"
+FROM "ss_items"
+JOIN "ws_items"
+  ON "ss_items"."item_id" = "ws_items"."item_id"
+  AND "ss_items"."ss_item_rev" <= 1.1 * "ws_items"."ws_item_rev"
+  AND "ss_items"."ss_item_rev" >= 0.9 * "ws_items"."ws_item_rev"
+  AND "ws_items"."ws_item_rev" <= 1.1 * "ss_items"."ss_item_rev"
+  AND "ws_items"."ws_item_rev" >= 0.9 * "ss_items"."ss_item_rev"
+JOIN "cs_items"
+  ON "cs_items"."cs_item_rev" <= 1.1 * "ss_items"."ss_item_rev"
+  AND "cs_items"."cs_item_rev" <= 1.1 * "ws_items"."ws_item_rev"
+  AND "cs_items"."cs_item_rev" >= 0.9 * "ss_items"."ss_item_rev"
+  AND "cs_items"."cs_item_rev" >= 0.9 * "ws_items"."ws_item_rev"
+  AND "ss_items"."item_id" = "cs_items"."item_id"
+  AND "ss_items"."ss_item_rev" <= 1.1 * "cs_items"."cs_item_rev"
+  AND "ss_items"."ss_item_rev" >= 0.9 * "cs_items"."cs_item_rev"
+  AND "ws_items"."ws_item_rev" <= 1.1 * "cs_items"."cs_item_rev"
+  AND "ws_items"."ws_item_rev" >= 0.9 * "cs_items"."cs_item_rev"
+ORDER BY
+  "item_id",
+  "ss_item_rev"
+LIMIT 100;
+
+--------------------------------------
 -- TPC-DS 59
 --------------------------------------
 WITH wss
@@ -7072,12 +7322,12 @@ WITH "wss" AS (
     "wss"."fri_sales" AS "fri_sales2",
     "wss"."sat_sales" AS "sat_sales2"
   FROM "wss"
+  JOIN "date_dim" AS "d"
+    ON "d"."d_month_seq" <= 1219
+    AND "d"."d_month_seq" >= 1208
+    AND "d"."d_week_seq" = "wss"."d_week_seq"
   JOIN "store" AS "store"
     ON "wss"."ss_store_sk" = "store"."s_store_sk"
-  JOIN "date_dim" AS "date_dim"
-    ON "date_dim"."d_month_seq" <= 1219
-    AND "date_dim"."d_month_seq" >= 1208
-    AND "date_dim"."d_week_seq" = "wss"."d_week_seq"
 )
 SELECT
   "store"."s_store_name" AS "s_store_name1",
@@ -7091,12 +7341,12 @@ SELECT
   "wss"."fri_sales" / "x"."fri_sales2" AS "_col_8",
   "wss"."sat_sales" / "x"."sat_sales2" AS "_col_9"
 FROM "wss"
+JOIN "date_dim" AS "d"
+  ON "d"."d_month_seq" <= 1207
+  AND "d"."d_month_seq" >= 1196
+  AND "d"."d_week_seq" = "wss"."d_week_seq"
 JOIN "store" AS "store"
   ON "wss"."ss_store_sk" = "store"."s_store_sk"
-JOIN "date_dim" AS "date_dim"
-  ON "date_dim"."d_month_seq" <= 1207
-  AND "date_dim"."d_month_seq" >= 1196
-  AND "date_dim"."d_week_seq" = "wss"."d_week_seq"
 JOIN "x" AS "x"
   ON "store"."s_store_id" = "x"."s_store_id2"
   AND "wss"."d_week_seq" = "x"."d_week_seq2" - 52
@@ -7174,7 +7424,14 @@ GROUP  BY i_item_id
 ORDER  BY i_item_id,
           total_sales
 LIMIT 100;
-WITH "date_dim_2" AS (
+WITH "customer_address_2" AS (
+  SELECT
+    "customer_address"."ca_address_sk" AS "ca_address_sk",
+    "customer_address"."ca_gmt_offset" AS "ca_gmt_offset"
+  FROM "customer_address" AS "customer_address"
+  WHERE
+    "customer_address"."ca_gmt_offset" = -6
+), "date_dim_2" AS (
   SELECT
     "date_dim"."d_date_sk" AS "d_date_sk",
     "date_dim"."d_year" AS "d_year",
@@ -7182,13 +7439,6 @@ WITH "date_dim_2" AS (
   FROM "date_dim" AS "date_dim"
   WHERE
     "date_dim"."d_moy" = 8 AND "date_dim"."d_year" = 1999
-), "customer_address_2" AS (
-  SELECT
-    "customer_address"."ca_address_sk" AS "ca_address_sk",
-    "customer_address"."ca_gmt_offset" AS "ca_gmt_offset"
-  FROM "customer_address" AS "customer_address"
-  WHERE
-    "customer_address"."ca_gmt_offset" = -6
 ), "item_2" AS (
   SELECT
     "item"."i_item_sk" AS "i_item_sk",
@@ -7207,10 +7457,10 @@ WITH "date_dim_2" AS (
     "item"."i_item_id" AS "i_item_id",
     SUM("store_sales"."ss_ext_sales_price") AS "total_sales"
   FROM "store_sales" AS "store_sales"
-  JOIN "date_dim_2" AS "date_dim"
-    ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
   JOIN "customer_address_2" AS "customer_address"
     ON "store_sales"."ss_addr_sk" = "customer_address"."ca_address_sk"
+  JOIN "date_dim_2" AS "date_dim"
+    ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
   JOIN "item_2" AS "item"
     ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
   LEFT JOIN "_u_0" AS "_u_0"
@@ -7224,10 +7474,10 @@ WITH "date_dim_2" AS (
     "item"."i_item_id" AS "i_item_id",
     SUM("catalog_sales"."cs_ext_sales_price") AS "total_sales"
   FROM "catalog_sales" AS "catalog_sales"
-  JOIN "date_dim_2" AS "date_dim"
-    ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk"
   JOIN "customer_address_2" AS "customer_address"
     ON "catalog_sales"."cs_bill_addr_sk" = "customer_address"."ca_address_sk"
+  JOIN "date_dim_2" AS "date_dim"
+    ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk"
   JOIN "item_2" AS "item"
     ON "catalog_sales"."cs_item_sk" = "item"."i_item_sk"
   LEFT JOIN "_u_0" AS "_u_1"
@@ -7241,10 +7491,10 @@ WITH "date_dim_2" AS (
     "item"."i_item_id" AS "i_item_id",
     SUM("web_sales"."ws_ext_sales_price") AS "total_sales"
   FROM "web_sales" AS "web_sales"
-  JOIN "date_dim_2" AS "date_dim"
-    ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk"
   JOIN "customer_address_2" AS "customer_address"
     ON "web_sales"."ws_bill_addr_sk" = "customer_address"."ca_address_sk"
+  JOIN "date_dim_2" AS "date_dim"
+    ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk"
   JOIN "item_2" AS "item"
     ON "web_sales"."ws_item_sk" = "item"."i_item_sk"
   LEFT JOIN "_u_0" AS "_u_2"
@@ -7334,13 +7584,11 @@ FROM   (SELECT Sum(ss_ext_sales_price) promotions
 ORDER  BY promotions,
           total
 LIMIT 100;
-WITH "store_2" AS (
+WITH "customer_2" AS (
   SELECT
-    "store"."s_store_sk" AS "s_store_sk",
-    "store"."s_gmt_offset" AS "s_gmt_offset"
-  FROM "store" AS "store"
-  WHERE
-    "store"."s_gmt_offset" = -7
+    "customer"."c_customer_sk" AS "c_customer_sk",
+    "customer"."c_current_addr_sk" AS "c_current_addr_sk"
+  FROM "customer" AS "customer"
 ), "date_dim_2" AS (
   SELECT
     "date_dim"."d_date_sk" AS "d_date_sk",
@@ -7349,11 +7597,20 @@ WITH "store_2" AS (
   FROM "date_dim" AS "date_dim"
   WHERE
     "date_dim"."d_moy" = 12 AND "date_dim"."d_year" = 2001
-), "customer_2" AS (
+), "item_2" AS (
   SELECT
-    "customer"."c_customer_sk" AS "c_customer_sk",
-    "customer"."c_current_addr_sk" AS "c_current_addr_sk"
-  FROM "customer" AS "customer"
+    "item"."i_item_sk" AS "i_item_sk",
+    "item"."i_category" AS "i_category"
+  FROM "item" AS "item"
+  WHERE
+    "item"."i_category" = 'Books'
+), "store_2" AS (
+  SELECT
+    "store"."s_store_sk" AS "s_store_sk",
+    "store"."s_gmt_offset" AS "s_gmt_offset"
+  FROM "store" AS "store"
+  WHERE
+    "store"."s_gmt_offset" = -7
 ), "customer_address_2" AS (
   SELECT
     "customer_address"."ca_address_sk" AS "ca_address_sk",
@@ -7361,19 +7618,16 @@ WITH "store_2" AS (
   FROM "customer_address" AS "customer_address"
   WHERE
     "customer_address"."ca_gmt_offset" = -7
-), "item_2" AS (
-  SELECT
-    "item"."i_item_sk" AS "i_item_sk",
-    "item"."i_category" AS "i_category"
-  FROM "item" AS "item"
-  WHERE
-    "item"."i_category" = 'Books'
 ), "promotional_sales" AS (
   SELECT
     SUM("store_sales"."ss_ext_sales_price") AS "promotions"
   FROM "store_sales" AS "store_sales"
-  JOIN "store_2" AS "store"
-    ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
+  JOIN "customer_2" AS "customer"
+    ON "store_sales"."ss_customer_sk" = "customer"."c_customer_sk"
+  JOIN "date_dim_2" AS "date_dim"
+    ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
+  JOIN "item_2" AS "item"
+    ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
   JOIN "promotion" AS "promotion"
     ON (
       "promotion"."p_channel_dmail" = 'Y'
@@ -7381,28 +7635,24 @@ WITH "store_2" AS (
       OR "promotion"."p_channel_tv" = 'Y'
     )
     AND "store_sales"."ss_promo_sk" = "promotion"."p_promo_sk"
-  JOIN "date_dim_2" AS "date_dim"
-    ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
-  JOIN "customer_2" AS "customer"
-    ON "store_sales"."ss_customer_sk" = "customer"."c_customer_sk"
+  JOIN "store_2" AS "store"
+    ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
   JOIN "customer_address_2" AS "customer_address"
     ON "customer_address"."ca_address_sk" = "customer"."c_current_addr_sk"
-  JOIN "item_2" AS "item"
-    ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
 ), "all_sales" AS (
   SELECT
     SUM("store_sales"."ss_ext_sales_price") AS "total"
   FROM "store_sales" AS "store_sales"
-  JOIN "store_2" AS "store"
-    ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
-  JOIN "date_dim_2" AS "date_dim"
-    ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
   JOIN "customer_2" AS "customer"
     ON "store_sales"."ss_customer_sk" = "customer"."c_customer_sk"
-  JOIN "customer_address_2" AS "customer_address"
-    ON "customer_address"."ca_address_sk" = "customer"."c_current_addr_sk"
+  JOIN "date_dim_2" AS "date_dim"
+    ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
   JOIN "item_2" AS "item"
     ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
+  JOIN "store_2" AS "store"
+    ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
+  JOIN "customer_address_2" AS "customer_address"
+    ON "customer_address"."ca_address_sk" = "customer"."c_current_addr_sk"
 )
 SELECT
   "promotional_sales"."promotions" AS "promotions",
@@ -7505,16 +7755,16 @@ SELECT
     END
   ) AS ">120 days"
 FROM "web_sales" AS "web_sales"
-JOIN "warehouse" AS "warehouse"
-  ON "web_sales"."ws_warehouse_sk" = "warehouse"."w_warehouse_sk"
-JOIN "ship_mode" AS "ship_mode"
-  ON "web_sales"."ws_ship_mode_sk" = "ship_mode"."sm_ship_mode_sk"
-JOIN "web_site" AS "web_site"
-  ON "web_sales"."ws_web_site_sk" = "web_site"."web_site_sk"
 JOIN "date_dim" AS "date_dim"
   ON "date_dim"."d_month_seq" <= 1233
   AND "date_dim"."d_month_seq" >= 1222
   AND "web_sales"."ws_ship_date_sk" = "date_dim"."d_date_sk"
+JOIN "ship_mode" AS "ship_mode"
+  ON "web_sales"."ws_ship_mode_sk" = "ship_mode"."sm_ship_mode_sk"
+JOIN "warehouse" AS "warehouse"
+  ON "web_sales"."ws_warehouse_sk" = "warehouse"."w_warehouse_sk"
+JOIN "web_site" AS "web_site"
+  ON "web_sales"."ws_web_site_sk" = "web_site"."web_site_sk"
 GROUP BY
   SUBSTR("warehouse"."w_warehouse_name", 1, 20),
   "ship_mode"."sm_type",
@@ -7780,33 +8030,6 @@ WITH "cs_ui" AS (
     SUM("catalog_sales"."cs_ext_list_price") > 2 * SUM(
       "catalog_returns"."cr_refunded_cash" + "catalog_returns"."cr_reversed_charge" + "catalog_returns"."cr_store_credit"
     )
-), "d1" AS (
-  SELECT
-    "date_dim"."d_date_sk" AS "d_date_sk",
-    "date_dim"."d_year" AS "d_year"
-  FROM "date_dim" AS "date_dim"
-), "ib2" AS (
-  SELECT
-    "income_band"."ib_income_band_sk" AS "ib_income_band_sk"
-  FROM "income_band" AS "income_band"
-), "hd2" AS (
-  SELECT
-    "household_demographics"."hd_demo_sk" AS "hd_demo_sk",
-    "household_demographics"."hd_income_band_sk" AS "hd_income_band_sk"
-  FROM "household_demographics" AS "household_demographics"
-), "cd1" AS (
-  SELECT
-    "customer_demographics"."cd_demo_sk" AS "cd_demo_sk",
-    "customer_demographics"."cd_marital_status" AS "cd_marital_status"
-  FROM "customer_demographics" AS "customer_demographics"
-), "ad1" AS (
-  SELECT
-    "customer_address"."ca_address_sk" AS "ca_address_sk",
-    "customer_address"."ca_street_number" AS "ca_street_number",
-    "customer_address"."ca_street_name" AS "ca_street_name",
-    "customer_address"."ca_city" AS "ca_city",
-    "customer_address"."ca_zip" AS "ca_zip"
-  FROM "customer_address" AS "customer_address"
 ), "cross_sales" AS (
   SELECT
     "item"."i_product_name" AS "product_name",
@@ -7827,45 +8050,45 @@ WITH "cs_ui" AS (
     SUM("store_sales"."ss_list_price") AS "s2",
     SUM("store_sales"."ss_coupon_amt") AS "s3"
   FROM "store_sales" AS "store_sales"
-  JOIN "store_returns" AS "store_returns"
-    ON "store_sales"."ss_item_sk" = "store_returns"."sr_item_sk"
-    AND "store_sales"."ss_ticket_number" = "store_returns"."sr_ticket_number"
+  CROSS JOIN "income_band" AS "ib2"
+  JOIN "customer_address" AS "ad1"
+    ON "store_sales"."ss_addr_sk" = "ad1"."ca_address_sk"
   JOIN "cs_ui"
     ON "store_sales"."ss_item_sk" = "cs_ui"."cs_item_sk"
-  JOIN "d1" AS "d1"
+  JOIN "date_dim" AS "d1"
     ON "store_sales"."ss_sold_date_sk" = "d1"."d_date_sk"
-  CROSS JOIN "ib2" AS "ib2"
-  JOIN "hd2" AS "hd2"
+  JOIN "household_demographics" AS "hd1"
+    ON "store_sales"."ss_hdemo_sk" = "hd1"."hd_demo_sk"
+  JOIN "household_demographics" AS "hd2"
     ON "hd2"."hd_income_band_sk" = "ib2"."ib_income_band_sk"
+  JOIN "item" AS "item"
+    ON "item"."i_color" IN ('cyan', 'peach', 'blush', 'frosted', 'powder', 'orange')
+    AND "item"."i_current_price" <= 68
+    AND "item"."i_current_price" >= 59
+    AND "store_sales"."ss_item_sk" = "item"."i_item_sk"
+  JOIN "promotion" AS "promotion"
+    ON "store_sales"."ss_promo_sk" = "promotion"."p_promo_sk"
+  JOIN "store" AS "store"
+    ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
+  JOIN "store_returns" AS "store_returns"
+    ON "store_sales"."ss_item_sk" = "store_returns"."sr_item_sk"
+    AND "store_sales"."ss_ticket_number" = "store_returns"."sr_ticket_number"
   JOIN "customer" AS "customer"
     ON "customer"."c_current_hdemo_sk" = "hd2"."hd_demo_sk"
     AND "store_sales"."ss_customer_sk" = "customer"."c_customer_sk"
-  JOIN "d1" AS "d2"
+  JOIN "income_band" AS "ib1"
+    ON "hd1"."hd_income_band_sk" = "ib1"."ib_income_band_sk"
+  JOIN "customer_address" AS "ad2"
+    ON "customer"."c_current_addr_sk" = "ad2"."ca_address_sk"
+  JOIN "customer_demographics" AS "cd2"
+    ON "customer"."c_current_cdemo_sk" = "cd2"."cd_demo_sk"
+  JOIN "date_dim" AS "d2"
     ON "customer"."c_first_sales_date_sk" = "d2"."d_date_sk"
-  JOIN "d1" AS "d3"
+  JOIN "date_dim" AS "d3"
     ON "customer"."c_first_shipto_date_sk" = "d3"."d_date_sk"
-  JOIN "store" AS "store"
-    ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
-  JOIN "cd1" AS "cd1"
-    ON "store_sales"."ss_cdemo_sk" = "cd1"."cd_demo_sk"
-  JOIN "cd1" AS "cd2"
+  JOIN "customer_demographics" AS "cd1"
     ON "cd1"."cd_marital_status" <> "cd2"."cd_marital_status"
-    AND "customer"."c_current_cdemo_sk" = "cd2"."cd_demo_sk"
-  JOIN "promotion" AS "promotion"
-    ON "store_sales"."ss_promo_sk" = "promotion"."p_promo_sk"
-  JOIN "hd2" AS "hd1"
-    ON "store_sales"."ss_hdemo_sk" = "hd1"."hd_demo_sk"
-  JOIN "ad1" AS "ad1"
-    ON "store_sales"."ss_addr_sk" = "ad1"."ca_address_sk"
-  JOIN "ad1" AS "ad2"
-    ON "customer"."c_current_addr_sk" = "ad2"."ca_address_sk"
-  JOIN "ib2" AS "ib1"
-    ON "hd1"."hd_income_band_sk" = "ib1"."ib_income_band_sk"
-  JOIN "item" AS "item"
-    ON "item"."i_color" IN ('cyan', 'peach', 'blush', 'frosted', 'powder', 'orange')
-    AND "item"."i_current_price" <= 68
-    AND "item"."i_current_price" >= 59
-    AND "store_sales"."ss_item_sk" = "item"."i_item_sk"
+    AND "store_sales"."ss_cdemo_sk" = "cd1"."cd_demo_sk"
   GROUP BY
     "item"."i_product_name",
     "item"."i_item_sk",
@@ -7920,6 +8143,107 @@ ORDER BY
   "cs2"."cnt";
 
 --------------------------------------
+-- TPC-DS 65
+--------------------------------------
+SELECT s_store_name,
+               i_item_desc,
+               sc.revenue,
+               i_current_price,
+               i_wholesale_cost,
+               i_brand
+FROM   store,
+       item,
+       (SELECT ss_store_sk,
+               Avg(revenue) AS ave
+        FROM   (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 1199 AND 1199 + 11
+                GROUP  BY ss_store_sk,
+                          ss_item_sk) sa
+        GROUP  BY ss_store_sk) sb,
+       (SELECT ss_store_sk,
+               ss_item_sk,
+               Sum(ss_sales_price) AS revenue
+        FROM   store_sales,
+               date_dim
+        WHERE  ss_sold_date_sk = d_date_sk
+               AND d_month_seq BETWEEN 1199 AND 1199 + 11
+        GROUP  BY ss_store_sk,
+                  ss_item_sk) sc
+WHERE  sb.ss_store_sk = sc.ss_store_sk
+       AND sc.revenue <= 0.1 * sb.ave
+       AND s_store_sk = sc.ss_store_sk
+       AND i_item_sk = sc.ss_item_sk
+ORDER  BY s_store_name,
+          i_item_desc
+LIMIT 100;
+WITH "store_sales_2" AS (
+  SELECT
+    "store_sales"."ss_sold_date_sk" AS "ss_sold_date_sk",
+    "store_sales"."ss_item_sk" AS "ss_item_sk",
+    "store_sales"."ss_store_sk" AS "ss_store_sk",
+    "store_sales"."ss_sales_price" AS "ss_sales_price"
+  FROM "store_sales" AS "store_sales"
+), "date_dim_2" AS (
+  SELECT
+    "date_dim"."d_date_sk" AS "d_date_sk",
+    "date_dim"."d_month_seq" AS "d_month_seq"
+  FROM "date_dim" AS "date_dim"
+  WHERE
+    "date_dim"."d_month_seq" <= 1210 AND "date_dim"."d_month_seq" >= 1199
+), "sc" AS (
+  SELECT
+    "store_sales"."ss_store_sk" AS "ss_store_sk",
+    "store_sales"."ss_item_sk" AS "ss_item_sk",
+    SUM("store_sales"."ss_sales_price") AS "revenue"
+  FROM "store_sales_2" AS "store_sales"
+  JOIN "date_dim_2" AS "date_dim"
+    ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
+  GROUP BY
+    "store_sales"."ss_store_sk",
+    "store_sales"."ss_item_sk"
+), "sa" AS (
+  SELECT
+    "store_sales"."ss_store_sk" AS "ss_store_sk",
+    SUM("store_sales"."ss_sales_price") AS "revenue"
+  FROM "store_sales_2" AS "store_sales"
+  JOIN "date_dim_2" AS "date_dim"
+    ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
+  GROUP BY
+    "store_sales"."ss_store_sk",
+    "store_sales"."ss_item_sk"
+), "sb" AS (
+  SELECT
+    "sa"."ss_store_sk" AS "ss_store_sk",
+    AVG("sa"."revenue") AS "ave"
+  FROM "sa" AS "sa"
+  GROUP BY
+    "sa"."ss_store_sk"
+)
+SELECT
+  "store"."s_store_name" AS "s_store_name",
+  "item"."i_item_desc" AS "i_item_desc",
+  "sc"."revenue" AS "revenue",
+  "item"."i_current_price" AS "i_current_price",
+  "item"."i_wholesale_cost" AS "i_wholesale_cost",
+  "item"."i_brand" AS "i_brand"
+FROM "store" AS "store"
+JOIN "sc" AS "sc"
+  ON "store"."s_store_sk" = "sc"."ss_store_sk"
+JOIN "item" AS "item"
+  ON "item"."i_item_sk" = "sc"."ss_item_sk"
+JOIN "sb" AS "sb"
+  ON "sb"."ss_store_sk" = "sc"."ss_store_sk" AND "sc"."revenue" <= 0.1 * "sb"."ave"
+ORDER BY
+  "s_store_name",
+  "i_item_desc"
+LIMIT 100;
+
+--------------------------------------
 -- TPC-DS 66
 --------------------------------------
 SELECT w_warehouse_name,
@@ -8227,17 +8551,7 @@ GROUP  BY w_warehouse_name,
           year1
 ORDER  BY w_warehouse_name
 LIMIT 100;
-WITH "warehouse_2" AS (
-  SELECT
-    "warehouse"."w_warehouse_sk" AS "w_warehouse_sk",
-    "warehouse"."w_warehouse_name" AS "w_warehouse_name",
-    "warehouse"."w_warehouse_sq_ft" AS "w_warehouse_sq_ft",
-    "warehouse"."w_city" AS "w_city",
-    "warehouse"."w_county" AS "w_county",
-    "warehouse"."w_state" AS "w_state",
-    "warehouse"."w_country" AS "w_country"
-  FROM "warehouse" AS "warehouse"
-), "date_dim_2" AS (
+WITH "date_dim_2" AS (
   SELECT
     "date_dim"."d_date_sk" AS "d_date_sk",
     "date_dim"."d_year" AS "d_year",
@@ -8245,6 +8559,13 @@ WITH "warehouse_2" AS (
   FROM "date_dim" AS "date_dim"
   WHERE
     "date_dim"."d_year" = 1998
+), "ship_mode_2" AS (
+  SELECT
+    "ship_mode"."sm_ship_mode_sk" AS "sm_ship_mode_sk",
+    "ship_mode"."sm_carrier" AS "sm_carrier"
+  FROM "ship_mode" AS "ship_mode"
+  WHERE
+    "ship_mode"."sm_carrier" IN ('ZOUROS', 'ZHOU')
 ), "time_dim_2" AS (
   SELECT
     "time_dim"."t_time_sk" AS "t_time_sk",
@@ -8252,13 +8573,16 @@ WITH "warehouse_2" AS (
   FROM "time_dim" AS "time_dim"
   WHERE
     "time_dim"."t_time" <= 36049 AND "time_dim"."t_time" >= 7249
-), "ship_mode_2" AS (
+), "warehouse_2" AS (
   SELECT
-    "ship_mode"."sm_ship_mode_sk" AS "sm_ship_mode_sk",
-    "ship_mode"."sm_carrier" AS "sm_carrier"
-  FROM "ship_mode" AS "ship_mode"
-  WHERE
-    "ship_mode"."sm_carrier" IN ('ZOUROS', 'ZHOU')
+    "warehouse"."w_warehouse_sk" AS "w_warehouse_sk",
+    "warehouse"."w_warehouse_name" AS "w_warehouse_name",
+    "warehouse"."w_warehouse_sq_ft" AS "w_warehouse_sq_ft",
+    "warehouse"."w_city" AS "w_city",
+    "warehouse"."w_county" AS "w_county",
+    "warehouse"."w_state" AS "w_state",
+    "warehouse"."w_country" AS "w_country"
+  FROM "warehouse" AS "warehouse"
 ), "cte" AS (
   SELECT
     "warehouse"."w_warehouse_name" AS "w_warehouse_name",
@@ -8438,14 +8762,14 @@ WITH "warehouse_2" AS (
       END
     ) AS "dec_net"
   FROM "web_sales" AS "web_sales"
-  JOIN "warehouse_2" AS "warehouse"
-    ON "web_sales"."ws_warehouse_sk" = "warehouse"."w_warehouse_sk"
   JOIN "date_dim_2" AS "date_dim"
     ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk"
-  JOIN "time_dim_2" AS "time_dim"
-    ON "web_sales"."ws_sold_time_sk" = "time_dim"."t_time_sk"
   JOIN "ship_mode_2" AS "ship_mode"
     ON "web_sales"."ws_ship_mode_sk" = "ship_mode"."sm_ship_mode_sk"
+  JOIN "time_dim_2" AS "time_dim"
+    ON "web_sales"."ws_sold_time_sk" = "time_dim"."t_time_sk"
+  JOIN "warehouse_2" AS "warehouse"
+    ON "web_sales"."ws_warehouse_sk" = "warehouse"."w_warehouse_sk"
   GROUP BY
     "warehouse"."w_warehouse_name",
     "warehouse"."w_warehouse_sq_ft",
@@ -8633,14 +8957,14 @@ WITH "warehouse_2" AS (
       END
     ) AS "dec_net"
   FROM "catalog_sales" AS "catalog_sales"
-  JOIN "warehouse_2" AS "warehouse"
-    ON "catalog_sales"."cs_warehouse_sk" = "warehouse"."w_warehouse_sk"
   JOIN "date_dim_2" AS "date_dim"
     ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk"
-  JOIN "time_dim_2" AS "time_dim"
-    ON "catalog_sales"."cs_sold_time_sk" = "time_dim"."t_time_sk"
   JOIN "ship_mode_2" AS "ship_mode"
     ON "catalog_sales"."cs_ship_mode_sk" = "ship_mode"."sm_ship_mode_sk"
+  JOIN "time_dim_2" AS "time_dim"
+    ON "catalog_sales"."cs_sold_time_sk" = "time_dim"."t_time_sk"
+  JOIN "warehouse_2" AS "warehouse"
+    ON "catalog_sales"."cs_warehouse_sk" = "warehouse"."w_warehouse_sk"
   GROUP BY
     "warehouse"."w_warehouse_name",
     "warehouse"."w_warehouse_sq_ft",
@@ -8840,10 +9164,10 @@ WITH "dw1" AS (
     ON "date_dim"."d_month_seq" <= 1192
     AND "date_dim"."d_month_seq" >= 1181
     AND "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
-  JOIN "store" AS "store"
-    ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
   JOIN "item" AS "item"
     ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
+  JOIN "store" AS "store"
+    ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
   GROUP BY
   ROLLUP (
     "item"."i_category",
@@ -8939,12 +9263,7 @@ WHERE  ss_customer_sk = c_customer_sk
 ORDER  BY c_last_name,
           ss_ticket_number
 LIMIT 100;
-WITH "customer_address_2" AS (
-  SELECT
-    "customer_address"."ca_address_sk" AS "ca_address_sk",
-    "customer_address"."ca_city" AS "ca_city"
-  FROM "customer_address" AS "customer_address"
-), "dn" AS (
+WITH "dn" AS (
   SELECT
     "store_sales"."ss_ticket_number" AS "ss_ticket_number",
     "store_sales"."ss_customer_sk" AS "ss_customer_sk",
@@ -8953,22 +9272,22 @@ WITH "customer_address_2" AS (
     SUM("store_sales"."ss_ext_list_price") AS "list_price",
     SUM("store_sales"."ss_ext_tax") AS "extended_tax"
   FROM "store_sales" AS "store_sales"
+  JOIN "customer_address" AS "customer_address"
+    ON "store_sales"."ss_addr_sk" = "customer_address"."ca_address_sk"
   JOIN "date_dim" AS "date_dim"
     ON "date_dim"."d_dom" <= 2
     AND "date_dim"."d_dom" >= 1
     AND "date_dim"."d_year" IN (1998, 1999, 2000)
     AND "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
-  JOIN "store" AS "store"
-    ON "store"."s_city" IN ('Fairview', 'Midway')
-    AND "store_sales"."ss_store_sk" = "store"."s_store_sk"
   JOIN "household_demographics" AS "household_demographics"
     ON (
       "household_demographics"."hd_dep_count" = 8
       OR "household_demographics"."hd_vehicle_count" = 3
     )
     AND "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk"
-  JOIN "customer_address_2" AS "customer_address"
-    ON "store_sales"."ss_addr_sk" = "customer_address"."ca_address_sk"
+  JOIN "store" AS "store"
+    ON "store"."s_city" IN ('Fairview', 'Midway')
+    AND "store_sales"."ss_store_sk" = "store"."s_store_sk"
   GROUP BY
     "store_sales"."ss_ticket_number",
     "store_sales"."ss_customer_sk",
@@ -8985,11 +9304,11 @@ SELECT
   "dn"."extended_tax" AS "extended_tax",
   "dn"."list_price" AS "list_price"
 FROM "dn" AS "dn"
-JOIN "customer_address_2" AS "current_addr"
-  ON "current_addr"."ca_city" <> "dn"."bought_city"
 JOIN "customer" AS "customer"
-  ON "customer"."c_current_addr_sk" = "current_addr"."ca_address_sk"
-  AND "dn"."ss_customer_sk" = "customer"."c_customer_sk"
+  ON "dn"."ss_customer_sk" = "customer"."c_customer_sk"
+JOIN "customer_address" AS "current_addr"
+  ON "current_addr"."ca_city" <> "dn"."bought_city"
+  AND "customer"."c_current_addr_sk" = "current_addr"."ca_address_sk"
 ORDER BY
   "c_last_name",
   "ss_ticket_number"
@@ -9111,18 +9430,18 @@ SELECT
   COUNT(*) AS "cnt2",
   "customer_demographics"."cd_credit_rating" AS "cd_credit_rating",
   COUNT(*) AS "cnt3"
-FROM "customer" AS "customer"
-JOIN "customer_address" AS "customer_address"
-  ON "customer"."c_current_addr_sk" = "customer_address"."ca_address_sk"
-  AND "customer_address"."ca_state" IN ('KS', 'AZ', 'NE')
-JOIN "customer_demographics" AS "customer_demographics"
-  ON "customer_demographics"."cd_demo_sk" = "customer"."c_current_cdemo_sk"
+FROM "customer" AS "c"
 LEFT JOIN "_u_0" AS "_u_0"
-  ON "customer"."c_customer_sk" = "_u_0"."_u_1"
+  ON "c"."c_customer_sk" = "_u_0"."_u_1"
 LEFT JOIN "_u_2" AS "_u_2"
-  ON "customer"."c_customer_sk" = "_u_2"."_u_3"
+  ON "c"."c_customer_sk" = "_u_2"."_u_3"
 LEFT JOIN "_u_4" AS "_u_4"
-  ON "customer"."c_customer_sk" = "_u_4"."_u_5"
+  ON "c"."c_customer_sk" = "_u_4"."_u_5"
+JOIN "customer_address" AS "ca"
+  ON "c"."c_current_addr_sk" = "ca"."ca_address_sk"
+  AND "ca"."ca_state" IN ('KS', 'AZ', 'NE')
+JOIN "customer_demographics" AS "customer_demographics"
+  ON "customer_demographics"."cd_demo_sk" = "c"."c_current_cdemo_sk"
 WHERE
   "_u_2"."_u_3" IS NULL AND "_u_4"."_u_5" IS NULL AND NOT "_u_0"."_u_1" IS NULL
 GROUP BY
@@ -9187,22 +9506,17 @@ WITH "store_sales_2" AS (
     "store_sales"."ss_store_sk" AS "ss_store_sk",
     "store_sales"."ss_net_profit" AS "ss_net_profit"
   FROM "store_sales" AS "store_sales"
-), "d1" AS (
-  SELECT
-    "date_dim"."d_date_sk" AS "d_date_sk",
-    "date_dim"."d_month_seq" AS "d_month_seq"
-  FROM "date_dim" AS "date_dim"
-  WHERE
-    "date_dim"."d_month_seq" <= 1211 AND "date_dim"."d_month_seq" >= 1200
 ), "tmp1" AS (
   SELECT
     "store"."s_state" AS "s_state",
     RANK() OVER (PARTITION BY "store"."s_state" ORDER BY SUM("store_sales"."ss_net_profit") DESC) AS "ranking"
   FROM "store_sales_2" AS "store_sales"
+  JOIN "date_dim" AS "date_dim"
+    ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk"
+    AND "date_dim"."d_month_seq" <= 1211
+    AND "date_dim"."d_month_seq" >= 1200
   JOIN "store" AS "store"
     ON "store"."s_store_sk" = "store_sales"."ss_store_sk"
-  JOIN "d1" AS "date_dim"
-    ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk"
   GROUP BY
     "store"."s_state"
 ), "_u_0" AS (
@@ -9221,8 +9535,10 @@ SELECT
   GROUPING("store"."s_state") + GROUPING("store"."s_county") AS "lochierarchy",
   RANK() OVER (PARTITION BY GROUPING("store"."s_state") + GROUPING("store"."s_county"), CASE WHEN GROUPING("store"."s_county") = 0 THEN "store"."s_state" END ORDER BY SUM("store_sales"."ss_net_profit") DESC) AS "rank_within_parent"
 FROM "store_sales_2" AS "store_sales"
-JOIN "d1" AS "d1"
+JOIN "date_dim" AS "d1"
   ON "d1"."d_date_sk" = "store_sales"."ss_sold_date_sk"
+  AND "d1"."d_month_seq" <= 1211
+  AND "d1"."d_month_seq" >= 1200
 JOIN "store" AS "store"
   ON "store"."s_store_sk" = "store_sales"."ss_store_sk"
 LEFT JOIN "_u_0" AS "_u_0"
@@ -9408,42 +9724,42 @@ LIMIT 100;
 SELECT
   "item"."i_item_desc" AS "i_item_desc",
   "warehouse"."w_warehouse_name" AS "w_warehouse_name",
-  "date_dim_2"."d_week_seq" AS "d_week_seq",
+  "d1"."d_week_seq" AS "d_week_seq",
   SUM(CASE WHEN "promotion"."p_promo_sk" IS NULL THEN 1 ELSE 0 END) AS "no_promo",
   SUM(CASE WHEN NOT "promotion"."p_promo_sk" IS NULL THEN 1 ELSE 0 END) AS "promo",
   COUNT(*) AS "total_cnt"
 FROM "catalog_sales" AS "catalog_sales"
-JOIN "inventory" AS "inventory"
-  ON "catalog_sales"."cs_item_sk" = "inventory"."inv_item_sk"
-  AND "inventory"."inv_quantity_on_hand" < "catalog_sales"."cs_quantity"
-JOIN "warehouse" AS "warehouse"
-  ON "warehouse"."w_warehouse_sk" = "inventory"."inv_warehouse_sk"
-JOIN "item" AS "item"
-  ON "item"."i_item_sk" = "catalog_sales"."cs_item_sk"
+LEFT JOIN "catalog_returns" AS "catalog_returns"
+  ON "catalog_returns"."cr_item_sk" = "catalog_sales"."cs_item_sk"
+  AND "catalog_returns"."cr_order_number" = "catalog_sales"."cs_order_number"
 JOIN "customer_demographics" AS "customer_demographics"
   ON "catalog_sales"."cs_bill_cdemo_sk" = "customer_demographics"."cd_demo_sk"
   AND "customer_demographics"."cd_marital_status" = 'M'
+JOIN "date_dim" AS "d3"
+  ON "catalog_sales"."cs_ship_date_sk" = "d3"."d_date_sk"
 JOIN "household_demographics" AS "household_demographics"
   ON "catalog_sales"."cs_bill_hdemo_sk" = "household_demographics"."hd_demo_sk"
   AND "household_demographics"."hd_buy_potential" = '501-1000'
-JOIN "date_dim" AS "date_dim"
-  ON "inventory"."inv_date_sk" = "date_dim"."d_date_sk"
-JOIN "date_dim" AS "date_dim_2"
-  ON "catalog_sales"."cs_sold_date_sk" = "date_dim_2"."d_date_sk"
-  AND "date_dim_2"."d_week_seq" = "date_dim"."d_week_seq"
-  AND "date_dim_2"."d_year" = 2002
-JOIN "date_dim" AS "date_dim_3"
-  ON "catalog_sales"."cs_ship_date_sk" = "date_dim_3"."d_date_sk"
-  AND "date_dim_3"."d_date" > CONCAT("date_dim_2"."d_date", INTERVAL '5' day)
+JOIN "inventory" AS "inventory"
+  ON "catalog_sales"."cs_item_sk" = "inventory"."inv_item_sk"
+  AND "inventory"."inv_quantity_on_hand" < "catalog_sales"."cs_quantity"
+JOIN "item" AS "item"
+  ON "item"."i_item_sk" = "catalog_sales"."cs_item_sk"
 LEFT JOIN "promotion" AS "promotion"
   ON "catalog_sales"."cs_promo_sk" = "promotion"."p_promo_sk"
-LEFT JOIN "catalog_returns" AS "catalog_returns"
-  ON "catalog_returns"."cr_item_sk" = "catalog_sales"."cs_item_sk"
-  AND "catalog_returns"."cr_order_number" = "catalog_sales"."cs_order_number"
+JOIN "date_dim" AS "d2"
+  ON "inventory"."inv_date_sk" = "d2"."d_date_sk"
+JOIN "warehouse" AS "warehouse"
+  ON "warehouse"."w_warehouse_sk" = "inventory"."inv_warehouse_sk"
+JOIN "date_dim" AS "d1"
+  ON "catalog_sales"."cs_sold_date_sk" = "d1"."d_date_sk"
+  AND "d1"."d_week_seq" = "d2"."d_week_seq"
+  AND "d1"."d_year" = 2002
+  AND "d3"."d_date" > CONCAT("d1"."d_date", INTERVAL '5' day)
 GROUP BY
   "item"."i_item_desc",
   "warehouse"."w_warehouse_name",
-  "date_dim_2"."d_week_seq"
+  "d1"."d_week_seq"
 ORDER BY
   "total_cnt" DESC,
   "i_item_desc",
@@ -9503,9 +9819,6 @@ WITH "dj" AS (
     AND "date_dim"."d_dom" >= 1
     AND "date_dim"."d_year" IN (2000, 2001, 2002)
     AND "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
-  JOIN "store" AS "store"
-    ON "store"."s_county" IN ('Williamson County', 'Williamson County', 'Williamson County', 'Williamson County')
-    AND "store_sales"."ss_store_sk" = "store"."s_store_sk"
   JOIN "household_demographics" AS "household_demographics"
     ON (
       "household_demographics"."hd_buy_potential" = '0-500'
@@ -9518,6 +9831,9 @@ WITH "dj" AS (
       THEN "household_demographics"."hd_dep_count" / "household_demographics"."hd_vehicle_count"
       ELSE NULL
     END > 1
+  JOIN "store" AS "store"
+    ON "store"."s_county" IN ('Williamson County', 'Williamson County', 'Williamson County', 'Williamson County')
+    AND "store_sales"."ss_store_sk" = "store"."s_store_sk"
   GROUP BY
     "store_sales"."ss_ticket_number",
     "store_sales"."ss_customer_sk"
@@ -9683,19 +9999,19 @@ SELECT
   "t_s_secyear"."customer_first_name" AS "customer_first_name",
   "t_s_secyear"."customer_last_name" AS "customer_last_name"
 FROM "year_total" AS "t_s_firstyear"
-JOIN "year_total" AS "t_s_secyear"
-  ON "t_s_secyear"."customer_id" = "t_s_firstyear"."customer_id"
-  AND "t_s_secyear"."sale_type" = 's'
-  AND "t_s_secyear"."year1" = 2000
-JOIN "year_total" AS "t_w_secyear"
-  ON "t_s_firstyear"."customer_id" = "t_w_secyear"."customer_id"
-  AND "t_w_secyear"."sale_type" = 'w'
-  AND "t_w_secyear"."year1" = 2000
 JOIN "year_total" AS "t_w_firstyear"
   ON "t_s_firstyear"."customer_id" = "t_w_firstyear"."customer_id"
   AND "t_w_firstyear"."sale_type" = 'w'
   AND "t_w_firstyear"."year1" = 1999
   AND "t_w_firstyear"."year_total" > 0
+JOIN "year_total" AS "t_w_secyear"
+  ON "t_s_firstyear"."customer_id" = "t_w_secyear"."customer_id"
+  AND "t_w_secyear"."sale_type" = 'w'
+  AND "t_w_secyear"."year1" = 2000
+JOIN "year_total" AS "t_s_secyear"
+  ON "t_s_secyear"."customer_id" = "t_s_firstyear"."customer_id"
+  AND "t_s_secyear"."sale_type" = 's'
+  AND "t_s_secyear"."year1" = 2000
   AND CASE
     WHEN "t_w_firstyear"."year_total" > 0
     THEN "t_w_secyear"."year_total" / "t_w_firstyear"."year_total"
@@ -9810,7 +10126,12 @@ WHERE  curr_yr.i_brand_id = prev_yr.i_brand_id
            < 0.9
 ORDER  BY sales_cnt_diff
 LIMIT 100;
-WITH "item_2" AS (
+WITH "date_dim_2" AS (
+  SELECT
+    "date_dim"."d_date_sk" AS "d_date_sk",
+    "date_dim"."d_year" AS "d_year"
+  FROM "date_dim" AS "date_dim"
+), "item_2" AS (
   SELECT
     "item"."i_item_sk" AS "i_item_sk",
     "item"."i_brand_id" AS "i_brand_id",
@@ -9821,11 +10142,6 @@ WITH "item_2" AS (
   FROM "item" AS "item"
   WHERE
     "item"."i_category" = 'Men'
-), "date_dim_2" AS (
-  SELECT
-    "date_dim"."d_date_sk" AS "d_date_sk",
-    "date_dim"."d_year" AS "d_year"
-  FROM "date_dim" AS "date_dim"
 ), "cte_4" AS (
   SELECT
     "date_dim"."d_year" AS "d_year",
@@ -9836,10 +10152,10 @@ WITH "item_2" AS (
     "store_sales"."ss_quantity" - COALESCE("store_returns"."sr_return_quantity", 0) AS "sales_cnt",
     "store_sales"."ss_ext_sales_price" - COALESCE("store_returns"."sr_return_amt", 0.0) AS "sales_amt"
   FROM "store_sales" AS "store_sales"
-  JOIN "item_2" AS "item"
-    ON "item"."i_item_sk" = "store_sales"."ss_item_sk"
   JOIN "date_dim_2" AS "date_dim"
     ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk"
+  JOIN "item_2" AS "item"
+    ON "item"."i_item_sk" = "store_sales"."ss_item_sk"
   LEFT JOIN "store_returns" AS "store_returns"
     ON "store_sales"."ss_item_sk" = "store_returns"."sr_item_sk"
     AND "store_sales"."ss_ticket_number" = "store_returns"."sr_ticket_number"
@@ -9853,10 +10169,10 @@ WITH "item_2" AS (
     "web_sales"."ws_quantity" - COALESCE("web_returns"."wr_return_quantity", 0) AS "sales_cnt",
     "web_sales"."ws_ext_sales_price" - COALESCE("web_returns"."wr_return_amt", 0.0) AS "sales_amt"
   FROM "web_sales" AS "web_sales"
-  JOIN "item_2" AS "item"
-    ON "item"."i_item_sk" = "web_sales"."ws_item_sk"
   JOIN "date_dim_2" AS "date_dim"
     ON "date_dim"."d_date_sk" = "web_sales"."ws_sold_date_sk"
+  JOIN "item_2" AS "item"
+    ON "item"."i_item_sk" = "web_sales"."ws_item_sk"
   LEFT JOIN "web_returns" AS "web_returns"
     ON "web_sales"."ws_item_sk" = "web_returns"."wr_item_sk"
     AND "web_sales"."ws_order_number" = "web_returns"."wr_order_number"
@@ -9870,13 +10186,13 @@ WITH "item_2" AS (
     "catalog_sales"."cs_quantity" - COALESCE("catalog_returns"."cr_return_quantity", 0) AS "sales_cnt",
     "catalog_sales"."cs_ext_sales_price" - COALESCE("catalog_returns"."cr_return_amount", 0.0) AS "sales_amt"
   FROM "catalog_sales" AS "catalog_sales"
-  JOIN "item_2" AS "item"
-    ON "item"."i_item_sk" = "catalog_sales"."cs_item_sk"
-  JOIN "date_dim_2" AS "date_dim"
-    ON "date_dim"."d_date_sk" = "catalog_sales"."cs_sold_date_sk"
   LEFT JOIN "catalog_returns" AS "catalog_returns"
     ON "catalog_sales"."cs_item_sk" = "catalog_returns"."cr_item_sk"
     AND "catalog_sales"."cs_order_number" = "catalog_returns"."cr_order_number"
+  JOIN "date_dim_2" AS "date_dim"
+    ON "date_dim"."d_date_sk" = "catalog_sales"."cs_sold_date_sk"
+  JOIN "item_2" AS "item"
+    ON "item"."i_item_sk" = "catalog_sales"."cs_item_sk"
   UNION
   SELECT
     "cte_4"."d_year" AS "d_year",
@@ -9988,17 +10304,17 @@ ORDER  BY channel,
           d_qoy,
           i_category
 LIMIT 100;
-WITH "item_2" AS (
-  SELECT
-    "item"."i_item_sk" AS "i_item_sk",
-    "item"."i_category" AS "i_category"
-  FROM "item" AS "item"
-), "date_dim_2" AS (
+WITH "date_dim_2" AS (
   SELECT
     "date_dim"."d_date_sk" AS "d_date_sk",
     "date_dim"."d_year" AS "d_year",
     "date_dim"."d_qoy" AS "d_qoy"
   FROM "date_dim" AS "date_dim"
+), "item_2" AS (
+  SELECT
+    "item"."i_item_sk" AS "i_item_sk",
+    "item"."i_category" AS "i_category"
+  FROM "item" AS "item"
 ), "cte_4" AS (
   SELECT
     'web' AS "channel",
@@ -10008,10 +10324,10 @@ WITH "item_2" AS (
     "item"."i_category" AS "i_category",
     "web_sales"."ws_ext_sales_price" AS "ext_sales_price"
   FROM "web_sales" AS "web_sales"
-  JOIN "item_2" AS "item"
-    ON "web_sales"."ws_item_sk" = "item"."i_item_sk"
   JOIN "date_dim_2" AS "date_dim"
     ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk"
+  JOIN "item_2" AS "item"
+    ON "web_sales"."ws_item_sk" = "item"."i_item_sk"
   WHERE
     "web_sales"."ws_ship_hdemo_sk" IS NULL
   UNION ALL
@@ -10023,10 +10339,10 @@ WITH "item_2" AS (
     "item"."i_category" AS "i_category",
     "catalog_sales"."cs_ext_sales_price" AS "ext_sales_price"
   FROM "catalog_sales" AS "catalog_sales"
-  JOIN "item_2" AS "item"
-    ON "catalog_sales"."cs_item_sk" = "item"."i_item_sk"
   JOIN "date_dim_2" AS "date_dim"
     ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk"
+  JOIN "item_2" AS "item"
+    ON "catalog_sales"."cs_item_sk" = "item"."i_item_sk"
   WHERE
     "catalog_sales"."cs_warehouse_sk" IS NULL
 ), "foo" AS (
@@ -10038,10 +10354,10 @@ WITH "item_2" AS (
     "item"."i_category" AS "i_category",
     "store_sales"."ss_ext_sales_price" AS "ext_sales_price"
   FROM "store_sales" AS "store_sales"
-  JOIN "item_2" AS "item"
-    ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
   JOIN "date_dim_2" AS "date_dim"
     ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
+  JOIN "item_2" AS "item"
+    ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
   WHERE
     "store_sales"."ss_hdemo_sk" IS NULL
   UNION ALL
@@ -10426,11 +10742,11 @@ WITH "date_dim_2" AS (
     SUM("web_sales"."ws_wholesale_cost") AS "ws_wc",
     SUM("web_sales"."ws_sales_price") AS "ws_sp"
   FROM "web_sales" AS "web_sales"
+  JOIN "date_dim_2" AS "date_dim"
+    ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk"
   LEFT JOIN "web_returns" AS "web_returns"
     ON "web_returns"."wr_order_number" = "web_sales"."ws_order_number"
     AND "web_sales"."ws_item_sk" = "web_returns"."wr_item_sk"
-  JOIN "date_dim_2" AS "date_dim"
-    ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk"
   WHERE
     "web_returns"."wr_order_number" IS NULL
   GROUP BY
@@ -10466,11 +10782,11 @@ WITH "date_dim_2" AS (
     SUM("store_sales"."ss_wholesale_cost") AS "ss_wc",
     SUM("store_sales"."ss_sales_price") AS "ss_sp"
   FROM "store_sales" AS "store_sales"
+  JOIN "date_dim_2" AS "date_dim"
+    ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
   LEFT JOIN "store_returns" AS "store_returns"
     ON "store_returns"."sr_ticket_number" = "store_sales"."ss_ticket_number"
     AND "store_sales"."ss_item_sk" = "store_returns"."sr_item_sk"
-  JOIN "date_dim_2" AS "date_dim"
-    ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
   WHERE
     "store_returns"."sr_ticket_number" IS NULL
   GROUP BY
@@ -10488,14 +10804,14 @@ SELECT
   COALESCE("ws"."ws_wc", 0) + COALESCE("cs"."cs_wc", 0) AS "other_chan_wholesale_cost",
   COALESCE("ws"."ws_sp", 0) + COALESCE("cs"."cs_sp", 0) AS "other_chan_sales_price"
 FROM "ss"
-LEFT JOIN "ws"
-  ON "ws"."ws_customer_sk" = "ss"."ss_customer_sk"
-  AND "ws"."ws_item_sk" = "ss"."ss_item_sk"
-  AND "ws"."ws_sold_year" = "ss"."ss_sold_year"
 LEFT JOIN "cs"
   ON "cs"."cs_customer_sk" = "ss"."ss_customer_sk"
   AND "cs"."cs_item_sk" = "cs"."cs_item_sk"
   AND "cs"."cs_sold_year" = "ss"."ss_sold_year"
+LEFT JOIN "ws"
+  ON "ws"."ws_customer_sk" = "ss"."ss_customer_sk"
+  AND "ws"."ws_item_sk" = "ss"."ss_item_sk"
+  AND "ws"."ws_sold_year" = "ss"."ss_sold_year"
 WHERE
   "ss"."ss_sold_year" = 1999
   AND COALESCE("cs"."cs_qty", 0) > 0
@@ -10560,16 +10876,16 @@ WITH "ms" AS (
     ON "date_dim"."d_dow" = 1
     AND "date_dim"."d_year" IN (2000, 2001, 2002)
     AND "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
-  JOIN "store" AS "store"
-    ON "store"."s_number_employees" <= 295
-    AND "store"."s_number_employees" >= 200
-    AND "store_sales"."ss_store_sk" = "store"."s_store_sk"
   JOIN "household_demographics" AS "household_demographics"
     ON (
       "household_demographics"."hd_dep_count" = 8
       OR "household_demographics"."hd_vehicle_count" > 4
     )
     AND "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk"
+  JOIN "store" AS "store"
+    ON "store"."s_number_employees" <= 295
+    AND "store"."s_number_employees" >= 200
+    AND "store_sales"."ss_store_sk" = "store"."s_store_sk"
   GROUP BY
     "store_sales"."ss_ticket_number",
     "store_sales"."ss_customer_sk",
@@ -10729,17 +11045,17 @@ WITH "date_dim_2" AS (
     SUM(COALESCE("store_returns"."sr_return_amt", 0)) AS "returns1",
     SUM("store_sales"."ss_net_profit" - COALESCE("store_returns"."sr_net_loss", 0)) AS "profit"
   FROM "store_sales" AS "store_sales"
-  LEFT JOIN "store_returns" AS "store_returns"
-    ON "store_sales"."ss_item_sk" = "store_returns"."sr_item_sk"
-    AND "store_sales"."ss_ticket_number" = "store_returns"."sr_ticket_number"
   JOIN "date_dim_2" AS "date_dim"
     ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
-  JOIN "store" AS "store"
-    ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
   JOIN "item_2" AS "item"
     ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
   JOIN "promotion_2" AS "promotion"
     ON "store_sales"."ss_promo_sk" = "promotion"."p_promo_sk"
+  JOIN "store" AS "store"
+    ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
+  LEFT JOIN "store_returns" AS "store_returns"
+    ON "store_sales"."ss_item_sk" = "store_returns"."sr_item_sk"
+    AND "store_sales"."ss_ticket_number" = "store_returns"."sr_ticket_number"
   GROUP BY
     "store"."s_store_id"
 ), "csr" AS (
@@ -10749,13 +11065,13 @@ WITH "date_dim_2" AS (
     SUM(COALESCE("catalog_returns"."cr_return_amount", 0)) AS "returns1",
     SUM("catalog_sales"."cs_net_profit" - COALESCE("catalog_returns"."cr_net_loss", 0)) AS "profit"
   FROM "catalog_sales" AS "catalog_sales"
+  JOIN "catalog_page" AS "catalog_page"
+    ON "catalog_sales"."cs_catalog_page_sk" = "catalog_page"."cp_catalog_page_sk"
   LEFT JOIN "catalog_returns" AS "catalog_returns"
     ON "catalog_sales"."cs_item_sk" = "catalog_returns"."cr_item_sk"
     AND "catalog_sales"."cs_order_number" = "catalog_returns"."cr_order_number"
   JOIN "date_dim_2" AS "date_dim"
     ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk"
-  JOIN "catalog_page" AS "catalog_page"
-    ON "catalog_sales"."cs_catalog_page_sk" = "catalog_page"."cp_catalog_page_sk"
   JOIN "item_2" AS "item"
     ON "catalog_sales"."cs_item_sk" = "item"."i_item_sk"
   JOIN "promotion_2" AS "promotion"
@@ -10769,17 +11085,17 @@ WITH "date_dim_2" AS (
     SUM(COALESCE("web_returns"."wr_return_amt", 0)) AS "returns1",
     SUM("web_sales"."ws_net_profit" - COALESCE("web_returns"."wr_net_loss", 0)) AS "profit"
   FROM "web_sales" AS "web_sales"
-  LEFT JOIN "web_returns" AS "web_returns"
-    ON "web_sales"."ws_item_sk" = "web_returns"."wr_item_sk"
-    AND "web_sales"."ws_order_number" = "web_returns"."wr_order_number"
   JOIN "date_dim_2" AS "date_dim"
     ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk"
-  JOIN "web_site" AS "web_site"
-    ON "web_sales"."ws_web_site_sk" = "web_site"."web_site_sk"
   JOIN "item_2" AS "item"
     ON "web_sales"."ws_item_sk" = "item"."i_item_sk"
   JOIN "promotion_2" AS "promotion"
     ON "web_sales"."ws_promo_sk" = "promotion"."p_promo_sk"
+  LEFT JOIN "web_returns" AS "web_returns"
+    ON "web_sales"."ws_item_sk" = "web_returns"."wr_item_sk"
+    AND "web_sales"."ws_order_number" = "web_returns"."wr_order_number"
+  JOIN "web_site" AS "web_site"
+    ON "web_sales"."ws_web_site_sk" = "web_site"."web_site_sk"
   GROUP BY
     "web_site"."web_site_id"
 ), "cte_4" AS (
@@ -10896,11 +11212,11 @@ WITH "customer_total_return" AS (
     "customer_address"."ca_state" AS "ctr_state",
     SUM("catalog_returns"."cr_return_amt_inc_tax") AS "ctr_total_return"
   FROM "catalog_returns" AS "catalog_returns"
+  JOIN "customer_address" AS "customer_address"
+    ON "catalog_returns"."cr_returning_addr_sk" = "customer_address"."ca_address_sk"
   JOIN "date_dim" AS "date_dim"
     ON "catalog_returns"."cr_returned_date_sk" = "date_dim"."d_date_sk"
     AND "date_dim"."d_year" = 1999
-  JOIN "customer_address" AS "customer_address"
-    ON "catalog_returns"."cr_returning_addr_sk" = "customer_address"."ca_address_sk"
   GROUP BY
     "catalog_returns"."cr_returning_customer_sk",
     "customer_address"."ca_state"
@@ -10930,13 +11246,13 @@ SELECT
   "customer_address"."ca_location_type" AS "ca_location_type",
   "ctr1"."ctr_total_return" AS "ctr_total_return"
 FROM "customer_total_return" AS "ctr1"
+LEFT JOIN "_u_0" AS "_u_0"
+  ON "ctr1"."ctr_state" = "_u_0"."_u_1"
 JOIN "customer" AS "customer"
   ON "ctr1"."ctr_customer_sk" = "customer"."c_customer_sk"
 JOIN "customer_address" AS "customer_address"
   ON "customer_address"."ca_address_sk" = "customer"."c_current_addr_sk"
   AND "customer_address"."ca_state" = 'TX'
-LEFT JOIN "_u_0" AS "_u_0"
-  ON "ctr1"."ctr_state" = "_u_0"."_u_1"
 WHERE
   "ctr1"."ctr_total_return" > "_u_0"."_col_0"
 ORDER BY
@@ -10992,12 +11308,12 @@ JOIN "inventory" AS "inventory"
   ON "inventory"."inv_item_sk" = "item"."i_item_sk"
   AND "inventory"."inv_quantity_on_hand" <= 500
   AND "inventory"."inv_quantity_on_hand" >= 100
+JOIN "store_sales" AS "store_sales"
+  ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
 JOIN "date_dim" AS "date_dim"
   ON "date_dim"."d_date_sk" = "inventory"."inv_date_sk"
   AND CAST("date_dim"."d_date" AS DATE) <= CAST('1998-06-26' AS DATE)
   AND CAST("date_dim"."d_date" AS DATE) >= CAST('1998-04-27' AS DATE)
-JOIN "store_sales" AS "store_sales"
-  ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
 WHERE
   "item"."i_current_price" <= 93
   AND "item"."i_current_price" >= 63
@@ -11087,16 +11403,16 @@ WHERE  sr_items.item_id = cr_items.item_id
 ORDER  BY sr_items.item_id,
           sr_item_qty
 LIMIT 100;
-WITH "item_2" AS (
-  SELECT
-    "item"."i_item_sk" AS "i_item_sk",
-    "item"."i_item_id" AS "i_item_id"
-  FROM "item" AS "item"
-), "date_dim_2" AS (
+WITH "date_dim_2" AS (
   SELECT
     "date_dim"."d_date_sk" AS "d_date_sk",
     "date_dim"."d_date" AS "d_date"
   FROM "date_dim" AS "date_dim"
+), "item_2" AS (
+  SELECT
+    "item"."i_item_sk" AS "i_item_sk",
+    "item"."i_item_id" AS "i_item_id"
+  FROM "item" AS "item"
 ), "_u_0" AS (
   SELECT
     "date_dim"."d_week_seq" AS "d_week_seq"
@@ -11120,10 +11436,10 @@ WITH "item_2" AS (
     "item"."i_item_id" AS "item_id",
     SUM("store_returns"."sr_return_quantity") AS "sr_item_qty"
   FROM "store_returns" AS "store_returns"
-  JOIN "item_2" AS "item"
-    ON "store_returns"."sr_item_sk" = "item"."i_item_sk"
   JOIN "date_dim_2" AS "date_dim"
     ON "store_returns"."sr_returned_date_sk" = "date_dim"."d_date_sk"
+  JOIN "item_2" AS "item"
+    ON "store_returns"."sr_item_sk" = "item"."i_item_sk"
   LEFT JOIN "_u_1" AS "_u_1"
     ON "date_dim"."d_date" = "_u_1"."d_date"
   WHERE
@@ -11145,10 +11461,10 @@ WITH "item_2" AS (
     "item"."i_item_id" AS "item_id",
     SUM("catalog_returns"."cr_return_quantity") AS "cr_item_qty"
   FROM "catalog_returns" AS "catalog_returns"
-  JOIN "item_2" AS "item"
-    ON "catalog_returns"."cr_item_sk" = "item"."i_item_sk"
   JOIN "date_dim_2" AS "date_dim"
     ON "catalog_returns"."cr_returned_date_sk" = "date_dim"."d_date_sk"
+  JOIN "item_2" AS "item"
+    ON "catalog_returns"."cr_item_sk" = "item"."i_item_sk"
   LEFT JOIN "_u_3" AS "_u_3"
     ON "date_dim"."d_date" = "_u_3"."d_date"
   WHERE
@@ -11170,10 +11486,10 @@ WITH "item_2" AS (
     "item"."i_item_id" AS "item_id",
     SUM("web_returns"."wr_return_quantity") AS "wr_item_qty"
   FROM "web_returns" AS "web_returns"
-  JOIN "item_2" AS "item"
-    ON "web_returns"."wr_item_sk" = "item"."i_item_sk"
   JOIN "date_dim_2" AS "date_dim"
     ON "web_returns"."wr_returned_date_sk" = "date_dim"."d_date_sk"
+  JOIN "item_2" AS "item"
+    ON "web_returns"."wr_item_sk" = "item"."i_item_sk"
   LEFT JOIN "_u_5" AS "_u_5"
     ON "date_dim"."d_date" = "_u_5"."d_date"
   WHERE
@@ -11306,27 +11622,46 @@ ORDER  BY Substr(r_reason_desc, 1, 20),
           Avg(wr_refunded_cash),
           Avg(wr_fee)
 LIMIT 100;
-WITH "cd2" AS (
-  SELECT
-    "customer_demographics"."cd_demo_sk" AS "cd_demo_sk",
-    "customer_demographics"."cd_marital_status" AS "cd_marital_status",
-    "customer_demographics"."cd_education_status" AS "cd_education_status"
-  FROM "customer_demographics" AS "customer_demographics"
-)
 SELECT
   SUBSTR("reason"."r_reason_desc", 1, 20) AS "_col_0",
   AVG("web_sales"."ws_quantity") AS "_col_1",
   AVG("web_returns"."wr_refunded_cash") AS "_col_2",
   AVG("web_returns"."wr_fee") AS "_col_3"
 FROM "web_sales" AS "web_sales"
+JOIN "date_dim" AS "date_dim"
+  ON "date_dim"."d_year" = 2001 AND "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk"
+JOIN "web_page" AS "web_page"
+  ON "web_sales"."ws_web_page_sk" = "web_page"."wp_web_page_sk"
 JOIN "web_returns" AS "web_returns"
   ON "web_sales"."ws_item_sk" = "web_returns"."wr_item_sk"
   AND "web_sales"."ws_order_number" = "web_returns"."wr_order_number"
-JOIN "web_page" AS "web_page"
-  ON "web_sales"."ws_web_page_sk" = "web_page"."wp_web_page_sk"
-JOIN "cd2" AS "cd2"
+JOIN "customer_demographics" AS "cd2"
   ON "cd2"."cd_demo_sk" = "web_returns"."wr_returning_cdemo_sk"
-JOIN "cd2" AS "cd1"
+JOIN "customer_address" AS "customer_address"
+  ON "customer_address"."ca_address_sk" = "web_returns"."wr_refunded_addr_sk"
+  AND (
+    (
+      "customer_address"."ca_country" = 'United States'
+      AND "customer_address"."ca_state" IN ('FL', 'WI', 'KS')
+      AND "web_sales"."ws_net_profit" <= 250
+      AND "web_sales"."ws_net_profit" >= 50
+    )
+    OR (
+      "customer_address"."ca_country" = 'United States'
+      AND "customer_address"."ca_state" IN ('KY', 'ME', 'IL')
+      AND "web_sales"."ws_net_profit" <= 200
+      AND "web_sales"."ws_net_profit" >= 100
+    )
+    OR (
+      "customer_address"."ca_country" = 'United States'
+      AND "customer_address"."ca_state" IN ('OK', 'NE', 'MN')
+      AND "web_sales"."ws_net_profit" <= 300
+      AND "web_sales"."ws_net_profit" >= 150
+    )
+  )
+JOIN "reason" AS "reason"
+  ON "reason"."r_reason_sk" = "web_returns"."wr_reason_sk"
+JOIN "customer_demographics" AS "cd1"
   ON "cd1"."cd_demo_sk" = "web_returns"."wr_refunded_cdemo_sk"
   AND (
     (
@@ -11354,32 +11689,6 @@ JOIN "cd2" AS "cd1"
       AND "web_sales"."ws_sales_price" >= 50.00
     )
   )
-JOIN "customer_address" AS "customer_address"
-  ON "customer_address"."ca_address_sk" = "web_returns"."wr_refunded_addr_sk"
-  AND (
-    (
-      "customer_address"."ca_country" = 'United States'
-      AND "customer_address"."ca_state" IN ('FL', 'WI', 'KS')
-      AND "web_sales"."ws_net_profit" <= 250
-      AND "web_sales"."ws_net_profit" >= 50
-    )
-    OR (
-      "customer_address"."ca_country" = 'United States'
-      AND "customer_address"."ca_state" IN ('KY', 'ME', 'IL')
-      AND "web_sales"."ws_net_profit" <= 200
-      AND "web_sales"."ws_net_profit" >= 100
-    )
-    OR (
-      "customer_address"."ca_country" = 'United States'
-      AND "customer_address"."ca_state" IN ('OK', 'NE', 'MN')
-      AND "web_sales"."ws_net_profit" <= 300
-      AND "web_sales"."ws_net_profit" >= 150
-    )
-  )
-JOIN "date_dim" AS "date_dim"
-  ON "date_dim"."d_year" = 2001 AND "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk"
-JOIN "reason" AS "reason"
-  ON "reason"."r_reason_sk" = "web_returns"."wr_reason_sk"
 GROUP BY
   "reason"."r_reason_desc"
 ORDER BY
@@ -11422,10 +11731,10 @@ SELECT
   GROUPING("item"."i_category") + GROUPING("item"."i_class") AS "lochierarchy",
   RANK() OVER (PARTITION BY GROUPING("item"."i_category") + GROUPING("item"."i_class"), CASE WHEN GROUPING("item"."i_class") = 0 THEN "item"."i_category" END ORDER BY SUM("web_sales"."ws_net_paid") DESC) AS "rank_within_parent"
 FROM "web_sales" AS "web_sales"
-JOIN "date_dim" AS "date_dim"
-  ON "date_dim"."d_date_sk" = "web_sales"."ws_sold_date_sk"
-  AND "date_dim"."d_month_seq" <= 1194
-  AND "date_dim"."d_month_seq" >= 1183
+JOIN "date_dim" AS "d1"
+  ON "d1"."d_date_sk" = "web_sales"."ws_sold_date_sk"
+  AND "d1"."d_month_seq" <= 1194
+  AND "d1"."d_month_seq" >= 1183
 JOIN "item" AS "item"
   ON "item"."i_item_sk" = "web_sales"."ws_item_sk"
 GROUP BY
@@ -11462,7 +11771,13 @@ from ((select distinct c_last_name, c_first_name, d_date
          and d_month_seq between 1188 and 1188+11)
 ) cool_cust
 ;
-WITH "date_dim_2" AS (
+WITH "customer_2" AS (
+  SELECT
+    "customer"."c_customer_sk" AS "c_customer_sk",
+    "customer"."c_first_name" AS "c_first_name",
+    "customer"."c_last_name" AS "c_last_name"
+  FROM "customer" AS "customer"
+), "date_dim_2" AS (
   SELECT
     "date_dim"."d_date_sk" AS "d_date_sk",
     "date_dim"."d_date" AS "d_date",
@@ -11470,42 +11785,36 @@ WITH "date_dim_2" AS (
   FROM "date_dim" AS "date_dim"
   WHERE
     "date_dim"."d_month_seq" <= 1199 AND "date_dim"."d_month_seq" >= 1188
-), "customer_2" AS (
-  SELECT
-    "customer"."c_customer_sk" AS "c_customer_sk",
-    "customer"."c_first_name" AS "c_first_name",
-    "customer"."c_last_name" AS "c_last_name"
-  FROM "customer" AS "customer"
 ), "cte" AS (
   SELECT DISTINCT
     "customer"."c_last_name" AS "c_last_name",
     "customer"."c_first_name" AS "c_first_name",
     "date_dim"."d_date" AS "d_date"
   FROM "store_sales" AS "store_sales"
-  JOIN "date_dim_2" AS "date_dim"
-    ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
   JOIN "customer_2" AS "customer"
     ON "store_sales"."ss_customer_sk" = "customer"."c_customer_sk"
+  JOIN "date_dim_2" AS "date_dim"
+    ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
 ), "cte_2" AS (
   SELECT DISTINCT
     "customer"."c_last_name" AS "c_last_name",
     "customer"."c_first_name" AS "c_first_name",
     "date_dim"."d_date" AS "d_date"
   FROM "catalog_sales" AS "catalog_sales"
-  JOIN "date_dim_2" AS "date_dim"
-    ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk"
   JOIN "customer_2" AS "customer"
     ON "catalog_sales"."cs_bill_customer_sk" = "customer"."c_customer_sk"
+  JOIN "date_dim_2" AS "date_dim"
+    ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk"
 ), "cte_3" AS (
   SELECT DISTINCT
     "customer"."c_last_name" AS "c_last_name",
     "customer"."c_first_name" AS "c_first_name",
     "date_dim"."d_date" AS "d_date"
   FROM "web_sales" AS "web_sales"
-  JOIN "date_dim_2" AS "date_dim"
-    ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk"
   JOIN "customer_2" AS "customer"
     ON "web_sales"."ws_bill_customer_sk" = "customer"."c_customer_sk"
+  JOIN "date_dim_2" AS "date_dim"
+    ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk"
 ), "cte_4" AS (
   (
     SELECT
@@ -11676,96 +11985,96 @@ WITH "store_sales_2" AS (
   FROM "store_sales_2" AS "store_sales"
   JOIN "household_demographics_2" AS "household_demographics"
     ON "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk"
+  JOIN "store_2" AS "store"
+    ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
   JOIN "time_dim" AS "time_dim"
     ON "store_sales"."ss_sold_time_sk" = "time_dim"."t_time_sk"
     AND "time_dim"."t_hour" = 8
     AND "time_dim"."t_minute" >= 30
-  JOIN "store_2" AS "store"
-    ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
 ), "s2" AS (
   SELECT
     COUNT(*) AS "h9_to_9_30"
   FROM "store_sales_2" AS "store_sales"
   JOIN "household_demographics_2" AS "household_demographics"
     ON "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk"
+  JOIN "store_2" AS "store"
+    ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
   JOIN "time_dim" AS "time_dim"
     ON "store_sales"."ss_sold_time_sk" = "time_dim"."t_time_sk"
     AND "time_dim"."t_hour" = 9
     AND "time_dim"."t_minute" < 30
-  JOIN "store_2" AS "store"
-    ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
 ), "s3" AS (
   SELECT
     COUNT(*) AS "h9_30_to_10"
   FROM "store_sales_2" AS "store_sales"
   JOIN "household_demographics_2" AS "household_demographics"
     ON "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk"
+  JOIN "store_2" AS "store"
+    ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
   JOIN "time_dim" AS "time_dim"
     ON "store_sales"."ss_sold_time_sk" = "time_dim"."t_time_sk"
     AND "time_dim"."t_hour" = 9
     AND "time_dim"."t_minute" >= 30
-  JOIN "store_2" AS "store"
-    ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
 ), "s4" AS (
   SELECT
     COUNT(*) AS "h10_to_10_30"
   FROM "store_sales_2" AS "store_sales"
   JOIN "household_demographics_2" AS "household_demographics"
     ON "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk"
+  JOIN "store_2" AS "store"
+    ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
   JOIN "time_dim" AS "time_dim"
     ON "store_sales"."ss_sold_time_sk" = "time_dim"."t_time_sk"
     AND "time_dim"."t_hour" = 10
     AND "time_dim"."t_minute" < 30
-  JOIN "store_2" AS "store"
-    ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
 ), "s5" AS (
   SELECT
     COUNT(*) AS "h10_30_to_11"
   FROM "store_sales_2" AS "store_sales"
   JOIN "household_demographics_2" AS "household_demographics"
     ON "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk"
+  JOIN "store_2" AS "store"
+    ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
   JOIN "time_dim" AS "time_dim"
     ON "store_sales"."ss_sold_time_sk" = "time_dim"."t_time_sk"
     AND "time_dim"."t_hour" = 10
     AND "time_dim"."t_minute" >= 30
-  JOIN "store_2" AS "store"
-    ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
 ), "s6" AS (
   SELECT
     COUNT(*) AS "h11_to_11_30"
   FROM "store_sales_2" AS "store_sales"
   JOIN "household_demographics_2" AS "household_demographics"
     ON "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk"
+  JOIN "store_2" AS "store"
+    ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
   JOIN "time_dim" AS "time_dim"
     ON "store_sales"."ss_sold_time_sk" = "time_dim"."t_time_sk"
     AND "time_dim"."t_hour" = 11
     AND "time_dim"."t_minute" < 30
-  JOIN "store_2" AS "store"
-    ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
 ), "s7" AS (
   SELECT
     COUNT(*) AS "h11_30_to_12"
   FROM "store_sales_2" AS "store_sales"
   JOIN "household_demographics_2" AS "household_demographics"
     ON "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk"
+  JOIN "store_2" AS "store"
+    ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
   JOIN "time_dim" AS "time_dim"
     ON "store_sales"."ss_sold_time_sk" = "time_dim"."t_time_sk"
     AND "time_dim"."t_hour" = 11
     AND "time_dim"."t_minute" >= 30
-  JOIN "store_2" AS "store"
-    ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
 ), "s8" AS (
   SELECT
     COUNT(*) AS "h12_to_12_30"
   FROM "store_sales_2" AS "store_sales"
   JOIN "household_demographics_2" AS "household_demographics"
     ON "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk"
+  JOIN "store_2" AS "store"
+    ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
   JOIN "time_dim" AS "time_dim"
     ON "store_sales"."ss_sold_time_sk" = "time_dim"."t_time_sk"
     AND "time_dim"."t_hour" = 12
     AND "time_dim"."t_minute" < 30
-  JOIN "store_2" AS "store"
-    ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
 )
 SELECT
   "s1"."h8_30_to_9" AS "h8_30_to_9",
@@ -12016,16 +12325,19 @@ SELECT
   "call_center"."cc_manager" AS "manager",
   SUM("catalog_returns"."cr_net_loss") AS "returns_loss"
 FROM "call_center" AS "call_center"
-JOIN "catalog_returns" AS "catalog_returns"
-  ON "catalog_returns"."cr_call_center_sk" = "call_center"."cc_call_center_sk"
-JOIN "date_dim" AS "date_dim"
-  ON "catalog_returns"."cr_returned_date_sk" = "date_dim"."d_date_sk"
-  AND "date_dim"."d_moy" = 12
-  AND "date_dim"."d_year" = 1999
 JOIN "household_demographics" AS "household_demographics"
   ON "household_demographics"."hd_buy_potential" LIKE 'Unknown%'
+JOIN "customer" AS "customer"
+  ON "household_demographics"."hd_demo_sk" = "customer"."c_current_hdemo_sk"
+JOIN "catalog_returns" AS "catalog_returns"
+  ON "catalog_returns"."cr_call_center_sk" = "call_center"."cc_call_center_sk"
+  AND "catalog_returns"."cr_returning_customer_sk" = "customer"."c_customer_sk"
+JOIN "customer_address" AS "customer_address"
+  ON "customer_address"."ca_address_sk" = "customer"."c_current_addr_sk"
+  AND "customer_address"."ca_gmt_offset" = -7
 JOIN "customer_demographics" AS "customer_demographics"
-  ON (
+  ON "customer_demographics"."cd_demo_sk" = "customer"."c_current_cdemo_sk"
+  AND (
     "customer_demographics"."cd_education_status" = 'Advanced Degree'
     OR "customer_demographics"."cd_education_status" = 'Unknown'
   )
@@ -12041,13 +12353,10 @@ JOIN "customer_demographics" AS "customer_demographics"
     "customer_demographics"."cd_marital_status" = 'M'
     OR "customer_demographics"."cd_marital_status" = 'W'
   )
-JOIN "customer_address" AS "customer_address"
-  ON "customer_address"."ca_gmt_offset" = -7
-JOIN "customer" AS "customer"
-  ON "catalog_returns"."cr_returning_customer_sk" = "customer"."c_customer_sk"
-  AND "customer_address"."ca_address_sk" = "customer"."c_current_addr_sk"
-  AND "customer_demographics"."cd_demo_sk" = "customer"."c_current_cdemo_sk"
-  AND "household_demographics"."hd_demo_sk" = "customer"."c_current_hdemo_sk"
+JOIN "date_dim" AS "date_dim"
+  ON "catalog_returns"."cr_returned_date_sk" = "date_dim"."d_date_sk"
+  AND "date_dim"."d_moy" = 12
+  AND "date_dim"."d_year" = 1999
 GROUP BY
   "call_center"."cc_call_center_id",
   "call_center"."cc_name",
@@ -12108,10 +12417,10 @@ WITH "web_sales_2" AS (
 SELECT
   SUM("web_sales"."ws_ext_discount_amt") AS "Excess Discount Amount"
 FROM "web_sales_2" AS "web_sales"
-JOIN "item" AS "item"
-  ON "item"."i_item_sk" = "web_sales"."ws_item_sk" AND "item"."i_manufact_id" = 718
 JOIN "date_dim_2" AS "date_dim"
   ON "date_dim"."d_date_sk" = "web_sales"."ws_sold_date_sk"
+JOIN "item" AS "item"
+  ON "item"."i_item_sk" = "web_sales"."ws_item_sk" AND "item"."i_manufact_id" = 718
 LEFT JOIN "_u_0" AS "_u_0"
   ON "_u_0"."_u_1" = "item"."i_item_sk"
 WHERE
@@ -12158,11 +12467,11 @@ SELECT
     END
   ) AS "sumsales"
 FROM "store_sales" AS "store_sales"
+JOIN "reason" AS "reason"
+  ON "reason"."r_reason_desc" = 'reason 38'
 LEFT JOIN "store_returns" AS "store_returns"
   ON "store_returns"."sr_item_sk" = "store_sales"."ss_item_sk"
   AND "store_returns"."sr_ticket_number" = "store_sales"."ss_ticket_number"
-JOIN "reason" AS "reason"
-  ON "reason"."r_reason_desc" = 'reason 38'
 WHERE
   "store_returns"."sr_reason_sk" = "reason"."r_reason_sk"
 GROUP BY
@@ -12218,32 +12527,32 @@ WITH "_u_0" AS (
     "wr1"."wr_order_number"
 )
 SELECT
-  COUNT(DISTINCT "web_sales"."ws_order_number") AS "order count",
-  SUM("web_sales"."ws_ext_ship_cost") AS "total shipping cost",
-  SUM("web_sales"."ws_net_profit") AS "total net profit"
-FROM "web_sales" AS "web_sales"
+  COUNT(DISTINCT "ws1"."ws_order_number") AS "order count",
+  SUM("ws1"."ws_ext_ship_cost") AS "total shipping cost",
+  SUM("ws1"."ws_net_profit") AS "total net profit"
+FROM "web_sales" AS "ws1"
+LEFT JOIN "_u_0" AS "_u_0"
+  ON "ws1"."ws_order_number" = "_u_0"."_u_1"
+LEFT JOIN "_u_3" AS "_u_3"
+  ON "ws1"."ws_order_number" = "_u_3"."_u_4"
+JOIN "customer_address" AS "customer_address"
+  ON "customer_address"."ca_state" = 'MT'
+  AND "ws1"."ws_ship_addr_sk" = "customer_address"."ca_address_sk"
 JOIN "date_dim" AS "date_dim"
   ON "date_dim"."d_date" >= '2000-3-01'
-  AND "web_sales"."ws_ship_date_sk" = "date_dim"."d_date_sk"
+  AND "ws1"."ws_ship_date_sk" = "date_dim"."d_date_sk"
   AND CAST("date_dim"."d_date" AS DATE) <= (
     CAST('2000-3-01' AS DATE) + INTERVAL '60' day
   )
-JOIN "customer_address" AS "customer_address"
-  ON "customer_address"."ca_state" = 'MT'
-  AND "web_sales"."ws_ship_addr_sk" = "customer_address"."ca_address_sk"
 JOIN "web_site" AS "web_site"
-  ON "web_sales"."ws_web_site_sk" = "web_site"."web_site_sk"
-  AND "web_site"."web_company_name" = 'pri'
-LEFT JOIN "_u_0" AS "_u_0"
-  ON "web_sales"."ws_order_number" = "_u_0"."_u_1"
-LEFT JOIN "_u_3" AS "_u_3"
-  ON "web_sales"."ws_order_number" = "_u_3"."_u_4"
+  ON "web_site"."web_company_name" = 'pri'
+  AND "ws1"."ws_web_site_sk" = "web_site"."web_site_sk"
 WHERE
   "_u_3"."_u_4" IS NULL
-  AND ARRAY_ANY("_u_0"."_u_2", "_x" -> "web_sales"."ws_warehouse_sk" <> "_x")
+  AND ARRAY_ANY("_u_0"."_u_2", "_x" -> "ws1"."ws_warehouse_sk" <> "_x")
   AND NOT "_u_0"."_u_1" IS NULL
 ORDER BY
-  COUNT(DISTINCT "web_sales"."ws_order_number")
+  COUNT(DISTINCT "ws1"."ws_order_number")
 LIMIT 100;
 
 --------------------------------------
@@ -12285,16 +12594,11 @@ AND      ws1.ws_order_number IN
                 WHERE  wr_order_number = ws_wh.ws_order_number)
 ORDER BY count(DISTINCT ws_order_number)
 LIMIT 100;
-WITH "ws1" AS (
-  SELECT
-    "web_sales"."ws_warehouse_sk" AS "ws_warehouse_sk",
-    "web_sales"."ws_order_number" AS "ws_order_number"
-  FROM "web_sales" AS "web_sales"
-), "ws_wh" AS (
+WITH "ws_wh" AS (
   SELECT
     "ws1"."ws_order_number" AS "ws_order_number"
-  FROM "ws1" AS "ws1"
-  JOIN "ws1" AS "ws2"
+  FROM "web_sales" AS "ws1"
+  JOIN "web_sales" AS "ws2"
     ON "ws1"."ws_order_number" = "ws2"."ws_order_number"
     AND "ws1"."ws_warehouse_sk" <> "ws2"."ws_warehouse_sk"
 ), "_u_0" AS (
@@ -12313,30 +12617,30 @@ WITH "ws1" AS (
     "web_returns"."wr_order_number"
 )
 SELECT
-  COUNT(DISTINCT "web_sales"."ws_order_number") AS "order count",
-  SUM("web_sales"."ws_ext_ship_cost") AS "total shipping cost",
-  SUM("web_sales"."ws_net_profit") AS "total net profit"
-FROM "web_sales" AS "web_sales"
+  COUNT(DISTINCT "ws1"."ws_order_number") AS "order count",
+  SUM("ws1"."ws_ext_ship_cost") AS "total shipping cost",
+  SUM("ws1"."ws_net_profit") AS "total net profit"
+FROM "web_sales" AS "ws1"
+LEFT JOIN "_u_0" AS "_u_0"
+  ON "ws1"."ws_order_number" = "_u_0"."ws_order_number"
+LEFT JOIN "_u_1" AS "_u_1"
+  ON "ws1"."ws_order_number" = "_u_1"."wr_order_number"
+JOIN "customer_address" AS "customer_address"
+  ON "customer_address"."ca_state" = 'IN'
+  AND "ws1"."ws_ship_addr_sk" = "customer_address"."ca_address_sk"
 JOIN "date_dim" AS "date_dim"
   ON "date_dim"."d_date" >= '2000-4-01'
-  AND "web_sales"."ws_ship_date_sk" = "date_dim"."d_date_sk"
+  AND "ws1"."ws_ship_date_sk" = "date_dim"."d_date_sk"
   AND CAST("date_dim"."d_date" AS DATE) <= (
     CAST('2000-4-01' AS DATE) + INTERVAL '60' day
   )
-JOIN "customer_address" AS "customer_address"
-  ON "customer_address"."ca_state" = 'IN'
-  AND "web_sales"."ws_ship_addr_sk" = "customer_address"."ca_address_sk"
 JOIN "web_site" AS "web_site"
-  ON "web_sales"."ws_web_site_sk" = "web_site"."web_site_sk"
-  AND "web_site"."web_company_name" = 'pri'
-LEFT JOIN "_u_0" AS "_u_0"
-  ON "web_sales"."ws_order_number" = "_u_0"."ws_order_number"
-LEFT JOIN "_u_1" AS "_u_1"
-  ON "web_sales"."ws_order_number" = "_u_1"."wr_order_number"
+  ON "web_site"."web_company_name" = 'pri'
+  AND "ws1"."ws_web_site_sk" = "web_site"."web_site_sk"
 WHERE
   NOT "_u_0"."ws_order_number" IS NULL AND NOT "_u_1"."wr_order_number" IS NULL
 ORDER BY
-  COUNT(DISTINCT "web_sales"."ws_order_number")
+  COUNT(DISTINCT "ws1"."ws_order_number")
 LIMIT 100;
 
 --------------------------------------
@@ -12362,12 +12666,12 @@ FROM "store_sales" AS "store_sales"
 JOIN "household_demographics" AS "household_demographics"
   ON "household_demographics"."hd_dep_count" = 7
   AND "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk"
+JOIN "store" AS "store"
+  ON "store"."s_store_name" = 'ese' AND "store_sales"."ss_store_sk" = "store"."s_store_sk"
 JOIN "time_dim" AS "time_dim"
   ON "store_sales"."ss_sold_time_sk" = "time_dim"."t_time_sk"
   AND "time_dim"."t_hour" = 15
   AND "time_dim"."t_minute" >= 30
-JOIN "store" AS "store"
-  ON "store"."s_store_name" = 'ese' AND "store_sales"."ss_store_sk" = "store"."s_store_sk"
 ORDER BY
   COUNT(*)
 LIMIT 100;
@@ -12509,13 +12813,13 @@ SELECT
   SUM("store_sales"."ss_ext_sales_price") AS "itemrevenue",
   SUM("store_sales"."ss_ext_sales_price") * 100 / SUM(SUM("store_sales"."ss_ext_sales_price")) OVER (PARTITION BY "item"."i_class") AS "revenueratio"
 FROM "store_sales" AS "store_sales"
-JOIN "item" AS "item"
-  ON "item"."i_category" IN ('Men', 'Home', 'Electronics')
-  AND "store_sales"."ss_item_sk" = "item"."i_item_sk"
 JOIN "date_dim" AS "date_dim"
   ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
   AND CAST("date_dim"."d_date" AS DATE) <= CAST('2000-06-17' AS DATE)
   AND CAST("date_dim"."d_date" AS DATE) >= CAST('2000-05-18' AS DATE)
+JOIN "item" AS "item"
+  ON "item"."i_category" IN ('Men', 'Home', 'Electronics')
+  AND "store_sales"."ss_item_sk" = "item"."i_item_sk"
 GROUP BY
   "item"."i_item_id",
   "item"."i_item_desc",
@@ -12621,16 +12925,16 @@ SELECT
     END
   ) AS ">120 days"
 FROM "catalog_sales" AS "catalog_sales"
-JOIN "warehouse" AS "warehouse"
-  ON "catalog_sales"."cs_warehouse_sk" = "warehouse"."w_warehouse_sk"
-JOIN "ship_mode" AS "ship_mode"
-  ON "catalog_sales"."cs_ship_mode_sk" = "ship_mode"."sm_ship_mode_sk"
 JOIN "call_center" AS "call_center"
   ON "catalog_sales"."cs_call_center_sk" = "call_center"."cc_call_center_sk"
 JOIN "date_dim" AS "date_dim"
   ON "catalog_sales"."cs_ship_date_sk" = "date_dim"."d_date_sk"
   AND "date_dim"."d_month_seq" <= 1211
   AND "date_dim"."d_month_seq" >= 1200
+JOIN "ship_mode" AS "ship_mode"
+  ON "catalog_sales"."cs_ship_mode_sk" = "ship_mode"."sm_ship_mode_sk"
+JOIN "warehouse" AS "warehouse"
+  ON "catalog_sales"."cs_warehouse_sk" = "warehouse"."w_warehouse_sk"
 GROUP BY
   SUBSTR("warehouse"."w_warehouse_name", 1, 20),
   "ship_mode"."sm_type",
diff --git a/tests/fixtures/optimizer/tpc-h/tpc-h.sql b/tests/fixtures/optimizer/tpc-h/tpc-h.sql
index a25e247..942295e 100644
--- a/tests/fixtures/optimizer/tpc-h/tpc-h.sql
+++ b/tests/fixtures/optimizer/tpc-h/tpc-h.sql
@@ -117,12 +117,12 @@ WITH "region_2" AS (
     MIN("partsupp"."ps_supplycost") AS "_col_0",
     "partsupp"."ps_partkey" AS "_u_1"
   FROM "partsupp_2" AS "partsupp"
-  CROSS JOIN "region_2" AS "region"
-  JOIN "nation" AS "nation"
-    ON "nation"."n_regionkey" = "region"."r_regionkey"
   JOIN "supplier" AS "supplier"
+    ON "supplier"."s_suppkey" = "partsupp"."ps_suppkey"
+  JOIN "nation" AS "nation"
     ON "supplier"."s_nationkey" = "nation"."n_nationkey"
-    AND "supplier"."s_suppkey" = "partsupp"."ps_suppkey"
+  JOIN "region_2" AS "region"
+    ON "nation"."n_regionkey" = "region"."r_regionkey"
   GROUP BY
     "partsupp"."ps_partkey"
 )
@@ -137,6 +137,8 @@ SELECT
   "supplier"."s_comment" AS "s_comment"
 FROM "part" AS "part"
 CROSS JOIN "region_2" AS "region"
+LEFT JOIN "_u_0" AS "_u_0"
+  ON "part"."p_partkey" = "_u_0"."_u_1"
 JOIN "nation" AS "nation"
   ON "nation"."n_regionkey" = "region"."r_regionkey"
 JOIN "partsupp_2" AS "partsupp"
@@ -144,8 +146,6 @@ JOIN "partsupp_2" AS "partsupp"
 JOIN "supplier" AS "supplier"
   ON "supplier"."s_nationkey" = "nation"."n_nationkey"
   AND "supplier"."s_suppkey" = "partsupp"."ps_suppkey"
-LEFT JOIN "_u_0" AS "_u_0"
-  ON "part"."p_partkey" = "_u_0"."_u_1"
 WHERE
   "part"."p_size" = 15
   AND "part"."p_type" LIKE '%BRASS'
@@ -294,16 +294,15 @@ JOIN "orders" AS "orders"
   ON "customer"."c_custkey" = "orders"."o_custkey"
   AND CAST("orders"."o_orderdate" AS DATE) < CAST('1995-01-01' AS DATE)
   AND CAST("orders"."o_orderdate" AS DATE) >= CAST('1994-01-01' AS DATE)
-JOIN "region" AS "region"
-  ON "region"."r_name" = 'ASIA'
-JOIN "nation" AS "nation"
-  ON "nation"."n_regionkey" = "region"."r_regionkey"
 JOIN "supplier" AS "supplier"
   ON "customer"."c_nationkey" = "supplier"."s_nationkey"
-  AND "supplier"."s_nationkey" = "nation"."n_nationkey"
 JOIN "lineitem" AS "lineitem"
   ON "lineitem"."l_orderkey" = "orders"."o_orderkey"
   AND "lineitem"."l_suppkey" = "supplier"."s_suppkey"
+JOIN "nation" AS "nation"
+  ON "supplier"."s_nationkey" = "nation"."n_nationkey"
+JOIN "region" AS "region"
+  ON "nation"."n_regionkey" = "region"."r_regionkey" AND "region"."r_name" = 'ASIA'
 GROUP BY
   "nation"."n_name"
 ORDER BY
@@ -373,14 +372,6 @@ order by
         supp_nation,
         cust_nation,
         l_year;
-WITH "n1" AS (
-  SELECT
-    "nation"."n_nationkey" AS "n_nationkey",
-    "nation"."n_name" AS "n_name"
-  FROM "nation" AS "nation"
-  WHERE
-    "nation"."n_name" = 'FRANCE' OR "nation"."n_name" = 'GERMANY'
-)
 SELECT
   "n1"."n_name" AS "supp_nation",
   "n2"."n_name" AS "cust_nation",
@@ -393,20 +384,26 @@ JOIN "lineitem" AS "lineitem"
   ON "supplier"."s_suppkey" = "lineitem"."l_suppkey"
   AND CAST("lineitem"."l_shipdate" AS DATE) <= CAST('1996-12-31' AS DATE)
   AND CAST("lineitem"."l_shipdate" AS DATE) >= CAST('1995-01-01' AS DATE)
-JOIN "orders" AS "orders"
-  ON "orders"."o_orderkey" = "lineitem"."l_orderkey"
-JOIN "customer" AS "customer"
-  ON "customer"."c_custkey" = "orders"."o_custkey"
-JOIN "n1" AS "n1"
-  ON "supplier"."s_nationkey" = "n1"."n_nationkey"
-JOIN "n1" AS "n2"
-  ON "customer"."c_nationkey" = "n2"."n_nationkey"
-  AND (
+JOIN "nation" AS "n1"
+  ON (
+    "n1"."n_name" = 'FRANCE' OR "n1"."n_name" = 'GERMANY'
+  )
+  AND "supplier"."s_nationkey" = "n1"."n_nationkey"
+JOIN "nation" AS "n2"
+  ON (
     "n1"."n_name" = 'FRANCE' OR "n2"."n_name" = 'FRANCE'
   )
   AND (
     "n1"."n_name" = 'GERMANY' OR "n2"."n_name" = 'GERMANY'
   )
+  AND (
+    "n2"."n_name" = 'FRANCE' OR "n2"."n_name" = 'GERMANY'
+  )
+JOIN "customer" AS "customer"
+  ON "customer"."c_nationkey" = "n2"."n_nationkey"
+JOIN "orders" AS "orders"
+  ON "customer"."c_custkey" = "orders"."o_custkey"
+  AND "orders"."o_orderkey" = "lineitem"."l_orderkey"
 GROUP BY
   "n1"."n_name",
   "n2"."n_name",
@@ -460,7 +457,7 @@ SELECT
   EXTRACT(year FROM CAST("orders"."o_orderdate" AS DATE)) AS "o_year",
   SUM(
     CASE
-      WHEN "nation_2"."n_name" = 'BRAZIL'
+      WHEN "n2"."n_name" = 'BRAZIL'
       THEN "lineitem"."l_extendedprice" * (
         1 - "lineitem"."l_discount"
       )
@@ -472,21 +469,21 @@ SELECT
 FROM "part" AS "part"
 JOIN "region" AS "region"
   ON "region"."r_name" = 'AMERICA'
-JOIN "nation" AS "nation"
-  ON "nation"."n_regionkey" = "region"."r_regionkey"
+JOIN "lineitem" AS "lineitem"
+  ON "part"."p_partkey" = "lineitem"."l_partkey"
+JOIN "nation" AS "n1"
+  ON "n1"."n_regionkey" = "region"."r_regionkey"
 JOIN "customer" AS "customer"
-  ON "customer"."c_nationkey" = "nation"."n_nationkey"
+  ON "customer"."c_nationkey" = "n1"."n_nationkey"
+JOIN "supplier" AS "supplier"
+  ON "supplier"."s_suppkey" = "lineitem"."l_suppkey"
+JOIN "nation" AS "n2"
+  ON "supplier"."s_nationkey" = "n2"."n_nationkey"
 JOIN "orders" AS "orders"
-  ON "orders"."o_custkey" = "customer"."c_custkey"
+  ON "lineitem"."l_orderkey" = "orders"."o_orderkey"
+  AND "orders"."o_custkey" = "customer"."c_custkey"
   AND CAST("orders"."o_orderdate" AS DATE) <= CAST('1996-12-31' AS DATE)
   AND CAST("orders"."o_orderdate" AS DATE) >= CAST('1995-01-01' AS DATE)
-JOIN "lineitem" AS "lineitem"
-  ON "lineitem"."l_orderkey" = "orders"."o_orderkey"
-  AND "part"."p_partkey" = "lineitem"."l_partkey"
-JOIN "supplier" AS "supplier"
-  ON "supplier"."s_suppkey" = "lineitem"."l_suppkey"
-JOIN "nation" AS "nation_2"
-  ON "supplier"."s_nationkey" = "nation_2"."n_nationkey"
 WHERE
   "part"."p_type" = 'ECONOMY ANODIZED STEEL'
 GROUP BY
@@ -540,13 +537,13 @@ SELECT
 FROM "part" AS "part"
 JOIN "lineitem" AS "lineitem"
   ON "part"."p_partkey" = "lineitem"."l_partkey"
-JOIN "supplier" AS "supplier"
-  ON "supplier"."s_suppkey" = "lineitem"."l_suppkey"
+JOIN "orders" AS "orders"
+  ON "orders"."o_orderkey" = "lineitem"."l_orderkey"
 JOIN "partsupp" AS "partsupp"
   ON "partsupp"."ps_partkey" = "lineitem"."l_partkey"
   AND "partsupp"."ps_suppkey" = "lineitem"."l_suppkey"
-JOIN "orders" AS "orders"
-  ON "orders"."o_orderkey" = "lineitem"."l_orderkey"
+JOIN "supplier" AS "supplier"
+  ON "supplier"."s_suppkey" = "lineitem"."l_suppkey"
 JOIN "nation" AS "nation"
   ON "supplier"."s_nationkey" = "nation"."n_nationkey"
 WHERE
@@ -606,14 +603,14 @@ SELECT
   "customer"."c_phone" AS "c_phone",
   "customer"."c_comment" AS "c_comment"
 FROM "customer" AS "customer"
+JOIN "nation" AS "nation"
+  ON "customer"."c_nationkey" = "nation"."n_nationkey"
 JOIN "orders" AS "orders"
   ON "customer"."c_custkey" = "orders"."o_custkey"
   AND CAST("orders"."o_orderdate" AS DATE) < CAST('1994-01-01' AS DATE)
   AND CAST("orders"."o_orderdate" AS DATE) >= CAST('1993-10-01' AS DATE)
 JOIN "lineitem" AS "lineitem"
   ON "lineitem"."l_orderkey" = "orders"."o_orderkey" AND "lineitem"."l_returnflag" = 'R'
-JOIN "nation" AS "nation"
-  ON "customer"."c_nationkey" = "nation"."n_nationkey"
 GROUP BY
   "customer"."c_custkey",
   "customer"."c_name",
@@ -681,11 +678,11 @@ SELECT
   "partsupp"."ps_partkey" AS "ps_partkey",
   SUM("partsupp"."ps_supplycost" * "partsupp"."ps_availqty") AS "value"
 FROM "partsupp" AS "partsupp"
+CROSS JOIN "_u_0" AS "_u_0"
 JOIN "supplier_2" AS "supplier"
   ON "partsupp"."ps_suppkey" = "supplier"."s_suppkey"
 JOIN "nation_2" AS "nation"
   ON "supplier"."s_nationkey" = "nation"."n_nationkey"
-CROSS JOIN "_u_0" AS "_u_0"
 GROUP BY
   "partsupp"."ps_partkey"
 HAVING
@@ -950,13 +947,13 @@ SELECT
   "part"."p_size" AS "p_size",
   COUNT(DISTINCT "partsupp"."ps_suppkey") AS "supplier_cnt"
 FROM "partsupp" AS "partsupp"
+LEFT JOIN "_u_0" AS "_u_0"
+  ON "partsupp"."ps_suppkey" = "_u_0"."s_suppkey"
 JOIN "part" AS "part"
   ON "part"."p_brand" <> 'Brand#45'
   AND "part"."p_partkey" = "partsupp"."ps_partkey"
   AND "part"."p_size" IN (49, 14, 23, 45, 19, 3, 36, 9)
   AND NOT "part"."p_type" LIKE 'MEDIUM POLISHED%'
-LEFT JOIN "_u_0" AS "_u_0"
-  ON "partsupp"."ps_suppkey" = "_u_0"."s_suppkey"
 WHERE
   "_u_0"."s_suppkey" IS NULL
 GROUP BY
@@ -1066,10 +1063,10 @@ SELECT
 FROM "customer" AS "customer"
 JOIN "orders" AS "orders"
   ON "customer"."c_custkey" = "orders"."o_custkey"
-JOIN "lineitem" AS "lineitem"
-  ON "orders"."o_orderkey" = "lineitem"."l_orderkey"
 LEFT JOIN "_u_0" AS "_u_0"
   ON "orders"."o_orderkey" = "_u_0"."l_orderkey"
+JOIN "lineitem" AS "lineitem"
+  ON "orders"."o_orderkey" = "lineitem"."l_orderkey"
 WHERE
   NOT "_u_0"."l_orderkey" IS NULL
 GROUP BY
@@ -1260,10 +1257,10 @@ SELECT
   "supplier"."s_name" AS "s_name",
   "supplier"."s_address" AS "s_address"
 FROM "supplier" AS "supplier"
-JOIN "nation" AS "nation"
-  ON "nation"."n_name" = 'CANADA' AND "supplier"."s_nationkey" = "nation"."n_nationkey"
 LEFT JOIN "_u_4" AS "_u_4"
   ON "supplier"."s_suppkey" = "_u_4"."ps_suppkey"
+JOIN "nation" AS "nation"
+  ON "nation"."n_name" = 'CANADA' AND "supplier"."s_nationkey" = "nation"."n_nationkey"
 WHERE
   NOT "_u_4"."ps_suppkey" IS NULL
 ORDER BY
@@ -1334,24 +1331,24 @@ SELECT
   "supplier"."s_name" AS "s_name",
   COUNT(*) AS "numwait"
 FROM "supplier" AS "supplier"
-JOIN "lineitem" AS "lineitem"
-  ON "lineitem"."l_receiptdate" > "lineitem"."l_commitdate"
-  AND "supplier"."s_suppkey" = "lineitem"."l_suppkey"
-JOIN "orders" AS "orders"
-  ON "orders"."o_orderkey" = "lineitem"."l_orderkey" AND "orders"."o_orderstatus" = 'F'
+JOIN "lineitem" AS "l1"
+  ON "l1"."l_receiptdate" > "l1"."l_commitdate"
+  AND "supplier"."s_suppkey" = "l1"."l_suppkey"
 JOIN "nation" AS "nation"
   ON "nation"."n_name" = 'SAUDI ARABIA'
   AND "supplier"."s_nationkey" = "nation"."n_nationkey"
 LEFT JOIN "_u_0" AS "_u_0"
-  ON "_u_0"."l_orderkey" = "lineitem"."l_orderkey"
+  ON "_u_0"."l_orderkey" = "l1"."l_orderkey"
 LEFT JOIN "_u_2" AS "_u_2"
-  ON "_u_2"."l_orderkey" = "lineitem"."l_orderkey"
+  ON "_u_2"."l_orderkey" = "l1"."l_orderkey"
+JOIN "orders" AS "orders"
+  ON "orders"."o_orderkey" = "l1"."l_orderkey" AND "orders"."o_orderstatus" = 'F'
 WHERE
   (
     "_u_2"."l_orderkey" IS NULL
-    OR NOT ARRAY_ANY("_u_2"."_u_3", "_x" -> "_x" <> "lineitem"."l_suppkey")
+    OR NOT ARRAY_ANY("_u_2"."_u_3", "_x" -> "_x" <> "l1"."l_suppkey")
   )
-  AND ARRAY_ANY("_u_0"."_u_1", "_x" -> "_x" <> "lineitem"."l_suppkey")
+  AND ARRAY_ANY("_u_0"."_u_1", "_x" -> "_x" <> "l1"."l_suppkey")
   AND NOT "_u_0"."l_orderkey" IS NULL
 GROUP BY
   "supplier"."s_name"
@@ -1430,3 +1427,4 @@ GROUP BY
   SUBSTRING("customer"."c_phone", 1, 2)
 ORDER BY
   "cntrycode";
+