Adding upstream version 20.3.0.upstream/20.3.0

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

24 files changed, 1818 insertions, 1248 deletions

diff --git a/tests/dialects/test_clickhouse.py b/tests/dialects/test_clickhouse.py
index 86ddb00..1f528b6 100644
--- a/tests/dialects/test_clickhouse.py
+++ b/tests/dialects/test_clickhouse.py
@@ -6,6 +6,8 @@ class TestClickhouse(Validator):
     dialect = "clickhouse"
 
     def test_clickhouse(self):
+        self.validate_identity("SELECT * FROM x LIMIT 1 UNION ALL SELECT * FROM y")
+
         string_types = [
             "BLOB",
             "LONGBLOB",
@@ -69,6 +71,18 @@ class TestClickhouse(Validator):
         self.validate_identity("CAST(x as MEDIUMINT)", "CAST(x AS Int32)")
         self.validate_identity("SELECT arrayJoin([1, 2, 3] AS src) AS dst, 'Hello', src")
         self.validate_identity(
+            "SELECT n, source FROM (SELECT toFloat32(number % 10) AS n, 'original' AS source FROM numbers(10) WHERE number % 3 = 1) ORDER BY n WITH FILL"
+        )
+        self.validate_identity(
+            "SELECT n, source FROM (SELECT toFloat32(number % 10) AS n, 'original' AS source FROM numbers(10) WHERE number % 3 = 1) ORDER BY n WITH FILL FROM 0 TO 5.51 STEP 0.5"
+        )
+        self.validate_identity(
+            "SELECT toDate((number * 10) * 86400) AS d1, toDate(number * 86400) AS d2, 'original' AS source FROM numbers(10) WHERE (number % 3) = 1 ORDER BY d2 WITH FILL, d1 WITH FILL STEP 5"
+        )
+        self.validate_identity(
+            "SELECT n, source, inter FROM (SELECT toFloat32(number % 10) AS n, 'original' AS source, number AS inter FROM numbers(10) WHERE number % 3 = 1) ORDER BY n WITH FILL FROM 0 TO 5.51 STEP 0.5 INTERPOLATE (inter AS inter + 1)"
+        )
+        self.validate_identity(
             "SELECT SUM(1) AS impressions, arrayJoin(cities) AS city, arrayJoin(browsers) AS browser FROM (SELECT ['Istanbul', 'Berlin', 'Bobruisk'] AS cities, ['Firefox', 'Chrome', 'Chrome'] AS browsers) GROUP BY 2, 3"
         )
         self.validate_identity(
diff --git a/tests/dialects/test_duckdb.py b/tests/dialects/test_duckdb.py
index 687a807..f915168 100644
--- a/tests/dialects/test_duckdb.py
+++ b/tests/dialects/test_duckdb.py
@@ -55,6 +55,13 @@ class TestDuckDB(Validator):
             )
 
         self.validate_all(
+            "SELECT {'bla': column1, 'foo': column2, 'bar': column3} AS data FROM source_table",
+            read={
+                "bigquery": "SELECT STRUCT(column1 AS bla, column2 AS foo, column3 AS bar) AS data FROM source_table",
+                "duckdb": "SELECT {'bla': column1, 'foo': column2, 'bar': column3} AS data FROM source_table",
+            },
+        )
+        self.validate_all(
             "WITH cte(x) AS (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) SELECT AVG(x) FILTER (WHERE x > 1) FROM cte",
             write={
                 "duckdb": "WITH cte(x) AS (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) SELECT AVG(x) FILTER(WHERE x > 1) FROM cte",
@@ -109,6 +116,8 @@ class TestDuckDB(Validator):
             parse_one("a // b", read="duckdb").assert_is(exp.IntDiv).sql(dialect="duckdb"), "a // b"
         )
 
+        self.validate_identity("MAKE_TIMESTAMP(1992, 9, 20, 13, 34, 27.123456)")
+        self.validate_identity("MAKE_TIMESTAMP(1667810584123456)")
         self.validate_identity("SELECT EPOCH_MS(10) AS t")
         self.validate_identity("SELECT MAKE_TIMESTAMP(10) AS t")
         self.validate_identity("SELECT TO_TIMESTAMP(10) AS t")
diff --git a/tests/dialects/test_postgres.py b/tests/dialects/test_postgres.py
index 17a65d7..9155696 100644
--- a/tests/dialects/test_postgres.py
+++ b/tests/dialects/test_postgres.py
@@ -8,6 +8,11 @@ class TestPostgres(Validator):
     dialect = "postgres"
 
     def test_ddl(self):
+        expr = parse_one("CREATE TABLE t (x INTERVAL day)", read="postgres")
+        cdef = expr.find(exp.ColumnDef)
+        cdef.args["kind"].assert_is(exp.DataType)
+        self.assertEqual(expr.sql(dialect="postgres"), "CREATE TABLE t (x INTERVAL day)")
+
         self.validate_identity("CREATE INDEX idx_x ON x USING BTREE(x, y) WHERE (NOT y IS NULL)")
         self.validate_identity("CREATE TABLE test (elems JSONB[])")
         self.validate_identity("CREATE TABLE public.y (x TSTZRANGE NOT NULL)")
@@ -203,6 +208,8 @@ class TestPostgres(Validator):
         self.validate_identity("SELECT 1 OPERATOR(pg_catalog.+) 2")
 
     def test_postgres(self):
+        self.validate_identity("EXEC AS myfunc @id = 123")
+
         expr = parse_one(
             "SELECT * FROM r CROSS JOIN LATERAL UNNEST(ARRAY[1]) AS s(location)", read="postgres"
         )
diff --git a/tests/dialects/test_presto.py b/tests/dialects/test_presto.py
index 6a82756..97a387c 100644
--- a/tests/dialects/test_presto.py
+++ b/tests/dialects/test_presto.py
@@ -379,6 +379,16 @@ class TestPresto(Validator):
                 "presto": "TIMESTAMP(x, '12:00:00')",
             },
         )
+        self.validate_all(
+            "DATE_ADD('DAY', CAST(x AS BIGINT), y)",
+            write={
+                "presto": "DATE_ADD('DAY', CAST(x AS BIGINT), y)",
+            },
+            read={
+                "presto": "DATE_ADD('DAY', x, y)",
+            },
+        )
+        self.validate_identity("DATE_ADD('DAY', 1, y)")
 
     def test_ddl(self):
         self.validate_all(
@@ -462,10 +472,10 @@ class TestPresto(Validator):
         )
 
         self.validate_all(
-            'CREATE TABLE IF NOT EXISTS x ("cola" INTEGER, "ds" TEXT) WITH (PARTITIONED BY=("ds"))',
+            """CREATE TABLE IF NOT EXISTS x ("cola" INTEGER, "ds" TEXT) COMMENT 'comment' WITH (PARTITIONED BY=("ds"))""",
             write={
-                "spark": "CREATE TABLE IF NOT EXISTS x (`cola` INT, `ds` STRING) PARTITIONED BY (`ds`)",
-                "presto": """CREATE TABLE IF NOT EXISTS x ("cola" INTEGER, "ds" VARCHAR) WITH (PARTITIONED_BY=ARRAY['ds'])""",
+                "spark": "CREATE TABLE IF NOT EXISTS x (`cola` INT, `ds` STRING) COMMENT 'comment' PARTITIONED BY (`ds`)",
+                "presto": """CREATE TABLE IF NOT EXISTS x ("cola" INTEGER, "ds" VARCHAR) COMMENT 'comment' WITH (PARTITIONED_BY=ARRAY['ds'])""",
             },
         )
 
@@ -534,26 +544,18 @@ class TestPresto(Validator):
             },
         )
 
-    def test_presto(self):
-        self.validate_identity("string_agg(x, ',')", "ARRAY_JOIN(ARRAY_AGG(x), ',')")
-        self.validate_identity(
-            "SELECT * FROM example.testdb.customer_orders FOR VERSION AS OF 8954597067493422955"
-        )
-        self.validate_identity(
-            "SELECT * FROM example.testdb.customer_orders FOR TIMESTAMP AS OF CAST('2022-03-23 09:59:29.803 Europe/Vienna' AS TIMESTAMP)"
-        )
-
-        self.validate_identity("SELECT * FROM x OFFSET 1 LIMIT 1")
-        self.validate_identity("SELECT * FROM x OFFSET 1 FETCH FIRST 1 ROWS ONLY")
-        self.validate_identity("SELECT BOOL_OR(a > 10) FROM asd AS T(a)")
-        self.validate_identity("SELECT * FROM (VALUES (1))")
-        self.validate_identity("START TRANSACTION READ WRITE, ISOLATION LEVEL SERIALIZABLE")
-        self.validate_identity("START TRANSACTION ISOLATION LEVEL REPEATABLE READ")
-        self.validate_identity("APPROX_PERCENTILE(a, b, c, d)")
-        self.validate_identity(
-            "SELECT SPLIT_TO_MAP('a:1;b:2;a:3', ';', ':', (k, v1, v2) -> CONCAT(v1, v2))"
-        )
+    def test_unicode_string(self):
+        for prefix in ("u&", "U&"):
+            self.validate_identity(
+                f"{prefix}'Hello winter \\2603 !'",
+                "U&'Hello winter \\2603 !'",
+            )
+            self.validate_identity(
+                f"{prefix}'Hello winter #2603 !' UESCAPE '#'",
+                "U&'Hello winter #2603 !' UESCAPE '#'",
+            )
 
+    def test_presto(self):
         with self.assertLogs(helper_logger) as cm:
             self.validate_all(
                 "SELECT COALESCE(ELEMENT_AT(MAP_FROM_ENTRIES(ARRAY[(51, '1')]), id), quantity) FROM my_table",
@@ -572,6 +574,24 @@ class TestPresto(Validator):
                 },
             )
 
+        self.validate_identity("string_agg(x, ',')", "ARRAY_JOIN(ARRAY_AGG(x), ',')")
+        self.validate_identity("SELECT * FROM x OFFSET 1 LIMIT 1")
+        self.validate_identity("SELECT * FROM x OFFSET 1 FETCH FIRST 1 ROWS ONLY")
+        self.validate_identity("SELECT BOOL_OR(a > 10) FROM asd AS T(a)")
+        self.validate_identity("SELECT * FROM (VALUES (1))")
+        self.validate_identity("START TRANSACTION READ WRITE, ISOLATION LEVEL SERIALIZABLE")
+        self.validate_identity("START TRANSACTION ISOLATION LEVEL REPEATABLE READ")
+        self.validate_identity("APPROX_PERCENTILE(a, b, c, d)")
+        self.validate_identity(
+            "SELECT SPLIT_TO_MAP('a:1;b:2;a:3', ';', ':', (k, v1, v2) -> CONCAT(v1, v2))"
+        )
+        self.validate_identity(
+            "SELECT * FROM example.testdb.customer_orders FOR VERSION AS OF 8954597067493422955"
+        )
+        self.validate_identity(
+            "SELECT * FROM example.testdb.customer_orders FOR TIMESTAMP AS OF CAST('2022-03-23 09:59:29.803 Europe/Vienna' AS TIMESTAMP)"
+        )
+
         self.validate_all(
             "SELECT MAX_BY(a.id, a.timestamp) FROM a",
             read={
@@ -1044,3 +1064,61 @@ MATCH_RECOGNIZE (
 )""",
             pretty=True,
         )
+
+    def test_to_char(self):
+        self.validate_all(
+            "TO_CHAR(ts, 'dd')",
+            write={
+                "bigquery": "FORMAT_DATE('%d', ts)",
+                "presto": "DATE_FORMAT(ts, '%d')",
+            },
+        )
+        self.validate_all(
+            "TO_CHAR(ts, 'hh')",
+            write={
+                "bigquery": "FORMAT_DATE('%H', ts)",
+                "presto": "DATE_FORMAT(ts, '%H')",
+            },
+        )
+        self.validate_all(
+            "TO_CHAR(ts, 'hh24')",
+            write={
+                "bigquery": "FORMAT_DATE('%H', ts)",
+                "presto": "DATE_FORMAT(ts, '%H')",
+            },
+        )
+        self.validate_all(
+            "TO_CHAR(ts, 'mi')",
+            write={
+                "bigquery": "FORMAT_DATE('%M', ts)",
+                "presto": "DATE_FORMAT(ts, '%i')",
+            },
+        )
+        self.validate_all(
+            "TO_CHAR(ts, 'mm')",
+            write={
+                "bigquery": "FORMAT_DATE('%m', ts)",
+                "presto": "DATE_FORMAT(ts, '%m')",
+            },
+        )
+        self.validate_all(
+            "TO_CHAR(ts, 'ss')",
+            write={
+                "bigquery": "FORMAT_DATE('%S', ts)",
+                "presto": "DATE_FORMAT(ts, '%s')",
+            },
+        )
+        self.validate_all(
+            "TO_CHAR(ts, 'yyyy')",
+            write={
+                "bigquery": "FORMAT_DATE('%Y', ts)",
+                "presto": "DATE_FORMAT(ts, '%Y')",
+            },
+        )
+        self.validate_all(
+            "TO_CHAR(ts, 'yy')",
+            write={
+                "bigquery": "FORMAT_DATE('%y', ts)",
+                "presto": "DATE_FORMAT(ts, '%y')",
+            },
+        )
diff --git a/tests/dialects/test_snowflake.py b/tests/dialects/test_snowflake.py
index 997c27b..4d8168a 100644
--- a/tests/dialects/test_snowflake.py
+++ b/tests/dialects/test_snowflake.py
@@ -36,6 +36,8 @@ WHERE
   )""",
         )
 
+        self.validate_identity("SELECT TO_ARRAY(CAST(x AS ARRAY))")
+        self.validate_identity("SELECT TO_ARRAY(CAST(['test'] AS VARIANT))")
         self.validate_identity("SELECT user_id, value FROM table_name sample ($s) SEED (0)")
         self.validate_identity("SELECT ARRAY_UNIQUE_AGG(x)")
         self.validate_identity("SELECT OBJECT_CONSTRUCT()")
@@ -73,6 +75,18 @@ WHERE
             'DESCRIBE TABLE "SNOWFLAKE_SAMPLE_DATA"."TPCDS_SF100TCL"."WEB_SITE" type=stage'
         )
         self.validate_identity(
+            "SELECT * FROM foo at",
+            "SELECT * FROM foo AS at",
+        )
+        self.validate_identity(
+            "SELECT * FROM foo before",
+            "SELECT * FROM foo AS before",
+        )
+        self.validate_identity(
+            "SELECT * FROM foo at (col)",
+            "SELECT * FROM foo AS at(col)",
+        )
+        self.validate_identity(
             "SELECT * FROM unnest(x) with ordinality",
             "SELECT * FROM TABLE(FLATTEN(INPUT => x)) AS _u(seq, key, path, index, value, this)",
         )
@@ -115,11 +129,37 @@ WHERE
             "SELECT TO_TIMESTAMP(x) FROM t",
             "SELECT CAST(x AS TIMESTAMPNTZ) FROM t",
         )
+        self.validate_identity(
+            "CAST(x AS BYTEINT)",
+            "CAST(x AS INT)",
+        )
+        self.validate_identity(
+            "CAST(x AS CHAR VARYING)",
+            "CAST(x AS VARCHAR)",
+        )
+        self.validate_identity(
+            "CAST(x AS CHARACTER VARYING)",
+            "CAST(x AS VARCHAR)",
+        )
+        self.validate_identity(
+            "CAST(x AS NCHAR VARYING)",
+            "CAST(x AS VARCHAR)",
+        )
 
-        self.validate_all("CAST(x AS BYTEINT)", write={"snowflake": "CAST(x AS INT)"})
-        self.validate_all("CAST(x AS CHAR VARYING)", write={"snowflake": "CAST(x AS VARCHAR)"})
-        self.validate_all("CAST(x AS CHARACTER VARYING)", write={"snowflake": "CAST(x AS VARCHAR)"})
-        self.validate_all("CAST(x AS NCHAR VARYING)", write={"snowflake": "CAST(x AS VARCHAR)"})
+        self.validate_all(
+            "SELECT TO_ARRAY(['test'])",
+            write={
+                "snowflake": "SELECT TO_ARRAY(['test'])",
+                "spark": "SELECT ARRAY('test')",
+            },
+        )
+        self.validate_all(
+            "SELECT TO_ARRAY(['test'])",
+            write={
+                "snowflake": "SELECT TO_ARRAY(['test'])",
+                "spark": "SELECT ARRAY('test')",
+            },
+        )
         self.validate_all(
             # We need to qualify the columns in this query because "value" would be ambiguous
             'WITH t(x, "value") AS (SELECT [1, 2, 3], 1) SELECT IFF(_u.pos = _u_2.pos_2, _u_2."value", NULL) AS "value" FROM t, TABLE(FLATTEN(INPUT => ARRAY_GENERATE_RANGE(0, (GREATEST(ARRAY_SIZE(t.x)) - 1) + 1))) AS _u(seq, key, path, index, pos, this) CROSS JOIN TABLE(FLATTEN(INPUT => t.x)) AS _u_2(seq, key, path, pos_2, "value", this) WHERE _u.pos = _u_2.pos_2 OR (_u.pos > (ARRAY_SIZE(t.x) - 1) AND _u_2.pos_2 = (ARRAY_SIZE(t.x) - 1))',
@@ -489,8 +529,8 @@ WHERE
         self.validate_all(
             "TO_ARRAY(x)",
             write={
-                "spark": "ARRAY(x)",
-                "snowflake": "[x]",
+                "spark": "IF(x IS NULL, NULL, ARRAY(x))",
+                "snowflake": "TO_ARRAY(x)",
             },
         )
         self.validate_all(
@@ -626,6 +666,10 @@ WHERE
             "SELECT * FROM @mystage t (c1)",
             "SELECT * FROM @mystage AS t(c1)",
         )
+        self.validate_identity(
+            "SELECT * FROM @foo/bar (PATTERN => 'test', FILE_FORMAT => ds_sandbox.test.my_csv_format) AS bla",
+            "SELECT * FROM @foo/bar (FILE_FORMAT => ds_sandbox.test.my_csv_format, PATTERN => 'test') AS bla",
+        )
 
     def test_sample(self):
         self.validate_identity("SELECT * FROM testtable TABLESAMPLE BERNOULLI (20.3)")
@@ -775,6 +819,53 @@ WHERE
             },
         )
 
