Merging upstream version 15.0.0.

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

19 files changed, 546 insertions, 382 deletions

diff --git a/tests/fixtures/optimizer/canonicalize.sql b/tests/fixtures/optimizer/canonicalize.sql
index ccf2f16..1fc44ef 100644
--- a/tests/fixtures/optimizer/canonicalize.sql
+++ b/tests/fixtures/optimizer/canonicalize.sql
@@ -10,8 +10,8 @@ SELECT CAST(1 AS VARCHAR) AS "a" FROM "w" AS "w";
 SELECT CAST(1 + 3.2 AS DOUBLE) AS a FROM w AS w;
 SELECT 1 + 3.2 AS "a" FROM "w" AS "w";
 
-SELECT CAST("2022-01-01" AS DATE) + INTERVAL '1' day;
-SELECT CAST("2022-01-01" AS DATE) + INTERVAL '1' day AS "_col_0";
+SELECT CAST('2022-01-01' AS DATE) + INTERVAL '1' day;
+SELECT CAST('2022-01-01' AS DATE) + INTERVAL '1' day AS "_col_0";
 
 --------------------------------------
 -- Ensure boolean predicates
diff --git a/tests/fixtures/optimizer/eliminate_subqueries.sql b/tests/fixtures/optimizer/eliminate_subqueries.sql
index 4fa63dd..4c79c22 100644
--- a/tests/fixtures/optimizer/eliminate_subqueries.sql
+++ b/tests/fixtures/optimizer/eliminate_subqueries.sql
@@ -35,8 +35,8 @@ SELECT * FROM (SELECT * FROM (SELECT a FROM x) AS x) AS y JOIN (SELECT * FROM x)
 WITH x_2 AS (SELECT a FROM x), y AS (SELECT * FROM x_2 AS x), z AS (SELECT * FROM x) SELECT * FROM y AS y JOIN z AS z ON x.a = y.a;
 
 -- Name conflicts with table alias
-SELECT a FROM (SELECT a FROM (SELECT a FROM x) AS y) AS z JOIN q AS y;
-WITH y AS (SELECT a FROM x), z AS (SELECT a FROM y AS y) SELECT a FROM z AS z JOIN q AS y;
+SELECT a FROM (SELECT a FROM (SELECT a FROM x) AS y) AS z CROSS JOIN q AS y;
+WITH y AS (SELECT a FROM x), z AS (SELECT a FROM y AS y) SELECT a FROM z AS z CROSS JOIN q AS y;
 
 -- Name conflicts with existing CTE
 WITH y AS (SELECT a FROM (SELECT a FROM x) AS y) SELECT a FROM y;
@@ -63,12 +63,12 @@ SELECT a FROM x WHERE b = (SELECT c FROM y WHERE y.a = x.a);
 SELECT a FROM x WHERE b = (SELECT c FROM y WHERE y.a = x.a);
 
 -- Duplicate CTE
-SELECT a FROM (SELECT b FROM x) AS y JOIN (SELECT b FROM x) AS z;
-WITH y AS (SELECT b FROM x) SELECT a FROM y AS y JOIN y AS z;
+SELECT a FROM (SELECT b FROM x) AS y CROSS JOIN (SELECT b FROM x) AS z;
+WITH y AS (SELECT b FROM x) SELECT a FROM y AS y CROSS JOIN y AS z;
 
 -- Doubly duplicate CTE
 SELECT * FROM (SELECT * FROM x JOIN (SELECT * FROM x) AS y) AS z JOIN (SELECT * FROM x JOIN (SELECT * FROM x) AS y) AS q;
-WITH y AS (SELECT * FROM x), z AS (SELECT * FROM x JOIN y AS y) SELECT * FROM z AS z JOIN z AS q;
+WITH y AS (SELECT * FROM x), z AS (SELECT * FROM x, y AS y) SELECT * FROM z AS z, z AS q;
 
 -- Another duplicate...
 SELECT x.id FROM (SELECT * FROM x AS x JOIN y AS y ON x.id = y.id) AS x JOIN (SELECT * FROM x AS x JOIN y AS y ON x.id = y.id) AS y ON x.id = y.id;
@@ -79,8 +79,8 @@ WITH x_2 AS (SELECT * FROM x AS x JOIN y AS y ON x.id = y.id) SELECT x.id FROM x
 (WITH cte AS (SELECT * FROM x) SELECT * FROM cte AS cte) LIMIT 1;
 
 -- Existing duplicate CTE
-WITH y AS (SELECT a FROM x) SELECT a FROM (SELECT a FROM x) AS y JOIN y AS z;
-WITH y AS (SELECT a FROM x) SELECT a FROM y AS y JOIN y AS z;
+WITH y AS (SELECT a FROM x) SELECT a FROM (SELECT a FROM x) AS y CROSS JOIN y AS z;
+WITH y AS (SELECT a FROM x) SELECT a FROM y AS y CROSS JOIN y AS z;
 
 -- Nested CTE
 WITH cte1 AS (SELECT a FROM x) SELECT a FROM (WITH cte2 AS (SELECT a FROM cte1) SELECT a FROM cte2);
diff --git a/tests/fixtures/optimizer/expand_laterals.sql b/tests/fixtures/optimizer/expand_laterals.sql
deleted file mode 100644
index 09bbd0f..0000000
--- a/tests/fixtures/optimizer/expand_laterals.sql
+++ /dev/null
@@ -1,40 +0,0 @@
-# title: expand alias reference
-SELECT
-  x.a + 1 AS i,
-  i + 1 AS j,
-  j + 1 AS k
-FROM x;
-SELECT
-  x.a + 1 AS i,
-  x.a + 1 + 1 AS j,
-  x.a + 1 + 1 + 1 AS k
-FROM x;
-
-# title: noop - reference comes before alias
-SELECT
-  b + 1 AS j,
-  x.a + 1 AS i
-FROM x;
-SELECT
-  b + 1 AS j,
-  x.a + 1 AS i
-FROM x;
-
-
-# title: subquery
-SELECT
-  *
-FROM (
-  SELECT
-    x.a + 1 AS i,
-    i + 1 AS j
-  FROM x
-);
-SELECT
-  *
-FROM (
-  SELECT
-    x.a + 1 AS i,
-    x.a + 1 + 1 AS j
-  FROM x
-);
diff --git a/tests/fixtures/optimizer/expand_multi_table_selects.sql b/tests/fixtures/optimizer/expand_multi_table_selects.sql
deleted file mode 100644
index a5a4664..0000000
--- a/tests/fixtures/optimizer/expand_multi_table_selects.sql
+++ /dev/null
@@ -1,11 +0,0 @@
---------------------------------------
--- Multi Table Selects
---------------------------------------
-SELECT * FROM x AS x, y AS y WHERE x.a = y.a;
-SELECT * FROM x AS x CROSS JOIN y AS y WHERE x.a = y.a;
-
-SELECT * FROM x AS x, y AS y WHERE x.a = y.a AND x.a = 1 and y.b = 1;
-SELECT * FROM x AS x CROSS JOIN y AS y WHERE x.a = y.a AND x.a = 1 AND y.b = 1;
-
-SELECT * FROM x AS x, y AS y WHERE x.a > y.a;
-SELECT * FROM x AS x CROSS JOIN y AS y WHERE x.a > y.a;
diff --git a/tests/fixtures/optimizer/isolate_table_selects.sql b/tests/fixtures/optimizer/isolate_table_selects.sql
index 93c0f7c..43540e8 100644
--- a/tests/fixtures/optimizer/isolate_table_selects.sql
+++ b/tests/fixtures/optimizer/isolate_table_selects.sql
@@ -4,20 +4,20 @@ SELECT * FROM (SELECT * FROM x AS x) AS x, (SELECT * FROM y AS y) AS y2;
 SELECT * FROM x AS x WHERE x = 1;
 SELECT * FROM x AS x WHERE x = 1;
 
-SELECT * FROM x AS x JOIN y AS y;
-SELECT * FROM (SELECT * FROM x AS x) AS x JOIN (SELECT * FROM y AS y) AS y;
+SELECT * FROM x AS x CROSS JOIN y AS y;
+SELECT * FROM (SELECT * FROM x AS x) AS x CROSS JOIN (SELECT * FROM y AS y) AS y;
 
-SELECT * FROM (SELECT 1) AS x JOIN y AS y;
-SELECT * FROM (SELECT 1) AS x JOIN (SELECT * FROM y AS y) AS y;
+SELECT * FROM (SELECT 1) AS x CROSS JOIN y AS y;
+SELECT * FROM (SELECT 1) AS x CROSS JOIN (SELECT * FROM y AS y) AS y;
 
 SELECT * FROM x AS x JOIN (SELECT * FROM y) AS y;
-SELECT * FROM (SELECT * FROM x AS x) AS x JOIN (SELECT * FROM y) AS y;
+SELECT * FROM (SELECT * FROM x AS x) AS x, (SELECT * FROM y) AS y;
 
 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 JOIN x AS z2) SELECT * FROM x AS x JOIN y as y;