+    def test_historical_data(self):
+        self.validate_identity("SELECT * FROM my_table AT (STATEMENT => $query_id_var)")
+        self.validate_identity("SELECT * FROM my_table AT (OFFSET => -60 * 5)")
+        self.validate_identity("SELECT * FROM my_table BEFORE (STATEMENT => $query_id_var)")
+        self.validate_identity("SELECT * FROM my_table BEFORE (OFFSET => -60 * 5)")
+        self.validate_identity("CREATE SCHEMA restored_schema CLONE my_schema AT (OFFSET => -3600)")
+        self.validate_identity(
+            "CREATE TABLE restored_table CLONE my_table AT (TIMESTAMP => CAST('Sat, 09 May 2015 01:01:00 +0300' AS TIMESTAMPTZ))",
+        )
+        self.validate_identity(
+            "CREATE DATABASE restored_db CLONE my_db BEFORE (STATEMENT => '8e5d0ca9-005e-44e6-b858-a8f5b37c5726')"
+        )
+        self.validate_identity(
+            "SELECT * FROM my_table AT (TIMESTAMP => TO_TIMESTAMP(1432669154242, 3))"
+        )
+        self.validate_identity(
+            "SELECT * FROM my_table AT (OFFSET => -60 * 5) AS T WHERE T.flag = 'valid'"
+        )
+        self.validate_identity(
+            "SELECT * FROM my_table AT (STATEMENT => '8e5d0ca9-005e-44e6-b858-a8f5b37c5726')"
+        )
+        self.validate_identity(
+            "SELECT * FROM my_table BEFORE (STATEMENT => '8e5d0ca9-005e-44e6-b858-a8f5b37c5726')"
+        )
+        self.validate_identity(
+            "SELECT * FROM my_table AT (TIMESTAMP => 'Fri, 01 May 2015 16:20:00 -0700'::timestamp)",
+            "SELECT * FROM my_table AT (TIMESTAMP => CAST('Fri, 01 May 2015 16:20:00 -0700' AS TIMESTAMPNTZ))",
+        )
+        self.validate_identity(
+            "SELECT * FROM my_table AT(TIMESTAMP => 'Fri, 01 May 2015 16:20:00 -0700'::timestamp_tz)",
+            "SELECT * FROM my_table AT (TIMESTAMP => CAST('Fri, 01 May 2015 16:20:00 -0700' AS TIMESTAMPTZ))",
+        )
+        self.validate_identity(
+            "SELECT * FROM my_table BEFORE (TIMESTAMP => 'Fri, 01 May 2015 16:20:00 -0700'::timestamp_tz);",
+            "SELECT * FROM my_table BEFORE (TIMESTAMP => CAST('Fri, 01 May 2015 16:20:00 -0700' AS TIMESTAMPTZ))",
+        )
+        self.validate_identity(
+            """
+            SELECT oldt.* , newt.*
+            FROM my_table BEFORE(STATEMENT => '8e5d0ca9-005e-44e6-b858-a8f5b37c5726') AS oldt
+            FULL OUTER JOIN my_table AT(STATEMENT => '8e5d0ca9-005e-44e6-b858-a8f5b37c5726') AS newt
+            ON oldt.id = newt.id
+            WHERE oldt.id IS NULL OR newt.id IS NULL;
+            """,
+            "SELECT oldt.*, newt.* FROM my_table BEFORE (STATEMENT => '8e5d0ca9-005e-44e6-b858-a8f5b37c5726') AS oldt FULL OUTER JOIN my_table AT (STATEMENT => '8e5d0ca9-005e-44e6-b858-a8f5b37c5726') AS newt ON oldt.id = newt.id WHERE oldt.id IS NULL OR newt.id IS NULL",
+        )
+
     def test_ddl(self):
         self.validate_identity(
             """create external table et2(
diff --git a/tests/dialects/test_spark.py b/tests/dialects/test_spark.py
index fe37027..24f68f8 100644
--- a/tests/dialects/test_spark.py
+++ b/tests/dialects/test_spark.py
@@ -75,7 +75,7 @@ class TestSpark(Validator):
   col_a INTEGER,
   date VARCHAR
 )
-COMMENT='Test comment: blah'
+COMMENT 'Test comment: blah'
 WITH (
   PARTITIONED_BY=ARRAY['date'],
   FORMAT='ICEBERG',
diff --git a/tests/dialects/test_tsql.py b/tests/dialects/test_tsql.py
index 07179ef..a2569ab 100644
--- a/tests/dialects/test_tsql.py
+++ b/tests/dialects/test_tsql.py
@@ -21,6 +21,12 @@ class TestTSQL(Validator):
         self.validate_identity("CAST(x AS int) OR y", "CAST(x AS INTEGER) <> 0 OR y <> 0")
 
         self.validate_all(
+            "SELECT TOP 1 * FROM (SELECT x FROM t1 UNION ALL SELECT x FROM t2) AS _l_0",
+            read={
+                "": "SELECT x FROM t1 UNION ALL SELECT x FROM t2 LIMIT 1",
+            },
+        )
+        self.validate_all(
             "WITH t(c) AS (SELECT 1) SELECT * INTO foo FROM (SELECT c AS c FROM t) AS temp",
             read={
                 "duckdb": "CREATE TABLE foo AS WITH t(c) AS (SELECT 1) SELECT c FROM t",
diff --git a/tests/fixtures/identity.sql b/tests/fixtures/identity.sql
index effebca..228f109 100644
--- a/tests/fixtures/identity.sql
+++ b/tests/fixtures/identity.sql
@@ -865,5 +865,8 @@ KILL CONNECTION 123
 KILL QUERY '123'
 CHR(97)
 SELECT * FROM UNNEST(x) WITH ORDINALITY UNION ALL SELECT * FROM UNNEST(y) WITH ORDINALITY
+SELECT x FROM t1 UNION ALL SELECT x FROM t2 LIMIT 1
+SELECT x FROM t1 UNION ALL SELECT x FROM t2 UNION ALL SELECT x FROM t3 LIMIT 1
 WITH use(use) AS (SELECT 1) SELECT use FROM use
 SELECT recursive FROM t
+SELECT (ROW_NUMBER() OVER (PARTITION BY user ORDER BY date ASC) - ROW_NUMBER() OVER (PARTITION BY user, segment ORDER BY date ASC)) AS group_id FROM example_table
diff --git a/tests/fixtures/optimizer/eliminate_ctes.sql b/tests/fixtures/optimizer/eliminate_ctes.sql
index 11e5e4f..f1f78f4 100644
--- a/tests/fixtures/optimizer/eliminate_ctes.sql
+++ b/tests/fixtures/optimizer/eliminate_ctes.sql
@@ -46,3 +46,71 @@ FROM x;
 SELECT
   a
 FROM x;
+
+# title: CTE reference in subquery where alias matches outer table name
+WITH q AS (
+  SELECT
+    a
+  FROM y
+)
+SELECT
+  a
+FROM x AS q
+WHERE
+  a IN (
+    SELECT
+      a
+    FROM q
+  );
+WITH q AS (
+  SELECT
+    a
+  FROM y
+)
+SELECT
+  a
+FROM x AS q
+WHERE
+  a IN (
+    SELECT
+      a
+    FROM q
+  );
+
+# title: CTE reference in subquery where alias matches outer table name and outer alias is also CTE
+WITH q AS (
+  SELECT
+    a
+  FROM y
+), q2 AS (
+  SELECT
+    a
+  FROM y
+)
+SELECT
+  a
+FROM q2 AS q
+WHERE
+  a IN (
+    SELECT
+      a
+    FROM q
+  );
+WITH q AS (
+  SELECT
+    a
+  FROM y
+), q2 AS (
+  SELECT
+    a
+  FROM y
+)
+SELECT
+  a
+FROM q2 AS q
+WHERE
+  a IN (
+    SELECT
+      a
+    FROM q
+  );
\ No newline at end of file
diff --git a/tests/fixtures/optimizer/eliminate_subqueries.sql b/tests/fixtures/optimizer/eliminate_subqueries.sql
index e18d2a4..f2b117b 100644
--- a/tests/fixtures/optimizer/eliminate_subqueries.sql
+++ b/tests/fixtures/optimizer/eliminate_subqueries.sql
@@ -42,17 +42,9 @@ WITH y AS (SELECT a FROM x), z AS (SELECT a FROM y AS y) SELECT a FROM z AS z CR
 WITH y AS (SELECT a FROM (SELECT a FROM x) AS y) SELECT a FROM y;
 WITH y_2 AS (SELECT a FROM x), y AS (SELECT a FROM y_2 AS y) SELECT a FROM y;
 
--- Union
-SELECT 1 AS x, 2 AS y UNION ALL SELECT 1 AS x, 2 AS y;
-WITH cte AS (SELECT 1 AS x, 2 AS y) SELECT cte.x AS x, cte.y AS y FROM cte AS cte UNION ALL SELECT cte.x AS x, cte.y AS y FROM cte AS cte;
-
 -- Union of selects with derived tables
 (SELECT a FROM (SELECT b FROM x)) UNION (SELECT a FROM (SELECT b FROM y));
-WITH cte AS (SELECT b FROM x), cte_2 AS (SELECT a FROM cte AS cte), cte_3 AS (SELECT b FROM y), cte_4 AS (SELECT a FROM cte_3 AS cte_3) (SELECT cte_2.a AS a FROM cte_2 AS cte_2) UNION (SELECT cte_4.a AS a FROM cte_4 AS cte_4);
-
--- Three unions
-SELECT a FROM x UNION ALL SELECT a FROM y UNION ALL SELECT a FROM z;
-WITH cte AS (SELECT a FROM x), cte_2 AS (SELECT a FROM y), cte_3 AS (SELECT a FROM z), cte_4 AS (SELECT cte_2.a AS a FROM cte_2 AS cte_2 UNION ALL SELECT cte_3.a AS a FROM cte_3 AS cte_3) SELECT cte.a AS a FROM cte AS cte UNION ALL SELECT cte_4.a AS a FROM cte_4 AS cte_4;
+WITH cte AS (SELECT b FROM x), cte_2 AS (SELECT b FROM y) (SELECT a FROM cte AS cte) UNION (SELECT a FROM cte_2 AS cte_2);
 
 -- Subquery
 SELECT a FROM x WHERE b = (SELECT y.c FROM y);
diff --git a/tests/fixtures/optimizer/merge_subqueries.sql b/tests/fixtures/optimizer/merge_subqueries.sql
index eec8a73..7fc4b25 100644
--- a/tests/fixtures/optimizer/merge_subqueries.sql
+++ b/tests/fixtures/optimizer/merge_subqueries.sql
@@ -411,3 +411,20 @@ FROM (
       ON _q_0.a = y.b
 );
 SELECT y.b AS b FROM (x AS x JOIN y AS y ON x.a = y.b);
+
+# title: merge cte into subquery with overlapping alias
+WITH q AS (
+  SELECT
+    y.b AS a
+  FROM y AS y
+)
+SELECT
+  q.a AS a
+FROM x AS q
+WHERE
+  q.a IN (
+    SELECT
+      q.a AS a
+    FROM q AS q
+  );
+SELECT q.a AS a FROM x AS q WHERE q.a IN (SELECT y.b AS a FROM y AS y);
\ No newline at end of file
diff --git a/tests/fixtures/optimizer/optimizer.sql b/tests/fixtures/optimizer/optimizer.sql
index f81d54a..4ae9e90 100644
--- a/tests/fixtures/optimizer/optimizer.sql
+++ b/tests/fixtures/optimizer/optimizer.sql
@@ -131,7 +131,7 @@ SELECT
   SUM("y"."b") AS "sum_b"
 FROM "x" AS "x"
 LEFT JOIN "_u_0" AS "_u_0"
-  ON "x"."b" = "_u_0"."_u_1"
+  ON "_u_0"."_u_1" = "x"."b"
 JOIN "y" AS "y"
   ON "x"."b" = "y"."b"
 WHERE
@@ -522,7 +522,7 @@ OR (
 SELECT
   *,
   IFF(
-    IFF("unioned"."uploaded_at" >= '2022-06-16', 'workday', 'bamboohr') = "unioned"."source_system",
+    "unioned"."source_system" = IFF("unioned"."uploaded_at" >= '2022-06-16', 'workday', 'bamboohr'),
     1,
     0
   ) AS "sort_order"
@@ -950,7 +950,7 @@ SELECT
 FROM "y" AS "y"
 CROSS JOIN "_u_0" AS "_u_0"
 JOIN "x" AS "x"
-  ON "y"."b" = "x"."b"
+  ON "x"."b" = "y"."b"
 GROUP BY
   "x"."a";
 
@@ -989,7 +989,7 @@ SELECT
   COALESCE("m"."a", "foo"."a") AS "a"
 FROM "m"
 JOIN "n" AS "foo"("a")
-  ON "m"."a" = "foo"."a";
+  ON "foo"."a" = "m"."a";
 
 # title: reduction of string concatenation that uses CONCAT(..), || and +
 # execute: false
@@ -1068,7 +1068,7 @@ SELECT
   COALESCE("alias3"."c_od", 0) AS "c_od"
 FROM "table1" AS "table1"
 LEFT JOIN "alias3"
-  ON "table1"."cid" = "alias3"."cid";
+  ON "alias3"."cid" = "table1"."cid";
 
 # title: CTE with EXPLODE cannot be merged
 # dialect: spark
@@ -1115,3 +1115,32 @@ WITH `t` AS (
 SELECT
   `t`.`CoL` AS `CoL`
 FROM `t`;
+
+# title: top-level query is parenthesized
+# execute: false
+WITH x AS (
+  SELECT a FROM t
+)
+(
+  SELECT * FROM x
+  UNION ALL
+  SELECT * FROM x
+  LIMIT 10
+)
+LIMIT 10;
+WITH "x" AS (
+  SELECT
+    "t"."a" AS "a"
+  FROM "t" AS "t"
+)
+(
+  SELECT
+    "x"."a" AS "a"
+  FROM "x"
+  UNION ALL
+  SELECT
+    "x"."a" AS "a"
+  FROM "x"
+  LIMIT 10
+)
+LIMIT 10;
diff --git a/tests/fixtures/optimizer/pushdown_predicates.sql b/tests/fixtures/optimizer/pushdown_predicates.sql
index 9912b89..43f9842 100644
--- a/tests/fixtures/optimizer/pushdown_predicates.sql
+++ b/tests/fixtures/optimizer/pushdown_predicates.sql
@@ -26,10 +26,10 @@ 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
 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.a;
-SELECT x.a AS a FROM x AS x JOIN (SELECT * FROM y AS y WHERE y.a = 1) AS y ON TRUE AND y.a = x.a 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 WHERE y.a = 1) AS y ON x.a = y.a AND TRUE WHERE x.a = 1 AND TRUE AND x.b = 1;
 
 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 TRUE AND y.a = x.a WHERE x.a = 1 AND x.b = 1 AND TRUE AND TRUE;
+SELECT x.a AS a FROM x AS x JOIN (SELECT * FROM y AS y WHERE y.a = 1) AS y ON x.a = y.a AND TRUE WHERE x.a = 1 AND TRUE AND x.b = 1 AND TRUE;
 
 with t1 as (SELECT x.a, x.b, ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.a) as row_num FROM x) SELECT t1.a, t1.b FROM t1 WHERE row_num = 1;
 WITH t1 AS (SELECT x.a, x.b, ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.a) AS row_num FROM x) SELECT t1.a, t1.b FROM t1 WHERE row_num = 1;
diff --git a/tests/fixtures/optimizer/simplify.sql b/tests/fixtures/optimizer/simplify.sql
index 2206e28..fbf5d2c 100644
--- a/tests/fixtures/optimizer/simplify.sql
+++ b/tests/fixtures/optimizer/simplify.sql
@@ -645,6 +645,9 @@ x < 5 AND x > 3;
 x > 3 AND 5 < x AND x BETWEEN 9 AND 10;
 x <= 10 AND x >= 9;
 
+NOT x BETWEEN 0 AND 1;
+x < 0 OR x > 1;
+
 1 < x AND 3 < x;
 x > 3;
 
@@ -657,6 +660,42 @@ x <> 2018 OR x = 2018;
 t0.x = t1.x AND t0.y < t1.y AND t0.y <= t1.y;
 t0.x = t1.x AND t0.y < t1.y AND t0.y <= t1.y;
 
+1 < x;
+x > 1;
+
+1 <= x;
+x >= 1;
+
+1 > x;
+x < 1;
+
+1 >= x;
+x <= 1;
+
+1 = x;
+x = 1;
+
+1 <> x;
+x <> 1;
+
+NOT 1 < x;
+x <= 1;
+
+NOT 1 <= x;
+x < 1;
+
+NOT 1 > x;
+x >= 1;
+
+NOT 1 >= x;
+x > 1;
+
+NOT 1 = x;
+x <> 1;
+
+NOT 1 <> x;
+x = 1;
+
 --------------------------------------
 -- COALESCE
 --------------------------------------
@@ -667,7 +706,7 @@ COALESCE(x, 1) = 2;
 NOT x IS NULL AND x = 2;
 
 2 = COALESCE(x, 1);
-2 = x AND NOT x IS NULL;
+NOT x IS NULL AND x = 2;
 
 COALESCE(x, 1, 1) = 1 + 1;
 NOT x IS NULL AND x = 2;
@@ -759,6 +798,20 @@ CONCAT(a, b) IN (SELECT * FROM foo WHERE cond);
 --------------------------------------
 -- DATE_TRUNC
 --------------------------------------
+DATE_TRUNC('week', CAST('2023-12-15' AS DATE));
+CAST('2023-12-11' AS DATE);
+
+DATE_TRUNC('week', CAST('2023-12-16' AS DATE));
+CAST('2023-12-11' AS DATE);
+
+# dialect: bigquery
+DATE_TRUNC(CAST('2023-12-15' AS DATE), WEEK);
+CAST('2023-12-10' AS DATE);
+
+# dialect: bigquery
+DATE_TRUNC(CAST('2023-12-16' AS DATE), WEEK);
+CAST('2023-12-10' AS DATE);
+
 DATE_TRUNC('year', x) = CAST('2021-01-01' AS DATE);
 x < CAST('2022-01-01' AS DATE) AND x >= CAST('2021-01-01' AS DATE);
 
@@ -801,7 +854,7 @@ DATE_TRUNC('year', x) < CAST('2021-01-01' AS DATE);
 x < CAST('2021-01-01' AS DATE);
 
 DATE_TRUNC('year', x) < CAST('2021-01-02' AS DATE);
-x < CAST('2021-01-01' AS DATE);
+x < CAST('2022-01-01' AS DATE);
 
 DATE_TRUNC('year', x) >= CAST('2021-01-01' AS DATE);
 x >= CAST('2021-01-01' AS DATE);
@@ -841,7 +894,7 @@ x < CAST('2022-01-01 00:00:00' AS DATETIME) AND x >= CAST('2021-01-01 00:00:00'
 
 -- right side is not a date literal
 DATE_TRUNC('day', x) = CAST(y AS DATE);
-DATE_TRUNC('day', x) = CAST(y AS DATE);
+CAST(y AS DATE) = DATE_TRUNC('day', x);
 
 -- nested cast
 DATE_TRUNC('day', x) = CAST(CAST('2021-01-01 01:02:03' AS DATETIME) AS DATE);
@@ -905,10 +958,10 @@ DATE_SUB(x, 1, DAY) <> CAST('2021-01-01' AS DATE);
 x <> CAST('2021-01-02' AS DATE);
 
 DATE_ADD(DATE_ADD(DATE_TRUNC('week', DATE_SUB(x, 1, DAY)), 1, DAY), 1, YEAR) < CAST('2021-01-08' AS DATE);
-x < CAST('2020-01-07' AS DATE);
+x < CAST('2020-01-14' AS DATE);
 
 x - INTERVAL '1' day = CAST(y AS DATE);
-x - INTERVAL '1' day = CAST(y AS DATE);
+CAST(y AS DATE) = x - INTERVAL '1' day;
 
 --------------------------------------
 -- Constant Propagation
@@ -917,16 +970,16 @@ x = 5 AND y = x;
 x = 5 AND y = 5;
 
 5 = x AND y = x;
-5 = x AND y = 5;
+x = 5 AND y = 5;
 
 x = 5 OR y = x;
-x = 5 OR y = x;
+x = 5 OR x = y;
 
 (x = 5 AND y = x) OR y = 1;
 (x = 5 AND y = 5) OR y = 1;
 
 t.x = 5 AND y = x;
-t.x = 5 AND y = x;
+t.x = 5 AND x = y;
 
 t.x = 'a' AND y = CONCAT_WS('-', t.x, 'b');
 t.x = 'a' AND y = 'a-b';
@@ -938,7 +991,7 @@ x = 5 AND x = 6;
 FALSE;
 
 x = 5 AND (y = x OR z = 1);
-x = 5 AND (y = x OR z = 1);
+x = 5 AND (x = y OR z = 1);
 
 x = 5 AND x + 3 = 8;
 x = 5;
@@ -950,7 +1003,7 @@ x = 1 AND y > 0 AND (SELECT z = 5 FROM t WHERE y = 1);
 (SELECT z = 5 FROM t WHERE y = 1) AND x = 1 AND y > 0;
 
 x = 1 AND x = y AND (SELECT z FROM t WHERE a AND (b OR c));
-(SELECT z FROM t WHERE a AND (b OR c)) AND 1 = y AND x = 1;
+(SELECT z FROM t WHERE a AND (b OR c)) AND x = 1 AND y = 1;
 
 t1.a = 39 AND t2.b = t1.a AND t3.c = t2.b;
 t1.a = 39 AND t2.b = 39 AND t3.c = 39;
@@ -968,7 +1021,7 @@ x = y AND CASE WHEN x = 5 THEN FALSE ELSE TRUE END;
 CASE WHEN x = 5 THEN FALSE ELSE TRUE END AND x = y;
 
 x = 1 AND CASE WHEN y = 5 THEN x = z END;
-CASE WHEN y = 5 THEN 1 = z END AND x = 1;
+CASE WHEN y = 5 THEN z = 1 END AND x = 1;
 
 --------------------------------------
 -- Simplify Conditionals
@@ -1028,4 +1081,4 @@ CASE x WHEN y THEN z END;
 CASE WHEN x = y THEN z END;
 
 CASE x1 + x2 WHEN x3 THEN x4 WHEN x5 + x6 THEN x7 ELSE x8 END;
-CASE WHEN (x1 + x2) = x3 THEN x4 WHEN (x1 + x2) = (x5 + x6) THEN x7 ELSE x8 END;
+CASE WHEN x3 = (x1 + x2) THEN x4 WHEN (x1 + x2) = (x5 + x6) THEN x7 ELSE x8 END;
diff --git a/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql b/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql
index d8cf64f..f0f584f 100644
--- a/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql
+++ b/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql
@@ -30,8 +30,8 @@ WITH "customer_total_return" AS (
     SUM("store_returns"."sr_return_amt") AS "ctr_total_return"
   FROM "store_returns" AS "store_returns"
   JOIN "date_dim" AS "date_dim"
-    ON "date_dim"."d_year" = 2001
-    AND "store_returns"."sr_returned_date_sk" = "date_dim"."d_date_sk"
+    ON "date_dim"."d_date_sk" = "store_returns"."sr_returned_date_sk"
+    AND "date_dim"."d_year" = 2001
   GROUP BY
     "store_returns"."sr_customer_sk",
     "store_returns"."sr_store_sk"
@@ -47,13 +47,13 @@ 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"
+  ON "_u_0"."_u_1" = "ctr1"."ctr_store_sk"
 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"
+  ON "ctr1"."ctr_store_sk" = "store"."s_store_sk" AND "store"."s_state" = 'TN'
 WHERE
-  "ctr1"."ctr_total_return" > "_u_0"."_col_0"
+  "_u_0"."_col_0" < "ctr1"."ctr_total_return"
 ORDER BY
   "c_customer_id"
 LIMIT 100;
@@ -246,7 +246,7 @@ FROM "date_dim" AS "dt"
 JOIN "store_sales" AS "store_sales"
   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"
+  ON "item"."i_item_sk" = "store_sales"."ss_item_sk" AND "item"."i_manufact_id" = 427
 WHERE
   "dt"."d_moy" = 11
 GROUP BY
@@ -429,7 +429,7 @@ WITH "customer_2" AS (
     "date_dim"."d_date_sk" AS "d_date_sk",
     "date_dim"."d_year" AS "d_year"
   FROM "date_dim" AS "date_dim"
-), "cte" AS (
+), "year_total" AS (
   SELECT
     "customer"."c_customer_id" AS "customer_id",
     "customer"."c_first_name" AS "customer_first_name",
@@ -448,7 +448,7 @@ WITH "customer_2" AS (
   JOIN "store_sales" AS "store_sales"
     ON "customer"."c_customer_sk" = "store_sales"."ss_customer_sk"
   JOIN "date_dim_2" AS "date_dim"
-    ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
+    ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk"
   GROUP BY
     "customer"."c_customer_id",
     "customer"."c_first_name",
@@ -458,7 +458,7 @@ WITH "customer_2" AS (
     "customer"."c_login",
     "customer"."c_email_address",
     "date_dim"."d_year"
-), "cte_2" AS (
+  UNION ALL
   SELECT
     "customer"."c_customer_id" AS "customer_id",
     "customer"."c_first_name" AS "customer_first_name",
@@ -477,7 +477,7 @@ WITH "customer_2" AS (
     'c' AS "sale_type"
   FROM "customer_2" AS "customer"
   JOIN "catalog_sales" AS "catalog_sales"
-    ON "customer"."c_customer_sk" = "catalog_sales"."cs_bill_customer_sk"
+    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"
   GROUP BY
@@ -489,7 +489,7 @@ WITH "customer_2" AS (
     "customer"."c_login",
     "customer"."c_email_address",
     "date_dim"."d_year"
-), "cte_3" AS (
+  UNION ALL
   SELECT
     "customer"."c_customer_id" AS "customer_id",
     "customer"."c_first_name" AS "customer_first_name",
@@ -510,7 +510,7 @@ WITH "customer_2" AS (
   JOIN "web_sales" AS "web_sales"
     ON "customer"."c_customer_sk" = "web_sales"."ws_bill_customer_sk"
   JOIN "date_dim_2" AS "date_dim"
-    ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk"
+    ON "date_dim"."d_date_sk" = "web_sales"."ws_sold_date_sk"
   GROUP BY
     "customer"."c_customer_id",
     "customer"."c_first_name",
@@ -520,46 +520,6 @@ WITH "customer_2" AS (
     "customer"."c_login",
     "customer"."c_email_address",
     "date_dim"."d_year"
-), "cte_4" AS (
-  SELECT
-    "cte_2"."customer_id" AS "customer_id",
-    "cte_2"."customer_first_name" AS "customer_first_name",
-    "cte_2"."customer_last_name" AS "customer_last_name",
-    "cte_2"."customer_preferred_cust_flag" AS "customer_preferred_cust_flag",
-    "cte_2"."dyear" AS "dyear",
-    "cte_2"."year_total" AS "year_total",
-    "cte_2"."sale_type" AS "sale_type"
-  FROM "cte_2" AS "cte_2"
-  UNION ALL
-  SELECT
-    "cte_3"."customer_id" AS "customer_id",
-    "cte_3"."customer_first_name" AS "customer_first_name",
-    "cte_3"."customer_last_name" AS "customer_last_name",
-    "cte_3"."customer_preferred_cust_flag" AS "customer_preferred_cust_flag",
-    "cte_3"."dyear" AS "dyear",
-    "cte_3"."year_total" AS "year_total",
-    "cte_3"."sale_type" AS "sale_type"
-  FROM "cte_3" AS "cte_3"
-), "year_total" AS (
-  SELECT
-    "cte"."customer_id" AS "customer_id",
-    "cte"."customer_first_name" AS "customer_first_name",
-    "cte"."customer_last_name" AS "customer_last_name",
-    "cte"."customer_preferred_cust_flag" AS "customer_preferred_cust_flag",
-    "cte"."dyear" AS "dyear",
-    "cte"."year_total" AS "year_total",
-    "cte"."sale_type" AS "sale_type"
-  FROM "cte" AS "cte"
-  UNION ALL
-  SELECT
-    "cte_4"."customer_id" AS "customer_id",
-    "cte_4"."customer_first_name" AS "customer_first_name",
-    "cte_4"."customer_last_name" AS "customer_last_name",
-    "cte_4"."customer_preferred_cust_flag" AS "customer_preferred_cust_flag",
-    "cte_4"."dyear" AS "dyear",
-    "cte_4"."year_total" AS "year_total",
-    "cte_4"."sale_type" AS "sale_type"
-  FROM "cte_4" AS "cte_4"
 )
 SELECT
   "t_s_secyear"."customer_id" AS "customer_id",
@@ -568,11 +528,11 @@ SELECT
   "t_s_secyear"."customer_preferred_cust_flag" AS "customer_preferred_cust_flag"
 FROM "year_total" AS "t_s_firstyear"
 JOIN "year_total" AS "t_c_secyear"
-  ON "t_c_secyear"."dyear" = 2002
+  ON "t_c_secyear"."customer_id" = "t_s_firstyear"."customer_id"
+  AND "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"
+  ON "t_s_firstyear"."customer_id" = "t_s_secyear"."customer_id"
   AND "t_s_secyear"."dyear" = 2002
   AND "t_s_secyear"."sale_type" = 's'
 JOIN "year_total" AS "t_w_firstyear"
@@ -585,10 +545,10 @@ JOIN "year_total" AS "t_w_secyear"
   AND "t_w_secyear"."dyear" = 2002
   AND "t_w_secyear"."sale_type" = 'w'
 JOIN "year_total" AS "t_c_firstyear"
-  ON "t_c_firstyear"."dyear" = 2001
+  ON "t_c_firstyear"."customer_id" = "t_s_firstyear"."customer_id"
+  AND "t_c_firstyear"."dyear" = 2001
   AND "t_c_firstyear"."sale_type" = 'c'
   AND "t_c_firstyear"."year_total" > 0
-  AND "t_s_firstyear"."customer_id" = "t_c_firstyear"."customer_id"
   AND CASE
     WHEN "t_c_firstyear"."year_total" > 0
     THEN "t_c_secyear"."year_total" / "t_c_firstyear"."year_total"
@@ -782,7 +742,7 @@ WITH "salesreturns" AS (
     SUM("salesreturns"."net_loss") AS "profit_loss"
   FROM "salesreturns" AS "salesreturns"
   JOIN "date_dim_2" AS "date_dim"
-    ON "salesreturns"."date_sk" = "date_dim"."d_date_sk"
+    ON "date_dim"."d_date_sk" = "salesreturns"."date_sk"
   JOIN "store" AS "store"
     ON "salesreturns"."store_sk" = "store"."s_store_sk"
   GROUP BY
@@ -814,9 +774,9 @@ WITH "salesreturns" AS (
     SUM("salesreturns"."net_loss") AS "profit_loss"
   FROM "salesreturns_2" AS "salesreturns"
   JOIN "catalog_page" AS "catalog_page"
-    ON "salesreturns"."page_sk" = "catalog_page"."cp_catalog_page_sk"
+    ON "catalog_page"."cp_catalog_page_sk" = "salesreturns"."page_sk"
   JOIN "date_dim_2" AS "date_dim"
-    ON "salesreturns"."date_sk" = "date_dim"."d_date_sk"
+    ON "date_dim"."d_date_sk" = "salesreturns"."date_sk"
   GROUP BY
     "catalog_page"."cp_catalog_page_id"
 ), "salesreturns_3" AS (
@@ -849,12 +809,20 @@ WITH "salesreturns" AS (
     SUM("salesreturns"."net_loss") AS "profit_loss"
   FROM "salesreturns_3" AS "salesreturns"
   JOIN "date_dim_2" AS "date_dim"
-    ON "salesreturns"."date_sk" = "date_dim"."d_date_sk"
+    ON "date_dim"."d_date_sk" = "salesreturns"."date_sk"
   JOIN "web_site" AS "web_site"
     ON "salesreturns"."wsr_web_site_sk" = "web_site"."web_site_sk"
   GROUP BY
     "web_site"."web_site_id"
-), "cte_10" AS (
+), "x" AS (
+  SELECT
+    'store channel' AS "channel",
+    CONCAT('store', "ssr"."s_store_id") AS "id",
+    "ssr"."sales" AS "sales",
+    "ssr"."returns1" AS "returns1",
+    "ssr"."profit" - "ssr"."profit_loss" AS "profit"
+  FROM "ssr"
+  UNION ALL
   SELECT
     'catalog channel' AS "channel",
     CONCAT('catalog_page', "csr"."cp_catalog_page_id") AS "id",
@@ -870,22 +838,6 @@ WITH "salesreturns" AS (
     "wsr"."returns1" AS "returns1",
     "wsr"."profit" - "wsr"."profit_loss" AS "profit"
   FROM "wsr"
-), "x" AS (
-  SELECT
-    'store channel' AS "channel",
-    CONCAT('store', "ssr"."s_store_id") AS "id",
-    "ssr"."sales" AS "sales",
-    "ssr"."returns1" AS "returns1",
-    "ssr"."profit" - "ssr"."profit_loss" AS "profit"
-  FROM "ssr"
-  UNION ALL
-  SELECT
-    "cte_10"."channel" AS "channel",
-    "cte_10"."id" AS "id",
-    "cte_10"."sales" AS "sales",
-    "cte_10"."returns1" AS "returns1",
-    "cte_10"."profit" AS "profit"
-  FROM "cte_10" AS "cte_10"
 )
 SELECT
   "x"."channel" AS "channel",
@@ -952,11 +904,11 @@ JOIN "customer" AS "c"
 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"
+  ON "d"."d_date_sk" = "s"."ss_sold_date_sk"
 JOIN "item" AS "i"
-  ON "s"."ss_item_sk" = "i"."i_item_sk"
+  ON "i"."i_item_sk" = "s"."ss_item_sk"
 JOIN "_u_0" AS "_u_0"
-  ON "d"."d_month_seq" = "_u_0"."d_month_seq"
+  ON "_u_0"."d_month_seq" = "d"."d_month_seq"
 LEFT JOIN "_u_1" AS "_u_1"
   ON "_u_1"."_u_2" = "i"."i_category"
 WHERE
@@ -1003,20 +955,20 @@ SELECT
   AVG("store_sales"."ss_sales_price") AS "agg4"
 FROM "store_sales" AS "store_sales"
 JOIN "customer_demographics" AS "customer_demographics"
-  ON "customer_demographics"."cd_education_status" = '2 yr Degree'
+  ON "customer_demographics"."cd_demo_sk" = "store_sales"."ss_cdemo_sk"
+  AND "customer_demographics"."cd_education_status" = '2 yr Degree'
   AND "customer_demographics"."cd_gender" = 'F'
   AND "customer_demographics"."cd_marital_status" = 'W'
-  AND "store_sales"."ss_cdemo_sk" = "customer_demographics"."cd_demo_sk"
 JOIN "date_dim" AS "date_dim"
-  ON "date_dim"."d_year" = 1998
-  AND "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
+  ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk"
+  AND "date_dim"."d_year" = 1998
 JOIN "item" AS "item"
-  ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
+  ON "item"."i_item_sk" = "store_sales"."ss_item_sk"
 JOIN "promotion" AS "promotion"
   ON (
     "promotion"."p_channel_email" = 'N' OR "promotion"."p_channel_event" = 'N'
   )
-  AND "store_sales"."ss_promo_sk" = "promotion"."p_promo_sk"
+  AND "promotion"."p_promo_sk" = "store_sales"."ss_promo_sk"
 GROUP BY
   "item"."i_item_id"
 ORDER BY
@@ -1257,8 +1209,8 @@ WITH "a1" AS (
     SUBSTR("customer_address"."ca_zip", 1, 5) AS "ca_zip"
   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"
+    ON "customer"."c_current_addr_sk" = "customer_address"."ca_address_sk"
+    AND "customer"."c_preferred_cust_flag" = 'Y'
   GROUP BY
     "customer_address"."ca_zip"
   HAVING
@@ -1279,13 +1231,13 @@ SELECT
   SUM("store_sales"."ss_net_profit") AS "_col_1"
 FROM "store_sales" AS "store_sales"
 JOIN "date_dim" AS "date_dim"
-  ON "date_dim"."d_qoy" = 2
+  ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk"
+  AND "date_dim"."d_qoy" = 2
   AND "date_dim"."d_year" = 2000
-  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"
+  ON "store"."s_store_sk" = "store_sales"."ss_store_sk"
 JOIN "a2" AS "a2"
-  ON SUBSTR("store"."s_zip", 1, 2) = SUBSTR("a2"."ca_zip", 1, 2)
+  ON SUBSTR("a2"."ca_zip", 1, 2) = SUBSTR("store"."s_zip", 1, 2)
 GROUP BY
   "store"."s_store_name"
 ORDER BY
@@ -1595,7 +1547,7 @@ WITH "date_dim_2" AS (
     "store_sales"."ss_customer_sk" AS "_u_1"
   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"
+    ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk"
   GROUP BY
     "store_sales"."ss_customer_sk"
 ), "_u_2" AS (
@@ -1603,7 +1555,7 @@ WITH "date_dim_2" AS (
     "web_sales"."ws_bill_customer_sk" AS "_u_3"
   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"
+    ON "date_dim"."d_date_sk" = "web_sales"."ws_sold_date_sk"
   GROUP BY
     "web_sales"."ws_bill_customer_sk"
 ), "_u_4" AS (
@@ -1632,16 +1584,16 @@ SELECT
   COUNT(*) AS "cnt6"
 FROM "customer" AS "c"
 LEFT JOIN "_u_0" AS "_u_0"
-  ON "c"."c_customer_sk" = "_u_0"."_u_1"
+  ON "_u_0"."_u_1" = "c"."c_customer_sk"
 LEFT JOIN "_u_2" AS "_u_2"
-  ON "c"."c_customer_sk" = "_u_2"."_u_3"
+  ON "_u_2"."_u_3" = "c"."c_customer_sk"
 LEFT JOIN "_u_4" AS "_u_4"
-  ON "c"."c_customer_sk" = "_u_4"."_u_5"
+  ON "_u_4"."_u_5" = "c"."c_customer_sk"
 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"
+  ON "c"."c_current_cdemo_sk" = "customer_demographics"."cd_demo_sk"
 WHERE
   NOT "_u_0"."_u_1" IS NULL
   AND (
@@ -1782,7 +1734,7 @@ WITH "customer_2" AS (
     "date_dim"."d_date_sk" AS "d_date_sk",
     "date_dim"."d_year" AS "d_year"
   FROM "date_dim" AS "date_dim"
-), "cte" AS (
+), "year_total" AS (
   SELECT
     "customer"."c_customer_id" AS "customer_id",
     "customer"."c_first_name" AS "customer_first_name",
@@ -1795,7 +1747,7 @@ WITH "customer_2" AS (
   JOIN "store_sales" AS "store_sales"
     ON "customer"."c_customer_sk" = "store_sales"."ss_customer_sk"
   JOIN "date_dim_2" AS "date_dim"
-    ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
+    ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk"
   GROUP BY
     "customer"."c_customer_id",
     "customer"."c_first_name",
@@ -1805,7 +1757,7 @@ WITH "customer_2" AS (
     "customer"."c_login",
     "customer"."c_email_address",
     "date_dim"."d_year"
-), "cte_2" AS (
+  UNION ALL
   SELECT
     "customer"."c_customer_id" AS "customer_id",
     "customer"."c_first_name" AS "customer_first_name",
@@ -1818,7 +1770,7 @@ WITH "customer_2" AS (
   JOIN "web_sales" AS "web_sales"
     ON "customer"."c_customer_sk" = "web_sales"."ws_bill_customer_sk"
   JOIN "date_dim_2" AS "date_dim"
-    ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk"
+    ON "date_dim"."d_date_sk" = "web_sales"."ws_sold_date_sk"
   GROUP BY
     "customer"."c_customer_id",
     "customer"."c_first_name",
@@ -1828,26 +1780,6 @@ WITH "customer_2" AS (
     "customer"."c_login",
     "customer"."c_email_address",
     "date_dim"."d_year"
-), "year_total" AS (
-  SELECT
-    "cte"."customer_id" AS "customer_id",
-    "cte"."customer_first_name" AS "customer_first_name",
-    "cte"."customer_last_name" AS "customer_last_name",
-    "cte"."customer_birth_country" AS "customer_birth_country",
-    "cte"."dyear" AS "dyear",
-    "cte"."year_total" AS "year_total",
-    "cte"."sale_type" AS "sale_type"
-  FROM "cte" AS "cte"
-  UNION ALL
-  SELECT
-    "cte_2"."customer_id" AS "customer_id",
-    "cte_2"."customer_first_name" AS "customer_first_name",
-    "cte_2"."customer_last_name" AS "customer_last_name",
-    "cte_2"."customer_birth_country" AS "customer_birth_country",
-    "cte_2"."dyear" AS "dyear",
-    "cte_2"."year_total" AS "year_total",
-    "cte_2"."sale_type" AS "sale_type"
-  FROM "cte_2" AS "cte_2"
 )
 SELECT
   "t_s_secyear"."customer_id" AS "customer_id",
@@ -1865,17 +1797,17 @@ JOIN "year_total" AS "t_w_secyear"
   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"
+  ON "t_s_firstyear"."customer_id" = "t_s_secyear"."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"
-    ELSE 0.0
-  END > CASE
     WHEN "t_s_firstyear"."year_total" > 0
     THEN "t_s_secyear"."year_total" / "t_s_firstyear"."year_total"
     ELSE 0.0
+  END < CASE
+    WHEN "t_w_firstyear"."year_total" > 0
+    THEN "t_w_secyear"."year_total" / "t_w_firstyear"."year_total"
+    ELSE 0.0
   END
 WHERE
   "t_s_firstyear"."dyear" = 2001
@@ -1930,12 +1862,12 @@ SELECT
   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 "date_dim" AS "date_dim"
-  ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk"
+  ON "date_dim"."d_date_sk" = "web_sales"."ws_sold_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"
+  AND "item"."i_item_sk" = "web_sales"."ws_item_sk"
 GROUP BY
   "item"."i_item_id",
   "item"."i_item_desc",
@@ -2005,36 +1937,36 @@ FROM "store_sales" AS "store_sales"
 CROSS JOIN "customer_demographics" AS "customer_demographics"
 JOIN "customer_address" AS "customer_address"
   ON (
-    "customer_address"."ca_country" = 'United States'
+    "customer_address"."ca_address_sk" = "store_sales"."ss_addr_sk"
+    AND "customer_address"."ca_country" = 'United States'
     AND "customer_address"."ca_state" IN ('AZ', 'NE', 'IA')
-    AND "store_sales"."ss_addr_sk" = "customer_address"."ca_address_sk"
     AND "store_sales"."ss_net_profit" <= 200
     AND "store_sales"."ss_net_profit" >= 100
   )
   OR (
-    "customer_address"."ca_country" = 'United States'
+    "customer_address"."ca_address_sk" = "store_sales"."ss_addr_sk"
+    AND "customer_address"."ca_country" = 'United States'
     AND "customer_address"."ca_state" IN ('GA', 'TX', 'NJ')
-    AND "store_sales"."ss_addr_sk" = "customer_address"."ca_address_sk"
     AND "store_sales"."ss_net_profit" <= 250
     AND "store_sales"."ss_net_profit" >= 50
   )
   OR (
-    "customer_address"."ca_country" = 'United States'
+    "customer_address"."ca_address_sk" = "store_sales"."ss_addr_sk"
+    AND "customer_address"."ca_country" = 'United States'
     AND "customer_address"."ca_state" IN ('MS', 'CA', 'NV')
-    AND "store_sales"."ss_addr_sk" = "customer_address"."ca_address_sk"
     AND "store_sales"."ss_net_profit" <= 300
     AND "store_sales"."ss_net_profit" >= 150
   )
 JOIN "date_dim" AS "date_dim"
-  ON "date_dim"."d_year" = 2001
-  AND "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
+  ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk"
+  AND "date_dim"."d_year" = 2001
 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_marital_status" = 'U'
+    AND "household_demographics"."hd_demo_sk" = "store_sales"."ss_hdemo_sk"
     AND "household_demographics"."hd_dep_count" = 3
-    AND "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk"
     AND "store_sales"."ss_sales_price" <= 150.00
     AND "store_sales"."ss_sales_price" >= 100.00
   )
@@ -2042,8 +1974,8 @@ JOIN "household_demographics" AS "household_demographics"
     "customer_demographics"."cd_demo_sk" = "store_sales"."ss_cdemo_sk"
     AND "customer_demographics"."cd_education_status" = 'Primary'
     AND "customer_demographics"."cd_marital_status" = 'M'
+    AND "household_demographics"."hd_demo_sk" = "store_sales"."ss_hdemo_sk"
     AND "household_demographics"."hd_dep_count" = 1
-    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" >= 50.00
   )
@@ -2051,8 +1983,8 @@ JOIN "household_demographics" AS "household_demographics"
     "customer_demographics"."cd_demo_sk" = "store_sales"."ss_cdemo_sk"
     AND "customer_demographics"."cd_education_status" = 'Secondary'
     AND "customer_demographics"."cd_marital_status" = 'D'
+    AND "household_demographics"."hd_demo_sk" = "store_sales"."ss_hdemo_sk"
     AND "household_demographics"."hd_dep_count" = 1
-    AND "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk"
     AND "store_sales"."ss_sales_price" <= 200.00
     AND "store_sales"."ss_sales_price" >= 150.00
   )
@@ -2200,7 +2132,19 @@ WITH "item_2" AS (
     "item"."i_class_id" AS "i_class_id",
     "item"."i_category_id" AS "i_category_id"
   FROM "item" AS "item"
-), "cte_4" AS (
+), "_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 "date_dim" AS "d1"
+    ON "d1"."d_date_sk" = "store_sales"."ss_sold_date_sk"
+    AND "d1"."d_year" <= 2001
+    AND "d1"."d_year" >= 1999
+  JOIN "item" AS "iss"
+    ON "iss"."i_item_sk" = "store_sales"."ss_item_sk"
+  INTERSECT
   SELECT
     "ics"."i_brand_id" AS "i_brand_id",
     "ics"."i_class_id" AS "i_class_id",
@@ -2219,29 +2163,11 @@ WITH "item_2" AS (
     "iws"."i_category_id" AS "i_category_id"
   FROM "web_sales" AS "web_sales"
   JOIN "date_dim" AS "d3"
-    ON "d3"."d_year" <= 2001
+    ON "d3"."d_date_sk" = "web_sales"."ws_sold_date_sk"
+    AND "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"
-), "_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 "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"
-  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"
+    ON "iws"."i_item_sk" = "web_sales"."ws_item_sk"
 ), "date_dim_2" AS (
   SELECT
     "date_dim"."d_date_sk" AS "d_date_sk",
@@ -2249,7 +2175,14 @@ WITH "item_2" AS (
   FROM "date_dim" AS "date_dim"
   WHERE
     "date_dim"."d_year" <= 2001 AND "date_dim"."d_year" >= 1999
-), "cte_8" AS (
+), "x" AS (
+  SELECT
+    "store_sales"."ss_quantity" AS "quantity",
+    "store_sales"."ss_list_price" AS "list_price"
+  FROM "store_sales" AS "store_sales"
+  JOIN "date_dim_2" AS "date_dim"
+    ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk"
+  UNION ALL
   SELECT
     "catalog_sales"."cs_quantity" AS "quantity",
     "catalog_sales"."cs_list_price" AS "list_price"
@@ -2262,19 +2195,7 @@ WITH "item_2" AS (
     "web_sales"."ws_list_price" AS "list_price"
   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"
-), "x" AS (
-  SELECT
-    "store_sales"."ss_quantity" AS "quantity",
-    "store_sales"."ss_list_price" AS "list_price"
-  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"
-  UNION ALL
-  SELECT
-    "cte_8"."quantity" AS "quantity",
-    "cte_8"."list_price" AS "list_price"
-  FROM "cte_8" AS "cte_8"
+    ON "date_dim"."d_date_sk" = "web_sales"."ws_sold_date_sk"
 ), "avg_sales" AS (
   SELECT
     AVG("x"."quantity" * "x"."list_price") AS "average_sales"
@@ -2288,9 +2209,9 @@ WITH "item_2" AS (
     "item"."i_item_sk" AS "ss_item_sk"
   FROM "item_2" AS "item"
   JOIN "_q_0" AS "_q_0"
-    ON "item"."i_brand_id" = "_q_0"."brand_id"
-    AND "item"."i_category_id" = "_q_0"."category_id"
-    AND "item"."i_class_id" = "_q_0"."class_id"
+    ON "_q_0"."brand_id" = "item"."i_brand_id"
+    AND "_q_0"."category_id" = "item"."i_category_id"
+    AND "_q_0"."class_id" = "item"."i_class_id"
   GROUP BY
     "item"."i_item_sk"
 ), "date_dim_3" AS (
@@ -2301,7 +2222,7 @@ WITH "item_2" AS (
   FROM "date_dim" AS "date_dim"
   WHERE
     "date_dim"."d_moy" = 11 AND "date_dim"."d_year" = 2001
-), "cte_9" AS (
+), "y" AS (
   SELECT
     'store' AS "channel",
     "item"."i_brand_id" AS "i_brand_id",
@@ -2312,11 +2233,11 @@ WITH "item_2" AS (
   FROM "store_sales" AS "store_sales"
   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"
+    ON "_u_0"."ss_item_sk" = "store_sales"."ss_item_sk"
   JOIN "date_dim_3" AS "date_dim"
-    ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
+    ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk"
   JOIN "item_2" AS "item"
-    ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
+    ON "item"."i_item_sk" = "store_sales"."ss_item_sk"
   WHERE
     NOT "_u_0"."ss_item_sk" IS NULL
   GROUP BY
@@ -2324,8 +2245,8 @@ WITH "item_2" AS (
     "item"."i_class_id",
     "item"."i_category_id"
   HAVING
-    SUM("store_sales"."ss_quantity" * "store_sales"."ss_list_price") > MAX("_u_1"."average_sales")
-), "cte_10" AS (
+    MAX("_u_1"."average_sales") < SUM("store_sales"."ss_quantity" * "store_sales"."ss_list_price")
+  UNION ALL
   SELECT
     'catalog' AS "channel",
     "item"."i_brand_id" AS "i_brand_id",
@@ -2336,7 +2257,7 @@ WITH "item_2" AS (
   FROM "catalog_sales" AS "catalog_sales"
   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"
+    ON "_u_2"."ss_item_sk" = "catalog_sales"."cs_item_sk"
   JOIN "date_dim_3" AS "date_dim"
     ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk"
   JOIN "item_2" AS "item"
@@ -2348,8 +2269,8 @@ WITH "item_2" AS (
     "item"."i_class_id",
     "item"."i_category_id"
   HAVING
-    SUM("catalog_sales"."cs_quantity" * "catalog_sales"."cs_list_price") > MAX("_u_3"."average_sales")
-), "cte_11" AS (
+    MAX("_u_3"."average_sales") < SUM("catalog_sales"."cs_quantity" * "catalog_sales"."cs_list_price")
+  UNION ALL
   SELECT
     'web' AS "channel",
     "item"."i_brand_id" AS "i_brand_id",
@@ -2360,11 +2281,11 @@ WITH "item_2" AS (
   FROM "web_sales" AS "web_sales"
   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"
+    ON "_u_4"."ss_item_sk" = "web_sales"."ws_item_sk"
   JOIN "date_dim_3" AS "date_dim"
-    ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk"
+    ON "date_dim"."d_date_sk" = "web_sales"."ws_sold_date_sk"
   JOIN "item_2" AS "item"
-    ON "web_sales"."ws_item_sk" = "item"."i_item_sk"
+    ON "item"."i_item_sk" = "web_sales"."ws_item_sk"
   WHERE
     NOT "_u_4"."ss_item_sk" IS NULL
   GROUP BY
@@ -2372,43 +2293,7 @@ WITH "item_2" AS (
     "item"."i_class_id",
     "item"."i_category_id"
   HAVING
-    SUM("web_sales"."ws_quantity" * "web_sales"."ws_list_price") > MAX("_u_5"."average_sales")
-), "cte_12" AS (
-  SELECT
-    "cte_10"."channel" AS "channel",
-    "cte_10"."i_brand_id" AS "i_brand_id",
-    "cte_10"."i_class_id" AS "i_class_id",
-    "cte_10"."i_category_id" AS "i_category_id",
-    "cte_10"."sales" AS "sales",
-    "cte_10"."number_sales" AS "number_sales"
-  FROM "cte_10" AS "cte_10"
-  UNION ALL
-  SELECT
-    "cte_11"."channel" AS "channel",
-    "cte_11"."i_brand_id" AS "i_brand_id",
-    "cte_11"."i_class_id" AS "i_class_id",
-    "cte_11"."i_category_id" AS "i_category_id",
-    "cte_11"."sales" AS "sales",
-    "cte_11"."number_sales" AS "number_sales"
-  FROM "cte_11" AS "cte_11"
-), "y" AS (
-  SELECT
-    "cte_9"."channel" AS "channel",
-    "cte_9"."i_brand_id" AS "i_brand_id",
-    "cte_9"."i_class_id" AS "i_class_id",
-    "cte_9"."i_category_id" AS "i_category_id",
-    "cte_9"."sales" AS "sales",
-    "cte_9"."number_sales" AS "number_sales"
-  FROM "cte_9" AS "cte_9"
-  UNION ALL
-  SELECT
-    "cte_12"."channel" AS "channel",
-    "cte_12"."i_brand_id" AS "i_brand_id",
-    "cte_12"."i_class_id" AS "i_class_id",
-    "cte_12"."i_category_id" AS "i_category_id",
-    "cte_12"."sales" AS "sales",
-    "cte_12"."number_sales" AS "number_sales"
-  FROM "cte_12" AS "cte_12"
+    MAX("_u_5"."average_sales") < SUM("web_sales"."ws_quantity" * "web_sales"."ws_list_price")
 )
 SELECT
   "y"."channel" AS "channel",
@@ -2530,21 +2415,21 @@ SELECT
   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"
+  ON "_u_0"."_u_1" = "cs1"."cs_order_number"
 LEFT JOIN "_u_3" AS "_u_3"
-  ON "cs1"."cs_order_number" = "_u_3"."_u_4"
+  ON "_u_3"."_u_4" = "cs1"."cs_order_number"
 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"
+  ON "call_center"."cc_call_center_sk" = "cs1"."cs_call_center_sk"
+  AND "call_center"."cc_county" IN ('Williamson County', 'Williamson County', 'Williamson County', 'Williamson County', 'Williamson County')
 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 "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) <= (
+  AND (
     CAST('2002-3-01' AS DATE) + INTERVAL '60' day
-  )
+  ) >= CAST("date_dim"."d_date" AS DATE)
 WHERE
   "_u_3"."_u_4" IS NULL
   AND NOT "_u_0"."_u_1" IS NULL
@@ -2636,15 +2521,15 @@ JOIN "item" AS "item"
 JOIN "store" AS "store"
   ON "store"."s_store_sk" = "store_sales"."ss_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"
+  ON "store_returns"."sr_customer_sk" = "store_sales"."ss_customer_sk"
+  AND "store_returns"."sr_item_sk" = "store_sales"."ss_item_sk"
+  AND "store_returns"."sr_ticket_number" = "store_sales"."ss_ticket_number"
 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"
+  ON "catalog_sales"."cs_bill_customer_sk" = "store_returns"."sr_customer_sk"
+  AND "catalog_sales"."cs_item_sk" = "store_returns"."sr_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"
+  ON "d2"."d_date_sk" = "store_returns"."sr_returned_date_sk"
+  AND "d2"."d_quarter_name" IN ('1999Q1', '1999Q2', '1999Q3')
 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')
@@ -2724,7 +2609,7 @@ JOIN "date_dim" AS "date_dim"
 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"
+  ON "cd2"."cd_demo_sk" = "customer"."c_current_cdemo_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')
@@ -2785,14 +2670,14 @@ 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"
+  ON "item"."i_item_sk" = "store_sales"."ss_item_sk" AND "item"."i_manager_id" = 38
 JOIN "store" AS "store"
-  ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
+  ON "store"."s_store_sk" = "store_sales"."ss_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"
+  AND "customer"."c_customer_sk" = "store_sales"."ss_customer_sk"
 WHERE
   "date_dim"."d_moy" = 12 AND "date_dim"."d_year" = 1998
 GROUP BY
@@ -2930,13 +2815,13 @@ WITH "x" AS (
     ) AS "inv_after"
   FROM "inventory" AS "inventory"
   JOIN "date_dim" AS "date_dim"
-    ON "inventory"."inv_date_sk" = "date_dim"."d_date_sk"
+    ON "date_dim"."d_date_sk" = "inventory"."inv_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
+    ON "inventory"."inv_item_sk" = "item"."i_item_sk"
+    AND "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
@@ -2988,9 +2873,9 @@ SELECT
   AVG("inventory"."inv_quantity_on_hand") AS "qoh"
 FROM "inventory" AS "inventory"
 JOIN "date_dim" AS "date_dim"
-  ON "date_dim"."d_month_seq" <= 1216
+  ON "date_dim"."d_date_sk" = "inventory"."inv_date_sk"
+  AND "date_dim"."d_month_seq" <= 1216
   AND "date_dim"."d_month_seq" >= 1205
-  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"
@@ -3076,10 +2961,10 @@ WITH "frequent_ss_items" AS (
     "item"."i_item_sk" AS "item_sk"
   FROM "store_sales" AS "store_sales"
   JOIN "date_dim" AS "date_dim"
-    ON "date_dim"."d_year" IN (1998, 1999, 2000, 2001)
-    AND "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
+    ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk"
+    AND "date_dim"."d_year" IN (1998, 1999, 2000, 2001)
   JOIN "item" AS "item"
-    ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
+    ON "item"."i_item_sk" = "store_sales"."ss_item_sk"
   GROUP BY
     SUBSTR("item"."i_item_desc", 1, 30),
     "item"."i_item_sk",
@@ -3095,10 +2980,10 @@ WITH "frequent_ss_items" AS (
     SUM("store_sales"."ss_quantity" * "store_sales"."ss_sales_price") AS "csales"
   FROM "store_sales" AS "store_sales"
   JOIN "customer_2" AS "customer"
-    ON "store_sales"."ss_customer_sk" = "customer"."c_customer_sk"
+    ON "customer"."c_customer_sk" = "store_sales"."ss_customer_sk"
   JOIN "date_dim" AS "date_dim"
-    ON "date_dim"."d_year" IN (1998, 1999, 2000, 2001)
-    AND "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
+    ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk"
+    AND "date_dim"."d_year" IN (1998, 1999, 2000, 2001)
   GROUP BY
     "customer"."c_customer_sk"
 ), "max_store_sales" AS (
@@ -3111,11 +2996,11 @@ WITH "frequent_ss_items" AS (
   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"
+    ON "customer"."c_customer_sk" = "store_sales"."ss_customer_sk"
   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")
+    0.95 * MAX("max_store_sales"."tpcds_cmax") < SUM("store_sales"."ss_quantity" * "store_sales"."ss_sales_price")
 ), "_u_1" AS (
   SELECT
     "frequent_ss_items"."item_sk" AS "item_sk"
@@ -3141,9 +3026,9 @@ WITH "frequent_ss_items" AS (
     "catalog_sales"."cs_quantity" * "catalog_sales"."cs_list_price" AS "sales"
   FROM "catalog_sales" AS "catalog_sales"
   LEFT JOIN "_u_1" AS "_u_1"
-    ON "catalog_sales"."cs_item_sk" = "_u_1"."item_sk"
+    ON "_u_1"."item_sk" = "catalog_sales"."cs_item_sk"
   LEFT JOIN "_u_2" AS "_u_2"
-    ON "catalog_sales"."cs_bill_customer_sk" = "_u_2"."c_customer_sk"
+    ON "_u_2"."c_customer_sk" = "catalog_sales"."cs_bill_customer_sk"
   JOIN "date_dim_4" AS "date_dim"
     ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk"
   WHERE
@@ -3153,11 +3038,11 @@ WITH "frequent_ss_items" AS (
     "web_sales"."ws_quantity" * "web_sales"."ws_list_price" AS "sales"
   FROM "web_sales" AS "web_sales"
   LEFT JOIN "_u_1" AS "_u_3"
-    ON "web_sales"."ws_item_sk" = "_u_3"."item_sk"
+    ON "_u_3"."item_sk" = "web_sales"."ws_item_sk"
   LEFT JOIN "_u_2" AS "_u_4"
-    ON "web_sales"."ws_bill_customer_sk" = "_u_4"."c_customer_sk"
+    ON "_u_4"."c_customer_sk" = "web_sales"."ws_bill_customer_sk"
   JOIN "date_dim_4" AS "date_dim"
-    ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk"
+    ON "date_dim"."d_date_sk" = "web_sales"."ws_sold_date_sk"
   WHERE
     NOT "_u_3"."item_sk" IS NULL AND NOT "_u_4"."c_customer_sk" IS NULL
 )
@@ -3225,17 +3110,17 @@ WITH "ssales" AS (
     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"
+    ON "item"."i_item_sk" = "store_sales"."ss_item_sk"
   JOIN "store" AS "store"
-    ON "store"."s_market_id" = 6 AND "store_sales"."ss_store_sk" = "store"."s_store_sk"
+    ON "store"."s_market_id" = 6 AND "store"."s_store_sk" = "store_sales"."ss_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"
+    ON "store_returns"."sr_item_sk" = "store_sales"."ss_item_sk"
+    AND "store_returns"."sr_ticket_number" = "store_sales"."ss_ticket_number"
   JOIN "customer_address" AS "customer_address"
-    ON "store"."s_zip" = "customer_address"."ca_zip"
+    ON "customer_address"."ca_zip" = "store"."s_zip"
   JOIN "customer" AS "customer"
     ON "customer"."c_birth_country" = UPPER("customer_address"."ca_country")
-    AND "store_sales"."ss_customer_sk" = "customer"."c_customer_sk"
+    AND "customer"."c_customer_sk" = "store_sales"."ss_customer_sk"
   GROUP BY
     "customer"."c_last_name",
     "customer"."c_first_name",
@@ -3266,7 +3151,7 @@ GROUP BY
   "ssales"."c_first_name",
   "ssales"."s_store_name"
 HAVING
-  SUM("ssales"."netpaid") > MAX("_u_0"."_col_0");
+  MAX("_u_0"."_col_0") < SUM("ssales"."netpaid");
 
 --------------------------------------
 -- TPC-DS 25
@@ -3329,17 +3214,17 @@ JOIN "item" AS "item"
 JOIN "store" AS "store"
   ON "store"."s_store_sk" = "store_sales"."ss_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"
+  ON "store_returns"."sr_customer_sk" = "store_sales"."ss_customer_sk"
+  AND "store_returns"."sr_item_sk" = "store_sales"."ss_item_sk"
+  AND "store_returns"."sr_ticket_number" = "store_sales"."ss_ticket_number"
 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"
+  ON "catalog_sales"."cs_bill_customer_sk" = "store_returns"."sr_customer_sk"
+  AND "catalog_sales"."cs_item_sk" = "store_returns"."sr_item_sk"
 JOIN "date_dim" AS "d2"
-  ON "d2"."d_moy" <= 10
+  ON "d2"."d_date_sk" = "store_returns"."sr_returned_date_sk"
+  AND "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
@@ -3450,18 +3335,18 @@ SELECT
   AVG("store_sales"."ss_sales_price") AS "agg4"
 FROM "store_sales" AS "store_sales"
 JOIN "customer_demographics" AS "customer_demographics"
-  ON "customer_demographics"."cd_education_status" = 'College'
+  ON "customer_demographics"."cd_demo_sk" = "store_sales"."ss_cdemo_sk"
+  AND "customer_demographics"."cd_education_status" = 'College'
   AND "customer_demographics"."cd_gender" = 'M'
   AND "customer_demographics"."cd_marital_status" = 'D'
-  AND "store_sales"."ss_cdemo_sk" = "customer_demographics"."cd_demo_sk"
 JOIN "date_dim" AS "date_dim"
-  ON "date_dim"."d_year" = 2000
-  AND "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
+  ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk"
+  AND "date_dim"."d_year" = 2000
 JOIN "item" AS "item"
-  ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
+  ON "item"."i_item_sk" = "store_sales"."ss_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"
+  AND "store"."s_store_sk" = "store_sales"."ss_store_sk"
 GROUP BY
 ROLLUP (
   "item"."i_item_id",
@@ -3697,17 +3582,17 @@ JOIN "item" AS "item"
 JOIN "store" AS "store"
   ON "store"."s_store_sk" = "store_sales"."ss_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"
+  ON "store_returns"."sr_customer_sk" = "store_sales"."ss_customer_sk"
+  AND "store_returns"."sr_item_sk" = "store_sales"."ss_item_sk"
+  AND "store_returns"."sr_ticket_number" = "store_sales"."ss_ticket_number"
 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"
+  ON "catalog_sales"."cs_bill_customer_sk" = "store_returns"."sr_customer_sk"
+  AND "catalog_sales"."cs_item_sk" = "store_returns"."sr_item_sk"
 JOIN "date_dim" AS "d2"
-  ON "d2"."d_moy" <= 7
+  ON "d2"."d_date_sk" = "store_returns"."sr_returned_date_sk"
+  AND "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)
@@ -3781,10 +3666,10 @@ WITH "customer_total_return" AS (
     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"
+    ON "customer_address"."ca_address_sk" = "web_returns"."wr_returning_addr_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"
+    ON "date_dim"."d_date_sk" = "web_returns"."wr_returned_date_sk"
+    AND "date_dim"."d_year" = 2000
   GROUP BY
     "web_returns"."wr_returning_customer_sk",
     "customer_address"."ca_state"
@@ -3812,14 +3697,14 @@ SELECT
   "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"
+  ON "_u_0"."_u_1" = "ctr1"."ctr_state"
 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"
+  ON "customer"."c_current_addr_sk" = "customer_address"."ca_address_sk"
   AND "customer_address"."ca_state" = 'IN'
 WHERE
-  "ctr1"."ctr_total_return" > "_u_0"."_col_0"
+  "_u_0"."_col_0" < "ctr1"."ctr_total_return"
 ORDER BY
   "c_customer_id",
   "c_salutation",
@@ -3930,9 +3815,9 @@ WITH "customer_address_2" AS (
     SUM("store_sales"."ss_ext_sales_price") AS "store_sales"
   FROM "store_sales" AS "store_sales"
   JOIN "customer_address_2" AS "customer_address"
-    ON "store_sales"."ss_addr_sk" = "customer_address"."ca_address_sk"
+    ON "customer_address"."ca_address_sk" = "store_sales"."ss_addr_sk"
   JOIN "date_dim_2" AS "date_dim"
-    ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
+    ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk"
   GROUP BY
     "customer_address"."ca_county",
     "date_dim"."d_qoy",
@@ -3945,9 +3830,9 @@ WITH "customer_address_2" AS (
     SUM("web_sales"."ws_ext_sales_price") AS "web_sales"
   FROM "web_sales" AS "web_sales"
   JOIN "customer_address_2" AS "customer_address"
-    ON "web_sales"."ws_bill_addr_sk" = "customer_address"."ca_address_sk"
+    ON "customer_address"."ca_address_sk" = "web_sales"."ws_bill_addr_sk"
   JOIN "date_dim_2" AS "date_dim"
-    ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk"
+    ON "date_dim"."d_date_sk" = "web_sales"."ws_sold_date_sk"
   GROUP BY
     "customer_address"."ca_county",
     "date_dim"."d_qoy",
@@ -3970,13 +3855,13 @@ JOIN "ws" AS "ws2"
   AND "ws2"."d_qoy" = 2
   AND "ws2"."d_year" = 2001
   AND CASE
-    WHEN "ws1"."web_sales" > 0
-    THEN "ws2"."web_sales" / "ws1"."web_sales"
-    ELSE NULL
-  END > CASE
     WHEN "ss1"."store_sales" > 0
     THEN "ss2"."store_sales" / "ss1"."store_sales"
     ELSE NULL
+  END < CASE
+    WHEN "ws1"."web_sales" > 0
+    THEN "ws2"."web_sales" / "ws1"."web_sales"
+    ELSE NULL
   END
 JOIN "ws" AS "ws3"
   ON "ws1"."ca_county" = "ws3"."ca_county" AND "ws3"."d_qoy" = 3 AND "ws3"."d_year" = 2001
@@ -3985,13 +3870,13 @@ JOIN "ss" AS "ss3"
   AND "ss3"."d_qoy" = 3
   AND "ss3"."d_year" = 2001
   AND CASE
-    WHEN "ws2"."web_sales" > 0
-    THEN "ws3"."web_sales" / "ws2"."web_sales"
-    ELSE NULL
-  END > CASE
     WHEN "ss2"."store_sales" > 0
     THEN "ss3"."store_sales" / "ss2"."store_sales"
     ELSE NULL
+  END < CASE
+    WHEN "ws2"."web_sales" > 0
+    THEN "ws3"."web_sales" / "ws2"."web_sales"
+    ELSE NULL
   END
 WHERE
   "ss1"."d_qoy" = 1 AND "ss1"."d_year" = 2001
@@ -4041,7 +3926,7 @@ WITH "catalog_sales_2" AS (
     "catalog_sales"."cs_item_sk" AS "_u_1"
   FROM "catalog_sales_2" AS "catalog_sales"
   JOIN "date_dim_2" AS "date_dim"
-    ON "date_dim"."d_date_sk" = "catalog_sales"."cs_sold_date_sk"
+    ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk"
   GROUP BY
     "catalog_sales"."cs_item_sk"
 )
@@ -4049,13 +3934,13 @@ SELECT
   SUM("catalog_sales"."cs_ext_discount_amt") AS "excess discount amount"
 FROM "catalog_sales_2" AS "catalog_sales"
 JOIN "date_dim_2" AS "date_dim"
-  ON "date_dim"."d_date_sk" = "catalog_sales"."cs_sold_date_sk"
+  ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk"
 JOIN "item" AS "item"
-  ON "item"."i_item_sk" = "catalog_sales"."cs_item_sk" AND "item"."i_manufact_id" = 610
+  ON "catalog_sales"."cs_item_sk" = "item"."i_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
-  "catalog_sales"."cs_ext_discount_amt" > "_u_0"."_col_0"
+  "_u_0"."_col_0" < "catalog_sales"."cs_ext_discount_amt"
 LIMIT 100;
 
 --------------------------------------
@@ -4159,13 +4044,13 @@ WITH "customer_address_2" AS (
     SUM("store_sales"."ss_ext_sales_price") AS "total_sales"
   FROM "store_sales" AS "store_sales"
   JOIN "customer_address_2" AS "customer_address"
-    ON "store_sales"."ss_addr_sk" = "customer_address"."ca_address_sk"
+    ON "customer_address"."ca_address_sk" = "store_sales"."ss_addr_sk"
   JOIN "date_dim_2" AS "date_dim"
-    ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
+    ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk"
   JOIN "item_2" AS "item"
-    ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
+    ON "item"."i_item_sk" = "store_sales"."ss_item_sk"
   LEFT JOIN "_u_0" AS "_u_0"
-    ON "item"."i_manufact_id" = "_u_0"."i_manufact_id"
+    ON "_u_0"."i_manufact_id" = "item"."i_manufact_id"
   WHERE
     NOT "_u_0"."i_manufact_id" IS NULL
   GROUP BY
@@ -4182,7 +4067,7 @@ WITH "customer_address_2" AS (
   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"
+    ON "_u_1"."i_manufact_id" = "item"."i_manufact_id"
   WHERE
     NOT "_u_1"."i_manufact_id" IS NULL
   GROUP BY
@@ -4193,18 +4078,23 @@ WITH "customer_address_2" AS (
     SUM("web_sales"."ws_ext_sales_price") AS "total_sales"
   FROM "web_sales" AS "web_sales"
   JOIN "customer_address_2" AS "customer_address"
-    ON "web_sales"."ws_bill_addr_sk" = "customer_address"."ca_address_sk"
+    ON "customer_address"."ca_address_sk" = "web_sales"."ws_bill_addr_sk"
   JOIN "date_dim_2" AS "date_dim"
-    ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk"
+    ON "date_dim"."d_date_sk" = "web_sales"."ws_sold_date_sk"
   JOIN "item_2" AS "item"
-    ON "web_sales"."ws_item_sk" = "item"."i_item_sk"
+    ON "item"."i_item_sk" = "web_sales"."ws_item_sk"
   LEFT JOIN "_u_0" AS "_u_2"
-    ON "item"."i_manufact_id" = "_u_2"."i_manufact_id"
+    ON "_u_2"."i_manufact_id" = "item"."i_manufact_id"
   WHERE
     NOT "_u_2"."i_manufact_id" IS NULL
   GROUP BY
     "item"."i_manufact_id"
-), "cte_4" AS (
+), "tmp1" AS (
+  SELECT
+    "ss"."i_manufact_id" AS "i_manufact_id",
+    "ss"."total_sales" AS "total_sales"
+  FROM "ss"
+  UNION ALL
   SELECT
     "cs"."i_manufact_id" AS "i_manufact_id",
     "cs"."total_sales" AS "total_sales"
@@ -4214,16 +4104,6 @@ WITH "customer_address_2" AS (
     "ws"."i_manufact_id" AS "i_manufact_id",
     "ws"."total_sales" AS "total_sales"
   FROM "ws"
-), "tmp1" AS (
-  SELECT
-    "ss"."i_manufact_id" AS "i_manufact_id",
-    "ss"."total_sales" AS "total_sales"
-  FROM "ss"
-  UNION ALL
-  SELECT
-    "cte_4"."i_manufact_id" AS "i_manufact_id",
-    "cte_4"."total_sales" AS "total_sales"
-  FROM "cte_4" AS "cte_4"
 )
 SELECT
   "tmp1"."i_manufact_id" AS "i_manufact_id",
@@ -4290,8 +4170,8 @@ WITH "dn" AS (
     COUNT(*) AS "cnt"
   FROM "store_sales" AS "store_sales"
   JOIN "date_dim" AS "date_dim"
-    ON "date_dim"."d_year" IN (1999, 2000, 2001)
-    AND "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
+    ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk"
+    AND "date_dim"."d_year" IN (1999, 2000, 2001)
     AND (
       (
         "date_dim"."d_dom" <= 28 AND "date_dim"."d_dom" >= 25
@@ -4305,8 +4185,8 @@ WITH "dn" AS (
       "household_demographics"."hd_buy_potential" = '>10000'
       OR "household_demographics"."hd_buy_potential" = 'unknown'
     )
+    AND "household_demographics"."hd_demo_sk" = "store_sales"."ss_hdemo_sk"
     AND "household_demographics"."hd_vehicle_count" > 0
-    AND "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk"
     AND CASE
       WHEN "household_demographics"."hd_vehicle_count" > 0
       THEN "household_demographics"."hd_dep_count" / "household_demographics"."hd_vehicle_count"
@@ -4314,7 +4194,7 @@ WITH "dn" AS (
     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"
+    AND "store"."s_store_sk" = "store_sales"."ss_store_sk"
   GROUP BY
     "store_sales"."ss_ticket_number",
     "store_sales"."ss_customer_sk"
@@ -4328,7 +4208,7 @@ SELECT
   "dn"."cnt" AS "cnt"
 FROM "dn" AS "dn"
 JOIN "customer" AS "customer"
-  ON "dn"."ss_customer_sk" = "customer"."c_customer_sk"
+  ON "customer"."c_customer_sk" = "dn"."ss_customer_sk"
 WHERE
   "dn"."cnt" <= 20 AND "dn"."cnt" >= 15
 ORDER BY
@@ -4435,7 +4315,7 @@ WITH "date_dim_2" AS (
     "store_sales"."ss_customer_sk" AS "_u_1"
   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"
+    ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk"
   GROUP BY
     "store_sales"."ss_customer_sk"
 ), "_u_2" AS (
@@ -4443,7 +4323,7 @@ WITH "date_dim_2" AS (
     "web_sales"."ws_bill_customer_sk" AS "_u_3"
   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"
+    ON "date_dim"."d_date_sk" = "web_sales"."ws_sold_date_sk"
   GROUP BY
     "web_sales"."ws_bill_customer_sk"
 ), "_u_4" AS (
@@ -4476,15 +4356,15 @@ SELECT
   MAX("customer_demographics"."cd_dep_college_count") AS "_col_17"
 FROM "customer" AS "c"
 LEFT JOIN "_u_0" AS "_u_0"
-  ON "c"."c_customer_sk" = "_u_0"."_u_1"
+  ON "_u_0"."_u_1" = "c"."c_customer_sk"
 LEFT JOIN "_u_2" AS "_u_2"
-  ON "c"."c_customer_sk" = "_u_2"."_u_3"
+  ON "_u_2"."_u_3" = "c"."c_customer_sk"
 LEFT JOIN "_u_4" AS "_u_4"
-  ON "c"."c_customer_sk" = "_u_4"."_u_5"
+  ON "_u_4"."_u_5" = "c"."c_customer_sk"
 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"
+  ON "c"."c_current_cdemo_sk" = "customer_demographics"."cd_demo_sk"
 WHERE
   NOT "_u_0"."_u_1" IS NULL
   AND (
@@ -4663,17 +4543,17 @@ WITH "customer_2" AS (
   FROM "date_dim" AS "date_dim"
   WHERE
     "date_dim"."d_month_seq" <= 1199 AND "date_dim"."d_month_seq" >= 1188
-), "cte" AS (
+), "hot_cust" 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 "customer_2" AS "customer"
-    ON "store_sales"."ss_customer_sk" = "customer"."c_customer_sk"
+    ON "customer"."c_customer_sk" = "store_sales"."ss_customer_sk"
   JOIN "date_dim_2" AS "date_dim"
-    ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
-), "cte_2" AS (
+    ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk"
+  INTERSECT
   SELECT DISTINCT
     "customer"."c_last_name" AS "c_last_name",
     "customer"."c_first_name" AS "c_first_name",
@@ -4683,40 +4563,16 @@ WITH "customer_2" AS (
     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 (
+  INTERSECT
   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 "customer_2" AS "customer"
-    ON "web_sales"."ws_bill_customer_sk" = "customer"."c_customer_sk"
+    ON "customer"."c_customer_sk" = "web_sales"."ws_bill_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",
-    "cte_2"."c_first_name" AS "c_first_name",
-    "cte_2"."d_date" AS "d_date"
-  FROM "cte_2" AS "cte_2"
-  INTERSECT
-  SELECT
-    "cte_3"."c_last_name" AS "c_last_name",
-    "cte_3"."c_first_name" AS "c_first_name",
-    "cte_3"."d_date" AS "d_date"
-  FROM "cte_3" AS "cte_3"
-), "hot_cust" AS (
-  SELECT
-    "cte"."c_last_name" AS "c_last_name",
-    "cte"."c_first_name" AS "c_first_name",
-    "cte"."d_date" AS "d_date"
-  FROM "cte" AS "cte"
-  INTERSECT
-  SELECT
-    "cte_4"."c_last_name" AS "c_last_name",
-    "cte_4"."c_first_name" AS "c_first_name",
-    "cte_4"."d_date" AS "d_date"
-  FROM "cte_4" AS "cte_4"
+    ON "date_dim"."d_date_sk" = "web_sales"."ws_sold_date_sk"
 )
 SELECT
   COUNT(*) AS "_col_0"
@@ -4792,7 +4648,7 @@ WITH "foo" AS (
     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"
+    ON "date_dim"."d_date_sk" = "inventory"."inv_date_sk" AND "date_dim"."d_year" = 2002
   JOIN "item" AS "item"
     ON "inventory"."inv_item_sk" = "item"."i_item_sk"
   JOIN "warehouse" AS "warehouse"
@@ -4897,16 +4753,16 @@ SELECT
   ) AS "sales_after"
 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"
+  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 "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
+  ON "catalog_sales"."cs_item_sk" = "item"."i_item_sk"
+  AND "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
@@ -4997,7 +4853,7 @@ WHERE
     FROM "item" AS "item"
     WHERE
       (
-        "item"."i_manufact" = "i1"."i_manufact"
+        "i1"."i_manufact" = "item"."i_manufact"
         AND (
           (
             "item"."i_category" = 'Men'
@@ -5050,7 +4906,7 @@ WHERE
         )
       )
       OR (
-        "item"."i_manufact" = "i1"."i_manufact"
+        "i1"."i_manufact" = "item"."i_manufact"
         AND (
           (
             "item"."i_category" = 'Men'
@@ -5139,7 +4995,7 @@ FROM "date_dim" AS "dt"
 JOIN "store_sales" AS "store_sales"
   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"
+  ON "item"."i_item_sk" = "store_sales"."ss_item_sk" AND "item"."i_manager_id" = 1
 WHERE
   "dt"."d_moy" = 12 AND "dt"."d_year" = 2000
 GROUP BY
@@ -5351,7 +5207,7 @@ WITH "_u_0" AS (
   GROUP BY
     "ss1"."ss_item_sk"
   HAVING
-    AVG("ss1"."ss_net_profit") > 0.9 * MAX("_u_0"."rank_col")
+    0.9 * MAX("_u_0"."rank_col") < AVG("ss1"."ss_net_profit")
 ), "v11" AS (
   SELECT
     "v1"."item_sk" AS "item_sk",
@@ -5368,7 +5224,7 @@ WITH "_u_0" AS (
   GROUP BY
     "ss1"."ss_item_sk"
   HAVING
-    AVG("ss1"."ss_net_profit") > 0.9 * MAX("_u_1"."rank_col")
+    0.9 * MAX("_u_1"."rank_col") < AVG("ss1"."ss_net_profit")
 ), "v21" AS (
   SELECT
     "v2"."item_sk" AS "item_sk",
@@ -5437,15 +5293,15 @@ SELECT
   SUM("web_sales"."ws_sales_price") AS "_col_2"
 FROM "web_sales" AS "web_sales"
 JOIN "customer" AS "customer"
-  ON "web_sales"."ws_bill_customer_sk" = "customer"."c_customer_sk"
+  ON "customer"."c_customer_sk" = "web_sales"."ws_bill_customer_sk"
 JOIN "date_dim" AS "date_dim"
-  ON "date_dim"."d_qoy" = 1
+  ON "date_dim"."d_date_sk" = "web_sales"."ws_sold_date_sk"
+  AND "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"
+  ON "item"."i_item_sk" = "web_sales"."ws_item_sk"
 LEFT JOIN "_u_0" AS "_u_0"
-  ON "item"."i_item_id" = "_u_0"."i_item_id"
+  ON "_u_0"."i_item_id" = "item"."i_item_id"
 JOIN "customer_address" AS "customer_address"
   ON "customer"."c_current_addr_sk" = "customer_address"."ca_address_sk"
 WHERE
@@ -5514,20 +5370,20 @@ WITH "dn" AS (
     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"
+    ON "customer_address"."ca_address_sk" = "store_sales"."ss_addr_sk"
   JOIN "date_dim" AS "date_dim"
-    ON "date_dim"."d_dow" IN (6, 0)
+    ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk"
+    AND "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 "household_demographics" AS "household_demographics"
-    ON (
+    ON "household_demographics"."hd_demo_sk" = "store_sales"."ss_hdemo_sk"
+    AND (
       "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 "store" AS "store"
     ON "store"."s_city" IN ('Midway', 'Fairview', 'Fairview', 'Fairview', 'Fairview')
-    AND "store_sales"."ss_store_sk" = "store"."s_store_sk"
+    AND "store"."s_store_sk" = "store_sales"."ss_store_sk"
   GROUP BY
     "store_sales"."ss_ticket_number",
     "store_sales"."ss_customer_sk",
@@ -5544,10 +5400,10 @@ SELECT
   "dn"."profit" AS "profit"
 FROM "dn" AS "dn"
 JOIN "customer" AS "customer"
-  ON "dn"."ss_customer_sk" = "customer"."c_customer_sk"
+  ON "customer"."c_customer_sk" = "dn"."ss_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"
+  ON "current_addr"."ca_address_sk" = "customer"."c_current_addr_sk"
+  AND "current_addr"."ca_city" <> "dn"."bought_city"
 ORDER BY
   "c_last_name",
   "c_first_name",
@@ -5643,9 +5499,10 @@ WITH "v1" AS (
     RANK() OVER (PARTITION BY "item"."i_category", "item"."i_brand", "store"."s_store_name", "store"."s_company_name" ORDER BY "date_dim"."d_year", "date_dim"."d_moy") AS "rn"
   FROM "item" AS "item"
   JOIN "store_sales" AS "store_sales"
-    ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
+    ON "item"."i_item_sk" = "store_sales"."ss_item_sk"
   JOIN "date_dim" AS "date_dim"
-    ON (
+    ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk"
+    AND (
       "date_dim"."d_moy" = 1 OR "date_dim"."d_moy" = 12 OR "date_dim"."d_year" = 1999
     )
     AND (
@@ -5657,9 +5514,8 @@ WITH "v1" AS (
     AND (
       "date_dim"."d_year" = 1998 OR "date_dim"."d_year" = 1999 OR "date_dim"."d_year" = 2000
     )
-    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"
+    ON "store"."s_store_sk" = "store_sales"."ss_store_sk"
   GROUP BY
     "item"."i_category",
     "item"."i_brand",
@@ -5743,23 +5599,23 @@ SELECT
 FROM "store_sales" AS "store_sales"
 JOIN "customer_address" AS "customer_address"
   ON (
-    "customer_address"."ca_country" = 'United States'
+    "customer_address"."ca_address_sk" = "store_sales"."ss_addr_sk"
+    AND "customer_address"."ca_country" = 'United States'
     AND "customer_address"."ca_state" IN ('CO', 'TN', 'ND')
-    AND "store_sales"."ss_addr_sk" = "customer_address"."ca_address_sk"
     AND "store_sales"."ss_net_profit" <= 3000
     AND "store_sales"."ss_net_profit" >= 150
   )
   OR (
-    "customer_address"."ca_country" = 'United States'
+    "customer_address"."ca_address_sk" = "store_sales"."ss_addr_sk"
+    AND "customer_address"."ca_country" = 'United States'
     AND "customer_address"."ca_state" IN ('OK', 'PA', 'CA')
-    AND "store_sales"."ss_addr_sk" = "customer_address"."ca_address_sk"
     AND "store_sales"."ss_net_profit" <= 25000
     AND "store_sales"."ss_net_profit" >= 50
   )
   OR (
-    "customer_address"."ca_country" = 'United States'
+    "customer_address"."ca_address_sk" = "store_sales"."ss_addr_sk"
+    AND "customer_address"."ca_country" = 'United States'
     AND "customer_address"."ca_state" IN ('TX', 'NE', 'MO')
-    AND "store_sales"."ss_addr_sk" = "customer_address"."ca_address_sk"
     AND "store_sales"."ss_net_profit" <= 2000
     AND "store_sales"."ss_net_profit" >= 0
   )
@@ -5786,8 +5642,8 @@ JOIN "customer_demographics" AS "customer_demographics"
     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"
+  ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk"
+  AND "date_dim"."d_year" = 1999
 JOIN "store" AS "store"
   ON "store"."s_store_sk" = "store_sales"."ss_store_sk";
 
@@ -5941,10 +5797,10 @@ WITH "date_dim_2" AS (
     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 "ws"."ws_sold_date_sk" = "date_dim"."d_date_sk"
+    ON "date_dim"."d_date_sk" = "ws"."ws_sold_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"
+    ON "wr"."wr_item_sk" = "ws"."ws_item_sk"
+    AND "wr"."wr_order_number" = "ws"."ws_order_number"
   WHERE
     "wr"."wr_return_amt" > 10000
     AND "ws"."ws_net_paid" > 0
@@ -5966,8 +5822,8 @@ WITH "date_dim_2" AS (
     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"
+    ON "cr"."cr_item_sk" = "cs"."cs_item_sk"
+    AND "cr"."cr_order_number" = "cs"."cs_order_number"
   JOIN "date_dim_2" AS "date_dim"
     ON "cs"."cs_sold_date_sk" = "date_dim"."d_date_sk"
   WHERE
@@ -5991,10 +5847,10 @@ WITH "date_dim_2" AS (
     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 "sts"."ss_sold_date_sk" = "date_dim"."d_date_sk"
+    ON "date_dim"."d_date_sk" = "sts"."ss_sold_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"
+    ON "sr"."sr_item_sk" = "sts"."ss_item_sk"
+    AND "sr"."sr_ticket_number" = "sts"."ss_ticket_number"
   WHERE
     "sr"."sr_return_amt" > 10000
     AND "sts"."ss_net_paid" > 0
@@ -6009,39 +5865,6 @@ WITH "date_dim_2" AS (
     RANK() OVER (ORDER BY "in_store"."return_ratio") AS "return_rank",
     RANK() OVER (ORDER BY "in_store"."currency_ratio") AS "currency_rank"
   FROM "in_store" AS "in_store"
-), "cte_3" AS (
-  SELECT
-    'store' AS "channel",
-    "store"."item" AS "item",
-    "store"."return_ratio" AS "return_ratio",
-    "store"."return_rank" AS "return_rank",
-    "store"."currency_rank" AS "currency_rank"
-  FROM "store" AS "store"
-  WHERE
-    "store"."currency_rank" <= 10 OR "store"."return_rank" <= 10
-  ORDER BY
-    "channel",
-    "return_rank",
-    "currency_rank"
-  LIMIT 100
-), "cte_4" AS (
-  SELECT
-    'catalog' AS "channel",
-    "catalog"."item" AS "item",
-    "catalog"."return_ratio" AS "return_ratio",
-    "catalog"."return_rank" AS "return_rank",
-    "catalog"."currency_rank" AS "currency_rank"
-  FROM "catalog" AS "catalog"
-  WHERE
-    "catalog"."currency_rank" <= 10 OR "catalog"."return_rank" <= 10
-  UNION
-  SELECT
-    "cte_3"."channel" AS "channel",
-    "cte_3"."item" AS "item",
-    "cte_3"."return_ratio" AS "return_ratio",
-    "cte_3"."return_rank" AS "return_rank",
-    "cte_3"."currency_rank" AS "currency_rank"
-  FROM "cte_3" AS "cte_3"
 )
 SELECT
   'web' AS "channel",
@@ -6054,12 +5877,29 @@ WHERE
   "web"."currency_rank" <= 10 OR "web"."return_rank" <= 10
 UNION
 SELECT
-  "cte_4"."channel" AS "channel",
-  "cte_4"."item" AS "item",
-  "cte_4"."return_ratio" AS "return_ratio",
-  "cte_4"."return_rank" AS "return_rank",
-  "cte_4"."currency_rank" AS "currency_rank"
-FROM "cte_4" AS "cte_4";
+  'catalog' AS "channel",
+  "catalog"."item" AS "item",
+  "catalog"."return_ratio" AS "return_ratio",
+  "catalog"."return_rank" AS "return_rank",
+  "catalog"."currency_rank" AS "currency_rank"
+FROM "catalog" AS "catalog"
+WHERE
+  "catalog"."currency_rank" <= 10 OR "catalog"."return_rank" <= 10
+UNION
+SELECT
+  'store' AS "channel",
+  "store"."item" AS "item",
+  "store"."return_ratio" AS "return_ratio",
+  "store"."return_rank" AS "return_rank",
+  "store"."currency_rank" AS "currency_rank"
+FROM "store" AS "store"
+WHERE
+  "store"."currency_rank" <= 10 OR "store"."return_rank" <= 10
+ORDER BY
+  "channel",
+  "return_rank",
+  "currency_rank"
+LIMIT 100;
 
 --------------------------------------
 -- TPC-DS 50
@@ -6185,17 +6025,17 @@ SELECT
   ) 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"
+  ON "d1"."d_date_sk" = "store_sales"."ss_sold_date_sk"
 JOIN "store" AS "store"
-  ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
+  ON "store"."s_store_sk" = "store_sales"."ss_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"
+  ON "store_returns"."sr_customer_sk" = "store_sales"."ss_customer_sk"
+  AND "store_returns"."sr_item_sk" = "store_sales"."ss_item_sk"
+  AND "store_returns"."sr_ticket_number" = "store_sales"."ss_ticket_number"
 JOIN "date_dim" AS "d2"
-  ON "d2"."d_moy" = 9
+  ON "d2"."d_date_sk" = "store_returns"."sr_returned_date_sk"
+  AND "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",
@@ -6291,7 +6131,7 @@ WITH "date_dim_2" AS (
     SUM(SUM("web_sales"."ws_sales_price")) OVER (PARTITION BY "web_sales"."ws_item_sk" ORDER BY "date_dim"."d_date" rows BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS "cume_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"
+    ON "date_dim"."d_date_sk" = "web_sales"."ws_sold_date_sk"
   WHERE
     NOT "web_sales"."ws_item_sk" IS NULL
   GROUP BY
@@ -6304,7 +6144,7 @@ WITH "date_dim_2" AS (
     SUM(SUM("store_sales"."ss_sales_price")) OVER (PARTITION BY "store_sales"."ss_item_sk" ORDER BY "date_dim"."d_date" rows BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS "cume_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"
+    ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk"
   WHERE
     NOT "store_sales"."ss_item_sk" IS NULL
   GROUP BY
@@ -6332,7 +6172,7 @@ WITH "date_dim_2" AS (
     END ORDER BY CASE WHEN NOT "web"."d_date" IS NULL THEN "web"."d_date" ELSE "store"."d_date" END rows BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS "store_cumulative"
   FROM "web_v1" AS "web"
   FULL JOIN "store_v1" AS "store"
-    ON "web"."d_date" = "store"."d_date" AND "web"."item_sk" = "store"."item_sk"
+    ON "store"."d_date" = "web"."d_date" AND "store"."item_sk" = "web"."item_sk"
 )
 SELECT
   "y"."item_sk" AS "item_sk",
@@ -6343,7 +6183,7 @@ SELECT
   "y"."store_cumulative" AS "store_cumulative"
 FROM "y" AS "y"
 WHERE
-  "y"."web_cumulative" > "y"."store_cumulative"
+  "y"."store_cumulative" < "y"."web_cumulative"
 ORDER BY
   "y"."item_sk",
   "y"."d_date"
@@ -6380,7 +6220,7 @@ FROM "date_dim" AS "dt"
 JOIN "store_sales" AS "store_sales"
   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"
+  ON "item"."i_item_sk" = "store_sales"."ss_item_sk" AND "item"."i_manager_id" = 1
 WHERE
   "dt"."d_moy" = 11 AND "dt"."d_year" = 1999
 GROUP BY
@@ -6448,12 +6288,12 @@ WITH "tmp1" AS (
     AVG(SUM("store_sales"."ss_sales_price")) OVER (PARTITION BY "item"."i_manufact_id") AS "avg_quarterly_sales"
   FROM "item" AS "item"
   JOIN "store_sales" AS "store_sales"
-    ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
+    ON "item"."i_item_sk" = "store_sales"."ss_item_sk"
   JOIN "date_dim" AS "date_dim"
-    ON "date_dim"."d_month_seq" IN (1199, 1200, 1201, 1202, 1203, 1204, 1205, 1206, 1207, 1208, 1209, 1210)
-    AND "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
+    ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk"
+    AND "date_dim"."d_month_seq" IN (1199, 1200, 1201, 1202, 1203, 1204, 1205, 1206, 1207, 1208, 1209, 1210)
   JOIN "store" AS "store"
-    ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
+    ON "store"."s_store_sk" = "store_sales"."ss_store_sk"
   WHERE
     (
       "item"."i_brand" IN ('amalgimporto #1', 'edu packscholar #1', 'exportiimporto #1', 'importoamalg #1')
@@ -6589,7 +6429,7 @@ WITH "cs_or_ws_sales" AS (
     "customer"."c_current_addr_sk" AS "c_current_addr_sk"
   FROM "cs_or_ws_sales" AS "cs_or_ws_sales"
   JOIN "customer" AS "customer"
-    ON "customer"."c_customer_sk" = "cs_or_ws_sales"."customer_sk"
+    ON "cs_or_ws_sales"."customer_sk" = "customer"."c_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
@@ -6615,18 +6455,18 @@ WITH "cs_or_ws_sales" AS (
     SUM("store_sales"."ss_ext_sales_price") AS "revenue"
   FROM "my_customers"
   JOIN "customer_address" AS "customer_address"
-    ON "my_customers"."c_current_addr_sk" = "customer_address"."ca_address_sk"
+    ON "customer_address"."ca_address_sk" = "my_customers"."c_current_addr_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"
+    ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk"
   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"
+    ON "_u_0"."_col_0" <= "date_dim"."d_month_seq"
   JOIN "_u_1" AS "_u_1"
-    ON "date_dim"."d_month_seq" <= "_u_1"."_col_0"
+    ON "_u_1"."_col_0" >= "date_dim"."d_month_seq"
   GROUP BY
     "my_customers"."c_customer_sk"
 )
@@ -6675,7 +6515,7 @@ 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" = 33 AND "store_sales"."ss_item_sk" = "item"."i_item_sk"
+  ON "item"."i_item_sk" = "store_sales"."ss_item_sk" AND "item"."i_manager_id" = 33
 WHERE
   "date_dim"."d_moy" = 12 AND "date_dim"."d_year" = 1998
 GROUP BY
@@ -6790,13 +6630,13 @@ WITH "customer_address_2" AS (
     SUM("store_sales"."ss_ext_sales_price") AS "total_sales"
   FROM "store_sales" AS "store_sales"
   JOIN "customer_address_2" AS "customer_address"
-    ON "store_sales"."ss_addr_sk" = "customer_address"."ca_address_sk"
+    ON "customer_address"."ca_address_sk" = "store_sales"."ss_addr_sk"
   JOIN "date_dim_2" AS "date_dim"
-    ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
+    ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk"
   JOIN "item_2" AS "item"
-    ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
+    ON "item"."i_item_sk" = "store_sales"."ss_item_sk"
   LEFT JOIN "_u_0" AS "_u_0"
-    ON "item"."i_item_id" = "_u_0"."i_item_id"
+    ON "_u_0"."i_item_id" = "item"."i_item_id"
   WHERE
     NOT "_u_0"."i_item_id" IS NULL
   GROUP BY
@@ -6813,7 +6653,7 @@ WITH "customer_address_2" AS (
   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"
+    ON "_u_1"."i_item_id" = "item"."i_item_id"
   WHERE
     NOT "_u_1"."i_item_id" IS NULL
   GROUP BY
@@ -6824,18 +6664,23 @@ WITH "customer_address_2" AS (
     SUM("web_sales"."ws_ext_sales_price") AS "total_sales"
   FROM "web_sales" AS "web_sales"
   JOIN "customer_address_2" AS "customer_address"
-    ON "web_sales"."ws_bill_addr_sk" = "customer_address"."ca_address_sk"
+    ON "customer_address"."ca_address_sk" = "web_sales"."ws_bill_addr_sk"
   JOIN "date_dim_2" AS "date_dim"
-    ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk"
+    ON "date_dim"."d_date_sk" = "web_sales"."ws_sold_date_sk"
   JOIN "item_2" AS "item"
-    ON "web_sales"."ws_item_sk" = "item"."i_item_sk"
+    ON "item"."i_item_sk" = "web_sales"."ws_item_sk"
   LEFT JOIN "_u_0" AS "_u_2"
-    ON "item"."i_item_id" = "_u_2"."i_item_id"
+    ON "_u_2"."i_item_id" = "item"."i_item_id"
   WHERE
     NOT "_u_2"."i_item_id" IS NULL
   GROUP BY
     "item"."i_item_id"
-), "cte_4" AS (
+), "tmp1" AS (
+  SELECT
+    "ss"."i_item_id" AS "i_item_id",
+    "ss"."total_sales" AS "total_sales"
+  FROM "ss"
+  UNION ALL
   SELECT
     "cs"."i_item_id" AS "i_item_id",
     "cs"."total_sales" AS "total_sales"
@@ -6845,16 +6690,6 @@ WITH "customer_address_2" AS (
     "ws"."i_item_id" AS "i_item_id",
     "ws"."total_sales" AS "total_sales"
   FROM "ws"
-), "tmp1" AS (
-  SELECT
-    "ss"."i_item_id" AS "i_item_id",
-    "ss"."total_sales" AS "total_sales"
-  FROM "ss"
-  UNION ALL
-  SELECT
-    "cte_4"."i_item_id" AS "i_item_id",
-    "cte_4"."total_sales" AS "total_sales"
-  FROM "cte_4" AS "cte_4"
 )
 SELECT
   "tmp1"."i_item_id" AS "i_item_id",
@@ -7095,7 +6930,7 @@ WITH "date_dim_2" AS (
     "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"
+    ON "_u_0"."d_week_seq" = "date_dim"."d_week_seq"
   GROUP BY
     "date_dim"."d_date"
 ), "ss_items" AS (
@@ -7104,11 +6939,11 @@ WITH "date_dim_2" AS (
     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"
+    ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk"
   JOIN "item_2" AS "item"
-    ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
+    ON "item"."i_item_sk" = "store_sales"."ss_item_sk"
   LEFT JOIN "_u_1" AS "_u_1"
-    ON "date_dim"."d_date" = "_u_1"."d_date"
+    ON "_u_1"."d_date" = "date_dim"."d_date"
   WHERE
     NOT "_u_1"."d_date" IS NULL
   GROUP BY
@@ -7118,7 +6953,7 @@ WITH "date_dim_2" AS (
     "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"
+    ON "_u_2"."d_week_seq" = "date_dim"."d_week_seq"
   GROUP BY
     "date_dim"."d_date"
 ), "cs_items" AS (
@@ -7131,7 +6966,7 @@ WITH "date_dim_2" AS (
   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"
+    ON "_u_3"."d_date" = "date_dim"."d_date"
   WHERE
     NOT "_u_3"."d_date" IS NULL
   GROUP BY
@@ -7141,7 +6976,7 @@ WITH "date_dim_2" AS (
     "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"
+    ON "_u_4"."d_week_seq" = "date_dim"."d_week_seq"
   GROUP BY
     "date_dim"."d_date"
 ), "ws_items" AS (
@@ -7150,11 +6985,11 @@ WITH "date_dim_2" AS (
     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"
+    ON "date_dim"."d_date_sk" = "web_sales"."ws_sold_date_sk"
   JOIN "item_2" AS "item"
-    ON "web_sales"."ws_item_sk" = "item"."i_item_sk"
+    ON "item"."i_item_sk" = "web_sales"."ws_item_sk"
   LEFT JOIN "_u_5" AS "_u_5"
-    ON "date_dim"."d_date" = "_u_5"."d_date"
+    ON "_u_5"."d_date" = "date_dim"."d_date"
   WHERE
     NOT "_u_5"."d_date" IS NULL
   GROUP BY
@@ -7189,7 +7024,7 @@ JOIN "cs_items"
   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 "cs_items"."item_id" = "ss_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"
@@ -7362,7 +7197,7 @@ WITH "wss" AS (
     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"
+    ON "store"."s_store_sk" = "wss"."ss_store_sk"
 )
 SELECT
   "store"."s_store_name" AS "s_store_name1",
@@ -7381,7 +7216,7 @@ JOIN "date_dim" AS "d"
   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"
+  ON "store"."s_store_sk" = "wss"."ss_store_sk"
 JOIN "x" AS "x"
   ON "store"."s_store_id" = "x"."s_store_id2"
   AND "wss"."d_week_seq" = "x"."d_week_seq2" - 52
@@ -7493,13 +7328,13 @@ WITH "customer_address_2" AS (
     SUM("store_sales"."ss_ext_sales_price") AS "total_sales"
   FROM "store_sales" AS "store_sales"
   JOIN "customer_address_2" AS "customer_address"
-    ON "store_sales"."ss_addr_sk" = "customer_address"."ca_address_sk"
+    ON "customer_address"."ca_address_sk" = "store_sales"."ss_addr_sk"
   JOIN "date_dim_2" AS "date_dim"
-    ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
+    ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk"
   JOIN "item_2" AS "item"
-    ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
+    ON "item"."i_item_sk" = "store_sales"."ss_item_sk"
   LEFT JOIN "_u_0" AS "_u_0"
-    ON "item"."i_item_id" = "_u_0"."i_item_id"
+    ON "_u_0"."i_item_id" = "item"."i_item_id"
   WHERE
     NOT "_u_0"."i_item_id" IS NULL
   GROUP BY
@@ -7516,7 +7351,7 @@ WITH "customer_address_2" AS (
   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"
+    ON "_u_1"."i_item_id" = "item"."i_item_id"
   WHERE
     NOT "_u_1"."i_item_id" IS NULL
   GROUP BY
@@ -7527,18 +7362,23 @@ WITH "customer_address_2" AS (
     SUM("web_sales"."ws_ext_sales_price") AS "total_sales"
   FROM "web_sales" AS "web_sales"
   JOIN "customer_address_2" AS "customer_address"
-    ON "web_sales"."ws_bill_addr_sk" = "customer_address"."ca_address_sk"
+    ON "customer_address"."ca_address_sk" = "web_sales"."ws_bill_addr_sk"
   JOIN "date_dim_2" AS "date_dim"
-    ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk"
+    ON "date_dim"."d_date_sk" = "web_sales"."ws_sold_date_sk"
   JOIN "item_2" AS "item"
-    ON "web_sales"."ws_item_sk" = "item"."i_item_sk"
+    ON "item"."i_item_sk" = "web_sales"."ws_item_sk"
   LEFT JOIN "_u_0" AS "_u_2"
-    ON "item"."i_item_id" = "_u_2"."i_item_id"
+    ON "_u_2"."i_item_id" = "item"."i_item_id"
   WHERE
     NOT "_u_2"."i_item_id" IS NULL
   GROUP BY
     "item"."i_item_id"
-), "cte_4" AS (
+), "tmp1" AS (
+  SELECT
+    "ss"."i_item_id" AS "i_item_id",
+    "ss"."total_sales" AS "total_sales"
+  FROM "ss"
+  UNION ALL
   SELECT
     "cs"."i_item_id" AS "i_item_id",
     "cs"."total_sales" AS "total_sales"
@@ -7548,16 +7388,6 @@ WITH "customer_address_2" AS (
     "ws"."i_item_id" AS "i_item_id",
     "ws"."total_sales" AS "total_sales"
   FROM "ws"
-), "tmp1" AS (
-  SELECT
-    "ss"."i_item_id" AS "i_item_id",
-    "ss"."total_sales" AS "total_sales"
-  FROM "ss"
-  UNION ALL
-  SELECT
-    "cte_4"."i_item_id" AS "i_item_id",
-    "cte_4"."total_sales" AS "total_sales"
-  FROM "cte_4" AS "cte_4"
 )
 SELECT
   "tmp1"."i_item_id" AS "i_item_id",
@@ -7658,36 +7488,36 @@ WITH "customer_2" AS (
     SUM("store_sales"."ss_ext_sales_price") AS "promotions"
   FROM "store_sales" AS "store_sales"
   JOIN "customer_2" AS "customer"
-    ON "store_sales"."ss_customer_sk" = "customer"."c_customer_sk"
+    ON "customer"."c_customer_sk" = "store_sales"."ss_customer_sk"
   JOIN "date_dim_2" AS "date_dim"
-    ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
+    ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk"
   JOIN "item_2" AS "item"
-    ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
+    ON "item"."i_item_sk" = "store_sales"."ss_item_sk"
   JOIN "promotion" AS "promotion"
     ON (
       "promotion"."p_channel_dmail" = 'Y'
       OR "promotion"."p_channel_email" = 'Y'
       OR "promotion"."p_channel_tv" = 'Y'
     )
-    AND "store_sales"."ss_promo_sk" = "promotion"."p_promo_sk"
+    AND "promotion"."p_promo_sk" = "store_sales"."ss_promo_sk"
   JOIN "store_2" AS "store"
-    ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
+    ON "store"."s_store_sk" = "store_sales"."ss_store_sk"
   JOIN "customer_address_2" AS "customer_address"
-    ON "customer_address"."ca_address_sk" = "customer"."c_current_addr_sk"
+    ON "customer"."c_current_addr_sk" = "customer_address"."ca_address_sk"
 ), "all_sales" AS (
   SELECT
     SUM("store_sales"."ss_ext_sales_price") AS "total"
   FROM "store_sales" AS "store_sales"
   JOIN "customer_2" AS "customer"
-    ON "store_sales"."ss_customer_sk" = "customer"."c_customer_sk"
+    ON "customer"."c_customer_sk" = "store_sales"."ss_customer_sk"
   JOIN "date_dim_2" AS "date_dim"
-    ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
+    ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk"
   JOIN "item_2" AS "item"
-    ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
+    ON "item"."i_item_sk" = "store_sales"."ss_item_sk"
   JOIN "store_2" AS "store"
-    ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
+    ON "store"."s_store_sk" = "store_sales"."ss_store_sk"
   JOIN "customer_address_2" AS "customer_address"
-    ON "customer_address"."ca_address_sk" = "customer"."c_current_addr_sk"
+    ON "customer"."c_current_addr_sk" = "customer_address"."ca_address_sk"
 )
 SELECT
   "promotional_sales"."promotions" AS "promotions",
@@ -7791,13 +7621,13 @@ SELECT
   ) AS ">120 days"
 FROM "web_sales" AS "web_sales"
 JOIN "date_dim" AS "date_dim"
-  ON "date_dim"."d_month_seq" <= 1233
+  ON "date_dim"."d_date_sk" = "web_sales"."ws_ship_date_sk"
+  AND "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"
+  ON "ship_mode"."sm_ship_mode_sk" = "web_sales"."ws_ship_mode_sk"
 JOIN "warehouse" AS "warehouse"
-  ON "web_sales"."ws_warehouse_sk" = "warehouse"."w_warehouse_sk"
+  ON "warehouse"."w_warehouse_sk" = "web_sales"."ws_warehouse_sk"
 JOIN "web_site" AS "web_site"
   ON "web_sales"."ws_web_site_sk" = "web_site"."web_site_sk"
 GROUP BY
@@ -7864,12 +7694,12 @@ WITH "tmp1" AS (
     AVG(SUM("store_sales"."ss_sales_price")) OVER (PARTITION BY "item"."i_manager_id") AS "avg_monthly_sales"
   FROM "item" AS "item"
   JOIN "store_sales" AS "store_sales"
-    ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
+    ON "item"."i_item_sk" = "store_sales"."ss_item_sk"
   JOIN "date_dim" AS "date_dim"
-    ON "date_dim"."d_month_seq" IN (1200, 1201, 1202, 1203, 1204, 1205, 1206, 1207, 1208, 1209, 1210, 1211)
-    AND "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
+    ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk"
+    AND "date_dim"."d_month_seq" IN (1200, 1201, 1202, 1203, 1204, 1205, 1206, 1207, 1208, 1209, 1210, 1211)
   JOIN "store" AS "store"
-    ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
+    ON "store"."s_store_sk" = "store_sales"."ss_store_sk"
   WHERE
     (
       "item"."i_brand" IN ('amalgimporto #1', 'edu packscholar #1', 'exportiimporto #1', 'importoamalg #1')
@@ -8057,14 +7887,14 @@ WITH "cs_ui" AS (
     "catalog_sales"."cs_item_sk" AS "cs_item_sk"
   FROM "catalog_sales" AS "catalog_sales"
   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"
+    ON "catalog_returns"."cr_item_sk" = "catalog_sales"."cs_item_sk"
+    AND "catalog_returns"."cr_order_number" = "catalog_sales"."cs_order_number"
   GROUP BY
     "catalog_sales"."cs_item_sk"
   HAVING
-    SUM("catalog_sales"."cs_ext_list_price") > 2 * SUM(
+    2 * SUM(
       "catalog_returns"."cr_refunded_cash" + "catalog_returns"."cr_reversed_charge" + "catalog_returns"."cr_store_credit"
-    )
+    ) < SUM("catalog_sales"."cs_ext_list_price")
 ), "cross_sales" AS (
   SELECT
     "item"."i_product_name" AS "product_name",
@@ -8087,43 +7917,43 @@ WITH "cs_ui" AS (
   FROM "store_sales" AS "store_sales"
   CROSS JOIN "income_band" AS "ib2"
   JOIN "customer_address" AS "ad1"
-    ON "store_sales"."ss_addr_sk" = "ad1"."ca_address_sk"
+    ON "ad1"."ca_address_sk" = "store_sales"."ss_addr_sk"
   JOIN "cs_ui"
-    ON "store_sales"."ss_item_sk" = "cs_ui"."cs_item_sk"
+    ON "cs_ui"."cs_item_sk" = "store_sales"."ss_item_sk"
   JOIN "date_dim" AS "d1"
-    ON "store_sales"."ss_sold_date_sk" = "d1"."d_date_sk"
+    ON "d1"."d_date_sk" = "store_sales"."ss_sold_date_sk"
   JOIN "household_demographics" AS "hd1"
-    ON "store_sales"."ss_hdemo_sk" = "hd1"."hd_demo_sk"
+    ON "hd1"."hd_demo_sk" = "store_sales"."ss_hdemo_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"
+    AND "item"."i_item_sk" = "store_sales"."ss_item_sk"
   JOIN "promotion" AS "promotion"
-    ON "store_sales"."ss_promo_sk" = "promotion"."p_promo_sk"
+    ON "promotion"."p_promo_sk" = "store_sales"."ss_promo_sk"
   JOIN "store" AS "store"
-    ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
+    ON "store"."s_store_sk" = "store_sales"."ss_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"
+    ON "store_returns"."sr_item_sk" = "store_sales"."ss_item_sk"
+    AND "store_returns"."sr_ticket_number" = "store_sales"."ss_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"
+    AND "customer"."c_customer_sk" = "store_sales"."ss_customer_sk"
   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"
+    ON "ad2"."ca_address_sk" = "customer"."c_current_addr_sk"
   JOIN "customer_demographics" AS "cd2"
-    ON "customer"."c_current_cdemo_sk" = "cd2"."cd_demo_sk"
+    ON "cd2"."cd_demo_sk" = "customer"."c_current_cdemo_sk"
   JOIN "date_dim" AS "d2"
     ON "customer"."c_first_sales_date_sk" = "d2"."d_date_sk"
   JOIN "date_dim" AS "d3"
     ON "customer"."c_first_shipto_date_sk" = "d3"."d_date_sk"
   JOIN "customer_demographics" AS "cd1"
-    ON "cd1"."cd_marital_status" <> "cd2"."cd_marital_status"
-    AND "store_sales"."ss_cdemo_sk" = "cd1"."cd_demo_sk"
+    ON "cd1"."cd_demo_sk" = "store_sales"."ss_cdemo_sk"
+    AND "cd1"."cd_marital_status" <> "cd2"."cd_marital_status"
   GROUP BY
     "item"."i_product_name",
     "item"."i_item_sk",
@@ -8165,10 +7995,10 @@ SELECT
   "cs2"."cnt" AS "cnt"
 FROM "cross_sales" AS "cs1"
 JOIN "cross_sales" AS "cs2"
-  ON "cs1"."item_sk" = "cs2"."item_sk"
+  ON "cs1"."cnt" >= "cs2"."cnt"
+  AND "cs1"."item_sk" = "cs2"."item_sk"
   AND "cs1"."store_name" = "cs2"."store_name"
   AND "cs1"."store_zip" = "cs2"."store_zip"
-  AND "cs2"."cnt" <= "cs1"."cnt"
   AND "cs2"."syear" = 2002
 WHERE
   "cs1"."syear" = 2001
@@ -8237,7 +8067,7 @@ WITH "store_sales_2" AS (
     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"
+    ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk"
   GROUP BY
     "store_sales"."ss_store_sk",
     "store_sales"."ss_item_sk"
@@ -8247,7 +8077,7 @@ WITH "store_sales_2" AS (
     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"
+    ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk"
   GROUP BY
     "store_sales"."ss_store_sk",
     "store_sales"."ss_item_sk"
@@ -8268,7 +8098,7 @@ SELECT
   "item"."i_brand" AS "i_brand"
 FROM "store" AS "store"
 JOIN "sc" AS "sc"
-  ON "store"."s_store_sk" = "sc"."ss_store_sk"
+  ON "sc"."ss_store_sk" = "store"."s_store_sk"
 JOIN "item" AS "item"
   ON "item"."i_item_sk" = "sc"."ss_item_sk"
 JOIN "sb" AS "sb"
@@ -8618,7 +8448,7 @@ WITH "date_dim_2" AS (
     "warehouse"."w_state" AS "w_state",
     "warehouse"."w_country" AS "w_country"
   FROM "warehouse" AS "warehouse"
-), "cte" AS (
+), "x" AS (
   SELECT
     "warehouse"."w_warehouse_name" AS "w_warehouse_name",
     "warehouse"."w_warehouse_sq_ft" AS "w_warehouse_sq_ft",
@@ -8798,13 +8628,13 @@ WITH "date_dim_2" AS (
     ) AS "dec_net"
   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"
+    ON "date_dim"."d_date_sk" = "web_sales"."ws_sold_date_sk"
   JOIN "ship_mode_2" AS "ship_mode"
-    ON "web_sales"."ws_ship_mode_sk" = "ship_mode"."sm_ship_mode_sk"
+    ON "ship_mode"."sm_ship_mode_sk" = "web_sales"."ws_ship_mode_sk"
   JOIN "time_dim_2" AS "time_dim"
-    ON "web_sales"."ws_sold_time_sk" = "time_dim"."t_time_sk"
+    ON "time_dim"."t_time_sk" = "web_sales"."ws_sold_time_sk"
   JOIN "warehouse_2" AS "warehouse"
-    ON "web_sales"."ws_warehouse_sk" = "warehouse"."w_warehouse_sk"
+    ON "warehouse"."w_warehouse_sk" = "web_sales"."ws_warehouse_sk"
   GROUP BY
     "warehouse"."w_warehouse_name",
     "warehouse"."w_warehouse_sq_ft",
@@ -8813,7 +8643,7 @@ WITH "date_dim_2" AS (
     "warehouse"."w_state",
     "warehouse"."w_country",
     "date_dim"."d_year"
-), "cte_2" AS (
+  UNION ALL
   SELECT
     "warehouse"."w_warehouse_name" AS "w_warehouse_name",
     "warehouse"."w_warehouse_sq_ft" AS "w_warehouse_sq_ft",
@@ -9008,76 +8838,6 @@ WITH "date_dim_2" AS (
     "warehouse"."w_state",
     "warehouse"."w_country",
     "date_dim"."d_year"
-), "x" AS (
-  SELECT
-    "cte"."w_warehouse_name" AS "w_warehouse_name",
-    "cte"."w_warehouse_sq_ft" AS "w_warehouse_sq_ft",
-    "cte"."w_city" AS "w_city",
-    "cte"."w_county" AS "w_county",
-    "cte"."w_state" AS "w_state",
-    "cte"."w_country" AS "w_country",
-    "cte"."ship_carriers" AS "ship_carriers",
-    "cte"."year1" AS "year1",
-    "cte"."jan_sales" AS "jan_sales",
-    "cte"."feb_sales" AS "feb_sales",
-    "cte"."mar_sales" AS "mar_sales",
-    "cte"."apr_sales" AS "apr_sales",
-    "cte"."may_sales" AS "may_sales",
-    "cte"."jun_sales" AS "jun_sales",
-    "cte"."jul_sales" AS "jul_sales",
-    "cte"."aug_sales" AS "aug_sales",
-    "cte"."sep_sales" AS "sep_sales",
-    "cte"."oct_sales" AS "oct_sales",
-    "cte"."nov_sales" AS "nov_sales",
-    "cte"."dec_sales" AS "dec_sales",
-    "cte"."jan_net" AS "jan_net",
-    "cte"."feb_net" AS "feb_net",
-    "cte"."mar_net" AS "mar_net",
-    "cte"."apr_net" AS "apr_net",
-    "cte"."may_net" AS "may_net",
-    "cte"."jun_net" AS "jun_net",
-    "cte"."jul_net" AS "jul_net",
-    "cte"."aug_net" AS "aug_net",
-    "cte"."sep_net" AS "sep_net",
-    "cte"."oct_net" AS "oct_net",
-    "cte"."nov_net" AS "nov_net",
-    "cte"."dec_net" AS "dec_net"
-  FROM "cte" AS "cte"
-  UNION ALL
-  SELECT
-    "cte_2"."w_warehouse_name" AS "w_warehouse_name",
-    "cte_2"."w_warehouse_sq_ft" AS "w_warehouse_sq_ft",
-    "cte_2"."w_city" AS "w_city",
-    "cte_2"."w_county" AS "w_county",
-    "cte_2"."w_state" AS "w_state",
-    "cte_2"."w_country" AS "w_country",
-    "cte_2"."ship_carriers" AS "ship_carriers",
-    "cte_2"."year1" AS "year1",
-    "cte_2"."jan_sales" AS "jan_sales",
-    "cte_2"."feb_sales" AS "feb_sales",
-    "cte_2"."mar_sales" AS "mar_sales",
-    "cte_2"."apr_sales" AS "apr_sales",
-    "cte_2"."may_sales" AS "may_sales",
-    "cte_2"."jun_sales" AS "jun_sales",
-    "cte_2"."jul_sales" AS "jul_sales",
-    "cte_2"."aug_sales" AS "aug_sales",
-    "cte_2"."sep_sales" AS "sep_sales",
-    "cte_2"."oct_sales" AS "oct_sales",
-    "cte_2"."nov_sales" AS "nov_sales",
-    "cte_2"."dec_sales" AS "dec_sales",
-    "cte_2"."jan_net" AS "jan_net",
-    "cte_2"."feb_net" AS "feb_net",
-    "cte_2"."mar_net" AS "mar_net",
-    "cte_2"."apr_net" AS "apr_net",
-    "cte_2"."may_net" AS "may_net",
-    "cte_2"."jun_net" AS "jun_net",
-    "cte_2"."jul_net" AS "jul_net",
-    "cte_2"."aug_net" AS "aug_net",
-    "cte_2"."sep_net" AS "sep_net",
-    "cte_2"."oct_net" AS "oct_net",
-    "cte_2"."nov_net" AS "nov_net",
-    "cte_2"."dec_net" AS "dec_net"
-  FROM "cte_2" AS "cte_2"
 )
 SELECT
   "x"."w_warehouse_name" AS "w_warehouse_name",
@@ -9181,8 +8941,7 @@ order by i_category
         ,s_store_id
         ,sumsales
         ,rk
-limit 100
-;
+limit 100;
 WITH "dw1" AS (
   SELECT
     "item"."i_category" AS "i_category",
@@ -9196,13 +8955,13 @@ WITH "dw1" AS (
     SUM(COALESCE("store_sales"."ss_sales_price" * "store_sales"."ss_quantity", 0)) AS "sumsales"
   FROM "store_sales" AS "store_sales"
   JOIN "date_dim" AS "date_dim"
-    ON "date_dim"."d_month_seq" <= 1192
+    ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk"
+    AND "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 "item" AS "item"
-    ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
+    ON "item"."i_item_sk" = "store_sales"."ss_item_sk"
   JOIN "store" AS "store"
-    ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
+    ON "store"."s_store_sk" = "store_sales"."ss_store_sk"
   GROUP BY
   ROLLUP (
     "item"."i_category",
@@ -9308,21 +9067,21 @@ WITH "dn" AS (
     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"
+    ON "customer_address"."ca_address_sk" = "store_sales"."ss_addr_sk"
   JOIN "date_dim" AS "date_dim"
-    ON "date_dim"."d_dom" <= 2
+    ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk"
+    AND "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 "household_demographics" AS "household_demographics"
-    ON (
+    ON "household_demographics"."hd_demo_sk" = "store_sales"."ss_hdemo_sk"
+    AND (
       "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 "store" AS "store"
     ON "store"."s_city" IN ('Fairview', 'Midway')
-    AND "store_sales"."ss_store_sk" = "store"."s_store_sk"
+    AND "store"."s_store_sk" = "store_sales"."ss_store_sk"
   GROUP BY
     "store_sales"."ss_ticket_number",
     "store_sales"."ss_customer_sk",
@@ -9340,10 +9099,10 @@ SELECT
   "dn"."list_price" AS "list_price"
 FROM "dn" AS "dn"
 JOIN "customer" AS "customer"
-  ON "dn"."ss_customer_sk" = "customer"."c_customer_sk"
+  ON "customer"."c_customer_sk" = "dn"."ss_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"
+  ON "current_addr"."ca_address_sk" = "customer"."c_current_addr_sk"
+  AND "current_addr"."ca_city" <> "dn"."bought_city"
 ORDER BY
   "c_last_name",
   "ss_ticket_number"
@@ -9436,7 +9195,7 @@ WITH "date_dim_2" AS (
     "store_sales"."ss_customer_sk" AS "_u_1"
   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"
+    ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk"
   GROUP BY
     "store_sales"."ss_customer_sk"
 ), "_u_2" AS (
@@ -9444,7 +9203,7 @@ WITH "date_dim_2" AS (
     "web_sales"."ws_bill_customer_sk" AS "_u_3"
   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"
+    ON "date_dim"."d_date_sk" = "web_sales"."ws_sold_date_sk"
   GROUP BY
     "web_sales"."ws_bill_customer_sk"
 ), "_u_4" AS (
@@ -9467,16 +9226,16 @@ SELECT
   COUNT(*) AS "cnt3"
 FROM "customer" AS "c"
 LEFT JOIN "_u_0" AS "_u_0"
-  ON "c"."c_customer_sk" = "_u_0"."_u_1"
+  ON "_u_0"."_u_1" = "c"."c_customer_sk"
 LEFT JOIN "_u_2" AS "_u_2"
-  ON "c"."c_customer_sk" = "_u_2"."_u_3"
+  ON "_u_2"."_u_3" = "c"."c_customer_sk"
 LEFT JOIN "_u_4" AS "_u_4"
-  ON "c"."c_customer_sk" = "_u_4"."_u_5"
+  ON "_u_4"."_u_5" = "c"."c_customer_sk"
 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"
+  ON "c"."c_current_cdemo_sk" = "customer_demographics"."cd_demo_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
@@ -9577,7 +9336,7 @@ JOIN "date_dim" AS "d1"
 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"
+  ON "_u_0"."s_state" = "store"."s_state"
 WHERE
   NOT "_u_0"."s_state" IS NULL
 GROUP BY
@@ -9649,14 +9408,22 @@ WITH "date_dim_2" AS (
   FROM "date_dim" AS "date_dim"
   WHERE
     "date_dim"."d_moy" = 11 AND "date_dim"."d_year" = 2001
-), "cte_4" AS (
+), "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"
+  JOIN "date_dim_2" AS "date_dim"
+    ON "date_dim"."d_date_sk" = "web_sales"."ws_sold_date_sk"
+  UNION ALL
   SELECT
     "catalog_sales"."cs_ext_sales_price" AS "ext_price",
     "catalog_sales"."cs_item_sk" AS "sold_item_sk",
     "catalog_sales"."cs_sold_time_sk" AS "time_sk"
   FROM "catalog_sales" AS "catalog_sales"
   JOIN "date_dim_2" AS "date_dim"
-    ON "date_dim"."d_date_sk" = "catalog_sales"."cs_sold_date_sk"
+    ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk"
   UNION ALL
   SELECT
     "store_sales"."ss_ext_sales_price" AS "ext_price",
@@ -9665,20 +9432,6 @@ WITH "date_dim_2" AS (
   FROM "store_sales" AS "store_sales"
   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"
-  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",
-    "cte_4"."sold_item_sk" AS "sold_item_sk",
-    "cte_4"."time_sk" AS "time_sk"
-  FROM "cte_4" AS "cte_4"
 )
 SELECT
   "item"."i_brand_id" AS "brand_id",
@@ -9688,12 +9441,12 @@ SELECT
   SUM("tmp"."ext_price") AS "ext_price"
 FROM "item" AS "item"
 JOIN "tmp" AS "tmp"
-  ON "tmp"."sold_item_sk" = "item"."i_item_sk"
+  ON "item"."i_item_sk" = "tmp"."sold_item_sk"
 JOIN "time_dim" AS "time_dim"
   ON (
     "time_dim"."t_meal_time" = 'breakfast' OR "time_dim"."t_meal_time" = 'dinner'
   )
-  AND "tmp"."time_sk" = "time_dim"."t_time_sk"
+  AND "time_dim"."t_time_sk" = "tmp"."time_sk"
 WHERE
   "item"."i_manager_id" = 1
 GROUP BY
@@ -9777,15 +9530,15 @@ JOIN "household_demographics" AS "household_demographics"
   AND "household_demographics"."hd_buy_potential" = '501-1000'
 JOIN "inventory" AS "inventory"
   ON "catalog_sales"."cs_item_sk" = "inventory"."inv_item_sk"
-  AND "inventory"."inv_quantity_on_hand" < "catalog_sales"."cs_quantity"
+  AND "catalog_sales"."cs_quantity" > "inventory"."inv_quantity_on_hand"
 JOIN "item" AS "item"
-  ON "item"."i_item_sk" = "catalog_sales"."cs_item_sk"
+  ON "catalog_sales"."cs_item_sk" = "item"."i_item_sk"
 LEFT JOIN "promotion" AS "promotion"
   ON "catalog_sales"."cs_promo_sk" = "promotion"."p_promo_sk"
 JOIN "date_dim" AS "d2"
-  ON "inventory"."inv_date_sk" = "d2"."d_date_sk"
+  ON "d2"."d_date_sk" = "inventory"."inv_date_sk"
 JOIN "warehouse" AS "warehouse"
-  ON "warehouse"."w_warehouse_sk" = "inventory"."inv_warehouse_sk"
+  ON "inventory"."inv_warehouse_sk" = "warehouse"."w_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"
@@ -9850,17 +9603,17 @@ WITH "dj" AS (
     COUNT(*) AS "cnt"
   FROM "store_sales" AS "store_sales"
   JOIN "date_dim" AS "date_dim"
-    ON "date_dim"."d_dom" <= 2
+    ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk"
+    AND "date_dim"."d_dom" <= 2
     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 "household_demographics" AS "household_demographics"
     ON (
       "household_demographics"."hd_buy_potential" = '0-500'
       OR "household_demographics"."hd_buy_potential" = '>10000'
     )
+    AND "household_demographics"."hd_demo_sk" = "store_sales"."ss_hdemo_sk"
     AND "household_demographics"."hd_vehicle_count" > 0
-    AND "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk"
     AND CASE
       WHEN "household_demographics"."hd_vehicle_count" > 0
       THEN "household_demographics"."hd_dep_count" / "household_demographics"."hd_vehicle_count"
@@ -9868,7 +9621,7 @@ WITH "dj" AS (
     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"
+    AND "store"."s_store_sk" = "store_sales"."ss_store_sk"
   GROUP BY
     "store_sales"."ss_ticket_number",
     "store_sales"."ss_customer_sk"
@@ -9882,7 +9635,7 @@ SELECT
   "dj"."cnt" AS "cnt"
 FROM "dj" AS "dj"
 JOIN "customer" AS "customer"
-  ON "dj"."ss_customer_sk" = "customer"."c_customer_sk"
+  ON "customer"."c_customer_sk" = "dj"."ss_customer_sk"
 WHERE
   "dj"."cnt" <= 5 AND "dj"."cnt" >= 1
 ORDER BY
@@ -9974,7 +9727,7 @@ WITH "customer_2" AS (
   FROM "date_dim" AS "date_dim"
   WHERE
     "date_dim"."d_year" IN (1999, 2000)
-), "cte" AS (
+), "year_total" AS (
   SELECT
     "customer"."c_customer_id" AS "customer_id",
     "customer"."c_first_name" AS "customer_first_name",
@@ -9986,13 +9739,13 @@ WITH "customer_2" AS (
   JOIN "store_sales" AS "store_sales"
     ON "customer"."c_customer_sk" = "store_sales"."ss_customer_sk"
   JOIN "date_dim_2" AS "date_dim"
-    ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
+    ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk"
   GROUP BY
     "customer"."c_customer_id",
     "customer"."c_first_name",
     "customer"."c_last_name",
     "date_dim"."d_year"
-), "cte_2" AS (
+  UNION ALL
   SELECT
     "customer"."c_customer_id" AS "customer_id",
     "customer"."c_first_name" AS "customer_first_name",
@@ -10004,30 +9757,12 @@ WITH "customer_2" AS (
   JOIN "web_sales" AS "web_sales"
     ON "customer"."c_customer_sk" = "web_sales"."ws_bill_customer_sk"
   JOIN "date_dim_2" AS "date_dim"
-    ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk"
+    ON "date_dim"."d_date_sk" = "web_sales"."ws_sold_date_sk"
   GROUP BY
     "customer"."c_customer_id",
     "customer"."c_first_name",
     "customer"."c_last_name",
     "date_dim"."d_year"
-), "year_total" AS (
-  SELECT
-    "cte"."customer_id" AS "customer_id",
-    "cte"."customer_first_name" AS "customer_first_name",
-    "cte"."customer_last_name" AS "customer_last_name",
-    "cte"."year1" AS "year1",
-    "cte"."year_total" AS "year_total",
-    "cte"."sale_type" AS "sale_type"
-  FROM "cte" AS "cte"
-  UNION ALL
-  SELECT
-    "cte_2"."customer_id" AS "customer_id",
-    "cte_2"."customer_first_name" AS "customer_first_name",
-    "cte_2"."customer_last_name" AS "customer_last_name",
-    "cte_2"."year1" AS "year1",
-    "cte_2"."year_total" AS "year_total",
-    "cte_2"."sale_type" AS "sale_type"
-  FROM "cte_2" AS "cte_2"
 )
 SELECT
   "t_s_secyear"."customer_id" AS "customer_id",
@@ -10044,17 +9779,17 @@ JOIN "year_total" AS "t_w_secyear"
   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"
+  ON "t_s_firstyear"."customer_id" = "t_s_secyear"."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"
-    ELSE NULL
-  END > CASE
     WHEN "t_s_firstyear"."year_total" > 0
     THEN "t_s_secyear"."year_total" / "t_s_firstyear"."year_total"
     ELSE NULL
+  END < CASE
+    WHEN "t_w_firstyear"."year_total" > 0
+    THEN "t_w_secyear"."year_total" / "t_w_firstyear"."year_total"
+    ELSE NULL
   END
 WHERE
   "t_s_firstyear"."sale_type" = 's'
@@ -10177,7 +9912,24 @@ WITH "date_dim_2" AS (
   FROM "item" AS "item"
   WHERE
     "item"."i_category" = 'Men'
-), "cte_4" AS (
+), "sales_detail" AS (
+  SELECT
+    "date_dim"."d_year" AS "d_year",
+    "item"."i_brand_id" AS "i_brand_id",
+    "item"."i_class_id" AS "i_class_id",
+    "item"."i_category_id" AS "i_category_id",
+    "item"."i_manufact_id" AS "i_manufact_id",
+    "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"
+  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_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"
+  UNION
   SELECT
     "date_dim"."d_year" AS "d_year",
     "item"."i_brand_id" AS "i_brand_id",
@@ -10192,8 +9944,8 @@ WITH "date_dim_2" AS (
   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"
+    ON "store_returns"."sr_item_sk" = "store_sales"."ss_item_sk"
+    AND "store_returns"."sr_ticket_number" = "store_sales"."ss_ticket_number"
   UNION
   SELECT
     "date_dim"."d_year" AS "d_year",
@@ -10209,35 +9961,8 @@ WITH "date_dim_2" AS (
   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"
-), "sales_detail" AS (
-  SELECT
-    "date_dim"."d_year" AS "d_year",
-    "item"."i_brand_id" AS "i_brand_id",
-    "item"."i_class_id" AS "i_class_id",
-    "item"."i_category_id" AS "i_category_id",
-    "item"."i_manufact_id" AS "i_manufact_id",
-    "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"
-  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",
-    "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",
-    "cte_4"."i_manufact_id" AS "i_manufact_id",
-    "cte_4"."sales_cnt" AS "sales_cnt",
-    "cte_4"."sales_amt" AS "sales_amt"
-  FROM "cte_4" AS "cte_4"
+    ON "web_returns"."wr_item_sk" = "web_sales"."ws_item_sk"
+    AND "web_returns"."wr_order_number" = "web_sales"."ws_order_number"
 ), "all_sales" AS (
   SELECT
     "sales_detail"."d_year" AS "d_year",
@@ -10350,7 +10075,22 @@ WITH "date_dim_2" AS (
     "item"."i_item_sk" AS "i_item_sk",
     "item"."i_category" AS "i_category"
   FROM "item" AS "item"
-), "cte_4" AS (
+), "foo" AS (
+  SELECT
+    'store' AS "channel",
+    'ss_hdemo_sk' AS "col_name",
+    "date_dim"."d_year" AS "d_year",
+    "date_dim"."d_qoy" AS "d_qoy",
+    "item"."i_category" AS "i_category",
+    "store_sales"."ss_ext_sales_price" AS "ext_sales_price"
+  FROM "store_sales" AS "store_sales"
+  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"
+  WHERE
+    "store_sales"."ss_hdemo_sk" IS NULL
+  UNION ALL
   SELECT
     'web' AS "channel",
     'ws_ship_hdemo_sk' AS "col_name",
@@ -10360,9 +10100,9 @@ WITH "date_dim_2" AS (
     "web_sales"."ws_ext_sales_price" AS "ext_sales_price"
   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"
+    ON "date_dim"."d_date_sk" = "web_sales"."ws_sold_date_sk"
   JOIN "item_2" AS "item"
-    ON "web_sales"."ws_item_sk" = "item"."i_item_sk"
+    ON "item"."i_item_sk" = "web_sales"."ws_item_sk"
   WHERE
     "web_sales"."ws_ship_hdemo_sk" IS NULL
   UNION ALL
@@ -10380,30 +10120,6 @@ WITH "date_dim_2" AS (
     ON "catalog_sales"."cs_item_sk" = "item"."i_item_sk"
   WHERE
     "catalog_sales"."cs_warehouse_sk" IS NULL
-), "foo" AS (
-  SELECT
-    'store' AS "channel",
-    'ss_hdemo_sk' AS "col_name",
-    "date_dim"."d_year" AS "d_year",
-    "date_dim"."d_qoy" AS "d_qoy",
-    "item"."i_category" AS "i_category",
-    "store_sales"."ss_ext_sales_price" AS "ext_sales_price"
-  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"
-  WHERE
-    "store_sales"."ss_hdemo_sk" IS NULL
-  UNION ALL
-  SELECT
-    "cte_4"."channel" AS "channel",
-    "cte_4"."col_name" AS "col_name",
-    "cte_4"."d_year" AS "d_year",
-    "cte_4"."d_qoy" AS "d_qoy",
-    "cte_4"."i_category" AS "i_category",
-    "cte_4"."ext_sales_price" AS "ext_sales_price"
-  FROM "cte_4" AS "cte_4"
 )
 SELECT
   "foo"."channel" AS "channel",
@@ -10431,7 +10147,6 @@ LIMIT 100;
 --------------------------------------
 -- TPC-DS 77
 --------------------------------------
-
 WITH ss AS
 (
          SELECT   s_store_sk,
@@ -10556,9 +10271,9 @@ WITH "date_dim_2" AS (
     SUM("store_sales"."ss_net_profit") AS "profit"
   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"
+    ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk"
   JOIN "store_2" AS "store"
-    ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
+    ON "store"."s_store_sk" = "store_sales"."ss_store_sk"
   GROUP BY
     "store"."s_store_sk"
 ), "sr" AS (
@@ -10568,9 +10283,9 @@ WITH "date_dim_2" AS (
     SUM("store_returns"."sr_net_loss") AS "profit_loss"
   FROM "store_returns" AS "store_returns"
   JOIN "date_dim_2" AS "date_dim"
-    ON "store_returns"."sr_returned_date_sk" = "date_dim"."d_date_sk"
+    ON "date_dim"."d_date_sk" = "store_returns"."sr_returned_date_sk"
   JOIN "store_2" AS "store"
-    ON "store_returns"."sr_store_sk" = "store"."s_store_sk"
+    ON "store"."s_store_sk" = "store_returns"."sr_store_sk"
   GROUP BY
     "store"."s_store_sk"
 ), "cs" AS (
@@ -10603,9 +10318,9 @@ WITH "date_dim_2" AS (
     SUM("web_sales"."ws_net_profit") AS "profit"
   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"
+    ON "date_dim"."d_date_sk" = "web_sales"."ws_sold_date_sk"
   JOIN "web_page_2" AS "web_page"
-    ON "web_sales"."ws_web_page_sk" = "web_page"."wp_web_page_sk"
+    ON "web_page"."wp_web_page_sk" = "web_sales"."ws_web_page_sk"
   GROUP BY
     "web_page"."wp_web_page_sk"
 ), "wr" AS (
@@ -10615,12 +10330,22 @@ WITH "date_dim_2" AS (
     SUM("web_returns"."wr_net_loss") AS "profit_loss"
   FROM "web_returns" AS "web_returns"
   JOIN "date_dim_2" AS "date_dim"
-    ON "web_returns"."wr_returned_date_sk" = "date_dim"."d_date_sk"
+    ON "date_dim"."d_date_sk" = "web_returns"."wr_returned_date_sk"
   JOIN "web_page_2" AS "web_page"
-    ON "web_returns"."wr_web_page_sk" = "web_page"."wp_web_page_sk"
+    ON "web_page"."wp_web_page_sk" = "web_returns"."wr_web_page_sk"
   GROUP BY
     "web_page"."wp_web_page_sk"
-), "cte_4" AS (
+), "x" AS (
+  SELECT
+    'store channel' AS "channel",
+    "ss"."s_store_sk" AS "id",
+    "ss"."sales" AS "sales",
+    COALESCE("sr"."returns1", 0) AS "returns1",
+    "ss"."profit" - COALESCE("sr"."profit_loss", 0) AS "profit"
+  FROM "ss"
+  LEFT JOIN "sr"
+    ON "sr"."s_store_sk" = "ss"."s_store_sk"
+  UNION ALL
   SELECT
     'catalog channel' AS "channel",
     "cs"."cs_call_center_sk" AS "id",
@@ -10638,25 +10363,7 @@ WITH "date_dim_2" AS (
     "ws"."profit" - COALESCE("wr"."profit_loss", 0) AS "profit"
   FROM "ws"
   LEFT JOIN "wr"
-    ON "ws"."wp_web_page_sk" = "wr"."wp_web_page_sk"
-), "x" AS (
-  SELECT
-    'store channel' AS "channel",
-    "ss"."s_store_sk" AS "id",
-    "ss"."sales" AS "sales",
-    COALESCE("sr"."returns1", 0) AS "returns1",
-    "ss"."profit" - COALESCE("sr"."profit_loss", 0) AS "profit"
-  FROM "ss"
-  LEFT JOIN "sr"
-    ON "ss"."s_store_sk" = "sr"."s_store_sk"
-  UNION ALL
-  SELECT
-    "cte_4"."channel" AS "channel",
-    "cte_4"."id" AS "id",
-    "cte_4"."sales" AS "sales",
-    "cte_4"."returns1" AS "returns1",
-    "cte_4"."profit" AS "profit"
-  FROM "cte_4" AS "cte_4"
+    ON "wr"."wp_web_page_sk" = "ws"."wp_web_page_sk"
 )
 SELECT
   "x"."channel" AS "channel",
@@ -10778,10 +10485,10 @@ WITH "date_dim_2" AS (
     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"
+    ON "date_dim"."d_date_sk" = "web_sales"."ws_sold_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"
+    ON "web_returns"."wr_item_sk" = "web_sales"."ws_item_sk"
+    AND "web_returns"."wr_order_number" = "web_sales"."ws_order_number"
   WHERE
     "web_returns"."wr_order_number" IS NULL
   GROUP BY
@@ -10798,8 +10505,8 @@ WITH "date_dim_2" AS (
     SUM("catalog_sales"."cs_sales_price") AS "cs_sp"
   FROM "catalog_sales" AS "catalog_sales"
   LEFT JOIN "catalog_returns" AS "catalog_returns"
-    ON "catalog_returns"."cr_order_number" = "catalog_sales"."cs_order_number"
-    AND "catalog_sales"."cs_item_sk" = "catalog_returns"."cr_item_sk"
+    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_2" AS "date_dim"
     ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk"
   WHERE
@@ -10818,10 +10525,10 @@ WITH "date_dim_2" AS (
     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"
+    ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_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"
+    ON "store_returns"."sr_item_sk" = "store_sales"."ss_item_sk"
+    AND "store_returns"."sr_ticket_number" = "store_sales"."ss_ticket_number"
   WHERE
     "store_returns"."sr_ticket_number" IS NULL
   GROUP BY
@@ -10844,9 +10551,9 @@ LEFT JOIN "cs"
   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"
+  ON "ss"."ss_customer_sk" = "ws"."ws_customer_sk"
+  AND "ss"."ss_item_sk" = "ws"."ws_item_sk"
+  AND "ss"."ss_sold_year" = "ws"."ws_sold_year"
 WHERE
   "cs"."cs_qty" > 0
   AND "ss"."ss_sold_year" = 1999
@@ -10910,19 +10617,19 @@ WITH "ms" AS (
     SUM("store_sales"."ss_net_profit") AS "profit"
   FROM "store_sales" AS "store_sales"
   JOIN "date_dim" AS "date_dim"
-    ON "date_dim"."d_dow" = 1
+    ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk"
+    AND "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 "household_demographics" AS "household_demographics"
-    ON (
+    ON "household_demographics"."hd_demo_sk" = "store_sales"."ss_hdemo_sk"
+    AND (
       "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"
+    AND "store"."s_store_sk" = "store_sales"."ss_store_sk"
   GROUP BY
     "store_sales"."ss_ticket_number",
     "store_sales"."ss_customer_sk",
@@ -10938,7 +10645,7 @@ SELECT
   "ms"."profit" AS "profit"
 FROM "ms" AS "ms"
 JOIN "customer" AS "customer"
-  ON "ms"."ss_customer_sk" = "customer"."c_customer_sk"
+  ON "customer"."c_customer_sk" = "ms"."ss_customer_sk"
 ORDER BY
   "c_last_name",
   "c_first_name",
@@ -11083,16 +10790,16 @@ WITH "date_dim_2" AS (
     SUM("store_sales"."ss_net_profit" - COALESCE("store_returns"."sr_net_loss", 0)) AS "profit"
   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"
+    ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk"
   JOIN "item_2" AS "item"
-    ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
+    ON "item"."i_item_sk" = "store_sales"."ss_item_sk"
   JOIN "promotion_2" AS "promotion"
-    ON "store_sales"."ss_promo_sk" = "promotion"."p_promo_sk"
+    ON "promotion"."p_promo_sk" = "store_sales"."ss_promo_sk"
   JOIN "store" AS "store"
-    ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
+    ON "store"."s_store_sk" = "store_sales"."ss_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"
+    ON "store_returns"."sr_item_sk" = "store_sales"."ss_item_sk"
+    AND "store_returns"."sr_ticket_number" = "store_sales"."ss_ticket_number"
   GROUP BY
     "store"."s_store_id"
 ), "csr" AS (
@@ -11103,10 +10810,10 @@ WITH "date_dim_2" AS (
     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"
+    ON "catalog_page"."cp_catalog_page_sk" = "catalog_sales"."cs_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"
+    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_2" AS "date_dim"
     ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk"
   JOIN "item_2" AS "item"
@@ -11123,19 +10830,27 @@ WITH "date_dim_2" AS (
     SUM("web_sales"."ws_net_profit" - COALESCE("web_returns"."wr_net_loss", 0)) AS "profit"
   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"
+    ON "date_dim"."d_date_sk" = "web_sales"."ws_sold_date_sk"
   JOIN "item_2" AS "item"
-    ON "web_sales"."ws_item_sk" = "item"."i_item_sk"
+    ON "item"."i_item_sk" = "web_sales"."ws_item_sk"
   JOIN "promotion_2" AS "promotion"
-    ON "web_sales"."ws_promo_sk" = "promotion"."p_promo_sk"
+    ON "promotion"."p_promo_sk" = "web_sales"."ws_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"
+    ON "web_returns"."wr_item_sk" = "web_sales"."ws_item_sk"
+    AND "web_returns"."wr_order_number" = "web_sales"."ws_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 (
+), "x" AS (
+  SELECT
+    'store channel' AS "channel",
+    CONCAT('store', "ssr"."store_id") AS "id",
+    "ssr"."sales" AS "sales",
+    "ssr"."returns1" AS "returns1",
+    "ssr"."profit" AS "profit"
+  FROM "ssr"
+  UNION ALL
   SELECT
     'catalog channel' AS "channel",
     CONCAT('catalog_page', "csr"."catalog_page_id") AS "id",
@@ -11151,22 +10866,6 @@ WITH "date_dim_2" AS (
     "wsr"."returns1" AS "returns1",
     "wsr"."profit" AS "profit"
   FROM "wsr"
-), "x" AS (
-  SELECT
-    'store channel' AS "channel",
-    CONCAT('store', "ssr"."store_id") AS "id",
-    "ssr"."sales" AS "sales",
-    "ssr"."returns1" AS "returns1",
-    "ssr"."profit" AS "profit"
-  FROM "ssr"
-  UNION ALL
-  SELECT
-    "cte_4"."channel" AS "channel",
-    "cte_4"."id" AS "id",
-    "cte_4"."sales" AS "sales",
-    "cte_4"."returns1" AS "returns1",
-    "cte_4"."profit" AS "profit"
-  FROM "cte_4" AS "cte_4"
 )
 SELECT
   "x"."channel" AS "channel",
@@ -11188,7 +10887,6 @@ LIMIT 100;
 --------------------------------------
 -- TPC-DS 81
 --------------------------------------
-
 WITH customer_total_return
      AS (SELECT cr_returning_customer_sk   AS ctr_customer_sk,
                 ca_state                   AS ctr_state,
@@ -11284,14 +10982,14 @@ SELECT
   "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"
+  ON "_u_0"."_u_1" = "ctr1"."ctr_state"
 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"
+  ON "customer"."c_current_addr_sk" = "customer_address"."ca_address_sk"
   AND "customer_address"."ca_state" = 'TX'
 WHERE
-  "ctr1"."ctr_total_return" > "_u_0"."_col_0"
+  "_u_0"."_col_0" < "ctr1"."ctr_total_return"
 ORDER BY
   "c_customer_id",
   "c_salutation",
@@ -11314,7 +11012,6 @@ LIMIT 100;
 --------------------------------------
 -- TPC-DS 82
 --------------------------------------
-
 SELECT
          i_item_id ,
          i_item_desc ,
@@ -11346,7 +11043,7 @@ JOIN "inventory" AS "inventory"
   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"
+  ON "item"."i_item_sk" = "store_sales"."ss_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)
@@ -11463,7 +11160,7 @@ WITH "date_dim_2" AS (
     "date_dim"."d_date" AS "d_date"
   FROM "date_dim" AS "date_dim"
   LEFT JOIN "_u_0" AS "_u_0"
-    ON "date_dim"."d_week_seq" = "_u_0"."d_week_seq"
+    ON "_u_0"."d_week_seq" = "date_dim"."d_week_seq"
   WHERE
     NOT "_u_0"."d_week_seq" IS NULL
   GROUP BY
@@ -11474,11 +11171,11 @@ WITH "date_dim_2" AS (
     SUM("store_returns"."sr_return_quantity") AS "sr_item_qty"
   FROM "store_returns" AS "store_returns"
   JOIN "date_dim_2" AS "date_dim"
-    ON "store_returns"."sr_returned_date_sk" = "date_dim"."d_date_sk"
+    ON "date_dim"."d_date_sk" = "store_returns"."sr_returned_date_sk"
   JOIN "item_2" AS "item"
-    ON "store_returns"."sr_item_sk" = "item"."i_item_sk"
+    ON "item"."i_item_sk" = "store_returns"."sr_item_sk"
   LEFT JOIN "_u_1" AS "_u_1"
-    ON "date_dim"."d_date" = "_u_1"."d_date"
+    ON "_u_1"."d_date" = "date_dim"."d_date"
   WHERE
     NOT "_u_1"."d_date" IS NULL
   GROUP BY
@@ -11488,7 +11185,7 @@ WITH "date_dim_2" AS (
     "date_dim"."d_date" AS "d_date"
   FROM "date_dim" AS "date_dim"
   LEFT JOIN "_u_0" AS "_u_2"
-    ON "date_dim"."d_week_seq" = "_u_2"."d_week_seq"
+    ON "_u_2"."d_week_seq" = "date_dim"."d_week_seq"
   WHERE
     NOT "_u_2"."d_week_seq" IS NULL
   GROUP BY
@@ -11503,7 +11200,7 @@ WITH "date_dim_2" AS (
   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"
+    ON "_u_3"."d_date" = "date_dim"."d_date"
   WHERE
     NOT "_u_3"."d_date" IS NULL
   GROUP BY
@@ -11513,7 +11210,7 @@ WITH "date_dim_2" AS (
     "date_dim"."d_date" AS "d_date"
   FROM "date_dim" AS "date_dim"
   LEFT JOIN "_u_0" AS "_u_4"
-    ON "date_dim"."d_week_seq" = "_u_4"."d_week_seq"
+    ON "_u_4"."d_week_seq" = "date_dim"."d_week_seq"
   WHERE
     NOT "_u_4"."d_week_seq" IS NULL
   GROUP BY
@@ -11524,11 +11221,11 @@ WITH "date_dim_2" AS (
     SUM("web_returns"."wr_return_quantity") AS "wr_item_qty"
   FROM "web_returns" AS "web_returns"
   JOIN "date_dim_2" AS "date_dim"
-    ON "web_returns"."wr_returned_date_sk" = "date_dim"."d_date_sk"
+    ON "date_dim"."d_date_sk" = "web_returns"."wr_returned_date_sk"
   JOIN "item_2" AS "item"
-    ON "web_returns"."wr_item_sk" = "item"."i_item_sk"
+    ON "item"."i_item_sk" = "web_returns"."wr_item_sk"
   LEFT JOIN "_u_5" AS "_u_5"
-    ON "date_dim"."d_date" = "_u_5"."d_date"
+    ON "_u_5"."d_date" = "date_dim"."d_date"
   WHERE
     NOT "_u_5"."d_date" IS NULL
   GROUP BY
@@ -11553,7 +11250,7 @@ SELECT
   ) / 3.0 AS "average"
 FROM "sr_items"
 JOIN "cr_items"
-  ON "sr_items"."item_id" = "cr_items"."item_id"
+  ON "cr_items"."item_id" = "sr_items"."item_id"
 JOIN "wr_items"
   ON "sr_items"."item_id" = "wr_items"."item_id"
 ORDER BY
@@ -11592,15 +11289,15 @@ JOIN "customer_address" AS "customer_address"
   ON "customer"."c_current_addr_sk" = "customer_address"."ca_address_sk"
   AND "customer_address"."ca_city" = 'Green Acres'
 JOIN "customer_demographics" AS "customer_demographics"
-  ON "customer_demographics"."cd_demo_sk" = "customer"."c_current_cdemo_sk"
+  ON "customer"."c_current_cdemo_sk" = "customer_demographics"."cd_demo_sk"
 JOIN "household_demographics" AS "household_demographics"
-  ON "household_demographics"."hd_demo_sk" = "customer"."c_current_hdemo_sk"
+  ON "customer"."c_current_hdemo_sk" = "household_demographics"."hd_demo_sk"
 JOIN "income_band" AS "income_band"
-  ON "income_band"."ib_income_band_sk" = "household_demographics"."hd_income_band_sk"
+  ON "household_demographics"."hd_income_band_sk" = "income_band"."ib_income_band_sk"
   AND "income_band"."ib_lower_bound" >= 54986
   AND "income_band"."ib_upper_bound" <= 104986
 JOIN "store_returns" AS "store_returns"
-  ON "store_returns"."sr_cdemo_sk" = "customer_demographics"."cd_demo_sk"
+  ON "customer_demographics"."cd_demo_sk" = "store_returns"."sr_cdemo_sk"
 ORDER BY
   "customer"."c_customer_id"
 LIMIT 100;
@@ -11666,12 +11363,12 @@ SELECT
   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"
+  ON "date_dim"."d_date_sk" = "web_sales"."ws_sold_date_sk" AND "date_dim"."d_year" = 2001
 JOIN "web_page" AS "web_page"
-  ON "web_sales"."ws_web_page_sk" = "web_page"."wp_web_page_sk"
+  ON "web_page"."wp_web_page_sk" = "web_sales"."ws_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"
+  ON "web_returns"."wr_item_sk" = "web_sales"."ws_item_sk"
+  AND "web_returns"."wr_order_number" = "web_sales"."ws_order_number"
 JOIN "customer_demographics" AS "cd2"
   ON "cd2"."cd_demo_sk" = "web_returns"."wr_returning_cdemo_sk"
 JOIN "customer_address" AS "customer_address"
@@ -11806,8 +11503,7 @@ from ((select distinct c_last_name, c_first_name, d_date
        where web_sales.ws_sold_date_sk = date_dim.d_date_sk
          and web_sales.ws_bill_customer_sk = customer.c_customer_sk
          and d_month_seq between 1188 and 1188+11)
-) cool_cust
-;
+) cool_cust;
 WITH "customer_2" AS (
   SELECT
     "customer"."c_customer_sk" AS "c_customer_sk",
@@ -11822,66 +11518,42 @@ WITH "customer_2" AS (
   FROM "date_dim" AS "date_dim"
   WHERE
     "date_dim"."d_month_seq" <= 1199 AND "date_dim"."d_month_seq" >= 1188
-), "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 "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 "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 "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 (
+), "cool_cust" AS (
   (
-    SELECT
-      "cte_2"."c_last_name" AS "c_last_name",
-      "cte_2"."c_first_name" AS "c_first_name",
-      "cte_2"."d_date" AS "d_date"
-    FROM "cte_2" AS "cte_2"
+    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 "customer_2" AS "customer"
+      ON "customer"."c_customer_sk" = "store_sales"."ss_customer_sk"
+    JOIN "date_dim_2" AS "date_dim"
+      ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk"
   )
   EXCEPT
   (
-    SELECT
-      "cte_3"."c_last_name" AS "c_last_name",
-      "cte_3"."c_first_name" AS "c_first_name",
-      "cte_3"."d_date" AS "d_date"
-    FROM "cte_3" AS "cte_3"
+    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 "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"
   )
-), "cool_cust" AS (
+  EXCEPT
   (
-    SELECT
-      "cte"."c_last_name" AS "c_last_name",
-      "cte"."c_first_name" AS "c_first_name",
-      "cte"."d_date" AS "d_date"
-    FROM "cte" AS "cte"
+    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 "customer_2" AS "customer"
+      ON "customer"."c_customer_sk" = "web_sales"."ws_bill_customer_sk"
+    JOIN "date_dim_2" AS "date_dim"
+      ON "date_dim"."d_date_sk" = "web_sales"."ws_sold_date_sk"
   )
-  EXCEPT
-  SELECT
-    "cte_4"."c_last_name" AS "c_last_name",
-    "cte_4"."c_first_name" AS "c_first_name",
-    "cte_4"."d_date" AS "d_date"
-  FROM "cte_4" AS "cte_4"
 )
 SELECT
   COUNT(*) AS "_col_0"
@@ -11979,8 +11651,7 @@ from
      and ((household_demographics.hd_dep_count = -1 and household_demographics.hd_vehicle_count<=-1+2) or
           (household_demographics.hd_dep_count = 2 and household_demographics.hd_vehicle_count<=2+2) or
           (household_demographics.hd_dep_count = 3 and household_demographics.hd_vehicle_count<=3+2))
-     and store.s_store_name = 'ese') s8
-;
+     and store.s_store_name = 'ese') s8;
 WITH "store_sales_2" AS (
   SELECT
     "store_sales"."ss_sold_time_sk" AS "ss_sold_time_sk",
@@ -12021,9 +11692,9 @@ WITH "store_sales_2" AS (
     COUNT(*) AS "h8_30_to_9"
   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"
+    ON "household_demographics"."hd_demo_sk" = "store_sales"."ss_hdemo_sk"
   JOIN "store_2" AS "store"
-    ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
+    ON "store"."s_store_sk" = "store_sales"."ss_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
@@ -12033,9 +11704,9 @@ WITH "store_sales_2" AS (
     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"
+    ON "household_demographics"."hd_demo_sk" = "store_sales"."ss_hdemo_sk"
   JOIN "store_2" AS "store"
-    ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
+    ON "store"."s_store_sk" = "store_sales"."ss_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
@@ -12045,9 +11716,9 @@ WITH "store_sales_2" AS (
     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"
+    ON "household_demographics"."hd_demo_sk" = "store_sales"."ss_hdemo_sk"
   JOIN "store_2" AS "store"
-    ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
+    ON "store"."s_store_sk" = "store_sales"."ss_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
@@ -12057,9 +11728,9 @@ WITH "store_sales_2" AS (
     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"
+    ON "household_demographics"."hd_demo_sk" = "store_sales"."ss_hdemo_sk"
   JOIN "store_2" AS "store"
-    ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
+    ON "store"."s_store_sk" = "store_sales"."ss_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
@@ -12069,9 +11740,9 @@ WITH "store_sales_2" AS (
     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"
+    ON "household_demographics"."hd_demo_sk" = "store_sales"."ss_hdemo_sk"
   JOIN "store_2" AS "store"
-    ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
+    ON "store"."s_store_sk" = "store_sales"."ss_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
@@ -12081,9 +11752,9 @@ WITH "store_sales_2" AS (
     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"
+    ON "household_demographics"."hd_demo_sk" = "store_sales"."ss_hdemo_sk"
   JOIN "store_2" AS "store"
-    ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
+    ON "store"."s_store_sk" = "store_sales"."ss_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
@@ -12093,9 +11764,9 @@ WITH "store_sales_2" AS (
     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"
+    ON "household_demographics"."hd_demo_sk" = "store_sales"."ss_hdemo_sk"
   JOIN "store_2" AS "store"
-    ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
+    ON "store"."s_store_sk" = "store_sales"."ss_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
@@ -12105,9 +11776,9 @@ WITH "store_sales_2" AS (
     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"
+    ON "household_demographics"."hd_demo_sk" = "store_sales"."ss_hdemo_sk"
   JOIN "store_2" AS "store"
-    ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
+    ON "store"."s_store_sk" = "store_sales"."ss_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
@@ -12185,12 +11856,12 @@ WITH "tmp1" AS (
     AVG(SUM("store_sales"."ss_sales_price")) OVER (PARTITION BY "item"."i_category", "item"."i_brand", "store"."s_store_name", "store"."s_company_name") AS "avg_monthly_sales"
   FROM "item" AS "item"
   JOIN "store_sales" AS "store_sales"
-    ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
+    ON "item"."i_item_sk" = "store_sales"."ss_item_sk"
   JOIN "date_dim" AS "date_dim"
-    ON "date_dim"."d_year" IN (2002)
-    AND "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
+    ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk"
+    AND "date_dim"."d_year" IN (2002)
   JOIN "store" AS "store"
-    ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
+    ON "store"."s_store_sk" = "store_sales"."ss_store_sk"
   WHERE
     (
       "item"."i_category" IN ('Home', 'Men', 'Sports')
@@ -12242,7 +11913,6 @@ LIMIT 100;
 --------------------------------------
 -- TPC-DS 90
 --------------------------------------
-
 SELECT Cast(amc AS DECIMAL(15, 4)) / Cast(pmc AS DECIMAL(15, 4))
                am_pm_ratio
 FROM   (SELECT Count(*) amc
@@ -12294,25 +11964,25 @@ WITH "web_sales_2" AS (
     COUNT(*) AS "amc"
   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"
+    ON "household_demographics"."hd_demo_sk" = "web_sales"."ws_ship_hdemo_sk"
   JOIN "time_dim" AS "time_dim"
     ON "time_dim"."t_hour" <= 13
     AND "time_dim"."t_hour" >= 12
-    AND "web_sales"."ws_sold_time_sk" = "time_dim"."t_time_sk"
+    AND "time_dim"."t_time_sk" = "web_sales"."ws_sold_time_sk"
   JOIN "web_page_2" AS "web_page"
-    ON "web_sales"."ws_web_page_sk" = "web_page"."wp_web_page_sk"
+    ON "web_page"."wp_web_page_sk" = "web_sales"."ws_web_page_sk"
 ), "pt" AS (
   SELECT
     COUNT(*) AS "pmc"
   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"
+    ON "household_demographics"."hd_demo_sk" = "web_sales"."ws_ship_hdemo_sk"
   JOIN "time_dim" AS "time_dim"
     ON "time_dim"."t_hour" <= 21
     AND "time_dim"."t_hour" >= 20
-    AND "web_sales"."ws_sold_time_sk" = "time_dim"."t_time_sk"
+    AND "time_dim"."t_time_sk" = "web_sales"."ws_sold_time_sk"
   JOIN "web_page_2" AS "web_page"
-    ON "web_sales"."ws_web_page_sk" = "web_page"."wp_web_page_sk"
+    ON "web_page"."wp_web_page_sk" = "web_sales"."ws_web_page_sk"
 )
 SELECT
   CAST("at1"."amc" AS DECIMAL(15, 4)) / CAST("pt"."pmc" AS DECIMAL(15, 4)) AS "am_pm_ratio"
@@ -12365,15 +12035,15 @@ FROM "call_center" AS "call_center"
 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"
+  ON "customer"."c_current_hdemo_sk" = "household_demographics"."hd_demo_sk"
 JOIN "catalog_returns" AS "catalog_returns"
-  ON "catalog_returns"."cr_call_center_sk" = "call_center"."cc_call_center_sk"
+  ON "call_center"."cc_call_center_sk" = "catalog_returns"."cr_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"
+  ON "customer"."c_current_addr_sk" = "customer_address"."ca_address_sk"
   AND "customer_address"."ca_gmt_offset" = -7
 JOIN "customer_demographics" AS "customer_demographics"
-  ON "customer_demographics"."cd_demo_sk" = "customer"."c_current_cdemo_sk"
+  ON "customer"."c_current_cdemo_sk" = "customer_demographics"."cd_demo_sk"
   AND (
     "customer_demographics"."cd_education_status" = 'Advanced Degree'
     OR "customer_demographics"."cd_education_status" = 'Unknown'
@@ -12461,7 +12131,7 @@ JOIN "item" AS "item"
 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"
+  "_u_0"."_col_0" < "web_sales"."ws_ext_discount_amt"
 ORDER BY
   SUM("web_sales"."ws_ext_discount_amt")
 LIMIT 100;
@@ -12510,7 +12180,7 @@ 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"
 WHERE
-  "store_returns"."sr_reason_sk" = "reason"."r_reason_sk"
+  "reason"."r_reason_sk" = "store_returns"."sr_reason_sk"
 GROUP BY
   "store_sales"."ss_customer_sk"
 ORDER BY
@@ -12569,21 +12239,21 @@ SELECT
   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"
+  ON "_u_0"."_u_1" = "ws1"."ws_order_number"
 LEFT JOIN "_u_3" AS "_u_3"
-  ON "ws1"."ws_order_number" = "_u_3"."_u_4"
+  ON "_u_3"."_u_4" = "ws1"."ws_order_number"
 JOIN "customer_address" AS "customer_address"
-  ON "customer_address"."ca_state" = 'MT'
-  AND "ws1"."ws_ship_addr_sk" = "customer_address"."ca_address_sk"
+  ON "customer_address"."ca_address_sk" = "ws1"."ws_ship_addr_sk"
+  AND "customer_address"."ca_state" = 'MT'
 JOIN "date_dim" AS "date_dim"
   ON "date_dim"."d_date" >= '2000-3-01'
-  AND "ws1"."ws_ship_date_sk" = "date_dim"."d_date_sk"
-  AND CAST("date_dim"."d_date" AS DATE) <= (
+  AND "date_dim"."d_date_sk" = "ws1"."ws_ship_date_sk"
+  AND (
     CAST('2000-3-01' AS DATE) + INTERVAL '60' day
-  )
+  ) >= CAST("date_dim"."d_date" AS DATE)
 JOIN "web_site" AS "web_site"
   ON "web_site"."web_company_name" = 'pri'
-  AND "ws1"."ws_web_site_sk" = "web_site"."web_site_sk"
+  AND "web_site"."web_site_sk" = "ws1"."ws_web_site_sk"
 WHERE
   "_u_3"."_u_4" IS NULL
   AND NOT "_u_0"."_u_1" IS NULL
@@ -12659,21 +12329,21 @@ SELECT
   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"
+  ON "_u_0"."ws_order_number" = "ws1"."ws_order_number"
 LEFT JOIN "_u_1" AS "_u_1"
-  ON "ws1"."ws_order_number" = "_u_1"."wr_order_number"
+  ON "_u_1"."wr_order_number" = "ws1"."ws_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"
+  ON "customer_address"."ca_address_sk" = "ws1"."ws_ship_addr_sk"
+  AND "customer_address"."ca_state" = 'IN'
 JOIN "date_dim" AS "date_dim"
   ON "date_dim"."d_date" >= '2000-4-01'
-  AND "ws1"."ws_ship_date_sk" = "date_dim"."d_date_sk"
-  AND CAST("date_dim"."d_date" AS DATE) <= (
+  AND "date_dim"."d_date_sk" = "ws1"."ws_ship_date_sk"
+  AND (
     CAST('2000-4-01' AS DATE) + INTERVAL '60' day
-  )
+  ) >= CAST("date_dim"."d_date" AS DATE)
 JOIN "web_site" AS "web_site"
   ON "web_site"."web_company_name" = 'pri'
-  AND "ws1"."ws_web_site_sk" = "web_site"."web_site_sk"
+  AND "web_site"."web_site_sk" = "ws1"."ws_web_site_sk"
 WHERE
   NOT "_u_0"."ws_order_number" IS NULL AND NOT "_u_1"."wr_order_number" IS NULL
 ORDER BY
@@ -12701,10 +12371,10 @@ SELECT
   COUNT(*) AS "_col_0"
 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"
+  ON "household_demographics"."hd_demo_sk" = "store_sales"."ss_hdemo_sk"
+  AND "household_demographics"."hd_dep_count" = 7
 JOIN "store" AS "store"
-  ON "store"."s_store_name" = 'ese' AND "store_sales"."ss_store_sk" = "store"."s_store_sk"
+  ON "store"."s_store_name" = 'ese' AND "store"."s_store_sk" = "store_sales"."ss_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
@@ -12716,7 +12386,6 @@ LIMIT 100;
 --------------------------------------
 -- TPC-DS 97
 --------------------------------------
-
 WITH ssci
      AS (SELECT ss_customer_sk customer_sk,
                 ss_item_sk     item_sk
@@ -12768,7 +12437,7 @@ WITH "date_dim_2" AS (
     "store_sales"."ss_item_sk" AS "item_sk"
   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"
+    ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk"
   GROUP BY
     "store_sales"."ss_customer_sk",
     "store_sales"."ss_item_sk"
@@ -12807,13 +12476,12 @@ SELECT
   ) AS "store_and_catalog"
 FROM "ssci"
 FULL JOIN "csci"
-  ON "ssci"."customer_sk" = "csci"."customer_sk" AND "ssci"."item_sk" = "csci"."item_sk"
+  ON "csci"."customer_sk" = "ssci"."customer_sk" AND "csci"."item_sk" = "ssci"."item_sk"
 LIMIT 100;
 
 --------------------------------------
 -- TPC-DS 98
 --------------------------------------
-
 SELECT i_item_id,
        i_item_desc,
        i_category,
@@ -12851,12 +12519,12 @@ SELECT
   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 "date_dim" AS "date_dim"
-  ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
+  ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_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"
+  AND "item"."i_item_sk" = "store_sales"."ss_item_sk"
 GROUP BY
   "item"."i_item_id",
   "item"."i_item_desc",
@@ -12873,8 +12541,6 @@ ORDER BY
 --------------------------------------
 -- TPC-DS 99
 --------------------------------------
-
-
 SELECT Substr(w_warehouse_name, 1, 20),
                sm_type,
                cc_name,
@@ -12963,7 +12629,7 @@ SELECT
   ) AS ">120 days"
 FROM "catalog_sales" AS "catalog_sales"
 JOIN "call_center" AS "call_center"
-  ON "catalog_sales"."cs_call_center_sk" = "call_center"."cc_call_center_sk"
+  ON "call_center"."cc_call_center_sk" = "catalog_sales"."cs_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
diff --git a/tests/fixtures/optimizer/tpc-h/tpc-h.sql b/tests/fixtures/optimizer/tpc-h/tpc-h.sql
index 14d8b53..660b565 100644
--- a/tests/fixtures/optimizer/tpc-h/tpc-h.sql
+++ b/tests/fixtures/optimizer/tpc-h/tpc-h.sql
@@ -118,9 +118,9 @@ WITH "region_2" AS (
     "partsupp"."ps_partkey" AS "_u_1"
   FROM "partsupp_2" AS "partsupp"
   JOIN "supplier" AS "supplier"
-    ON "supplier"."s_suppkey" = "partsupp"."ps_suppkey"
+    ON "partsupp"."ps_suppkey" = "supplier"."s_suppkey"
   JOIN "nation" AS "nation"
-    ON "supplier"."s_nationkey" = "nation"."n_nationkey"
+    ON "nation"."n_nationkey" = "supplier"."s_nationkey"
   JOIN "region_2" AS "region"
     ON "nation"."n_regionkey" = "region"."r_regionkey"
   GROUP BY
@@ -138,18 +138,18 @@ SELECT
 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"
+  ON "_u_0"."_u_1" = "part"."p_partkey"
 JOIN "nation" AS "nation"
   ON "nation"."n_regionkey" = "region"."r_regionkey"
 JOIN "partsupp_2" AS "partsupp"
   ON "part"."p_partkey" = "partsupp"."ps_partkey"
 JOIN "supplier" AS "supplier"
-  ON "supplier"."s_nationkey" = "nation"."n_nationkey"
-  AND "supplier"."s_suppkey" = "partsupp"."ps_suppkey"
+  ON "nation"."n_nationkey" = "supplier"."s_nationkey"
+  AND "partsupp"."ps_suppkey" = "supplier"."s_suppkey"
 WHERE
-  "part"."p_size" = 15
+  "_u_0"."_col_0" = "partsupp"."ps_supplycost"
+  AND "part"."p_size" = 15
   AND "part"."p_type" LIKE '%BRASS'
-  AND "partsupp"."ps_supplycost" = "_u_0"."_col_0"
 ORDER BY
   "s_acctbal" DESC,
   "n_name",
@@ -300,7 +300,7 @@ 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"
+  ON "nation"."n_nationkey" = "supplier"."s_nationkey"
 JOIN "region" AS "region"
   ON "nation"."n_regionkey" = "region"."r_regionkey" AND "region"."r_name" = 'ASIA'
 GROUP BY
@@ -381,14 +381,14 @@ SELECT
   )) AS "revenue"
 FROM "supplier" AS "supplier"
 JOIN "lineitem" AS "lineitem"
-  ON "supplier"."s_suppkey" = "lineitem"."l_suppkey"
+  ON "lineitem"."l_suppkey" = "supplier"."s_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 "nation" AS "n1"
   ON (
     "n1"."n_name" = 'FRANCE' OR "n1"."n_name" = 'GERMANY'
   )
-  AND "supplier"."s_nationkey" = "n1"."n_nationkey"
+  AND "n1"."n_nationkey" = "supplier"."s_nationkey"
 JOIN "nation" AS "n2"
   ON (
     "n1"."n_name" = 'FRANCE' OR "n2"."n_name" = 'FRANCE'
@@ -403,7 +403,7 @@ 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"
+  AND "lineitem"."l_orderkey" = "orders"."o_orderkey"
 GROUP BY
   "n1"."n_name",
   "n2"."n_name",
@@ -470,18 +470,18 @@ FROM "part" AS "part"
 JOIN "region" AS "region"
   ON "region"."r_name" = 'AMERICA'
 JOIN "lineitem" AS "lineitem"
-  ON "part"."p_partkey" = "lineitem"."l_partkey"
+  ON "lineitem"."l_partkey" = "part"."p_partkey"
 JOIN "nation" AS "n1"
   ON "n1"."n_regionkey" = "region"."r_regionkey"
 JOIN "customer" AS "customer"
   ON "customer"."c_nationkey" = "n1"."n_nationkey"
 JOIN "supplier" AS "supplier"
-  ON "supplier"."s_suppkey" = "lineitem"."l_suppkey"
+  ON "lineitem"."l_suppkey" = "supplier"."s_suppkey"
 JOIN "nation" AS "n2"
-  ON "supplier"."s_nationkey" = "n2"."n_nationkey"
+  ON "n2"."n_nationkey" = "supplier"."s_nationkey"
 JOIN "orders" AS "orders"
-  ON "lineitem"."l_orderkey" = "orders"."o_orderkey"
-  AND "orders"."o_custkey" = "customer"."c_custkey"
+  ON "customer"."c_custkey" = "orders"."o_custkey"
+  AND "lineitem"."l_orderkey" = "orders"."o_orderkey"
   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)
 WHERE
@@ -536,16 +536,16 @@ SELECT
   ) AS "sum_profit"
 FROM "part" AS "part"
 JOIN "lineitem" AS "lineitem"
-  ON "part"."p_partkey" = "lineitem"."l_partkey"
+  ON "lineitem"."l_partkey" = "part"."p_partkey"
 JOIN "orders" AS "orders"
-  ON "orders"."o_orderkey" = "lineitem"."l_orderkey"
+  ON "lineitem"."l_orderkey" = "orders"."o_orderkey"
 JOIN "partsupp" AS "partsupp"
-  ON "partsupp"."ps_partkey" = "lineitem"."l_partkey"
-  AND "partsupp"."ps_suppkey" = "lineitem"."l_suppkey"
+  ON "lineitem"."l_partkey" = "partsupp"."ps_partkey"
+  AND "lineitem"."l_suppkey" = "partsupp"."ps_suppkey"
 JOIN "supplier" AS "supplier"
-  ON "supplier"."s_suppkey" = "lineitem"."l_suppkey"
+  ON "lineitem"."l_suppkey" = "supplier"."s_suppkey"
 JOIN "nation" AS "nation"
-  ON "supplier"."s_nationkey" = "nation"."n_nationkey"
+  ON "nation"."n_nationkey" = "supplier"."s_nationkey"
 WHERE
   "part"."p_name" LIKE '%green%'
 GROUP BY
@@ -672,7 +672,7 @@ WITH "supplier_2" AS (
   JOIN "supplier_2" AS "supplier"
     ON "partsupp"."ps_suppkey" = "supplier"."s_suppkey"
   JOIN "nation_2" AS "nation"
-    ON "supplier"."s_nationkey" = "nation"."n_nationkey"
+    ON "nation"."n_nationkey" = "supplier"."s_nationkey"
 )
 SELECT
   "partsupp"."ps_partkey" AS "ps_partkey",
@@ -682,11 +682,11 @@ 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"
+  ON "nation"."n_nationkey" = "supplier"."s_nationkey"
 GROUP BY
   "partsupp"."ps_partkey"
 HAVING
-  SUM("partsupp"."ps_supplycost" * "partsupp"."ps_availqty") > MAX("_u_0"."_col_0")
+  MAX("_u_0"."_col_0") < SUM("partsupp"."ps_supplycost" * "partsupp"."ps_availqty")
 ORDER BY
   "value" DESC;
 
@@ -740,9 +740,9 @@ SELECT
 FROM "orders" AS "orders"
 JOIN "lineitem" AS "lineitem"
   ON "lineitem"."l_commitdate" < "lineitem"."l_receiptdate"
-  AND "lineitem"."l_shipdate" < "lineitem"."l_commitdate"
+  AND "lineitem"."l_commitdate" > "lineitem"."l_shipdate"
+  AND "lineitem"."l_orderkey" = "orders"."o_orderkey"
   AND "lineitem"."l_shipmode" IN ('MAIL', 'SHIP')
-  AND "orders"."o_orderkey" = "lineitem"."l_orderkey"
   AND CAST("lineitem"."l_receiptdate" AS DATE) < CAST('1995-01-01' AS DATE)
   AND CAST("lineitem"."l_receiptdate" AS DATE) >= CAST('1994-01-01' AS DATE)
 GROUP BY
@@ -893,9 +893,9 @@ SELECT
   "revenue"."total_revenue" AS "total_revenue"
 FROM "supplier" AS "supplier"
 JOIN "revenue"
-  ON "supplier"."s_suppkey" = "revenue"."supplier_no"
+  ON "revenue"."supplier_no" = "supplier"."s_suppkey"
 JOIN "_u_0" AS "_u_0"
-  ON "revenue"."total_revenue" = "_u_0"."_col_0"
+  ON "_u_0"."_col_0" = "revenue"."total_revenue"
 ORDER BY
   "s_suppkey";
 
@@ -948,7 +948,7 @@ SELECT
   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"
+  ON "_u_0"."s_suppkey" = "partsupp"."ps_suppkey"
 JOIN "part" AS "part"
   ON "part"."p_brand" <> 'Brand#45'
   AND "part"."p_partkey" = "partsupp"."ps_partkey"
@@ -998,13 +998,13 @@ SELECT
   SUM("lineitem"."l_extendedprice") / 7.0 AS "avg_yearly"
 FROM "lineitem" AS "lineitem"
 JOIN "part" AS "part"
-  ON "part"."p_brand" = 'Brand#23'
+  ON "lineitem"."l_partkey" = "part"."p_partkey"
+  AND "part"."p_brand" = 'Brand#23'
   AND "part"."p_container" = 'MED BOX'
-  AND "part"."p_partkey" = "lineitem"."l_partkey"
 LEFT JOIN "_u_0" AS "_u_0"
   ON "_u_0"."_u_1" = "part"."p_partkey"
 WHERE
-  "lineitem"."l_quantity" < "_u_0"."_col_0";
+  "_u_0"."_col_0" > "lineitem"."l_quantity";
 
 --------------------------------------
 -- TPC-H 18
@@ -1064,9 +1064,9 @@ 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"
+  ON "_u_0"."l_orderkey" = "orders"."o_orderkey"
 JOIN "lineitem" AS "lineitem"
-  ON "orders"."o_orderkey" = "lineitem"."l_orderkey"
+  ON "lineitem"."l_orderkey" = "orders"."o_orderkey"
 WHERE
   NOT "_u_0"."l_orderkey" IS NULL
 GROUP BY
@@ -1125,57 +1125,57 @@ SELECT
 FROM "lineitem" AS "lineitem"
 JOIN "part" AS "part"
   ON (
-    "part"."p_brand" = 'Brand#12'
+    "lineitem"."l_partkey" = "part"."p_partkey"
+    AND "part"."p_brand" = 'Brand#12'
     AND "part"."p_container" IN ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
-    AND "part"."p_partkey" = "lineitem"."l_partkey"
     AND "part"."p_size" <= 5
     AND "part"."p_size" >= 1
   )
   OR (
-    "part"."p_brand" = 'Brand#23'
+    "lineitem"."l_partkey" = "part"."p_partkey"
+    AND "part"."p_brand" = 'Brand#23'
     AND "part"."p_container" IN ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')
-    AND "part"."p_partkey" = "lineitem"."l_partkey"
     AND "part"."p_size" <= 10
     AND "part"."p_size" >= 1
   )
   OR (
-    "part"."p_brand" = 'Brand#34'
+    "lineitem"."l_partkey" = "part"."p_partkey"
+    AND "part"."p_brand" = 'Brand#34'
     AND "part"."p_container" IN ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')
-    AND "part"."p_partkey" = "lineitem"."l_partkey"
     AND "part"."p_size" <= 15
     AND "part"."p_size" >= 1
   )
 WHERE
   (
-    "lineitem"."l_quantity" <= 11
+    "lineitem"."l_partkey" = "part"."p_partkey"
+    AND "lineitem"."l_quantity" <= 11
     AND "lineitem"."l_quantity" >= 1
     AND "lineitem"."l_shipinstruct" = 'DELIVER IN PERSON'
     AND "lineitem"."l_shipmode" IN ('AIR', 'AIR REG')
     AND "part"."p_brand" = 'Brand#12'
     AND "part"."p_container" IN ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
-    AND "part"."p_partkey" = "lineitem"."l_partkey"
     AND "part"."p_size" <= 5
     AND "part"."p_size" >= 1
   )
   OR (
-    "lineitem"."l_quantity" <= 20
+    "lineitem"."l_partkey" = "part"."p_partkey"
+    AND "lineitem"."l_quantity" <= 20
     AND "lineitem"."l_quantity" >= 10
     AND "lineitem"."l_shipinstruct" = 'DELIVER IN PERSON'
     AND "lineitem"."l_shipmode" IN ('AIR', 'AIR REG')
     AND "part"."p_brand" = 'Brand#23'
     AND "part"."p_container" IN ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')
-    AND "part"."p_partkey" = "lineitem"."l_partkey"
     AND "part"."p_size" <= 10
     AND "part"."p_size" >= 1
   )
   OR (
-    "lineitem"."l_quantity" <= 30
+    "lineitem"."l_partkey" = "part"."p_partkey"
+    AND "lineitem"."l_quantity" <= 30
     AND "lineitem"."l_quantity" >= 20
     AND "lineitem"."l_shipinstruct" = 'DELIVER IN PERSON'
     AND "lineitem"."l_shipmode" IN ('AIR', 'AIR REG')
     AND "part"."p_brand" = 'Brand#34'
     AND "part"."p_container" IN ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')
-    AND "part"."p_partkey" = "lineitem"."l_partkey"
     AND "part"."p_size" <= 15
     AND "part"."p_size" >= 1
   );
@@ -1245,11 +1245,11 @@ WITH "_u_0" AS (
     "partsupp"."ps_suppkey" AS "ps_suppkey"
   FROM "partsupp" AS "partsupp"
   LEFT JOIN "_u_0" AS "_u_0"
-    ON "partsupp"."ps_partkey" = "_u_0"."p_partkey"
+    ON "_u_0"."p_partkey" = "partsupp"."ps_partkey"
   LEFT JOIN "_u_1" AS "_u_1"
     ON "_u_1"."_u_2" = "partsupp"."ps_partkey" AND "_u_1"."_u_3" = "partsupp"."ps_suppkey"
   WHERE
-    "partsupp"."ps_availqty" > "_u_1"."_col_0" AND NOT "_u_0"."p_partkey" IS NULL
+    "_u_1"."_col_0" < "partsupp"."ps_availqty" AND NOT "_u_0"."p_partkey" IS NULL
   GROUP BY
     "partsupp"."ps_suppkey"
 )
@@ -1258,9 +1258,9 @@ SELECT
   "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"
+  ON "_u_4"."ps_suppkey" = "supplier"."s_suppkey"
 JOIN "nation" AS "nation"
-  ON "nation"."n_name" = 'CANADA' AND "supplier"."s_nationkey" = "nation"."n_nationkey"
+  ON "nation"."n_name" = 'CANADA' AND "nation"."n_nationkey" = "supplier"."s_nationkey"
 WHERE
   NOT "_u_4"."ps_suppkey" IS NULL
 ORDER BY
@@ -1323,7 +1323,7 @@ WITH "_u_0" AS (
     ARRAY_AGG("l3"."l_suppkey") AS "_u_3"
   FROM "lineitem" AS "l3"
   WHERE
-    "l3"."l_receiptdate" > "l3"."l_commitdate"
+    "l3"."l_commitdate" < "l3"."l_receiptdate"
   GROUP BY
     "l3"."l_orderkey"
 )
@@ -1332,24 +1332,24 @@ SELECT
   COUNT(*) AS "numwait"
 FROM "supplier" AS "supplier"
 JOIN "lineitem" AS "l1"
-  ON "l1"."l_receiptdate" > "l1"."l_commitdate"
-  AND "supplier"."s_suppkey" = "l1"."l_suppkey"
+  ON "l1"."l_commitdate" < "l1"."l_receiptdate"
+  AND "l1"."l_suppkey" = "supplier"."s_suppkey"
 JOIN "nation" AS "nation"
   ON "nation"."n_name" = 'SAUDI ARABIA'
-  AND "supplier"."s_nationkey" = "nation"."n_nationkey"
+  AND "nation"."n_nationkey" = "supplier"."s_nationkey"
 LEFT JOIN "_u_0" AS "_u_0"
   ON "_u_0"."l_orderkey" = "l1"."l_orderkey"
 LEFT JOIN "_u_2" AS "_u_2"
   ON "_u_2"."l_orderkey" = "l1"."l_orderkey"
 JOIN "orders" AS "orders"
-  ON "orders"."o_orderkey" = "l1"."l_orderkey" AND "orders"."o_orderstatus" = 'F'
+  ON "l1"."l_orderkey" = "orders"."o_orderkey" AND "orders"."o_orderstatus" = 'F'
 WHERE
   (
     "_u_2"."l_orderkey" IS NULL
-    OR NOT ARRAY_ANY("_u_2"."_u_3", "_x" -> "_x" <> "l1"."l_suppkey")
+    OR NOT ARRAY_ANY("_u_2"."_u_3", "_x" -> "l1"."l_suppkey" <> "_x")
   )
   AND NOT "_u_0"."l_orderkey" IS NULL
-  AND ARRAY_ANY("_u_0"."_u_1", "_x" -> "_x" <> "l1"."l_suppkey")
+  AND ARRAY_ANY("_u_0"."_u_1", "_x" -> "l1"."l_suppkey" <> "_x")
 GROUP BY
   "supplier"."s_name"
 ORDER BY
@@ -1417,7 +1417,7 @@ SELECT
   SUM("customer"."c_acctbal") AS "totacctbal"
 FROM "customer" AS "customer"
 JOIN "_u_0" AS "_u_0"
-  ON "customer"."c_acctbal" > "_u_0"."_col_0"
+  ON "_u_0"."_col_0" < "customer"."c_acctbal"
 LEFT JOIN "_u_1" AS "_u_1"
   ON "_u_1"."_u_2" = "customer"."c_custkey"
 WHERE
diff --git a/tests/fixtures/optimizer/unnest_subqueries.sql b/tests/fixtures/optimizer/unnest_subqueries.sql
index e78bed0..f83191d 100644
--- a/tests/fixtures/optimizer/unnest_subqueries.sql
+++ b/tests/fixtures/optimizer/unnest_subqueries.sql
@@ -24,6 +24,7 @@ WHERE
   AND x.a = (SELECT SUM(y.c) AS c FROM y WHERE y.a = x.a OFFSET 10)
   AND x.a > ALL (SELECT y.c FROM y WHERE y.a = x.a)
   AND x.a > (SELECT COUNT(*) as d FROM y WHERE y.a = x.a)
+  AND x.a = SUM(SELECT 1)  -- invalid statement left alone
 ;
 SELECT
   *
@@ -208,7 +209,10 @@ WHERE
     OFFSET 10
   )
   AND ARRAY_ALL(_u_19."", _x -> _x = x.a)
-  AND x.a > COALESCE(_u_21.d, 0);
+  AND x.a > COALESCE(_u_21.d, 0)
+  AND x.a = SUM(SELECT
+    1) /* invalid statement left alone */
+;
 SELECT
   CAST((
     SELECT
diff --git a/tests/gen_fixtures.py b/tests/gen_fixtures.py
new file mode 100644
index 0000000..7538b87
--- /dev/null
+++ b/tests/gen_fixtures.py
@@ -0,0 +1,602 @@
+import time
+
+from sqlglot.optimizer import optimize
+
+TPCH_SCHEMA = {
+    "lineitem": {
+        "l_orderkey": "bigint",
+        "l_partkey": "bigint",
+        "l_suppkey": "bigint",
+        "l_linenumber": "bigint",
+        "l_quantity": "double",
+        "l_extendedprice": "double",
+        "l_discount": "double",
+        "l_tax": "double",
+        "l_returnflag": "string",
+        "l_linestatus": "string",
+        "l_shipdate": "string",
+        "l_commitdate": "string",
+        "l_receiptdate": "string",
+        "l_shipinstruct": "string",
+        "l_shipmode": "string",
+        "l_comment": "string",
+    },
+    "orders": {
+        "o_orderkey": "bigint",
+        "o_custkey": "bigint",
+        "o_orderstatus": "string",
+        "o_totalprice": "double",
+        "o_orderdate": "string",
+        "o_orderpriority": "string",
+        "o_clerk": "string",
+        "o_shippriority": "int",
+        "o_comment": "string",
+    },
+    "customer": {
+        "c_custkey": "bigint",
+        "c_name": "string",
+        "c_address": "string",
+        "c_nationkey": "bigint",
+        "c_phone": "string",
+        "c_acctbal": "double",
+        "c_mktsegment": "string",
+        "c_comment": "string",
+    },
+    "part": {
+        "p_partkey": "bigint",
+        "p_name": "string",
+        "p_mfgr": "string",
+        "p_brand": "string",
+        "p_type": "string",
+        "p_size": "int",
+        "p_container": "string",
+        "p_retailprice": "double",
+        "p_comment": "string",
+    },
+    "supplier": {
+        "s_suppkey": "bigint",
+        "s_name": "string",
+        "s_address": "string",
+        "s_nationkey": "bigint",
+        "s_phone": "string",
+        "s_acctbal": "double",
+        "s_comment": "string",
+    },
+    "partsupp": {
+        "ps_partkey": "bigint",
+        "ps_suppkey": "bigint",
+        "ps_availqty": "int",
+        "ps_supplycost": "double",
+        "ps_comment": "string",
+    },
+    "nation": {
+        "n_nationkey": "bigint",
+        "n_name": "string",
+        "n_regionkey": "bigint",
+        "n_comment": "string",
+    },
+    "region": {
+        "r_regionkey": "bigint",
+        "r_name": "string",
+        "r_comment": "string",
+    },
+}
+
+TPCDS_SCHEMA = {
+    "catalog_sales": {
+        "cs_sold_date_sk": "bigint",
+        "cs_sold_time_sk": "bigint",
+        "cs_ship_date_sk": "bigint",
+        "cs_bill_customer_sk": "bigint",
+        "cs_bill_cdemo_sk": "bigint",
+        "cs_bill_hdemo_sk": "bigint",
+        "cs_bill_addr_sk": "bigint",
+        "cs_ship_customer_sk": "bigint",
+        "cs_ship_cdemo_sk": "bigint",
+        "cs_ship_hdemo_sk": "bigint",
+        "cs_ship_addr_sk": "bigint",
+        "cs_call_center_sk": "bigint",
+        "cs_catalog_page_sk": "bigint",
+        "cs_ship_mode_sk": "bigint",
+        "cs_warehouse_sk": "bigint",
+        "cs_item_sk": "bigint",
+        "cs_promo_sk": "bigint",
+        "cs_order_number": "bigint",
+        "cs_quantity": "bigint",
+        "cs_wholesale_cost": "double",
+        "cs_list_price": "double",
+        "cs_sales_price": "double",
+        "cs_ext_discount_amt": "double",
+        "cs_ext_sales_price": "double",
+        "cs_ext_wholesale_cost": "double",
+        "cs_ext_list_price": "double",
+        "cs_ext_tax": "double",
+        "cs_coupon_amt": "double",
+        "cs_ext_ship_cost": "double",
+        "cs_net_paid": "double",
+        "cs_net_paid_inc_tax": "double",
+        "cs_net_paid_inc_ship": "double",
+        "cs_net_paid_inc_ship_tax": "double",
+        "cs_net_profit": "double",
+    },
+    "catalog_returns": {
+        "cr_returned_date_sk": "bigint",
+        "cr_returned_time_sk": "bigint",
+        "cr_item_sk": "bigint",
+        "cr_refunded_customer_sk": "bigint",
+        "cr_refunded_cdemo_sk": "bigint",
+        "cr_refunded_hdemo_sk": "bigint",
+        "cr_refunded_addr_sk": "bigint",
+        "cr_returning_customer_sk": "bigint",
+        "cr_returning_cdemo_sk": "bigint",
+        "cr_returning_hdemo_sk": "bigint",
+        "cr_returning_addr_sk": "bigint",
+        "cr_call_center_sk": "bigint",
+        "cr_catalog_page_sk": "bigint",
+        "cr_ship_mode_sk": "bigint",
+        "cr_warehouse_sk": "bigint",
+        "cr_reason_sk": "bigint",
+        "cr_order_number": "bigint",
+        "cr_return_quantity": "bigint",
+        "cr_return_amount": "double",
+        "cr_return_tax": "double",
+        "cr_return_amt_inc_tax": "double",
+        "cr_fee": "double",
+        "cr_return_ship_cost": "double",
+        "cr_refunded_cash": "double",
+        "cr_reversed_charge": "double",
+        "cr_store_credit": "double",
+        "cr_net_loss": "double",
+    },
+    "inventory": {
+        "inv_date_sk": "bigint",
+        "inv_item_sk": "bigint",
+        "inv_warehouse_sk": "bigint",
+        "inv_quantity_on_hand": "bigint",
+    },
+    "store_sales": {
+        "ss_sold_date_sk": "bigint",
+        "ss_sold_time_sk": "bigint",
+        "ss_item_sk": "bigint",
+        "ss_customer_sk": "bigint",
+        "ss_cdemo_sk": "bigint",
+        "ss_hdemo_sk": "bigint",
+        "ss_addr_sk": "bigint",
+        "ss_store_sk": "bigint",
+        "ss_promo_sk": "bigint",
+        "ss_ticket_number": "bigint",
+        "ss_quantity": "bigint",
+        "ss_wholesale_cost": "double",
+        "ss_list_price": "double",
+        "ss_sales_price": "double",
+        "ss_ext_discount_amt": "double",
+        "ss_ext_sales_price": "double",
+        "ss_ext_wholesale_cost": "double",
+        "ss_ext_list_price": "double",
+        "ss_ext_tax": "double",
+        "ss_coupon_amt": "double",
+        "ss_net_paid": "double",
+        "ss_net_paid_inc_tax": "double",
+        "ss_net_profit": "double",
+    },
+    "store_returns": {
+        "sr_returned_date_sk": "bigint",
+        "sr_return_time_sk": "bigint",
+        "sr_item_sk": "bigint",
+        "sr_customer_sk": "bigint",
+        "sr_cdemo_sk": "bigint",
+        "sr_hdemo_sk": "bigint",
+        "sr_addr_sk": "bigint",
+        "sr_store_sk": "bigint",
+        "sr_reason_sk": "bigint",
+        "sr_ticket_number": "bigint",
+        "sr_return_quantity": "bigint",
+        "sr_return_amt": "double",
+        "sr_return_tax": "double",
+        "sr_return_amt_inc_tax": "double",
+        "sr_fee": "double",
+        "sr_return_ship_cost": "double",
+        "sr_refunded_cash": "double",
+        "sr_reversed_charge": "double",
+        "sr_store_credit": "double",
+        "sr_net_loss": "double",
+    },
+    "web_sales": {
+        "ws_sold_date_sk": "bigint",
+        "ws_sold_time_sk": "bigint",
+        "ws_ship_date_sk": "bigint",
+        "ws_item_sk": "bigint",
+        "ws_bill_customer_sk": "bigint",
+        "ws_bill_cdemo_sk": "bigint",
+        "ws_bill_hdemo_sk": "bigint",
+        "ws_bill_addr_sk": "bigint",
+        "ws_ship_customer_sk": "bigint",
+        "ws_ship_cdemo_sk": "bigint",
+        "ws_ship_hdemo_sk": "bigint",
+        "ws_ship_addr_sk": "bigint",
+        "ws_web_page_sk": "bigint",
+        "ws_web_site_sk": "bigint",
+        "ws_ship_mode_sk": "bigint",
+        "ws_warehouse_sk": "bigint",
+        "ws_promo_sk": "bigint",
+        "ws_order_number": "bigint",
+        "ws_quantity": "bigint",
+        "ws_wholesale_cost": "double",
+        "ws_list_price": "double",
+        "ws_sales_price": "double",
+        "ws_ext_discount_amt": "double",
+        "ws_ext_sales_price": "double",
+        "ws_ext_wholesale_cost": "double",
+        "ws_ext_list_price": "double",
+        "ws_ext_tax": "double",
+        "ws_coupon_amt": "double",
+        "ws_ext_ship_cost": "double",
+        "ws_net_paid": "double",
+        "ws_net_paid_inc_tax": "double",
+        "ws_net_paid_inc_ship": "double",
+        "ws_net_paid_inc_ship_tax": "double",
+        "ws_net_profit": "double",
+    },
+    "web_returns": {
+        "wr_returned_date_sk": "bigint",
+        "wr_returned_time_sk": "bigint",
+        "wr_item_sk": "bigint",
+        "wr_refunded_customer_sk": "bigint",
+        "wr_refunded_cdemo_sk": "bigint",
+        "wr_refunded_hdemo_sk": "bigint",
+        "wr_refunded_addr_sk": "bigint",
+        "wr_returning_customer_sk": "bigint",
+        "wr_returning_cdemo_sk": "bigint",
+        "wr_returning_hdemo_sk": "bigint",
+        "wr_returning_addr_sk": "bigint",
+        "wr_web_page_sk": "bigint",
+        "wr_reason_sk": "bigint",
+        "wr_order_number": "bigint",
+        "wr_return_quantity": "bigint",
+        "wr_return_amt": "double",
+        "wr_return_tax": "double",
+        "wr_return_amt_inc_tax": "double",
+        "wr_fee": "double",
+        "wr_return_ship_cost": "double",
+        "wr_refunded_cash": "double",
+        "wr_reversed_charge": "double",
+        "wr_account_credit": "double",
+        "wr_net_loss": "double",
+    },
+    "call_center": {
+        "cc_call_center_sk": "bigint",
+        "cc_call_center_id": "string",
+        "cc_rec_start_date": "string",
+        "cc_rec_end_date": "string",
+        "cc_closed_date_sk": "bigint",
+        "cc_open_date_sk": "bigint",
+        "cc_name": "string",
+        "cc_class": "string",
+        "cc_employees": "bigint",
+        "cc_sq_ft": "bigint",
+        "cc_hours": "string",
+        "cc_manager": "string",
+        "cc_mkt_id": "bigint",
+        "cc_mkt_class": "string",
+        "cc_mkt_desc": "string",
+        "cc_market_manager": "string",
+        "cc_division": "bigint",
+        "cc_division_name": "string",
+        "cc_company": "bigint",
+        "cc_company_name": "string",
+        "cc_street_number": "string",
+        "cc_street_name": "string",
+        "cc_street_type": "string",
+        "cc_suite_number": "string",
+        "cc_city": "string",
+        "cc_county": "string",
+        "cc_state": "string",
+        "cc_zip": "string",
+        "cc_country": "string",
+        "cc_gmt_offset": "double",
+        "cc_tax_percentage": "double",
+    },
+    "catalog_page": {
+        "cp_catalog_page_sk": "bigint",
+        "cp_catalog_page_id": "string",
+        "cp_start_date_sk": "bigint",
+        "cp_end_date_sk": "bigint",
+        "cp_department": "string",
+        "cp_catalog_number": "bigint",
+        "cp_catalog_page_number": "bigint",
+        "cp_description": "string",
+        "cp_type": "string",
+    },
+    "customer": {
+        "c_customer_sk": "bigint",
+        "c_customer_id": "string",
+        "c_current_cdemo_sk": "bigint",
+        "c_current_hdemo_sk": "bigint",
+        "c_current_addr_sk": "bigint",
+        "c_first_shipto_date_sk": "bigint",
+        "c_first_sales_date_sk": "bigint",
+        "c_salutation": "string",
+        "c_first_name": "string",
+        "c_last_name": "string",
+        "c_preferred_cust_flag": "string",
+        "c_birth_day": "bigint",
+        "c_birth_month": "bigint",
+        "c_birth_year": "bigint",
+        "c_birth_country": "string",
+        "c_login": "string",
+        "c_email_address": "string",
+        "c_last_review_date": "string",
+    },
+    "customer_address": {
+        "ca_address_sk": "bigint",
+        "ca_address_id": "string",
+        "ca_street_number": "string",
+        "ca_street_name": "string",
+        "ca_street_type": "string",
+        "ca_suite_number": "string",
+        "ca_city": "string",
+        "ca_county": "string",
+        "ca_state": "string",
+        "ca_zip": "string",
+        "ca_country": "string",
+        "ca_gmt_offset": "double",
+        "ca_location_type": "string",
+    },
+    "customer_demographics": {
+        "cd_demo_sk": "bigint",
+        "cd_gender": "string",
+        "cd_marital_status": "string",
+        "cd_education_status": "string",
+        "cd_purchase_estimate": "bigint",
+        "cd_credit_rating": "string",
+        "cd_dep_count": "bigint",
+        "cd_dep_employed_count": "bigint",
+        "cd_dep_college_count": "bigint",
+    },
+    "date_dim": {
+        "d_date_sk": "bigint",
+        "d_date_id": "string",
+        "d_date": "string",
+        "d_month_seq": "bigint",
+        "d_week_seq": "bigint",
+        "d_quarter_seq": "bigint",
+        "d_year": "bigint",
+        "d_dow": "bigint",
+        "d_moy": "bigint",
+        "d_dom": "bigint",
+        "d_qoy": "bigint",
+        "d_fy_year": "bigint",
+        "d_fy_quarter_seq": "bigint",
+        "d_fy_week_seq": "bigint",
+        "d_day_name": "string",
+        "d_quarter_name": "string",
+        "d_holiday": "string",
+        "d_weekend": "string",
+        "d_following_holiday": "string",
+        "d_first_dom": "bigint",
+        "d_last_dom": "bigint",
+        "d_same_day_ly": "bigint",
+        "d_same_day_lq": "bigint",
+        "d_current_day": "string",
+        "d_current_week": "string",
+        "d_current_month": "string",
+        "d_current_quarter": "string",
+        "d_current_year": "string",
+    },
+    "household_demographics": {
+        "hd_demo_sk": "bigint",
+        "hd_income_band_sk": "bigint",
+        "hd_buy_potential": "string",
+        "hd_dep_count": "bigint",
+        "hd_vehicle_count": "bigint",
+    },
+    "income_band": {
+        "ib_income_band_sk": "bigint",
+        "ib_lower_bound": "bigint",
+        "ib_upper_bound": "bigint",
+    },
+    "item": {
+        "i_item_sk": "bigint",
+        "i_item_id": "string",
+        "i_rec_start_date": "string",
+        "i_rec_end_date": "string",
+        "i_item_desc": "string",
+        "i_current_price": "double",
+        "i_wholesale_cost": "double",
+        "i_brand_id": "bigint",
+        "i_brand": "string",
+        "i_class_id": "bigint",
+        "i_class": "string",
+        "i_category_id": "bigint",
+        "i_category": "string",
+        "i_manufact_id": "bigint",
+        "i_manufact": "string",
+        "i_size": "string",
+        "i_formulation": "string",
+        "i_color": "string",
+        "i_units": "string",
+        "i_container": "string",
+        "i_manager_id": "bigint",
+        "i_product_name": "string",
+    },
+    "promotion": {
+        "p_promo_sk": "bigint",
+        "p_promo_id": "string",
+        "p_start_date_sk": "bigint",
+        "p_end_date_sk": "bigint",
+        "p_item_sk": "bigint",
+        "p_cost": "double",
+        "p_response_target": "bigint",
+        "p_promo_name": "string",
+        "p_channel_dmail": "string",
+        "p_channel_email": "string",
+        "p_channel_catalog": "string",
+        "p_channel_tv": "string",
+        "p_channel_radio": "string",
+        "p_channel_press": "string",
+        "p_channel_event": "string",
+        "p_channel_demo": "string",
+        "p_channel_details": "string",
+        "p_purpose": "string",
+        "p_discount_active": "string",
+    },
+    "reason": {"r_reason_sk": "bigint", "r_reason_id": "string", "r_reason_desc": "string"},
+    "ship_mode": {
+        "sm_ship_mode_sk": "bigint",
+        "sm_ship_mode_id": "string",
+        "sm_type": "string",
+        "sm_code": "string",
+        "sm_carrier": "string",
+        "sm_contract": "string",
+    },
+    "store": {
+        "s_store_sk": "bigint",
+        "s_store_id": "string",
+        "s_rec_start_date": "string",
+        "s_rec_end_date": "string",
+        "s_closed_date_sk": "bigint",
+        "s_store_name": "string",
+        "s_number_employees": "bigint",
+        "s_floor_space": "bigint",
+        "s_hours": "string",
+        "s_manager": "string",
+        "s_market_id": "bigint",
+        "s_geography_class": "string",
+        "s_market_desc": "string",
+        "s_market_manager": "string",
+        "s_division_id": "bigint",
+        "s_division_name": "string",
+        "s_company_id": "bigint",
+        "s_company_name": "string",
+        "s_street_number": "string",
+        "s_street_name": "string",
+        "s_street_type": "string",
+        "s_suite_number": "string",
+        "s_city": "string",
+        "s_county": "string",
+        "s_state": "string",
+        "s_zip": "string",
+        "s_country": "string",
+        "s_gmt_offset": "double",
+        "s_tax_precentage": "double",
+    },
+    "time_dim": {
+        "t_time_sk": "bigint",
+        "t_time_id": "string",
+        "t_time": "bigint",
+        "t_hour": "bigint",
+        "t_minute": "bigint",
+        "t_second": "bigint",
+        "t_am_pm": "string",
+        "t_shift": "string",
+        "t_sub_shift": "string",
+        "t_meal_time": "string",
+    },
+    "warehouse": {
+        "w_warehouse_sk": "bigint",
+        "w_warehouse_id": "string",
+        "w_warehouse_name": "string",
+        "w_warehouse_sq_ft": "bigint",
+        "w_street_number": "string",
+        "w_street_name": "string",
+        "w_street_type": "string",
+        "w_suite_number": "string",
+        "w_city": "string",
+        "w_county": "string",
+        "w_state": "string",
+        "w_zip": "string",
+        "w_country": "string",
+        "w_gmt_offset": "double",
+    },
+    "web_page": {
+        "wp_web_page_sk": "bigint",
+        "wp_web_page_id": "string",
+        "wp_rec_start_date": "string",
+        "wp_rec_end_date": "string",
+        "wp_creation_date_sk": "bigint",
+        "wp_access_date_sk": "bigint",
+        "wp_autogen_flag": "string",
+        "wp_customer_sk": "bigint",
+        "wp_url": "string",
+        "wp_type": "string",
+        "wp_char_count": "bigint",
+        "wp_link_count": "bigint",
+        "wp_image_count": "bigint",
+        "wp_max_ad_count": "bigint",
+    },
+    "web_site": {
+        "web_site_sk": "bigint",
+        "web_site_id": "string",
+        "web_rec_start_date": "string",
+        "web_rec_end_date": "string",
+        "web_name": "string",
+        "web_open_date_sk": "bigint",
+        "web_close_date_sk": "bigint",
+        "web_class": "string",
+        "web_manager": "string",
+        "web_mkt_id": "bigint",
+        "web_mkt_class": "string",
+        "web_mkt_desc": "string",
+        "web_market_manager": "string",
+        "web_company_id": "bigint",
+        "web_company_name": "string",
+        "web_street_number": "string",
+        "web_street_name": "string",
+        "web_street_type": "string",
+        "web_suite_number": "string",
+        "web_city": "string",
+        "web_county": "string",
+        "web_state": "string",
+        "web_zip": "string",
+        "web_country": "string",
+        "web_gmt_offset": "string",
+        "web_tax_percentage": "double",
+    },
+}
+
+
+def rewrite_fixtures(in_path, out_path, schema, num, kind):
+    with open(out_path, "w", encoding="UTF-8") as fixture:
+        for i in range(num):
+            i = i + 1
+            with open(in_path.format(i=i), encoding="UTF-8") as file:
+                original = "\n".join(
+                    line.rstrip()
+                    for line in file.read().split(";")[0].split("\n")
+                    if not line.startswith("--")
+                )
+                original = original.replace("`", '"').strip()
+                now = time.time()
+                try:
+                    optimized = optimize(original, schema=schema)
+                except Exception as e:
+                    print("****", i, e, "****")
+                    continue
+
+                fixture.write(
+                    f"""--------------------------------------
+-- TPC-{kind} {i}
+--------------------------------------
+{original};
+{optimized.sql(pretty=True)};
+
+"""
+                )
+                print(i, time.time() - now)
+
+
+rewrite_fixtures(
+    "/home/toby/dev/tpch/{i}.sql",
+    "/home/toby/dev/sqlglot/tests/fixtures/optimizer/tpc-h/tpc-h.sql",
+    TPCH_SCHEMA,
+    22,
+    "H",
+)
+
+rewrite_fixtures(
+    "/home/toby/dev/tpcds/query{i}.sql",
+    "/home/toby/dev/sqlglot/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql",
+    TPCDS_SCHEMA,
+    99,
+    "DS",
+)
diff --git a/tests/test_executor.py b/tests/test_executor.py
index ffe00a7..78d037a 100644
--- a/tests/test_executor.py
+++ b/tests/test_executor.py
@@ -290,11 +290,6 @@ class TestExecutor(unittest.TestCase):
                 [(1,), (2,), (3,)],
             ),
             (
-                "SELECT 1 AS a UNION SELECT 2 AS a UNION SELECT 3 AS a",
-                ["a"],
-                [(1,), (2,), (3,)],
-            ),
-            (
                 "SELECT 1 / 2 AS a",
                 ["a"],
                 [
@@ -320,6 +315,11 @@ class TestExecutor(unittest.TestCase):
                     (None,),
                 ],
             ),
+            (
+                "SELECT a FROM x UNION ALL SELECT a FROM x LIMIT 1",
+                ["a"],
+                [("a",)],
+            ),
         ]:
             with self.subTest(sql):
                 if isinstance(rows, list):
diff --git a/tests/test_optimizer.py b/tests/test_optimizer.py
index 141203d..9b68c78 100644
--- a/tests/test_optimizer.py
+++ b/tests/test_optimizer.py
@@ -109,7 +109,9 @@ class TestOptimizer(unittest.TestCase):
             },
         }
 
-    def check_file(self, file, func, pretty=False, execute=False, set_dialect=False, **kwargs):
+    def check_file(
+        self, file, func, pretty=False, execute=False, set_dialect=False, only=None, **kwargs
+    ):
         with ProcessPoolExecutor() as pool:
             results = {}
 
@@ -117,6 +119,8 @@ class TestOptimizer(unittest.TestCase):
                 load_sql_fixture_pairs(f"optimizer/{file}.sql"), start=1
             ):
                 title = meta.get("title") or f"{i}, {sql}"
+                if only and title != only:
+                    continue
                 dialect = meta.get("dialect")
                 leave_tables_isolated = meta.get("leave_tables_isolated")
 
@@ -137,13 +141,14 @@ class TestOptimizer(unittest.TestCase):
                 )
 
         for future in as_completed(results):
-            optimized = future.result()
             sql, title, expected, dialect, execute = results[future]
 
             with self.subTest(title):
+                optimized = future.result()
+                actual = optimized.sql(pretty=pretty, dialect=dialect)
                 self.assertEqual(
                     expected,
-                    optimized.sql(pretty=pretty, dialect=dialect),
+                    actual,
                 )
 
             if string_to_bool(execute):
@@ -309,7 +314,7 @@ class TestOptimizer(unittest.TestCase):
         self.check_file("pushdown_projections", pushdown_projections, schema=self.schema)
 
     def test_simplify(self):
-        self.check_file("simplify", simplify)
+        self.check_file("simplify", simplify, set_dialect=True)
 
         expression = parse_one("TRUE AND TRUE AND TRUE")
         self.assertEqual(exp.true(), optimizer.simplify.simplify(expression))
diff --git a/tests/test_parser.py b/tests/test_parser.py
index f3e663e..6611b87 100644
--- a/tests/test_parser.py
+++ b/tests/test_parser.py
@@ -17,6 +17,13 @@ class TestParser(unittest.TestCase):
         self.assertIsInstance(parse_one("int", into=exp.DataType), exp.DataType)
         self.assertIsInstance(parse_one("array<int>", into=exp.DataType), exp.DataType)
         self.assertIsInstance(parse_one("foo", into=exp.Table), exp.Table)
+        self.assertIsInstance(
+            parse_one(
+                "WHEN MATCHED THEN UPDATE SET target.salary = COALESCE(source.salary, target.salary)",
+                into=exp.When,
+            ),
+            exp.When,
+        )
 
         with self.assertRaises(ParseError) as ctx:
             parse_one("SELECT * FROM tbl", into=exp.Table)
@@ -94,12 +101,31 @@ class TestParser(unittest.TestCase):
         tables = [t.sql() for t in parse_one("select * from a, b.c, .d").find_all(exp.Table)]
         self.assertEqual(set(tables), {"a", "b.c", "d"})
 
-    def test_union_order(self):
+    def test_union(self):
         self.assertIsInstance(parse_one("SELECT * FROM (SELECT 1) UNION SELECT 2"), exp.Union)
         self.assertIsInstance(
             parse_one("SELECT x FROM y HAVING x > (SELECT 1) UNION SELECT 2"), exp.Union
         )
 
+        # Check that modifiers are attached to the topmost union node and not the rightmost query
+        single_union = "SELECT x FROM t1 UNION ALL SELECT x FROM t2 LIMIT 1"
+        expr = parse_one(single_union)
+        limit = expr.assert_is(exp.Union).args.get("limit")
+        self.assertIsInstance(limit, exp.Limit)
+        self.assertEqual(expr.sql(), single_union)
+
+        two_unions = (
+            "SELECT x FROM t1 UNION ALL SELECT x FROM t2 UNION ALL SELECT x FROM t3 LIMIT 1"
+        )
+        expr = parse_one(two_unions)
+        limit = expr.assert_is(exp.Union).args.get("limit")
+        self.assertIsInstance(limit, exp.Limit)
+        self.assertEqual(expr.sql(), two_unions)
+
+        expr = parse_one(single_union, read="clickhouse")
+        self.assertIsNone(expr.args.get("limit"))
+        self.assertEqual(expr.sql(dialect="clickhouse"), single_union)
+
     def test_select(self):
         self.assertIsNotNone(parse_one("select 1 natural"))
         self.assertIsNotNone(parse_one("select * from (select 1) x order by x.y").args["order"])
diff --git a/tests/test_tokens.py b/tests/test_tokens.py
index b97f54a..970c1ac 100644
--- a/tests/test_tokens.py
+++ b/tests/test_tokens.py
@@ -71,6 +71,20 @@ x"""
         self.assertEqual(tokens[2].line, 2)
         self.assertEqual(tokens[3].line, 3)
 
+    def test_crlf(self):
+        tokens = Tokenizer().tokenize("SELECT a\r\nFROM b")
+        tokens = [(token.token_type, token.text) for token in tokens]
+
+        self.assertEqual(
+            tokens,
+            [
+                (TokenType.SELECT, "SELECT"),
+                (TokenType.VAR, "a"),
+                (TokenType.FROM, "FROM"),
+                (TokenType.VAR, "b"),
+            ],
+        )
+
     def test_command(self):
         tokens = Tokenizer().tokenize("SHOW;")
         self.assertEqual(tokens[0].token_type, TokenType.SHOW)
diff --git a/tests/test_transpile.py b/tests/test_transpile.py
index b732b45..fb8f831 100644
--- a/tests/test_transpile.py
+++ b/tests/test_transpile.py
@@ -89,6 +89,7 @@ class TestTranspile(unittest.TestCase):
         self.validate("SELECT MIN(3)>=MIN(2)", "SELECT MIN(3) >= MIN(2)")
         self.validate("SELECT 1>0", "SELECT 1 > 0")
         self.validate("SELECT 3>=3", "SELECT 3 >= 3")
+        self.validate("SELECT a\r\nFROM b", "SELECT a FROM b")
 
     def test_comments(self):
         self.validate(
diff --git a/tests/tpch.py b/tests/tpch.py
deleted file mode 100644
index ef2b666..0000000
--- a/tests/tpch.py
+++ /dev/null
@@ -1,115 +0,0 @@
-import time
-
-from sqlglot.optimizer import optimize
-
-INPUT = "/home/toby/dev/tpch/{i}.sql"
-OUTPUT = "/home/toby/dev/sqlglot/tests/fixtures/optimizer/tpc-h/tpc-h.sql"
-NUM = 22
-SCHEMA = {
-    "lineitem": {
-        "l_orderkey": "bigint",
-        "l_partkey": "bigint",
-        "l_suppkey": "bigint",
-        "l_linenumber": "bigint",
-        "l_quantity": "double",
-        "l_extendedprice": "double",
-        "l_discount": "double",
-        "l_tax": "double",
-        "l_returnflag": "string",
-        "l_linestatus": "string",
-        "l_shipdate": "string",
-        "l_commitdate": "string",
-        "l_receiptdate": "string",
-        "l_shipinstruct": "string",
-        "l_shipmode": "string",
-        "l_comment": "string",
-    },
-    "orders": {
-        "o_orderkey": "bigint",
-        "o_custkey": "bigint",
-        "o_orderstatus": "string",
-        "o_totalprice": "double",
-        "o_orderdate": "string",
-        "o_orderpriority": "string",
-        "o_clerk": "string",
-        "o_shippriority": "int",
-        "o_comment": "string",
-    },
-    "customer": {
-        "c_custkey": "bigint",
-        "c_name": "string",
-        "c_address": "string",
-        "c_nationkey": "bigint",
-        "c_phone": "string",
-        "c_acctbal": "double",
-        "c_mktsegment": "string",
-        "c_comment": "string",
-    },
-    "part": {
-        "p_partkey": "bigint",
-        "p_name": "string",
-        "p_mfgr": "string",
-        "p_brand": "string",
-        "p_type": "string",
-        "p_size": "int",
-        "p_container": "string",
-        "p_retailprice": "double",
-        "p_comment": "string",
-    },
-    "supplier": {
-        "s_suppkey": "bigint",
-        "s_name": "string",
-        "s_address": "string",
-        "s_nationkey": "bigint",
-        "s_phone": "string",
-        "s_acctbal": "double",
-        "s_comment": "string",
-    },
-    "partsupp": {
-        "ps_partkey": "bigint",
-        "ps_suppkey": "bigint",
-        "ps_availqty": "int",
-        "ps_supplycost": "double",
-        "ps_comment": "string",
-    },
-    "nation": {
-        "n_nationkey": "bigint",
-        "n_name": "string",
-        "n_regionkey": "bigint",
-        "n_comment": "string",
-    },
-    "region": {
-        "r_regionkey": "bigint",
-        "r_name": "string",
-        "r_comment": "string",
-    },
-}
-KIND = "H"
-
-with open(OUTPUT, "w", encoding="UTF-8") as fixture:
-    for i in range(NUM):
-        i = i + 1
-        with open(INPUT.format(i=i), encoding="UTF-8") as file:
-            original = "\n".join(
-                line.rstrip()
-                for line in file.read().split(";")[0].split("\n")
-                if not line.startswith("--")
-            )
-            original = original.replace("`", '"').strip()
-            now = time.time()
-            try:
-                optimized = optimize(original, schema=SCHEMA)
-            except Exception as e:
-                print("****", i, e, "****")
-                continue
-
-            fixture.write(
-                f"""--------------------------------------
--- TPC-{KIND} {i}
---------------------------------------
-{original};
-{optimized.sql(pretty=True)};
-
-"""
-            )
-            print(i, time.time() - now)