-WITH y AS (SELECT * FROM (SELECT * FROM y AS y) AS y2 JOIN (SELECT * FROM x AS x) AS z2) SELECT * FROM (SELECT * FROM x AS x) AS x JOIN y AS y;
+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;
 
-SELECT * FROM x AS x JOIN xx AS y;
-SELECT * FROM (SELECT * FROM x AS x) AS x JOIN xx 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/merge_subqueries.sql b/tests/fixtures/optimizer/merge_subqueries.sql
index 4c06e42..1124a79 100644
--- a/tests/fixtures/optimizer/merge_subqueries.sql
+++ b/tests/fixtures/optimizer/merge_subqueries.sql
@@ -48,8 +48,8 @@ SELECT r.b FROM (SELECT b FROM x AS x) AS q JOIN (SELECT b FROM x) AS r ON q.b =
 SELECT x_2.b AS b FROM x AS x JOIN x AS x_2 ON x.b = x_2.b;
 
 # title: WHERE clause in joined derived table is merged to ON clause
-SELECT x.a, y.c FROM x JOIN (SELECT b, c FROM y WHERE c > 1) AS y ON x.b = y.b;
-SELECT x.a AS a, y.c AS c FROM x AS x JOIN y AS y ON x.b = y.b AND y.c > 1;
+SELECT x.a, y.c FROM x JOIN (SELECT b, c FROM y WHERE c > 1) AS y ON x.b = y.b ORDER BY x.a;
+SELECT x.a AS a, y.c AS c FROM x AS x JOIN y AS y ON x.b = y.b AND y.c > 1 ORDER BY x.a;
 
 # title: Comma JOIN in outer query
 SELECT x.a, y.c FROM (SELECT a FROM x) AS x, (SELECT c FROM y) AS y;
@@ -57,7 +57,7 @@ SELECT x.a AS a, y.c AS c FROM x AS x, y AS y;
 
 # title: Comma JOIN in inner query
 SELECT x.a, x.c FROM (SELECT x.a, z.c FROM x, y AS z) AS x;
-SELECT x.a AS a, z.c AS c FROM x AS x CROSS JOIN y AS z;
+SELECT x.a AS a, z.c AS c FROM x AS x, y AS z;
 
 # title: (Regression) Column in ORDER BY
 SELECT * FROM (SELECT * FROM (SELECT * FROM x)) ORDER BY a LIMIT 1;
diff --git a/tests/fixtures/optimizer/normalize.sql b/tests/fixtures/optimizer/normalize.sql
index 803a474..12bc388 100644
--- a/tests/fixtures/optimizer/normalize.sql
+++ b/tests/fixtures/optimizer/normalize.sql
@@ -42,3 +42,6 @@ A OR ((((B OR C) AND (B OR D)) OR C) AND (((B OR C) AND (B OR D)) OR D));
 
 SELECT * FROM x WHERE (A AND B) OR C;
 SELECT * FROM x WHERE (A OR C) AND (B OR C);
+
+dt2 between '2022-01-01 12:00:00' and '2022-12-31' and dt2 >= '2022-05-01 12:00:00' or dt2 = '2021-06-01 12:00:00';
+(dt2 <= '2022-12-31' OR dt2 = '2021-06-01 12:00:00') AND (dt2 = '2021-06-01 12:00:00' OR dt2 >= '2022-01-01 12:00:00') AND (dt2 = '2021-06-01 12:00:00' OR dt2 >= '2022-05-01 12:00:00')
diff --git a/tests/fixtures/optimizer/lower_identities.sql b/tests/fixtures/optimizer/normalize_identifiers.sql
index cea346f..ddb755f 100644
--- a/tests/fixtures/optimizer/lower_identities.sql
+++ b/tests/fixtures/optimizer/normalize_identifiers.sql
@@ -1,11 +1,19 @@
 SELECT a FROM x;
 SELECT a FROM x;
 
+# dialect: snowflake
+SELECT A FROM X;
+SELECT A FROM X;
+
 SELECT "A" FROM "X";
 SELECT "A" FROM "X";
 
 SELECT a AS A FROM x;
-SELECT a AS A FROM x;
+SELECT a AS a FROM x;
+
+# dialect: snowflake
+SELECT A AS a FROM X;
+SELECT A AS A FROM X;
 
 SELECT * FROM x;
 SELECT * FROM x;
@@ -13,29 +21,37 @@ SELECT * FROM x;
 SELECT A FROM x;
 SELECT a FROM x;
 
+# dialect: snowflake
+SELECT a FROM X;
+SELECT A FROM X;
+
 SELECT a FROM X;
 SELECT a FROM x;
 
+# dialect: snowflake
+SELECT A FROM x;
+SELECT A FROM X;
+
 SELECT A AS A FROM (SELECT a AS A FROM x);
-SELECT a AS A FROM (SELECT a AS a FROM x);
+SELECT a AS a FROM (SELECT a AS a FROM x);
 
 SELECT a AS B FROM x ORDER BY B;
-SELECT a AS B FROM x ORDER BY B;
+SELECT a AS b FROM x ORDER BY b;
 
 SELECT A FROM x ORDER BY A;
 SELECT a FROM x ORDER BY a;
 
 SELECT A AS B FROM X GROUP BY A HAVING SUM(B) > 0;
-SELECT a AS B FROM x GROUP BY a HAVING SUM(b) > 0;
+SELECT a AS b FROM x GROUP BY a HAVING SUM(b) > 0;
 
 SELECT A AS B, SUM(B) AS C FROM X GROUP BY A HAVING C > 0;
-SELECT a AS B, SUM(b) AS C FROM x GROUP BY a HAVING C > 0;
+SELECT a AS b, SUM(b) AS c FROM x GROUP BY a HAVING c > 0;
 
 SELECT A FROM X UNION SELECT A FROM X;
 SELECT a FROM x UNION SELECT a FROM x;
 
 SELECT A AS A FROM X UNION SELECT A AS A FROM X;
-SELECT a AS A FROM x UNION SELECT a AS A FROM x;
+SELECT a AS a FROM x UNION SELECT a AS a FROM x;
 
 (SELECT A AS A FROM X);
-(SELECT a AS A FROM x);
+(SELECT a AS a FROM x);
diff --git a/tests/fixtures/optimizer/optimize_joins.sql b/tests/fixtures/optimizer/optimize_joins.sql
index b64544e..b8e39c3 100644
--- a/tests/fixtures/optimizer/optimize_joins.sql
+++ b/tests/fixtures/optimizer/optimize_joins.sql
@@ -10,11 +10,23 @@ SELECT * FROM x JOIN z ON x.a = z.a AND TRUE JOIN y ON y.a = z.a;
 SELECT * FROM x LEFT JOIN y ON y.a = 1 JOIN z ON x.a = z.a AND y.a = z.a;
 SELECT * FROM x JOIN z ON x.a = z.a AND TRUE LEFT JOIN y ON y.a = 1 AND y.a = z.a;
 
-SELECT * FROM x INNER JOIN z;
-SELECT * FROM x JOIN z;
+SELECT * FROM x INNER JOIN z ON x.id = z.id;
+SELECT * FROM x JOIN z ON x.id = z.id;
 
 SELECT * FROM x LEFT OUTER JOIN z;
 SELECT * FROM x LEFT JOIN z;
 
 SELECT * FROM x CROSS JOIN z;
 SELECT * FROM x CROSS JOIN z;
+
+SELECT * FROM x JOIN z;
+SELECT * FROM x CROSS JOIN z;
+
+SELECT * FROM x NATURAL JOIN z;
+SELECT * FROM x NATURAL JOIN z;
+
+SELECT * FROM x RIGHT JOIN z;
+SELECT * FROM x RIGHT JOIN z;
+
+SELECT * FROM x JOIN z USING (id);
+SELECT * FROM x JOIN z USING (id);
diff --git a/tests/fixtures/optimizer/optimizer.sql b/tests/fixtures/optimizer/optimizer.sql
index 9e7880c..e0567d7 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"
-LEFT JOIN "_u_0" AS "_u_0"
-  ON "x"."b" = "_u_0"."_u_1"
 JOIN "y" AS "y"
   ON "x"."b" = "y"."b"
+LEFT JOIN "_u_0" AS "_u_0"
+  ON "x"."b" = "_u_0"."_u_1"
 WHERE
   "_u_0"."_col_0" >= 0 AND "x"."a" > 1
 GROUP BY
@@ -502,3 +502,120 @@ WHERE
   "unioned"."source_system" = 'bamboohr' OR "unioned"."source_system" = 'workday'
 QUALIFY
   ROW_NUMBER() OVER (PARTITION BY "unioned"."unique_filter_key" ORDER BY "unioned"."sort_order" DESC, 1) = 1;
+
+# title: pivoted source with explicit selections
+# execute: false
+SELECT * FROM (SELECT a, b, c FROM sc.tb) PIVOT (SUM(c) FOR b IN ('x','y','z'));
+SELECT
+  "_q_1"."a" AS "a",
+  "_q_1"."x" AS "x",
+  "_q_1"."y" AS "y",
+  "_q_1"."z" AS "z"
+FROM (
+  SELECT
+    "tb"."a" AS "a",
+    "tb"."b" AS "b",
+    "tb"."c" AS "c"
+  FROM "sc"."tb" AS "tb"
+) AS "_q_0" PIVOT(SUM("_q_0"."c") FOR "_q_0"."b" IN ('x', 'y', 'z')) AS "_q_1";
+
+# title: pivoted source with implicit selections
+# execute: false
+SELECT * FROM (SELECT * FROM u) PIVOT (SUM(f) FOR h IN ('x', 'y'));
+SELECT
+  "_q_1"."g" AS "g",
+  "_q_1"."x" AS "x",
+  "_q_1"."y" AS "y"
+FROM (
+  SELECT
+    "u"."f" AS "f",
+    "u"."g" AS "g",
+    "u"."h" AS "h"
+  FROM "u" AS "u"
+) AS "_q_0" PIVOT(SUM("_q_0"."f") FOR "_q_0"."h" IN ('x', 'y')) AS "_q_1";
+
+# title: selecting explicit qualified columns from pivoted source with explicit selections
+# execute: false
+SELECT piv.x, piv.y FROM (SELECT f, h FROM u) PIVOT (SUM(f) FOR h IN ('x', 'y')) AS piv;
+SELECT
+  "piv"."x" AS "x",
+  "piv"."y" AS "y"
+FROM (
+  SELECT
+    "u"."f" AS "f",
+    "u"."h" AS "h"
+  FROM "u" AS "u"
+) AS "_q_0" PIVOT(SUM("_q_0"."f") FOR "_q_0"."h" IN ('x', 'y')) AS "piv";
+
+# title: selecting explicit unqualified columns from pivoted source with implicit selections
+# execute: false
+SELECT x, y FROM u PIVOT (SUM(f) FOR h IN ('x', 'y'));
+SELECT
+  "_q_0"."x" AS "x",
+  "_q_0"."y" AS "y"
+FROM "u" AS "u" PIVOT(SUM("u"."f") FOR "u"."h" IN ('x', 'y')) AS "_q_0";
+
+# title: selecting all columns from a pivoted CTE source, using alias for the aggregation and generating bigquery
+# execute: false
+# dialect: bigquery
+WITH u_cte(f, g, h) AS (SELECT * FROM u) SELECT * FROM u_cte PIVOT(SUM(f) AS sum FOR h IN ('x', 'y'));
+WITH `u_cte` AS (
+  SELECT
+    `u`.`f` AS `f`,
+    `u`.`g` AS `g`,
+    `u`.`h` AS `h`
+  FROM `u` AS `u`
+)
+SELECT
+  `_q_0`.`g` AS `g`,
+  `_q_0`.`sum_x` AS `sum_x`,
+  `_q_0`.`sum_y` AS `sum_y`
+FROM `u_cte` AS `u_cte` PIVOT(SUM(`u_cte`.`f`) AS `sum` FOR `u_cte`.`h` IN ('x', 'y')) AS `_q_0`;
+
+# title: selecting all columns from a pivoted source and generating snowflake
+# execute: false
+# dialect: snowflake
+SELECT * FROM u PIVOT (SUM(f) FOR h IN ('x', 'y'));
+SELECT
+  "_q_0"."G" AS "G",
+  "_q_0"."'x'" AS "'x'",
+  "_q_0"."'y'" AS "'y'"
+FROM "U" AS "U" PIVOT(SUM("U"."F") FOR "U"."H" IN ('x', 'y')) AS "_q_0"
+;
+
+# title: selecting all columns from a pivoted source and generating spark
+# note: spark doesn't allow pivot aliases or qualified columns for the pivot's "field" (`h`)
+# execute: false
+# dialect: spark
+SELECT * FROM u PIVOT (SUM(f) FOR h IN ('x', 'y'));
+SELECT
+  `_q_0`.`g` AS `g`,
+  `_q_0`.`x` AS `x`,
+  `_q_0`.`y` AS `y`
+FROM (
+  SELECT
+    *
+  FROM `u` AS `u` PIVOT(SUM(`u`.`f`) FOR `h` IN ('x', 'y'))
+) AS `_q_0`;
+
+# title: quoting is maintained
+# dialect: snowflake
+with cte1("id", foo) as (select 1, 2) select "id" from cte1;
+WITH "CTE1" AS (
+  SELECT
+    1 AS "id"
+)
+SELECT
+  "CTE1"."id" AS "id"
+FROM "CTE1";
+
+# title: ensures proper quoting happens after all optimizations
+# execute: false
+SELECT "foO".x FROM (SELECT 1 AS x) AS "foO";
+WITH "foO" AS (
+  SELECT
+    1 AS "x"
+)
+SELECT
+  "foO"."x" AS "x"
+FROM "foO" AS "foO";
diff --git a/tests/fixtures/optimizer/pushdown_predicates.sql b/tests/fixtures/optimizer/pushdown_predicates.sql
index dd318a2..83a353d 100644
--- a/tests/fixtures/optimizer/pushdown_predicates.sql
+++ b/tests/fixtures/optimizer/pushdown_predicates.sql
@@ -4,8 +4,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
 WITH x AS (SELECT y.a FROM y) SELECT * FROM x WHERE x.a = 1;
 WITH x AS (SELECT y.a FROM y WHERE y.a = 1) SELECT * FROM x WHERE TRUE;
 
-SELECT x.a FROM (SELECT * FROM x) AS x JOIN y WHERE y.a = 1 OR (x.a = 1 AND x.b = 1);
-SELECT x.a FROM (SELECT * FROM x) AS x JOIN y WHERE (x.a = 1 AND x.b = 1) OR y.a = 1;
+SELECT x.a FROM (SELECT * FROM x) AS x CROSS JOIN y WHERE y.a = 1 OR (x.a = 1 AND x.b = 1);
+SELECT x.a FROM (SELECT * FROM x) AS x CROSS JOIN y WHERE (x.a = 1 AND x.b = 1) OR y.a = 1;
 
 SELECT x.a FROM (SELECT * FROM x) AS x JOIN y WHERE (x.a = y.a AND x.a = 1 AND x.b = 1) OR x.a = y.a;
 SELECT x.a FROM (SELECT * FROM x) AS x JOIN y ON x.a = y.a WHERE TRUE;
diff --git a/tests/fixtures/optimizer/pushdown_projections.sql b/tests/fixtures/optimizer/pushdown_projections.sql
index 6ff9383..0821339 100644
--- a/tests/fixtures/optimizer/pushdown_projections.sql
+++ b/tests/fixtures/optimizer/pushdown_projections.sql
@@ -61,9 +61,13 @@ SELECT q.x AS x FROM (VALUES (1, 2)) AS q(x, y);
 SELECT i.a FROM x AS i LEFT JOIN (SELECT a, b FROM (SELECT a, b FROM x)) AS j ON i.a = j.a;
 SELECT i.a AS a FROM x AS i LEFT JOIN (SELECT _q_0.a AS a FROM (SELECT x.a AS a FROM x AS x) AS _q_0) AS j ON i.a = j.a;
 
+WITH cte AS (SELECT source.a AS a, ROW_NUMBER() OVER (PARTITION BY source.id, source.timestamp ORDER BY source.a DESC) AS index FROM source AS source QUALIFY index) SELECT cte.a AS a FROM cte;
+WITH cte AS (SELECT source.a AS a FROM source AS source QUALIFY ROW_NUMBER() OVER (PARTITION BY source.id, source.timestamp ORDER BY source.a DESC)) SELECT cte.a AS a FROM cte;
+
 --------------------------------------
 -- Unknown Star Expansion
 --------------------------------------
+
 SELECT a FROM (SELECT * FROM zz) WHERE b = 1;
 SELECT _q_0.a AS a FROM (SELECT zz.a AS a, zz.b AS b FROM zz AS zz) AS _q_0 WHERE _q_0.b = 1;
 
diff --git a/tests/fixtures/optimizer/qualify_columns.sql b/tests/fixtures/optimizer/qualify_columns.sql
index f077647..7be2c7f 100644
--- a/tests/fixtures/optimizer/qualify_columns.sql
+++ b/tests/fixtures/optimizer/qualify_columns.sql
@@ -5,7 +5,7 @@ SELECT a FROM x;
 SELECT x.a AS a FROM x AS x;
 
 SELECT "a" FROM x;
-SELECT x."a" AS "a" FROM x AS x;
+SELECT x.a AS a FROM x AS x;
 
 # execute: false
 SELECT a FROM zz GROUP BY a ORDER BY a;
@@ -396,8 +396,39 @@ SELECT x.a + 1 AS a, ROW_NUMBER() OVER (PARTITION BY x.b ORDER BY x.a) AS row_nu
 
 # dialect: bigquery
 SELECT ROW_NUMBER() OVER (PARTITION BY a ORDER BY b) AS row_num FROM x QUALIFY row_num = 1;
-SELECT ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.b) AS row_num FROM x AS x QUALIFY row_num = 1;
+SELECT ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.b) AS row_num FROM x AS x QUALIFY ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.b) = 1;
 
 # dialect: bigquery
 SELECT x.b, x.a FROM x LEFT JOIN y ON x.b = y.b QUALIFY ROW_NUMBER() OVER(PARTITION BY x.b ORDER BY x.a DESC) = 1;
 SELECT x.b AS b, x.a AS a FROM x AS x LEFT JOIN y AS y ON x.b = y.b QUALIFY ROW_NUMBER() OVER (PARTITION BY x.b ORDER BY x.a DESC) = 1;
+
+SELECT * FROM x QUALIFY COUNT(a) OVER (PARTITION BY b) > 1;
+SELECT x.a AS a, x.b AS b FROM x AS x QUALIFY COUNT(x.a) OVER (PARTITION BY x.b) > 1;
+
+--------------------------------------
+-- Expand laterals
+--------------------------------------
+
+# title: expand alias reference
+SELECT
+  x.a + 1 AS i,
+  i + 1 AS j,
+  j + 1 AS k
+FROM x;
+SELECT x.a + 1 AS i, x.a + 1 + 1 AS j, x.a + 1 + 1 + 1 AS k FROM x AS x;
+
+# title: noop - reference comes before alias
+# execute: false
+SELECT i + 1 AS j, x.a + 1 AS i FROM x;
+SELECT i + 1 AS j, x.a + 1 AS i FROM x AS x;
+
+# title: subquery
+SELECT
+  *
+FROM (
+  SELECT
+    x.a + 1 AS i,
+    i + 1 AS j
+  FROM x
+);
+SELECT _q_0.i AS i, _q_0.j AS j FROM (SELECT x.a + 1 AS i, x.a + 1 + 1 AS j FROM x AS x) AS _q_0;
diff --git a/tests/fixtures/optimizer/qualify_columns__invalid.sql b/tests/fixtures/optimizer/qualify_columns__invalid.sql
index f0f9f87..d2d4959 100644
--- a/tests/fixtures/optimizer/qualify_columns__invalid.sql
+++ b/tests/fixtures/optimizer/qualify_columns__invalid.sql
@@ -1,7 +1,7 @@
 SELECT z.a FROM x;
 SELECT z.* FROM x;
 SELECT x FROM x;
-INSERT INTO x VALUES (1, 2);
+SELECT x FROM VALUES (1, 2);
 SELECT a FROM x AS z JOIN y AS z;
 SELECT a FROM x JOIN (SELECT b FROM y WHERE y.b = x.c);
 SELECT a FROM x AS y JOIN (SELECT a FROM y) AS q ON y.a = q.a;
diff --git a/tests/fixtures/optimizer/qualify_columns__with_invisible.sql b/tests/fixtures/optimizer/qualify_columns__with_invisible.sql
index 05253f3..1e07015 100644
--- a/tests/fixtures/optimizer/qualify_columns__with_invisible.sql
+++ b/tests/fixtures/optimizer/qualify_columns__with_invisible.sql
@@ -10,11 +10,11 @@ SELECT x.b AS b FROM x AS x;
 --------------------------------------
 -- Derived tables
 --------------------------------------
-SELECT x.a FROM x AS x JOIN (SELECT * FROM x);
-SELECT x.a AS a FROM x AS x JOIN (SELECT x.a AS a FROM x AS x) AS _q_0;
+SELECT x.a FROM x AS x CROSS JOIN (SELECT * FROM x);
+SELECT x.a AS a FROM x AS x CROSS JOIN (SELECT x.a AS a FROM x AS x) AS _q_0;
 
-SELECT x.b FROM x AS x JOIN (SELECT b FROM x);
-SELECT x.b AS b FROM x AS x JOIN (SELECT x.b AS b FROM x AS x) AS _q_0;
+SELECT x.b FROM x AS x CROSS JOIN (SELECT b FROM x);
+SELECT x.b AS b FROM x AS x CROSS JOIN (SELECT x.b AS b FROM x AS x) AS _q_0;
 
 --------------------------------------
 -- Expand *
@@ -22,11 +22,11 @@ SELECT x.b AS b FROM x AS x JOIN (SELECT x.b AS b FROM x AS x) AS _q_0;
 SELECT * FROM x;
 SELECT x.a AS a FROM x AS x;
 
-SELECT * FROM y JOIN z ON y.b = z.b;
-SELECT y.b AS b, z.b AS b FROM y AS y JOIN z AS z ON y.b = z.b;
+SELECT * FROM y CROSS JOIN z ON y.b = z.b;
+SELECT y.b AS b, z.b AS b FROM y AS y CROSS JOIN z AS z ON y.b = z.b;
 
-SELECT * FROM y JOIN z ON y.c = z.c;
-SELECT y.b AS b, z.b AS b FROM y AS y JOIN z AS z ON y.c = z.c;
+SELECT * FROM y CROSS JOIN z ON y.c = z.c;
+SELECT y.b AS b, z.b AS b FROM y AS y CROSS JOIN z AS z ON y.c = z.c;
 
 SELECT a FROM (SELECT * FROM x);
 SELECT _q_0.a AS a FROM (SELECT x.a AS a FROM x AS x) AS _q_0;
diff --git a/tests/fixtures/optimizer/qualify_tables.sql b/tests/fixtures/optimizer/qualify_tables.sql
index 0ad155a..24d1b65 100644
--- a/tests/fixtures/optimizer/qualify_tables.sql
+++ b/tests/fixtures/optimizer/qualify_tables.sql
@@ -16,9 +16,12 @@ WITH a AS (SELECT 1 FROM c.db.z AS z) SELECT 1 FROM a;
 SELECT (SELECT y.c FROM y AS y) FROM x;
 SELECT (SELECT y.c FROM c.db.y AS y) FROM c.db.x AS x;
 
--------------------------
+SELECT * FROM x PIVOT (SUM(a) FOR b IN ('a', 'b'));
+SELECT * FROM c.db.x AS x PIVOT(SUM(a) FOR b IN ('a', 'b')) AS _q_0;
+
+----------------------------
 -- Expand join constructs
--------------------------
+----------------------------
 
 -- This is valid in Trino, so we treat the (tbl AS tbl) as a "join construct" per postgres' terminology.
 SELECT * FROM (tbl AS tbl) AS _q_0;
diff --git a/tests/fixtures/optimizer/simplify.sql b/tests/fixtures/optimizer/simplify.sql
index a2cd859..5c8d371 100644
--- a/tests/fixtures/optimizer/simplify.sql
+++ b/tests/fixtures/optimizer/simplify.sql
@@ -201,6 +201,21 @@ A AND B AND C AND D;
 (((((A) AND B)) AND C)) AND D;
 A AND B AND C AND D;
 
+(x + 1) + 2;
+x + 3;
+
+x + (1 + 2);
+x + 3;
+
+(x * 2) * 4 + (1 + 3) + 5;
+x * 8 + 9;
+
+(x - 1) - 2;
+(x - 1) - 2;
+
+x - (3 - 2);
+x - 1;
+
 --------------------------------------
 -- Comparison and Pruning
 --------------------------------------
@@ -574,4 +589,4 @@ x > 3;
 TRUE;
 
 x = 2018 OR x <> 2018;
-x <> 2018 OR x = 2018;
\ No newline at end of file
+x <> 2018 OR x = 2018;
diff --git a/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql b/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql
index 9908756..a6ee325 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"
-LEFT JOIN "_u_0" AS "_u_0"
-  ON "ctr1"."ctr_store_sk" = "_u_0"."_u_1"
 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"
 WHERE
   "ctr1"."ctr_total_return" > "_u_0"."_col_0"
 ORDER BY
@@ -211,9 +211,8 @@ JOIN "date_dim" AS "date_dim"
   ON "date_dim"."d_week_seq" = "wswscs"."d_week_seq" AND "date_dim"."d_year" = 1998
 JOIN "wswscs" AS "wswscs_2"
   ON "wswscs"."d_week_seq" = "wswscs_2"."d_week_seq" - 53
-CROSS JOIN "date_dim" AS "date_dim_2"
-WHERE
-  "date_dim_2"."d_week_seq" = "wswscs_2"."d_week_seq" AND "date_dim_2"."d_year" = 1999
+JOIN "date_dim" AS "date_dim_2"
+  ON "date_dim_2"."d_week_seq" = "wswscs_2"."d_week_seq" AND "date_dim_2"."d_year" = 1999
 ORDER BY
   "d_week_seq1";
 
@@ -953,13 +952,13 @@ 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"
 LEFT JOIN "_u_1" AS "_u_1"
   ON "_u_1"."_u_2" = "item"."i_category"
-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"
 WHERE
   "item"."i_current_price" > 1.2 * "_u_1"."_col_0"
 GROUP BY
@@ -1256,9 +1255,9 @@ LIMIT 100;
 WITH "a1" AS (
   SELECT
     SUBSTR("customer_address"."ca_zip", 1, 5) AS "ca_zip"
-  FROM "customer_address" AS "customer_address", "customer" AS "customer"
-  WHERE
-    "customer"."c_preferred_cust_flag" = 'Y'
+  FROM "customer_address" AS "customer_address"
+  JOIN "customer" AS "customer"
+    ON "customer"."c_preferred_cust_flag" = 'Y'
     AND "customer_address"."ca_address_sk" = "customer"."c_current_addr_sk"
   GROUP BY
     "customer_address"."ca_zip"
@@ -1612,17 +1611,17 @@ SELECT
   "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"
 LEFT JOIN "_u_0" AS "_u_0"
   ON "customer"."c_customer_sk" = "_u_0"."_u_1"
 LEFT JOIN "_u_2" AS "_u_2"
   ON "customer"."c_customer_sk" = "_u_2"."_u_3"
 LEFT JOIN "_u_4" AS "_u_4"
   ON "customer"."c_customer_sk" = "_u_4"."_u_5"
-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"
 WHERE
   NOT "_u_0"."_u_1" IS NULL
   AND (
@@ -2179,33 +2178,30 @@ WITH "item_2" AS (
     "ics"."i_class_id" AS "i_class_id",
     "ics"."i_category_id" AS "i_category_id"
   FROM "catalog_sales" AS "catalog_sales"
-  CROSS JOIN "item_2" AS "ics"
-  CROSS JOIN "d1" AS "d2"
-  WHERE
-    "catalog_sales"."cs_item_sk" = "ics"."i_item_sk"
-    AND "catalog_sales"."cs_sold_date_sk" = "d2"."d_date_sk"
+  JOIN "item_2" AS "ics"
+    ON "catalog_sales"."cs_item_sk" = "ics"."i_item_sk"
+  JOIN "d1" AS "d2"
+    ON "catalog_sales"."cs_sold_date_sk" = "d2"."d_date_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"
-  CROSS JOIN "item_2" AS "iws"
-  CROSS JOIN "d1" AS "d3"
-  WHERE
-    "web_sales"."ws_item_sk" = "iws"."i_item_sk"
-    AND "web_sales"."ws_sold_date_sk" = "d3"."d_date_sk"
+  JOIN "item_2" 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"
-  CROSS JOIN "item_2" AS "iss"
-  CROSS JOIN "d1" AS "d1"
-  WHERE
-    "store_sales"."ss_item_sk" = "iss"."i_item_sk"
-    AND "store_sales"."ss_sold_date_sk" = "d1"."d_date_sk"
+  JOIN "item_2" 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",
@@ -2242,6 +2238,14 @@ WITH "item_2" AS (
   SELECT
     AVG("x"."quantity" * "x"."list_price") AS "average_sales"
   FROM "x" AS "x"
+), "date_dim_2" 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
 ), "_u_0" AS (
   SELECT
     "item"."i_item_sk" AS "ss_item_sk"
@@ -2256,14 +2260,6 @@ WITH "item_2" AS (
   SELECT
     "avg_sales"."average_sales" AS "average_sales"
   FROM "avg_sales"
-), "date_dim_2" 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
 ), "cte_9" AS (
   SELECT
     'store' AS "channel",
@@ -2273,13 +2269,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"
-  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 "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"
+  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"
   WHERE
     NOT "_u_0"."ss_item_sk" IS NULL
   GROUP BY
@@ -2297,13 +2293,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"
-  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 "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"
+  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"
   WHERE
     NOT "_u_2"."ss_item_sk" IS NULL
   GROUP BY
@@ -2321,13 +2317,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"
-  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 "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"
+  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"
   WHERE
     NOT "_u_4"."ss_item_sk" IS NULL
   GROUP BY
@@ -2492,10 +2488,6 @@ SELECT
   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"
-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"
 JOIN "date_dim" AS "date_dim"
   ON "catalog_sales"."cs_ship_date_sk" = "date_dim"."d_date_sk"
   AND "date_dim"."d_date" >= '2002-3-01'
@@ -2508,6 +2500,10 @@ JOIN "customer_address" AS "customer_address"
 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")
@@ -3012,13 +3008,21 @@ 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"
@@ -3031,36 +3035,28 @@ 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
 )
@@ -3717,13 +3713,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
@@ -3957,10 +3953,10 @@ SELECT
 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
-LEFT JOIN "_u_0" AS "_u_0"
-  ON "_u_0"."_u_1" = "item"."i_item_sk"
 JOIN "date_dim_2" AS "date_dim"
   ON "date_dim"."d_date_sk" = "catalog_sales"."cs_sold_date_sk"
+LEFT JOIN "_u_0" AS "_u_0"
+  ON "_u_0"."_u_1" = "item"."i_item_sk"
 WHERE
   "catalog_sales"."cs_ext_discount_amt" > "_u_0"."_col_0"
 LIMIT 100;
@@ -4032,20 +4028,7 @@ FROM   (SELECT *
 GROUP  BY i_manufact_id
 ORDER  BY total_sales
 LIMIT 100;
-WITH "item_2" AS (
-  SELECT
-    "item"."i_item_sk" AS "i_item_sk",
-    "item"."i_manufact_id" AS "i_manufact_id"
-  FROM "item" AS "item"
-), "_u_0" AS (
-  SELECT
-    "item"."i_manufact_id" AS "i_manufact_id"
-  FROM "item" AS "item"
-  WHERE
-    "item"."i_category" IN ('Books')
-  GROUP BY
-    "item"."i_manufact_id"
-), "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",
@@ -4060,19 +4043,32 @@ WITH "item_2" AS (
   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",
+    "item"."i_manufact_id" AS "i_manufact_id"
+  FROM "item" AS "item"
+), "_u_0" AS (
+  SELECT
+    "item"."i_manufact_id" AS "i_manufact_id"
+  FROM "item" AS "item"
+  WHERE
+    "item"."i_category" IN ('Books')
+  GROUP BY
+    "item"."i_manufact_id"
 ), "ss" AS (
   SELECT
     "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 "item_2" AS "item"
-    ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
-  LEFT JOIN "_u_0" AS "_u_0"
-    ON "item"."i_manufact_id" = "_u_0"."i_manufact_id"
   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 "item_2" AS "item"
+    ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
+  LEFT JOIN "_u_0" AS "_u_0"
+    ON "item"."i_manufact_id" = "_u_0"."i_manufact_id"
   WHERE
     NOT "_u_0"."i_manufact_id" IS NULL
   GROUP BY
@@ -4082,14 +4078,14 @@ WITH "item_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 "item_2" AS "item"
-    ON "catalog_sales"."cs_item_sk" = "item"."i_item_sk"
-  LEFT JOIN "_u_0" AS "_u_1"
-    ON "item"."i_manufact_id" = "_u_1"."i_manufact_id"
   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 "item_2" AS "item"
+    ON "catalog_sales"."cs_item_sk" = "item"."i_item_sk"
+  LEFT JOIN "_u_0" AS "_u_1"
+    ON "item"."i_manufact_id" = "_u_1"."i_manufact_id"
   WHERE
     NOT "_u_1"."i_manufact_id" IS NULL
   GROUP BY
@@ -4099,14 +4095,14 @@ WITH "item_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 "item_2" AS "item"
-    ON "web_sales"."ws_item_sk" = "item"."i_item_sk"
-  LEFT JOIN "_u_0" AS "_u_2"
-    ON "item"."i_manufact_id" = "_u_2"."i_manufact_id"
   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 "item_2" AS "item"
+    ON "web_sales"."ws_item_sk" = "item"."i_item_sk"
+  LEFT JOIN "_u_0" AS "_u_2"
+    ON "item"."i_manufact_id" = "_u_2"."i_manufact_id"
   WHERE
     NOT "_u_2"."i_manufact_id" IS NULL
   GROUP BY
@@ -4382,16 +4378,16 @@ SELECT
   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"
 LEFT JOIN "_u_0" AS "_u_0"
   ON "customer"."c_customer_sk" = "_u_0"."_u_1"
 LEFT JOIN "_u_2" AS "_u_2"
   ON "customer"."c_customer_sk" = "_u_2"."_u_3"
 LEFT JOIN "_u_4" AS "_u_4"
   ON "customer"."c_customer_sk" = "_u_4"."_u_5"
-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"
 WHERE
   NOT "_u_0"."_u_1" IS NULL
   AND (
@@ -4468,7 +4464,7 @@ ROLLUP (
 )
 ORDER BY
   "lochierarchy" DESC,
-  CASE WHEN "lochierarchy" = 0 THEN "item"."i_category" END,
+  CASE WHEN "lochierarchy" = 0 THEN "i_category" END,
   "rank_within_parent"
 LIMIT 100;
 
@@ -5349,10 +5345,6 @@ SELECT
   "customer_address"."ca_state" AS "ca_state",
   SUM("web_sales"."ws_sales_price") AS "_col_2"
 FROM "web_sales" AS "web_sales"
-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" AS "customer"
   ON "web_sales"."ws_bill_customer_sk" = "customer"."c_customer_sk"
 JOIN "customer_address" AS "customer_address"
@@ -5361,6 +5353,10 @@ JOIN "date_dim" AS "date_dim"
   ON "date_dim"."d_qoy" = 1
   AND "date_dim"."d_year" = 2000
   AND "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk"
+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"
 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')
@@ -6145,7 +6141,7 @@ WITH web_v1 AS
 (
          SELECT   ws_item_sk item_sk,
                   d_date,
-                  sum(Sum(ws_sales_price)) OVER (partition BY ws_item_sk ORDER BY d_date rows BETWEEN UNBOUNDED PRECEDING AND      CURRENT ROW) cume_sales
+                  sum(Sum(ws_sales_price)) OVER (partition BY ws_item_sk ORDER BY d_date rows BETWEEN UNBOUNDED PRECEDING AND      CURRENT row) cume_sales
          FROM     web_sales ,
                   date_dim
          WHERE    ws_sold_date_sk=d_date_sk
@@ -6156,7 +6152,7 @@ WITH web_v1 AS
 (
          SELECT   ss_item_sk item_sk,
                   d_date,
-                  sum(sum(ss_sales_price)) OVER (partition BY ss_item_sk ORDER BY d_date rows BETWEEN UNBOUNDED PRECEDING AND      CURRENT ROW) cume_sales
+                  sum(sum(ss_sales_price)) OVER (partition BY ss_item_sk ORDER BY d_date rows BETWEEN UNBOUNDED PRECEDING AND      CURRENT row) cume_sales
          FROM     store_sales ,
                   date_dim
          WHERE    ss_sold_date_sk=d_date_sk
@@ -6171,8 +6167,8 @@ FROM     (
                            d_date ,
                            web_sales ,
                            store_sales ,
-                           max(web_sales) OVER (partition BY item_sk ORDER BY d_date rows BETWEEN UNBOUNDED PRECEDING AND      CURRENT ROW)   web_cumulative ,
-                           max(store_sales) OVER (partition BY item_sk ORDER BY d_date rows BETWEEN UNBOUNDED PRECEDING AND      CURRENT ROW) store_cumulative
+                           max(web_sales) OVER (partition BY item_sk ORDER BY d_date rows BETWEEN UNBOUNDED PRECEDING AND      CURRENT row)   web_cumulative ,
+                           max(store_sales) OVER (partition BY item_sk ORDER BY d_date rows BETWEEN UNBOUNDED PRECEDING AND      CURRENT row) store_cumulative
                   FROM     (
                                            SELECT
                                                            CASE
@@ -6533,10 +6529,6 @@ WITH "cs_or_ws_sales" AS (
     SUM("store_sales"."ss_ext_sales_price") AS "revenue"
   FROM "my_customers"
   CROSS JOIN "date_dim" AS "date_dim"
-  JOIN "_u_0" AS "_u_0"
-    ON "date_dim"."d_month_seq" >= "_u_0"."_col_0"
-  JOIN "_u_1" AS "_u_1"
-    ON "date_dim"."d_month_seq" <= "_u_1"."_col_0"
   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"
@@ -6545,6 +6537,10 @@ WITH "cs_or_ws_sales" AS (
   JOIN "store" AS "store"
     ON "customer_address"."ca_county" = "store"."s_county"
     AND "customer_address"."ca_state" = "store"."s_state"
+  JOIN "_u_0" AS "_u_0"
+    ON "date_dim"."d_month_seq" >= "_u_0"."_col_0"
+  JOIN "_u_1" AS "_u_1"
+    ON "date_dim"."d_month_seq" <= "_u_1"."_col_0"
   GROUP BY
     "my_customers"."c_customer_sk"
 )
@@ -6674,20 +6670,7 @@ FROM   (SELECT *
 GROUP  BY i_item_id
 ORDER  BY total_sales
 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"
-), "_u_0" AS (
-  SELECT
-    "item"."i_item_id" AS "i_item_id"
-  FROM "item" AS "item"
-  WHERE
-    "item"."i_color" IN ('firebrick', 'rosy', 'white')
-  GROUP BY
-    "item"."i_item_id"
-), "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",
@@ -6702,19 +6685,32 @@ WITH "item_2" AS (
   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",
+    "item"."i_item_id" AS "i_item_id"
+  FROM "item" AS "item"
+), "_u_0" AS (
+  SELECT
+    "item"."i_item_id" AS "i_item_id"
+  FROM "item" AS "item"
+  WHERE
+    "item"."i_color" IN ('firebrick', 'rosy', 'white')
+  GROUP BY
+    "item"."i_item_id"
 ), "ss" AS (
   SELECT
     "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 "item_2" AS "item"
-    ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
-  LEFT JOIN "_u_0" AS "_u_0"
-    ON "item"."i_item_id" = "_u_0"."i_item_id"
   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 "item_2" AS "item"
+    ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
+  LEFT JOIN "_u_0" AS "_u_0"
+    ON "item"."i_item_id" = "_u_0"."i_item_id"
   WHERE
     NOT "_u_0"."i_item_id" IS NULL
   GROUP BY
@@ -6724,14 +6720,14 @@ WITH "item_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 "item_2" AS "item"
-    ON "catalog_sales"."cs_item_sk" = "item"."i_item_sk"
-  LEFT JOIN "_u_0" AS "_u_1"
-    ON "item"."i_item_id" = "_u_1"."i_item_id"
   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 "item_2" AS "item"
+    ON "catalog_sales"."cs_item_sk" = "item"."i_item_sk"
+  LEFT JOIN "_u_0" AS "_u_1"
+    ON "item"."i_item_id" = "_u_1"."i_item_id"
   WHERE
     NOT "_u_1"."i_item_id" IS NULL
   GROUP BY
@@ -6741,14 +6737,14 @@ WITH "item_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 "item_2" AS "item"
-    ON "web_sales"."ws_item_sk" = "item"."i_item_sk"
-  LEFT JOIN "_u_0" AS "_u_2"
-    ON "item"."i_item_id" = "_u_2"."i_item_id"
   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 "item_2" AS "item"
+    ON "web_sales"."ws_item_sk" = "item"."i_item_sk"
+  LEFT JOIN "_u_0" AS "_u_2"
+    ON "item"."i_item_id" = "_u_2"."i_item_id"
   WHERE
     NOT "_u_2"."i_item_id" IS NULL
   GROUP BY
@@ -7075,12 +7071,13 @@ WITH "wss" AS (
     "wss"."thu_sales" AS "thu_sales2",
     "wss"."fri_sales" AS "fri_sales2",
     "wss"."sat_sales" AS "sat_sales2"
-  FROM "wss", "store" AS "store", "date_dim" AS "date_dim"
-  WHERE
-    "date_dim"."d_month_seq" <= 1219
+  FROM "wss"
+  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"
-    AND "wss"."ss_store_sk" = "store"."s_store_sk"
 )
 SELECT
   "store"."s_store_name" AS "s_store_name1",
@@ -7177,20 +7174,7 @@ GROUP  BY i_item_id
 ORDER  BY i_item_id,
           total_sales
 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"
-), "_u_0" AS (
-  SELECT
-    "item"."i_item_id" AS "i_item_id"
-  FROM "item" AS "item"
-  WHERE
-    "item"."i_category" IN ('Jewelry')
-  GROUP BY
-    "item"."i_item_id"
-), "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",
@@ -7205,19 +7189,32 @@ WITH "item_2" AS (
   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",
+    "item"."i_item_id" AS "i_item_id"
+  FROM "item" AS "item"
+), "_u_0" AS (
+  SELECT
+    "item"."i_item_id" AS "i_item_id"
+  FROM "item" AS "item"
+  WHERE
+    "item"."i_category" IN ('Jewelry')
+  GROUP BY
+    "item"."i_item_id"
 ), "ss" AS (
   SELECT
     "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 "item_2" AS "item"
-    ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
-  LEFT JOIN "_u_0" AS "_u_0"
-    ON "item"."i_item_id" = "_u_0"."i_item_id"
   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 "item_2" AS "item"
+    ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
+  LEFT JOIN "_u_0" AS "_u_0"
+    ON "item"."i_item_id" = "_u_0"."i_item_id"
   WHERE
     NOT "_u_0"."i_item_id" IS NULL
   GROUP BY
@@ -7227,14 +7224,14 @@ WITH "item_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 "item_2" AS "item"
-    ON "catalog_sales"."cs_item_sk" = "item"."i_item_sk"
-  LEFT JOIN "_u_0" AS "_u_1"
-    ON "item"."i_item_id" = "_u_1"."i_item_id"
   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 "item_2" AS "item"
+    ON "catalog_sales"."cs_item_sk" = "item"."i_item_sk"
+  LEFT JOIN "_u_0" AS "_u_1"
+    ON "item"."i_item_id" = "_u_1"."i_item_id"
   WHERE
     NOT "_u_1"."i_item_id" IS NULL
   GROUP BY
@@ -7244,14 +7241,14 @@ WITH "item_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 "item_2" AS "item"
-    ON "web_sales"."ws_item_sk" = "item"."i_item_sk"
-  LEFT JOIN "_u_0" AS "_u_2"
-    ON "item"."i_item_id" = "_u_2"."i_item_id"
   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 "item_2" AS "item"
+    ON "web_sales"."ws_item_sk" = "item"."i_item_sk"
+  LEFT JOIN "_u_0" AS "_u_2"
+    ON "item"."i_item_id" = "_u_2"."i_item_id"
   WHERE
     NOT "_u_2"."i_item_id" IS NULL
   GROUP BY
@@ -7395,13 +7392,17 @@ WITH "store_2" AS (
 ), "all_sales" AS (
   SELECT
     SUM("store_sales"."ss_ext_sales_price") AS "total"
-  FROM "store_sales" AS "store_sales", "store_2" AS "store", "date_dim_2" AS "date_dim", "customer_2" AS "customer", "customer_address_2" AS "customer_address", "item_2" AS "item"
-  WHERE
-    "customer_address"."ca_address_sk" = "customer"."c_current_addr_sk"
-    AND "store_sales"."ss_customer_sk" = "customer"."c_customer_sk"
-    AND "store_sales"."ss_item_sk" = "item"."i_item_sk"
-    AND "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
-    AND "store_sales"."ss_store_sk" = "store"."s_store_sk"
+  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 "item_2" AS "item"
+    ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
 )
 SELECT
   "promotional_sales"."promotions" AS "promotions",
@@ -9111,17 +9112,17 @@ SELECT
   "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"
 LEFT JOIN "_u_0" AS "_u_0"
   ON "customer"."c_customer_sk" = "_u_0"."_u_1"
 LEFT JOIN "_u_2" AS "_u_2"
   ON "customer"."c_customer_sk" = "_u_2"."_u_3"
 LEFT JOIN "_u_4" AS "_u_4"
   ON "customer"."c_customer_sk" = "_u_4"."_u_5"
-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"
 WHERE
   "_u_2"."_u_3" IS NULL AND "_u_4"."_u_5" IS NULL AND NOT "_u_0"."_u_1" IS NULL
 GROUP BY
@@ -9186,7 +9187,7 @@ 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"
-), "date_dim_2" AS (
+), "d1" AS (
   SELECT
     "date_dim"."d_date_sk" AS "d_date_sk",
     "date_dim"."d_month_seq" AS "d_month_seq"
@@ -9200,7 +9201,7 @@ WITH "store_sales_2" AS (
   FROM "store_sales_2" AS "store_sales"
   JOIN "store" AS "store"
     ON "store"."s_store_sk" = "store_sales"."ss_store_sk"
-  JOIN "date_dim_2" AS "date_dim"
+  JOIN "d1" AS "date_dim"
     ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk"
   GROUP BY
     "store"."s_state"
@@ -9220,12 +9221,12 @@ 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"
+  ON "d1"."d_date_sk" = "store_sales"."ss_sold_date_sk"
 JOIN "store" AS "store"
   ON "store"."s_store_sk" = "store_sales"."ss_store_sk"
 LEFT JOIN "_u_0" AS "_u_0"
   ON "store"."s_state" = "_u_0"."s_state"
-JOIN "date_dim_2" AS "d1"
-  ON "d1"."d_date_sk" = "store_sales"."ss_sold_date_sk"
 WHERE
   NOT "_u_0"."s_state" IS NULL
 GROUP BY
@@ -9235,7 +9236,7 @@ ROLLUP (
 )
 ORDER BY
   "lochierarchy" DESC,
-  CASE WHEN "lochierarchy" = 0 THEN "store"."s_state" END,
+  CASE WHEN "lochierarchy" = 0 THEN "s_state" END,
   "rank_within_parent"
 LIMIT 100;
 
@@ -9303,27 +9304,24 @@ WITH "date_dim_2" AS (
     "catalog_sales"."cs_item_sk" AS "sold_item_sk",
     "catalog_sales"."cs_sold_time_sk" AS "time_sk"
   FROM "catalog_sales" AS "catalog_sales"
-  CROSS JOIN "date_dim_2" AS "date_dim"
-  WHERE
-    "date_dim"."d_date_sk" = "catalog_sales"."cs_sold_date_sk"
+  JOIN "date_dim_2" AS "date_dim"
+    ON "date_dim"."d_date_sk" = "catalog_sales"."cs_sold_date_sk"
   UNION ALL
   SELECT
     "store_sales"."ss_ext_sales_price" AS "ext_price",
     "store_sales"."ss_item_sk" AS "sold_item_sk",
     "store_sales"."ss_sold_time_sk" AS "time_sk"
   FROM "store_sales" AS "store_sales"
-  CROSS JOIN "date_dim_2" AS "date_dim"
-  WHERE
-    "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk"
+  JOIN "date_dim_2" AS "date_dim"
+    ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk"
 ), "tmp" AS (
   SELECT
     "web_sales"."ws_ext_sales_price" AS "ext_price",
     "web_sales"."ws_item_sk" AS "sold_item_sk",
     "web_sales"."ws_sold_time_sk" AS "time_sk"
   FROM "web_sales" AS "web_sales"
-  CROSS JOIN "date_dim_2" AS "date_dim"
-  WHERE
-    "date_dim"."d_date_sk" = "web_sales"."ws_sold_date_sk"
+  JOIN "date_dim_2" AS "date_dim"
+    ON "date_dim"."d_date_sk" = "web_sales"."ws_sold_date_sk"
   UNION ALL
   SELECT
     "cte_4"."ext_price" AS "ext_price",
@@ -10932,13 +10930,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
@@ -11089,7 +11087,12 @@ WHERE  sr_items.item_id = cr_items.item_id
 ORDER  BY sr_items.item_id,
           sr_item_qty
 LIMIT 100;
-WITH "date_dim_2" AS (
+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 (
   SELECT
     "date_dim"."d_date_sk" AS "d_date_sk",
     "date_dim"."d_date" AS "d_date"
@@ -11112,22 +11115,17 @@ WITH "date_dim_2" AS (
     NOT "_u_0"."d_week_seq" IS NULL
   GROUP BY
     "date_dim"."d_date"
-), "item_2" AS (
-  SELECT
-    "item"."i_item_sk" AS "i_item_sk",
-    "item"."i_item_id" AS "i_item_id"
-  FROM "item" AS "item"
 ), "sr_items" AS (
   SELECT
     "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"
   LEFT JOIN "_u_1" AS "_u_1"
     ON "date_dim"."d_date" = "_u_1"."d_date"
-  JOIN "item_2" AS "item"
-    ON "store_returns"."sr_item_sk" = "item"."i_item_sk"
   WHERE
     NOT "_u_1"."d_date" IS NULL
   GROUP BY
@@ -11147,12 +11145,12 @@ WITH "date_dim_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"
   LEFT JOIN "_u_3" AS "_u_3"
     ON "date_dim"."d_date" = "_u_3"."d_date"
-  JOIN "item_2" AS "item"
-    ON "catalog_returns"."cr_item_sk" = "item"."i_item_sk"
   WHERE
     NOT "_u_3"."d_date" IS NULL
   GROUP BY
@@ -11172,12 +11170,12 @@ WITH "date_dim_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"
   LEFT JOIN "_u_5" AS "_u_5"
     ON "date_dim"."d_date" = "_u_5"."d_date"
-  JOIN "item_2" AS "item"
-    ON "web_returns"."wr_item_sk" = "item"."i_item_sk"
   WHERE
     NOT "_u_5"."d_date" IS NULL
   GROUP BY
@@ -11437,7 +11435,7 @@ ROLLUP (
 )
 ORDER BY
   "lochierarchy" DESC,
-  CASE WHEN "lochierarchy" = 0 THEN "item"."i_category" END,
+  CASE WHEN "lochierarchy" = 0 THEN "i_category" END,
   "rank_within_parent"
 LIMIT 100;
 
@@ -11687,73 +11685,87 @@ WITH "store_sales_2" AS (
 ), "s2" AS (
   SELECT
     COUNT(*) AS "h9_to_9_30"
-  FROM "store_sales_2" AS "store_sales", "household_demographics_2" AS "household_demographics", "time_dim" AS "time_dim", "store_2" AS "store"
-  WHERE
-    "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk"
-    AND "store_sales"."ss_sold_time_sk" = "time_dim"."t_time_sk"
-    AND "store_sales"."ss_store_sk" = "store"."s_store_sk"
+  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 "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", "household_demographics_2" AS "household_demographics", "time_dim" AS "time_dim", "store_2" AS "store"
-  WHERE
-    "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk"
-    AND "store_sales"."ss_sold_time_sk" = "time_dim"."t_time_sk"
-    AND "store_sales"."ss_store_sk" = "store"."s_store_sk"
+  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 "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", "household_demographics_2" AS "household_demographics", "time_dim" AS "time_dim", "store_2" AS "store"
-  WHERE
-    "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk"
-    AND "store_sales"."ss_sold_time_sk" = "time_dim"."t_time_sk"
-    AND "store_sales"."ss_store_sk" = "store"."s_store_sk"
+  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 "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", "household_demographics_2" AS "household_demographics", "time_dim" AS "time_dim", "store_2" AS "store"
-  WHERE
-    "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk"
-    AND "store_sales"."ss_sold_time_sk" = "time_dim"."t_time_sk"
-    AND "store_sales"."ss_store_sk" = "store"."s_store_sk"
+  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 "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", "household_demographics_2" AS "household_demographics", "time_dim" AS "time_dim", "store_2" AS "store"
-  WHERE
-    "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk"
-    AND "store_sales"."ss_sold_time_sk" = "time_dim"."t_time_sk"
-    AND "store_sales"."ss_store_sk" = "store"."s_store_sk"
+  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 "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", "household_demographics_2" AS "household_demographics", "time_dim" AS "time_dim", "store_2" AS "store"
-  WHERE
-    "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk"
-    AND "store_sales"."ss_sold_time_sk" = "time_dim"."t_time_sk"
-    AND "store_sales"."ss_store_sk" = "store"."s_store_sk"
+  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 "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", "household_demographics_2" AS "household_demographics", "time_dim" AS "time_dim", "store_2" AS "store"
-  WHERE
-    "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk"
-    AND "store_sales"."ss_sold_time_sk" = "time_dim"."t_time_sk"
-    AND "store_sales"."ss_store_sk" = "store"."s_store_sk"
+  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 "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",
@@ -11946,13 +11958,15 @@ WITH "web_sales_2" AS (
 ), "pt" AS (
   SELECT
     COUNT(*) AS "pmc"
-  FROM "web_sales_2" AS "web_sales", "household_demographics_2" AS "household_demographics", "time_dim" AS "time_dim", "web_page_2" AS "web_page"
-  WHERE
-    "time_dim"."t_hour" <= 21
+  FROM "web_sales_2" AS "web_sales"
+  JOIN "household_demographics_2" AS "household_demographics"
+    ON "web_sales"."ws_ship_hdemo_sk" = "household_demographics"."hd_demo_sk"
+  JOIN "time_dim" AS "time_dim"
+    ON "time_dim"."t_hour" <= 21
     AND "time_dim"."t_hour" >= 20
-    AND "web_sales"."ws_ship_hdemo_sk" = "household_demographics"."hd_demo_sk"
     AND "web_sales"."ws_sold_time_sk" = "time_dim"."t_time_sk"
-    AND "web_sales"."ws_web_page_sk" = "web_page"."wp_web_page_sk"
+  JOIN "web_page_2" AS "web_page"
+    ON "web_sales"."ws_web_page_sk" = "web_page"."wp_web_page_sk"
 )
 SELECT
   CAST("at1"."amc" AS DECIMAL(15, 4)) / CAST("pt"."pmc" AS DECIMAL(15, 4)) AS "am_pm_ratio"
@@ -11997,10 +12011,10 @@ GROUP  BY cc_call_center_id,
           cd_education_status
 ORDER  BY Sum(cr_net_loss) DESC;
 SELECT
-  "call_center"."cc_call_center_id" AS Call_Center,
-  "call_center"."cc_name" AS Call_Center_Name,
-  "call_center"."cc_manager" AS Manager,
-  SUM("catalog_returns"."cr_net_loss") AS Returns_Loss
+  "call_center"."cc_call_center_id" AS "call_center",
+  "call_center"."cc_name" AS "call_center_name",
+  "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"
@@ -12096,10 +12110,10 @@ SELECT
 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
-LEFT JOIN "_u_0" AS "_u_0"
-  ON "_u_0"."_u_1" = "item"."i_item_sk"
 JOIN "date_dim_2" AS "date_dim"
   ON "date_dim"."d_date_sk" = "web_sales"."ws_sold_date_sk"
+LEFT JOIN "_u_0" AS "_u_0"
+  ON "_u_0"."_u_1" = "item"."i_item_sk"
 WHERE
   "web_sales"."ws_ext_discount_amt" > "_u_0"."_col_0"
 ORDER BY
@@ -12208,10 +12222,6 @@ SELECT
   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"
-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"
 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"
@@ -12224,6 +12234,10 @@ JOIN "customer_address" AS "customer_address"
 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"
 WHERE
   "_u_3"."_u_4" IS NULL
   AND ARRAY_ANY("_u_0"."_u_2", "_x" -> "web_sales"."ws_warehouse_sk" <> "_x")
@@ -12303,10 +12317,6 @@ SELECT
   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"
-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"
 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"
@@ -12319,6 +12329,10 @@ JOIN "customer_address" AS "customer_address"
 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"
 WHERE
   NOT "_u_0"."ws_order_number" IS NULL AND NOT "_u_1"."wr_order_number" IS NULL
 ORDER BY
diff --git a/tests/fixtures/optimizer/tpc-h/tpc-h.sql b/tests/fixtures/optimizer/tpc-h/tpc-h.sql
index ae50f92..a25e247 100644
--- a/tests/fixtures/optimizer/tpc-h/tpc-h.sql
+++ b/tests/fixtures/optimizer/tpc-h/tpc-h.sql
@@ -99,19 +99,19 @@ order by
    p_partkey
 limit
    100;
-WITH "partsupp_2" AS (
-  SELECT
-    "partsupp"."ps_partkey" AS "ps_partkey",
-    "partsupp"."ps_suppkey" AS "ps_suppkey",
-    "partsupp"."ps_supplycost" AS "ps_supplycost"
-  FROM "partsupp" AS "partsupp"
-), "region_2" AS (
+WITH "region_2" AS (
   SELECT
     "region"."r_regionkey" AS "r_regionkey",
     "region"."r_name" AS "r_name"
   FROM "region" AS "region"
   WHERE
     "region"."r_name" = 'EUROPE'
+), "partsupp_2" AS (
+  SELECT
+    "partsupp"."ps_partkey" AS "ps_partkey",
+    "partsupp"."ps_suppkey" AS "ps_suppkey",
+    "partsupp"."ps_supplycost" AS "ps_supplycost"
+  FROM "partsupp" AS "partsupp"
 ), "_u_0" AS (
   SELECT
     MIN("partsupp"."ps_supplycost") AS "_col_0",
@@ -136,8 +136,6 @@ SELECT
   "supplier"."s_phone" AS "s_phone",
   "supplier"."s_comment" AS "s_comment"
 FROM "part" AS "part"
-LEFT JOIN "_u_0" AS "_u_0"
-  ON "part"."p_partkey" = "_u_0"."_u_1"
 CROSS JOIN "region_2" AS "region"
 JOIN "nation" AS "nation"
   ON "nation"."n_regionkey" = "region"."r_regionkey"
@@ -146,6 +144,8 @@ 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'
@@ -681,11 +681,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 +950,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 +1066,10 @@ SELECT
 FROM "customer" AS "customer"
 JOIN "orders" AS "orders"
   ON "customer"."c_custkey" = "orders"."o_custkey"
-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"
+LEFT JOIN "_u_0" AS "_u_0"
+  ON "orders"."o_orderkey" = "_u_0"."l_orderkey"
 WHERE
   NOT "_u_0"."l_orderkey" IS NULL
 GROUP BY
@@ -1260,10 +1260,10 @@ SELECT
   "supplier"."s_name" AS "s_name",
   "supplier"."s_address" AS "s_address"
 FROM "supplier" AS "supplier"
-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"
+LEFT JOIN "_u_4" AS "_u_4"
+  ON "supplier"."s_suppkey" = "_u_4"."ps_suppkey"
 WHERE
   NOT "_u_4"."ps_suppkey" IS NULL
 ORDER BY
@@ -1337,15 +1337,15 @@ FROM "supplier" AS "supplier"
 JOIN "lineitem" AS "lineitem"
   ON "lineitem"."l_receiptdate" > "lineitem"."l_commitdate"
   AND "supplier"."s_suppkey" = "lineitem"."l_suppkey"
-LEFT JOIN "_u_0" AS "_u_0"
-  ON "_u_0"."l_orderkey" = "lineitem"."l_orderkey"
-LEFT JOIN "_u_2" AS "_u_2"
-  ON "_u_2"."l_orderkey" = "lineitem"."l_orderkey"
 JOIN "orders" AS "orders"
   ON "orders"."o_orderkey" = "lineitem"."l_orderkey" AND "orders"."o_orderstatus" = 'F'
 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"
+LEFT JOIN "_u_2" AS "_u_2"
+  ON "_u_2"."l_orderkey" = "lineitem"."l_orderkey"
 WHERE
   (
     "_u_2"."l_orderkey" IS NULL