Adding upstream version 16.2.1.upstream/16.2.1

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

27 files changed, 1629 insertions, 1026 deletions

diff --git a/tests/dataframe/unit/test_functions.py b/tests/dataframe/unit/test_functions.py
index befa68b..556001c 100644
--- a/tests/dataframe/unit/test_functions.py
+++ b/tests/dataframe/unit/test_functions.py
@@ -1278,7 +1278,7 @@ class TestFunctions(unittest.TestCase):
         col = SF.concat(SF.col("cola"), SF.col("colb"))
         self.assertEqual("CONCAT(cola, colb)", col.sql())
         col_single = SF.concat("cola")
-        self.assertEqual("cola", col_single.sql())
+        self.assertEqual("CONCAT(cola)", col_single.sql())
 
     def test_array_position(self):
         col_str = SF.array_position("cola", SF.col("colb"))
diff --git a/tests/dialects/test_bigquery.py b/tests/dialects/test_bigquery.py
index 05ded11..1c8aa51 100644
--- a/tests/dialects/test_bigquery.py
+++ b/tests/dialects/test_bigquery.py
@@ -6,6 +6,9 @@ class TestBigQuery(Validator):
     dialect = "bigquery"
 
     def test_bigquery(self):
+        self.validate_identity("DATE(2016, 12, 25)")
+        self.validate_identity("DATE(CAST('2016-12-25 23:59:59' AS DATETIME))")
+        self.validate_identity("SELECT foo IN UNNEST(bar) AS bla")
         self.validate_identity("SELECT * FROM x-0.a")
         self.validate_identity("SELECT * FROM pivot CROSS JOIN foo")
         self.validate_identity("SAFE_CAST(x AS STRING)")
@@ -28,6 +31,9 @@ class TestBigQuery(Validator):
         self.validate_identity("""CREATE TABLE x (a STRUCT<values ARRAY<INT64>>)""")
         self.validate_identity("""CREATE TABLE x (a STRUCT<b STRING OPTIONS (description='b')>)""")
         self.validate_identity(
+            "DATE(CAST('2016-12-25 05:30:00+07' AS DATETIME), 'America/Los_Angeles')"
+        )
+        self.validate_identity(
             """CREATE TABLE x (a STRING OPTIONS (description='x')) OPTIONS (table_expiration_days=1)"""
         )
         self.validate_identity(
@@ -37,6 +43,19 @@ class TestBigQuery(Validator):
             "CREATE TABLE IF NOT EXISTS foo AS SELECT * FROM bla EXCEPT DISTINCT (SELECT * FROM bar) LIMIT 0"
         )
 
+        self.validate_all("SELECT SPLIT(foo)", write={"bigquery": "SELECT SPLIT(foo, ',')"})
+        self.validate_all(
+            "cast(x as date format 'MM/DD/YYYY')",
+            write={
+                "bigquery": "PARSE_DATE('%m/%d/%Y', x)",
+            },
+        )
+        self.validate_all(
+            "cast(x as time format 'YYYY.MM.DD HH:MI:SSTZH')",
+            write={
+                "bigquery": "PARSE_TIMESTAMP('%Y.%m.%d %I:%M:%S%z', x)",
+            },
+        )
         self.validate_all("SELECT 1 AS hash", write={"bigquery": "SELECT 1 AS `hash`"})
         self.validate_all('x <> ""', write={"bigquery": "x <> ''"})
         self.validate_all('x <> """"""', write={"bigquery": "x <> ''"})
@@ -55,11 +74,12 @@ class TestBigQuery(Validator):
             "SELECT * FROM `my-project.my-dataset.my-table`",
             write={"bigquery": "SELECT * FROM `my-project`.`my-dataset`.`my-table`"},
         )
+        self.validate_all("CAST(x AS DATETIME)", read={"": "x::timestamp"})
+        self.validate_identity("CAST(x AS TIMESTAMP)")
         self.validate_all("LEAST(x, y)", read={"sqlite": "MIN(x, y)"})
         self.validate_all("CAST(x AS CHAR)", write={"bigquery": "CAST(x AS STRING)"})
         self.validate_all("CAST(x AS NCHAR)", write={"bigquery": "CAST(x AS STRING)"})
         self.validate_all("CAST(x AS NVARCHAR)", write={"bigquery": "CAST(x AS STRING)"})
-        self.validate_all("CAST(x AS TIMESTAMP)", write={"bigquery": "CAST(x AS DATETIME)"})
         self.validate_all("CAST(x AS TIMESTAMPTZ)", write={"bigquery": "CAST(x AS TIMESTAMP)"})
         self.validate_all("CAST(x AS RECORD)", write={"bigquery": "CAST(x AS STRUCT)"})
         self.validate_all(
@@ -418,9 +438,11 @@ class TestBigQuery(Validator):
         self.validate_all(
             "SELECT REGEXP_EXTRACT(abc, 'pattern(group)') FROM table",
             write={
+                "bigquery": "SELECT REGEXP_EXTRACT(abc, 'pattern(group)') FROM table",
                 "duckdb": "SELECT REGEXP_EXTRACT(abc, 'pattern(group)', 1) FROM table",
             },
         )
+        self.validate_identity("REGEXP_EXTRACT(`foo`, 'bar: (.+?)', 1, 1)")
         self.validate_identity("BEGIN A B C D E F")
         self.validate_identity("BEGIN TRANSACTION")
         self.validate_identity("COMMIT TRANSACTION")
diff --git a/tests/dialects/test_clickhouse.py b/tests/dialects/test_clickhouse.py
index f5372d9..7584c67 100644
--- a/tests/dialects/test_clickhouse.py
+++ b/tests/dialects/test_clickhouse.py
@@ -45,8 +45,22 @@ class TestClickhouse(Validator):
         self.validate_identity(
             "CREATE MATERIALIZED VIEW test_view ON CLUSTER cl1 (id UInt8) ENGINE=AggregatingMergeTree() ORDER BY tuple() AS SELECT * FROM test_data"
         )
+        self.validate_identity(
+            "CREATE MATERIALIZED VIEW test_view ON CLUSTER cl1 (id UInt8) TO table1 AS SELECT * FROM test_data"
+        )
+        self.validate_identity(
+            "CREATE MATERIALIZED VIEW test_view (id UInt8) TO db.table1 AS SELECT * FROM test_data"
+        )
 
         self.validate_all(
+            "CONCAT(CASE WHEN COALESCE(a, '') IS NULL THEN COALESCE(a, '') ELSE CAST(COALESCE(a, '') AS TEXT) END, CASE WHEN COALESCE(b, '') IS NULL THEN COALESCE(b, '') ELSE CAST(COALESCE(b, '') AS TEXT) END)",
+            read={"postgres": "CONCAT(a, b)"},
+        )
+        self.validate_all(
+            "CONCAT(CASE WHEN a IS NULL THEN a ELSE CAST(a AS TEXT) END, CASE WHEN b IS NULL THEN b ELSE CAST(b AS TEXT) END)",
+            read={"mysql": "CONCAT(a, b)"},
+        )
+        self.validate_all(
             r"'Enum8(\'Sunday\' = 0)'", write={"clickhouse": "'Enum8(''Sunday'' = 0)'"}
         )
         self.validate_all(
diff --git a/tests/dialects/test_dialect.py b/tests/dialects/test_dialect.py
index 7e20812..8ffdf07 100644
--- a/tests/dialects/test_dialect.py
+++ b/tests/dialects/test_dialect.py
@@ -1085,6 +1085,14 @@ class TestDialect(Validator):
 
         self.validate_all("LIKE(x, 'z')", write={"": "'z' LIKE x"})
         self.validate_all(
+            "CONCAT(a, b, c)",
+            write={
+                "": "CONCAT(a, b, c)",
+                "redshift": "a || b || c",
+                "sqlite": "a || b || c",
+            },
+        )
+        self.validate_all(
             "x ILIKE '%y'",
             read={
                 "clickhouse": "x ILIKE '%y'",
@@ -1177,11 +1185,22 @@ class TestDialect(Validator):
         self.validate_all(
             "CONCAT(a)",
             write={
-                "mysql": "a",
+                "clickhouse": "a",
+                "presto": "a",
+                "trino": "a",
                 "tsql": "a",
             },
         )
         self.validate_all(
+            "COALESCE(a, '')",
+            read={
+                "drill": "CONCAT(a)",
+                "duckdb": "CONCAT(a)",
+                "postgres": "CONCAT(a)",
+                "tsql": "CONCAT(a)",
+            },
+        )
+        self.validate_all(
             "IF(x > 1, 1, 0)",
             write={
                 "drill": "`IF`(x > 1, 1, 0)",
@@ -1276,7 +1295,7 @@ class TestDialect(Validator):
     def test_limit(self):
         self.validate_all(
             "SELECT * FROM data LIMIT 10, 20",
-            write={"sqlite": "SELECT * FROM data LIMIT 10 OFFSET 20"},
+            write={"sqlite": "SELECT * FROM data LIMIT 20 OFFSET 10"},
         )
         self.validate_all(
             "SELECT x FROM y LIMIT 10",
diff --git a/tests/dialects/test_duckdb.py b/tests/dialects/test_duckdb.py
index ee15d04..f0caafc 100644
--- a/tests/dialects/test_duckdb.py
+++ b/tests/dialects/test_duckdb.py
@@ -10,6 +10,20 @@ class TestDuckDB(Validator):
         self.validate_identity("SELECT CURRENT_TIMESTAMP")
 
         self.validate_all(
+            "SELECT CAST('2020-01-01' AS DATE) + INTERVAL (-1) DAY",
+            read={"mysql": "SELECT DATE '2020-01-01' + INTERVAL -1 DAY"},
+        )
+        self.validate_all(
+            "SELECT INTERVAL '1 quarter'",
+            write={"duckdb": "SELECT (90 * INTERVAL '1' day)"},
+        )
+        self.validate_all(
+            "SELECT ((DATE_TRUNC('DAY', CAST(CAST(DATE_TRUNC('DAY', CURRENT_TIMESTAMP) AS DATE) AS TIMESTAMP) + INTERVAL (0 - MOD((DAYOFWEEK(CAST(CAST(DATE_TRUNC('DAY', CURRENT_TIMESTAMP) AS DATE) AS TIMESTAMP)) % 7) - 1 + 7, 7)) day) + (7 * INTERVAL (-5) day))) AS t1",
+            read={
+                "presto": "SELECT ((DATE_ADD('week', -5, DATE_TRUNC('DAY', DATE_ADD('day', (0 - MOD((DAY_OF_WEEK(CAST(CAST(DATE_TRUNC('DAY', NOW()) AS DATE) AS TIMESTAMP)) % 7) - 1 + 7, 7)), CAST(CAST(DATE_TRUNC('DAY', NOW()) AS DATE) AS TIMESTAMP)))))) AS t1",
+            },
+        )
+        self.validate_all(
             "EPOCH(x)",
             read={
                 "presto": "TO_UNIXTIME(x)",
@@ -51,7 +65,7 @@ class TestDuckDB(Validator):
         self.validate_all(
             "STRPTIME(x, '%y-%-m')",
             write={
-                "bigquery": "PARSE_TIMESTAMP('%y-%m', x)",
+                "bigquery": "PARSE_TIMESTAMP('%y-%-m', x)",
                 "duckdb": "STRPTIME(x, '%y-%-m')",
                 "presto": "DATE_PARSE(x, '%y-%c')",
                 "hive": "CAST(FROM_UNIXTIME(UNIX_TIMESTAMP(x, 'yy-M')) AS TIMESTAMP)",
@@ -70,7 +84,7 @@ class TestDuckDB(Validator):
         self.validate_all(
             "STRPTIME(x, '%-m/%-d/%y %-I:%M %p')",
             write={
-                "bigquery": "PARSE_TIMESTAMP('%m/%d/%y %I:%M %p', x)",
+                "bigquery": "PARSE_TIMESTAMP('%-m/%-d/%y %-I:%M %p', x)",
                 "duckdb": "STRPTIME(x, '%-m/%-d/%y %-I:%M %p')",
                 "presto": "DATE_PARSE(x, '%c/%e/%y %l:%i %p')",
                 "hive": "CAST(FROM_UNIXTIME(UNIX_TIMESTAMP(x, 'M/d/yy h:mm a')) AS TIMESTAMP)",
diff --git a/tests/dialects/test_mysql.py b/tests/dialects/test_mysql.py
index 4fb6fa5..0b9c8b7 100644
--- a/tests/dialects/test_mysql.py
+++ b/tests/dialects/test_mysql.py
@@ -45,6 +45,8 @@ class TestMySQL(Validator):
         )
 
     def test_identity(self):
+        self.validate_identity("CAST(x AS ENUM('a', 'b'))")
+        self.validate_identity("CAST(x AS SET('a', 'b'))")
         self.validate_identity("SELECT CURRENT_TIMESTAMP(6)")
         self.validate_identity("x ->> '$.name'")
         self.validate_identity("SELECT CAST(`a`.`b` AS INT) FROM foo")
diff --git a/tests/dialects/test_oracle.py b/tests/dialects/test_oracle.py
index 12ff699..2c67805 100644
--- a/tests/dialects/test_oracle.py
+++ b/tests/dialects/test_oracle.py
@@ -26,8 +26,8 @@ class TestOracle(Validator):
         self.validate_all(
             "NVL(NULL, 1)",
             write={
+                "": "COALESCE(NULL, 1)",
                 "oracle": "NVL(NULL, 1)",
-                "": "IFNULL(NULL, 1)",
             },
         )
         self.validate_all(
diff --git a/tests/dialects/test_postgres.py b/tests/dialects/test_postgres.py
index 972a8c8..4e57b36 100644
--- a/tests/dialects/test_postgres.py
+++ b/tests/dialects/test_postgres.py
@@ -1,3 +1,5 @@
+from unittest import mock
+
 from sqlglot import ParseError, exp, parse_one, transpile
 from tests.dialects.test_dialect import Validator
 
@@ -85,6 +87,39 @@ class TestPostgres(Validator):
                 read="postgres",
             )
 
+    def test_unnest(self):
+        self.validate_identity(
+            "SELECT * FROM UNNEST(ARRAY[1, 2], ARRAY['foo', 'bar', 'baz']) AS x(a, b)"
+        )
+
+        self.validate_all(
+            "SELECT UNNEST(c) FROM t",
+            write={
+                "hive": "SELECT EXPLODE(c) FROM t",
+                "postgres": "SELECT UNNEST(c) FROM t",
+                "presto": "SELECT col FROM t CROSS JOIN UNNEST(c) AS _u(col)",
+            },
+        )
+        self.validate_all(
+            "SELECT UNNEST(ARRAY[1])",
+            write={
+                "hive": "SELECT EXPLODE(ARRAY(1))",
+                "postgres": "SELECT UNNEST(ARRAY[1])",
+                "presto": "SELECT col FROM UNNEST(ARRAY[1]) AS _u(col)",
+            },
+        )
+
+    @mock.patch("sqlglot.helper.logger")
+    def test_array_offset(self, mock_logger):
+        self.validate_all(
+            "SELECT col[1]",
+            write={
+                "hive": "SELECT col[0]",
+                "postgres": "SELECT col[1]",
+                "presto": "SELECT col[1]",
+            },
+        )
+
     def test_postgres(self):
         self.validate_identity("CAST(x AS INT4RANGE)")
         self.validate_identity("CAST(x AS INT4MULTIRANGE)")
@@ -540,3 +575,24 @@ class TestPostgres(Validator):
             "SELECT a, LOGICAL_OR(b) FROM table GROUP BY a",
             write={"postgres": "SELECT a, BOOL_OR(b) FROM table GROUP BY a"},
         )
+
+    def test_string_concat(self):
+        self.validate_all(
+            "CONCAT(a, b)",
+            write={
+                "": "CONCAT(COALESCE(a, ''), COALESCE(b, ''))",
+                "duckdb": "CONCAT(COALESCE(a, ''), COALESCE(b, ''))",
+                "postgres": "CONCAT(COALESCE(a, ''), COALESCE(b, ''))",
+                "presto": "CONCAT(CAST(COALESCE(a, '') AS VARCHAR), CAST(COALESCE(b, '') AS VARCHAR))",
+            },
+        )
+        self.validate_all(
+            "a || b",
+            write={
+                "": "a || b",
+                "clickhouse": "CONCAT(CAST(a AS TEXT), CAST(b AS TEXT))",
+                "duckdb": "a || b",
+                "postgres": "a || b",
+                "presto": "CONCAT(CAST(a AS VARCHAR), CAST(b AS VARCHAR))",
+            },
+        )
diff --git a/tests/dialects/test_presto.py b/tests/dialects/test_presto.py
index e3d09ef..4f37be5 100644
--- a/tests/dialects/test_presto.py
+++ b/tests/dialects/test_presto.py
@@ -440,6 +440,8 @@ class TestPresto(Validator):
         )
 
     def test_presto(self):
+        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")
diff --git a/tests/dialects/test_snowflake.py b/tests/dialects/test_snowflake.py
index 941f2aa..426e188 100644
--- a/tests/dialects/test_snowflake.py
+++ b/tests/dialects/test_snowflake.py
@@ -30,6 +30,10 @@ class TestSnowflake(Validator):
         self.validate_identity("SELECT CONVERT_TIMEZONE('UTC', 'America/Los_Angeles', col)")
 
         self.validate_all("CAST(x AS CHAR VARYING)", write={"snowflake": "CAST(x AS VARCHAR)"})
+        self.validate_all(
+            "SELECT * FROM (VALUES (0) foo(bar))",
+            write={"snowflake": "SELECT * FROM (VALUES (0)) AS foo(bar)"},
+        )
         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(
@@ -274,8 +278,8 @@ class TestSnowflake(Validator):
             "SELECT TO_TIMESTAMP('2013-04-05 01:02:03')",
             write={
                 "bigquery": "SELECT PARSE_TIMESTAMP('%Y-%m-%d %H:%M:%S', '2013-04-05 01:02:03')",
-                "snowflake": "SELECT TO_TIMESTAMP('2013-04-05 01:02:03', 'yyyy-mm-dd hh24:mi:ss')",
-                "spark": "SELECT TO_TIMESTAMP('2013-04-05 01:02:03', 'yyyy-MM-d HH:mm:ss')",
+                "snowflake": "SELECT TO_TIMESTAMP('2013-04-05 01:02:03', 'yyyy-mm-DD hh24:mi:ss')",
+                "spark": "SELECT TO_TIMESTAMP('2013-04-05 01:02:03', 'yyyy-MM-dd HH:mm:ss')",
             },
         )
         self.validate_all(
diff --git a/tests/dialects/test_teradata.py b/tests/dialects/test_teradata.py
index 9f789d0..6906e47 100644
--- a/tests/dialects/test_teradata.py
+++ b/tests/dialects/test_teradata.py
@@ -138,11 +138,15 @@ class TestTeradata(Validator):
     def test_cast(self):
         self.validate_all(
             "CAST('1992-01' AS DATE FORMAT 'YYYY-DD')",
+            read={
+                "bigquery": "CAST('1992-01' AS DATE FORMAT 'YYYY-DD')",
+            },
             write={
                 "teradata": "CAST('1992-01' AS DATE FORMAT 'YYYY-DD')",
-                "databricks": "DATE_FORMAT('1992-01', 'YYYY-DD')",
-                "mysql": "DATE_FORMAT('1992-01', 'YYYY-DD')",
-                "spark": "DATE_FORMAT('1992-01', 'YYYY-DD')",
-                "": "TIME_TO_STR('1992-01', 'YYYY-DD')",
+                "bigquery": "PARSE_DATE('%Y-%d', '1992-01')",
+                "databricks": "TO_DATE('1992-01', 'yyyy-dd')",
+                "mysql": "STR_TO_DATE('1992-01', '%Y-%d')",
+                "spark": "TO_DATE('1992-01', 'yyyy-dd')",
+                "": "STR_TO_DATE('1992-01', '%Y-%d')",
             },
         )
diff --git a/tests/fixtures/identity.sql b/tests/fixtures/identity.sql
index 9fdddf1..e0ea9cb 100644
--- a/tests/fixtures/identity.sql
+++ b/tests/fixtures/identity.sql
@@ -1,7 +1,11 @@
 SUM(1)
 SUM(CASE WHEN x > 1 THEN 1 ELSE 0 END) / y
 1
+(1)
+1.
+(1.)
 1.0
+(1.0)
 1E2
 1E+2
 1E-2
@@ -69,6 +73,8 @@ a.B()
 a['x'].C()
 int.x
 map.x
+SELECT update
+SELECT x.update
 SELECT call.x
 a.b.INT(1.234)
 INT(x / 100)
@@ -155,6 +161,7 @@ DATE(x) = DATE(y)
 TIMESTAMP(DATE(x))
 TIMESTAMP_TRUNC(COALESCE(time_field, CURRENT_TIMESTAMP()), DAY)
 COUNT(DISTINCT CASE WHEN DATE_TRUNC(DATE(time_field), isoweek) = DATE_TRUNC(DATE(time_field2), isoweek) THEN report_id ELSE NULL END)
+COUNT(a, b)
 x[y - 1]
 CASE WHEN SUM(x) > 3 THEN 1 END OVER (PARTITION BY x)
 SUM(ROW() OVER (PARTITION BY x))
@@ -224,6 +231,7 @@ SELECT SUM(x IGNORE NULLS) AS x
 SELECT COUNT(x RESPECT NULLS)
 SELECT TRUNCATE(a, b)
 SELECT ARRAY_AGG(DISTINCT x IGNORE NULLS ORDER BY a, b DESC LIMIT 10) AS x
+SELECT ARRAY_AGG(DISTINCT x IGNORE NULLS ORDER BY a, b DESC LIMIT 1, 10) AS x
 SELECT ARRAY_AGG(STRUCT(x, x AS y) ORDER BY z DESC) AS x
 SELECT LAST_VALUE(x IGNORE NULLS) OVER y AS x
 SELECT LAG(x) OVER (ORDER BY y) AS x
@@ -601,6 +609,7 @@ CREATE FUNCTION a.b.c()
 CREATE INDEX abc ON t (a)
 CREATE INDEX abc ON t (a, b, b)
 CREATE INDEX abc ON t (a NULLS LAST)
+CREATE INDEX pointloc ON points USING GIST(BOX(location, location))
 CREATE UNIQUE INDEX abc ON t (a, b, b)
 CREATE UNIQUE INDEX IF NOT EXISTS my_idx ON tbl (a, b)
 CREATE SCHEMA x
@@ -671,6 +680,7 @@ INSERT INTO x VALUES (1, 'a', 2.0), (1, 'a', 3.0), (X(), y[1], z.x)
 INSERT INTO y (a, b, c) SELECT a, b, c FROM x
 INSERT INTO y (SELECT 1) UNION (SELECT 2)
 INSERT INTO result_table (WITH test AS (SELECT * FROM source_table) SELECT * FROM test)
+INSERT INTO "tests_user" ("username", "first_name", "last_name") VALUES ('fiara', 'Fiara', 'Ironhide') RETURNING "tests_user"."id"
 INSERT OVERWRITE TABLE x IF EXISTS SELECT * FROM y
 INSERT OVERWRITE TABLE a.b IF EXISTS SELECT * FROM y
 INSERT OVERWRITE DIRECTORY 'x' SELECT 1
@@ -805,6 +815,7 @@ PRAGMA schema.synchronous = 2
 PRAGMA schema.synchronous = FULL
 PRAGMA schema.memory_limit = '1GB'
 JSON_OBJECT()
+JSON_OBJECT(*)
 JSON_OBJECT('key1': 1, 'key2': TRUE)
 JSON_OBJECT('id': '5', 'fld1': 'bla', 'fld2': 'bar')
 JSON_OBJECT('x': NULL, 'y': 1 NULL ON NULL)
@@ -820,3 +831,7 @@ SELECT PERCENTILE_CONT(x, 0.5 RESPECT NULLS) OVER ()
 SELECT PERCENTILE_CONT(x, 0.5 IGNORE NULLS) OVER ()
 WITH my_cte AS (SELECT 'a' AS desc) SELECT desc AS description FROM my_cte
 WITH my_cte AS (SELECT 'a' AS asc) SELECT asc AS description FROM my_cte
+SELECT * FROM case
+SELECT * FROM schema.case
+SELECT * FROM current_date
+SELECT * FROM schema.current_date
diff --git a/tests/fixtures/optimizer/isolate_table_selects.sql b/tests/fixtures/optimizer/isolate_table_selects.sql
index 43540e8..36f2d8e 100644
--- a/tests/fixtures/optimizer/isolate_table_selects.sql
+++ b/tests/fixtures/optimizer/isolate_table_selects.sql
@@ -1,5 +1,5 @@
 SELECT * FROM x AS x, y AS y2;
-SELECT * FROM (SELECT * FROM x AS x) AS x, (SELECT * FROM y AS y) AS y2;
+SELECT * FROM (SELECT * FROM x AS x) AS x, (SELECT * FROM y AS y2) AS y2;
 
 SELECT * FROM x AS x WHERE x = 1;
 SELECT * FROM x AS x WHERE x = 1;
@@ -17,7 +17,7 @@ WITH y AS (SELECT *) SELECT * FROM x AS x;
 WITH y AS (SELECT *) SELECT * FROM x AS x;
 
 WITH y AS (SELECT * FROM y AS y2 CROSS JOIN x AS z2) SELECT * FROM x AS x CROSS JOIN y as y;
-WITH y AS (SELECT * FROM (SELECT * FROM y AS y) AS y2 CROSS JOIN (SELECT * FROM x AS x) AS z2) SELECT * FROM (SELECT * FROM x AS x) AS x CROSS JOIN y AS y;
+WITH y AS (SELECT * FROM (SELECT * FROM y AS y2) AS y2 CROSS JOIN (SELECT * FROM x AS z2) AS z2) SELECT * FROM (SELECT * FROM x AS x) AS x CROSS JOIN y AS y;
 
 SELECT * FROM x AS x CROSS JOIN xx AS y;
 SELECT * FROM (SELECT * FROM x AS x) AS x CROSS JOIN xx AS y;
diff --git a/tests/fixtures/optimizer/optimizer.sql b/tests/fixtures/optimizer/optimizer.sql
index e0567d7..0cb1a58 100644
--- a/tests/fixtures/optimizer/optimizer.sql
+++ b/tests/fixtures/optimizer/optimizer.sql
@@ -101,10 +101,10 @@ SELECT
   "x"."a" AS "a",
   SUM("y"."b") AS "sum_b"
 FROM "x" AS "x"
-JOIN "y" AS "y"
-  ON "x"."b" = "y"."b"
 LEFT JOIN "_u_0" AS "_u_0"
   ON "x"."b" = "_u_0"."_u_1"
+JOIN "y" AS "y"
+  ON "x"."b" = "y"."b"
 WHERE
   "_u_0"."_col_0" >= 0 AND "x"."a" > 1
 GROUP BY
@@ -210,10 +210,10 @@ SELECT
   "n"."b" AS "b",
   "o"."b" AS "b"
 FROM "n"
-FULL JOIN "o"
-  ON "n"."a" = "o"."a"
 JOIN "n" AS "n2"
   ON "n"."a" = "n2"."a"
+FULL JOIN "o"
+  ON "n"."a" = "o"."a"
 WHERE
   "o"."b" > 0;
 
@@ -619,3 +619,30 @@ WITH "foO" AS (
 SELECT
   "foO"."x" AS "x"
 FROM "foO" AS "foO";
+
+# title: lateral subquery
+# execute: false
+# dialect: postgres
+SELECT u.user_id, l.log_date
+FROM   users u
+CROSS JOIN LATERAL (
+    SELECT l.log_date
+    FROM   logs l
+    WHERE  l.user_id = u.user_id AND l.log_date <= 100
+    ORDER  BY l.log_date DESC NULLS LAST
+    LIMIT  1
+) l;
+SELECT
+  "u"."user_id" AS "user_id",
+  "l"."log_date" AS "log_date"
+FROM "users" AS "u"
+CROSS JOIN LATERAL (
+  SELECT
+    "l"."log_date"
+  FROM "logs" AS "l"
+  WHERE
+    "l"."log_date" <= 100 AND "l"."user_id" = "u"."user_id"
+  ORDER BY
+    "l"."log_date" DESC NULLS LAST
+  LIMIT 1
+) AS "l";
diff --git a/tests/fixtures/optimizer/pushdown_predicates.sql b/tests/fixtures/optimizer/pushdown_predicates.sql
index 83a353d..79ce353 100644
--- a/tests/fixtures/optimizer/pushdown_predicates.sql
+++ b/tests/fixtures/optimizer/pushdown_predicates.sql
@@ -25,8 +25,8 @@ SELECT x.a AS a FROM (SELECT x.a FROM x AS x WHERE x.a = 1 AND x.b = 1) AS x JOI
 SELECT x.a FROM x AS x JOIN (SELECT y.a FROM y AS y) AS y ON y.a = 1 AND x.a = y.a;
 SELECT x.a FROM x AS x JOIN (SELECT y.a FROM y AS y WHERE y.a = 1) AS y ON x.a = y.a AND TRUE;
 
-SELECT x.a AS a FROM x AS x JOIN (SELECT * FROM y AS y) AS y ON y.a = 1 WHERE x.a = 1 AND x.b = 1 AND y.a = x;
-SELECT x.a AS a FROM x AS x JOIN (SELECT * FROM y AS y WHERE y.a = 1) AS y ON y.a = x AND TRUE WHERE x.a = 1 AND x.b = 1 AND TRUE;
+SELECT x.a AS a FROM x AS x JOIN (SELECT * FROM y AS y) AS y ON y.a = 1 WHERE x.a = 1 AND x.b = 1 AND y.a = x.a;
+SELECT x.a AS a FROM x AS x JOIN (SELECT * FROM y AS y WHERE y.a = 1) AS y ON y.a = x.a AND TRUE WHERE x.a = 1 AND x.b = 1 AND TRUE;
 
 SELECT x.a AS a FROM x AS x CROSS JOIN (SELECT * FROM y AS y) AS y WHERE x.a = 1 AND x.b = 1 AND y.a = x.a AND y.a = 1;
 SELECT x.a AS a FROM x AS x JOIN (SELECT * FROM y AS y WHERE y.a = 1) AS y ON y.a = x.a AND TRUE WHERE x.a = 1 AND x.b = 1 AND TRUE AND TRUE;
diff --git a/tests/fixtures/optimizer/qualify_columns.sql b/tests/fixtures/optimizer/qualify_columns.sql
index 7be2c7f..81c0b5e 100644
--- a/tests/fixtures/optimizer/qualify_columns.sql
+++ b/tests/fixtures/optimizer/qualify_columns.sql
@@ -296,6 +296,10 @@ SELECT x.b AS b FROM x AS x;
 SELECT x.b FROM x JOIN y USING (b);
 SELECT x.b AS b FROM x AS x JOIN y AS y ON x.b = y.b;
 
+# execute: false
+WITH cte AS (SELECT a.b.c.d.f.g FROM tbl1) SELECT g FROM (SELECT g FROM tbl2) tbl2 JOIN cte USING(g);
+WITH cte AS (SELECT tbl1.a.b.c.d.f.g AS g FROM tbl1 AS tbl1) SELECT COALESCE(tbl2.g, cte.g) AS g FROM (SELECT tbl2.g AS g FROM tbl2 AS tbl2) AS tbl2 JOIN cte ON tbl2.g = cte.g;
+
 SELECT x.b FROM x JOIN y USING (b) JOIN z USING (b);
 SELECT x.b AS b FROM x AS x JOIN y AS y ON x.b = y.b JOIN z AS z ON x.b = z.b;
 
diff --git a/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql b/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql
index a6ee325..7ef7a6d 100644
--- a/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql
+++ b/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql
@@ -46,12 +46,12 @@ WITH "customer_total_return" AS (
 SELECT
   "customer"."c_customer_id" AS "c_customer_id"
 FROM "customer_total_return" AS "ctr1"
-JOIN "store" AS "store"
-  ON "store"."s_state" = 'TN' AND "store"."s_store_sk" = "ctr1"."ctr_store_sk"
-JOIN "customer" AS "customer"
-  ON "ctr1"."ctr_customer_sk" = "customer"."c_customer_sk"
 LEFT JOIN "_u_0" AS "_u_0"
   ON "ctr1"."ctr_store_sk" = "_u_0"."_u_1"
+JOIN "customer" AS "customer"
+  ON "ctr1"."ctr_customer_sk" = "customer"."c_customer_sk"
+JOIN "store" AS "store"
+  ON "store"."s_state" = 'TN' AND "store"."s_store_sk" = "ctr1"."ctr_store_sk"
 WHERE
   "ctr1"."ctr_total_return" > "_u_0"."_col_0"
 ORDER BY
@@ -238,23 +238,23 @@ ORDER  BY dt.d_year,
           brand_id
 LIMIT 100;
 SELECT
-  "date_dim"."d_year" AS "d_year",
+  "dt"."d_year" AS "d_year",
   "item"."i_brand_id" AS "brand_id",
   "item"."i_brand" AS "brand",
   SUM("store_sales"."ss_ext_discount_amt") AS "sum_agg"
-FROM "date_dim" AS "date_dim"
+FROM "date_dim" AS "dt"
 JOIN "store_sales" AS "store_sales"
-  ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk"
+  ON "dt"."d_date_sk" = "store_sales"."ss_sold_date_sk"
 JOIN "item" AS "item"
   ON "item"."i_manufact_id" = 427 AND "store_sales"."ss_item_sk" = "item"."i_item_sk"
 WHERE
-  "date_dim"."d_moy" = 11
+  "dt"."d_moy" = 11
 GROUP BY
-  "date_dim"."d_year",
+  "dt"."d_year",
   "item"."i_brand",
   "item"."i_brand_id"
 ORDER BY
-  "date_dim"."d_year",
+  "dt"."d_year",
   "sum_agg" DESC,
   "brand_id"
 LIMIT 100;
@@ -567,14 +567,14 @@ SELECT
   "t_s_secyear"."customer_last_name" AS "customer_last_name",
   "t_s_secyear"."customer_preferred_cust_flag" AS "customer_preferred_cust_flag"
 FROM "year_total" AS "t_s_firstyear"
-JOIN "year_total" AS "t_s_secyear"
-  ON "t_s_secyear"."customer_id" = "t_s_firstyear"."customer_id"
-  AND "t_s_secyear"."dyear" = 2002
-  AND "t_s_secyear"."sale_type" = 's'
 JOIN "year_total" AS "t_c_secyear"
   ON "t_c_secyear"."dyear" = 2002
   AND "t_c_secyear"."sale_type" = 'c'
   AND "t_s_firstyear"."customer_id" = "t_c_secyear"."customer_id"
+JOIN "year_total" AS "t_s_secyear"
+  ON "t_s_secyear"."customer_id" = "t_s_firstyear"."customer_id"
+  AND "t_s_secyear"."dyear" = 2002
+  AND "t_s_secyear"."sale_type" = 's'
 JOIN "year_total" AS "t_w_firstyear"
   ON "t_s_firstyear"."customer_id" = "t_w_firstyear"."customer_id"
   AND "t_w_firstyear"."dyear" = 2001
@@ -813,10 +813,10 @@ WITH "salesreturns" AS (
     SUM("salesreturns"."return_amt") AS "returns1",
     SUM("salesreturns"."net_loss") AS "profit_loss"
   FROM "salesreturns_2" AS "salesreturns"
-  JOIN "date_dim_2" AS "date_dim"
-    ON "salesreturns"."date_sk" = "date_dim"."d_date_sk"
   JOIN "catalog_page" AS "catalog_page"
     ON "salesreturns"."page_sk" = "catalog_page"."cp_catalog_page_sk"
+  JOIN "date_dim_2" AS "date_dim"
+    ON "salesreturns"."date_sk" = "date_dim"."d_date_sk"
   GROUP BY
     "catalog_page"."cp_catalog_page_id"
 ), "salesreturns_3" AS (
@@ -931,7 +931,7 @@ ORDER  BY cnt
 LIMIT 100;
 WITH "_u_0" AS (
   SELECT DISTINCT
-    "date_dim"."d_month_seq" AS "_col_0"
+    "date_dim"."d_month_seq" AS "d_month_seq"
   FROM "date_dim" AS "date_dim"
   WHERE
     "date_dim"."d_moy" = 7 AND "date_dim"."d_year" = 1998
@@ -944,25 +944,25 @@ WITH "_u_0" AS (
     "j"."i_category"
 )
 SELECT
-  "customer_address"."ca_state" AS "state",
+  "a"."ca_state" AS "state",
   COUNT(*) AS "cnt"
-FROM "customer_address" AS "customer_address"
-CROSS JOIN "_u_0" AS "_u_0"
-JOIN "date_dim" AS "date_dim"
-  ON "date_dim"."d_month_seq" = "_u_0"."_col_0"
-JOIN "store_sales" AS "store_sales"
-  ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
-JOIN "customer" AS "customer"
-  ON "customer"."c_customer_sk" = "store_sales"."ss_customer_sk"
-  AND "customer_address"."ca_address_sk" = "customer"."c_current_addr_sk"
-JOIN "item" AS "item"
-  ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
+FROM "customer_address" AS "a"
+JOIN "customer" AS "c"
+  ON "a"."ca_address_sk" = "c"."c_current_addr_sk"
+JOIN "store_sales" AS "s"
+  ON "c"."c_customer_sk" = "s"."ss_customer_sk"
+JOIN "date_dim" AS "d"
+  ON "s"."ss_sold_date_sk" = "d"."d_date_sk"
+JOIN "item" AS "i"
+  ON "s"."ss_item_sk" = "i"."i_item_sk"
+JOIN "_u_0" AS "_u_0"
+  ON "d"."d_month_seq" = "_u_0"."d_month_seq"
 LEFT JOIN "_u_1" AS "_u_1"
-  ON "_u_1"."_u_2" = "item"."i_category"
+  ON "_u_1"."_u_2" = "i"."i_category"
 WHERE
-  "item"."i_current_price" > 1.2 * "_u_1"."_col_0"
+  "i"."i_current_price" > 1.2 * "_u_1"."_col_0"
 GROUP BY
-  "customer_address"."ca_state"
+  "a"."ca_state"
 HAVING
   COUNT(*) >= 10
 ORDER BY
@@ -1369,6 +1369,36 @@ WITH "_u_0" AS (
   FROM "store_sales" AS "store_sales"
   WHERE
     "store_sales"."ss_quantity" <= 20 AND "store_sales"."ss_quantity" >= 1
+), "_u_10" AS (
+  SELECT
+    AVG("store_sales"."ss_ext_list_price") AS "_col_0"
+  FROM "store_sales" AS "store_sales"
+  WHERE
+    "store_sales"."ss_quantity" <= 80 AND "store_sales"."ss_quantity" >= 61
+), "_u_11" AS (
+  SELECT
+    AVG("store_sales"."ss_net_profit") AS "_col_0"
+  FROM "store_sales" AS "store_sales"
+  WHERE
+    "store_sales"."ss_quantity" <= 80 AND "store_sales"."ss_quantity" >= 61
+), "_u_12" AS (
+  SELECT
+    COUNT(*) AS "_col_0"
+  FROM "store_sales" AS "store_sales"
+  WHERE
+    "store_sales"."ss_quantity" <= 100 AND "store_sales"."ss_quantity" >= 81
+), "_u_13" AS (
+  SELECT
+    AVG("store_sales"."ss_ext_list_price") AS "_col_0"
+  FROM "store_sales" AS "store_sales"
+  WHERE
+    "store_sales"."ss_quantity" <= 100 AND "store_sales"."ss_quantity" >= 81
+), "_u_14" AS (
+  SELECT
+    AVG("store_sales"."ss_net_profit") AS "_col_0"
+  FROM "store_sales" AS "store_sales"
+  WHERE
+    "store_sales"."ss_quantity" <= 100 AND "store_sales"."ss_quantity" >= 81
 ), "_u_2" AS (
   SELECT
     AVG("store_sales"."ss_net_profit") AS "_col_0"
@@ -1417,36 +1447,6 @@ WITH "_u_0" AS (
   FROM "store_sales" AS "store_sales"
   WHERE
     "store_sales"."ss_quantity" <= 80 AND "store_sales"."ss_quantity" >= 61
-), "_u_10" AS (
-  SELECT
-    AVG("store_sales"."ss_ext_list_price") AS "_col_0"
-  FROM "store_sales" AS "store_sales"
-  WHERE
-    "store_sales"."ss_quantity" <= 80 AND "store_sales"."ss_quantity" >= 61
-), "_u_11" AS (
-  SELECT
-    AVG("store_sales"."ss_net_profit") AS "_col_0"
-  FROM "store_sales" AS "store_sales"
-  WHERE
-    "store_sales"."ss_quantity" <= 80 AND "store_sales"."ss_quantity" >= 61
-), "_u_12" AS (
-  SELECT
-    COUNT(*) AS "_col_0"
-  FROM "store_sales" AS "store_sales"
-  WHERE
-    "store_sales"."ss_quantity" <= 100 AND "store_sales"."ss_quantity" >= 81
-), "_u_13" AS (
-  SELECT
-    AVG("store_sales"."ss_ext_list_price") AS "_col_0"
-  FROM "store_sales" AS "store_sales"
-  WHERE
-    "store_sales"."ss_quantity" <= 100 AND "store_sales"."ss_quantity" >= 81
-), "_u_14" AS (
-  SELECT
-    AVG("store_sales"."ss_net_profit") AS "_col_0"
-  FROM "store_sales" AS "store_sales"
-  WHERE
-    "store_sales"."ss_quantity" <= 100 AND "store_sales"."ss_quantity" >= 81
 )
 SELECT
   CASE WHEN "_u_0"."_col_0" > 3672 THEN "_u_1"."_col_0" ELSE "_u_2"."_col_0" END AS "bucket1",
@@ -1457,6 +1457,11 @@ SELECT
 FROM "reason" AS "reason"
 CROSS JOIN "_u_0" AS "_u_0"
 CROSS JOIN "_u_1" AS "_u_1"
+CROSS JOIN "_u_10" AS "_u_10"
+CROSS JOIN "_u_11" AS "_u_11"
+CROSS JOIN "_u_12" AS "_u_12"
+CROSS JOIN "_u_13" AS "_u_13"
+CROSS JOIN "_u_14" AS "_u_14"
 CROSS JOIN "_u_2" AS "_u_2"
 CROSS JOIN "_u_3" AS "_u_3"
 CROSS JOIN "_u_4" AS "_u_4"
@@ -1465,11 +1470,6 @@ CROSS JOIN "_u_6" AS "_u_6"
 CROSS JOIN "_u_7" AS "_u_7"
 CROSS JOIN "_u_8" AS "_u_8"
 CROSS JOIN "_u_9" AS "_u_9"
-CROSS JOIN "_u_10" AS "_u_10"
-CROSS JOIN "_u_11" AS "_u_11"
-CROSS JOIN "_u_12" AS "_u_12"
-CROSS JOIN "_u_13" AS "_u_13"
-CROSS JOIN "_u_14" AS "_u_14"
 WHERE
   "reason"."r_reason_sk" = 1;
 
@@ -1610,18 +1610,18 @@ SELECT
   COUNT(*) AS "cnt5",
   "customer_demographics"."cd_dep_college_count" AS "cd_dep_college_count",
   COUNT(*) AS "cnt6"
-FROM "customer" AS "customer"
-JOIN "customer_address" AS "customer_address"
-  ON "customer"."c_current_addr_sk" = "customer_address"."ca_address_sk"
-  AND "customer_address"."ca_county" IN ('Lycoming County', 'Sheridan County', 'Kandiyohi County', 'Pike County', 'Greene County')
-JOIN "customer_demographics" AS "customer_demographics"
-  ON "customer_demographics"."cd_demo_sk" = "customer"."c_current_cdemo_sk"
+FROM "customer" AS "c"
 LEFT JOIN "_u_0" AS "_u_0"
-  ON "customer"."c_customer_sk" = "_u_0"."_u_1"
+  ON "c"."c_customer_sk" = "_u_0"."_u_1"
 LEFT JOIN "_u_2" AS "_u_2"
-  ON "customer"."c_customer_sk" = "_u_2"."_u_3"
+  ON "c"."c_customer_sk" = "_u_2"."_u_3"
 LEFT JOIN "_u_4" AS "_u_4"
-  ON "customer"."c_customer_sk" = "_u_4"."_u_5"
+  ON "c"."c_customer_sk" = "_u_4"."_u_5"
+JOIN "customer_address" AS "ca"
+  ON "c"."c_current_addr_sk" = "ca"."ca_address_sk"
+  AND "ca"."ca_county" IN ('Lycoming County', 'Sheridan County', 'Kandiyohi County', 'Pike County', 'Greene County')
+JOIN "customer_demographics" AS "customer_demographics"
+  ON "customer_demographics"."cd_demo_sk" = "c"."c_current_cdemo_sk"
 WHERE
   NOT "_u_0"."_u_1" IS NULL
   AND (
@@ -1835,19 +1835,19 @@ SELECT
   "t_s_secyear"."customer_last_name" AS "customer_last_name",
   "t_s_secyear"."customer_birth_country" AS "customer_birth_country"
 FROM "year_total" AS "t_s_firstyear"
-JOIN "year_total" AS "t_s_secyear"
-  ON "t_s_secyear"."customer_id" = "t_s_firstyear"."customer_id"
-  AND "t_s_secyear"."dyear" = 2002
-  AND "t_s_secyear"."sale_type" = 's'
-JOIN "year_total" AS "t_w_secyear"
-  ON "t_s_firstyear"."customer_id" = "t_w_secyear"."customer_id"
-  AND "t_w_secyear"."dyear" = 2002
-  AND "t_w_secyear"."sale_type" = 'w'
 JOIN "year_total" AS "t_w_firstyear"
   ON "t_s_firstyear"."customer_id" = "t_w_firstyear"."customer_id"
   AND "t_w_firstyear"."dyear" = 2001
   AND "t_w_firstyear"."sale_type" = 'w'
   AND "t_w_firstyear"."year_total" > 0
+JOIN "year_total" AS "t_w_secyear"
+  ON "t_s_firstyear"."customer_id" = "t_w_secyear"."customer_id"
+  AND "t_w_secyear"."dyear" = 2002
+  AND "t_w_secyear"."sale_type" = 'w'
+JOIN "year_total" AS "t_s_secyear"
+  ON "t_s_secyear"."customer_id" = "t_s_firstyear"."customer_id"
+  AND "t_s_secyear"."dyear" = 2002
+  AND "t_s_secyear"."sale_type" = 's'
   AND CASE
     WHEN "t_w_firstyear"."year_total" > 0
     THEN "t_w_secyear"."year_total" / "t_w_firstyear"."year_total"
@@ -1909,13 +1909,13 @@ SELECT
   SUM("web_sales"."ws_ext_sales_price") AS "itemrevenue",
   SUM("web_sales"."ws_ext_sales_price") * 100 / SUM(SUM("web_sales"."ws_ext_sales_price")) OVER (PARTITION BY "item"."i_class") AS "revenueratio"
 FROM "web_sales" AS "web_sales"
-JOIN "item" AS "item"
-  ON "item"."i_category" IN ('Home', 'Men', 'Women')
-  AND "web_sales"."ws_item_sk" = "item"."i_item_sk"
 JOIN "date_dim" AS "date_dim"
   ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk"
   AND CAST("date_dim"."d_date" AS DATE) <= CAST('2000-06-10' AS DATE)
   AND CAST("date_dim"."d_date" AS DATE) >= CAST('2000-05-11' AS DATE)
+JOIN "item" AS "item"
+  ON "item"."i_category" IN ('Home', 'Men', 'Women')
+  AND "web_sales"."ws_item_sk" = "item"."i_item_sk"
 GROUP BY
   "item"."i_item_id",
   "item"."i_item_desc",
@@ -1982,22 +1982,7 @@ SELECT
   AVG("store_sales"."ss_ext_wholesale_cost") AS "_col_2",
   SUM("store_sales"."ss_ext_wholesale_cost") AS "_col_3"
 FROM "store_sales" AS "store_sales"
-JOIN "store" AS "store"
-  ON "store"."s_store_sk" = "store_sales"."ss_store_sk"
-CROSS JOIN "household_demographics" AS "household_demographics"
-JOIN "customer_demographics" AS "customer_demographics"
-  ON "customer_demographics"."cd_demo_sk" = "store_sales"."ss_cdemo_sk"
-  AND "customer_demographics"."cd_education_status" = 'Advanced Degree'
-  AND "customer_demographics"."cd_education_status" = 'Primary'
-  AND "customer_demographics"."cd_education_status" = 'Secondary'
-  AND "customer_demographics"."cd_marital_status" = 'D'
-  AND "customer_demographics"."cd_marital_status" = 'M'
-  AND "customer_demographics"."cd_marital_status" = 'U'
-  AND "household_demographics"."hd_dep_count" = 1
-  AND "household_demographics"."hd_dep_count" = 3
-  AND "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk"
-  AND "store_sales"."ss_sales_price" <= 100.00
-  AND "store_sales"."ss_sales_price" >= 150.00
+CROSS JOIN "customer_demographics" AS "customer_demographics"
 JOIN "customer_address" AS "customer_address"
   ON (
     "customer_address"."ca_country" = 'United States'
@@ -2022,7 +2007,22 @@ JOIN "customer_address" AS "customer_address"
   )
 JOIN "date_dim" AS "date_dim"
   ON "date_dim"."d_year" = 2001
-  AND "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk";
+  AND "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
+JOIN "household_demographics" AS "household_demographics"
+  ON "customer_demographics"."cd_demo_sk" = "store_sales"."ss_cdemo_sk"
+  AND "customer_demographics"."cd_education_status" = 'Advanced Degree'
+  AND "customer_demographics"."cd_education_status" = 'Primary'
+  AND "customer_demographics"."cd_education_status" = 'Secondary'
+  AND "customer_demographics"."cd_marital_status" = 'D'
+  AND "customer_demographics"."cd_marital_status" = 'M'
+  AND "customer_demographics"."cd_marital_status" = 'U'
+  AND "household_demographics"."hd_dep_count" = 1
+  AND "household_demographics"."hd_dep_count" = 3
+  AND "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk"
+  AND "store_sales"."ss_sales_price" <= 100.00
+  AND "store_sales"."ss_sales_price" >= 150.00
+JOIN "store" AS "store"
+  ON "store"."s_store_sk" = "store_sales"."ss_store_sk";
 
 --------------------------------------
 -- TPC-DS 14
@@ -2165,69 +2165,75 @@ WITH "item_2" AS (
     "item"."i_class_id" AS "i_class_id",
     "item"."i_category_id" AS "i_category_id"
   FROM "item" AS "item"
-), "d1" AS (
-  SELECT
-    "date_dim"."d_date_sk" AS "d_date_sk",
-    "date_dim"."d_year" AS "d_year"
-  FROM "date_dim" AS "date_dim"
-  WHERE
-    "date_dim"."d_year" <= 2001 AND "date_dim"."d_year" >= 1999
 ), "cte_4" AS (
   SELECT
     "ics"."i_brand_id" AS "i_brand_id",
     "ics"."i_class_id" AS "i_class_id",
     "ics"."i_category_id" AS "i_category_id"
   FROM "catalog_sales" AS "catalog_sales"
-  JOIN "item_2" AS "ics"
-    ON "catalog_sales"."cs_item_sk" = "ics"."i_item_sk"
-  JOIN "d1" AS "d2"
+  JOIN "date_dim" AS "d2"
     ON "catalog_sales"."cs_sold_date_sk" = "d2"."d_date_sk"
+    AND "d2"."d_year" <= 2001
+    AND "d2"."d_year" >= 1999
+  JOIN "item" AS "ics"
+    ON "catalog_sales"."cs_item_sk" = "ics"."i_item_sk"
   INTERSECT
   SELECT
     "iws"."i_brand_id" AS "i_brand_id",
     "iws"."i_class_id" AS "i_class_id",
     "iws"."i_category_id" AS "i_category_id"
   FROM "web_sales" AS "web_sales"
-  JOIN "item_2" AS "iws"
+  JOIN "date_dim" AS "d3"
+    ON "d3"."d_year" <= 2001
+    AND "d3"."d_year" >= 1999
+    AND "web_sales"."ws_sold_date_sk" = "d3"."d_date_sk"
+  JOIN "item" AS "iws"
     ON "web_sales"."ws_item_sk" = "iws"."i_item_sk"
-  JOIN "d1" AS "d3"
-    ON "web_sales"."ws_sold_date_sk" = "d3"."d_date_sk"
 ), "_q_0" AS (
   SELECT
     "iss"."i_brand_id" AS "brand_id",
     "iss"."i_class_id" AS "class_id",
     "iss"."i_category_id" AS "category_id"
   FROM "store_sales" AS "store_sales"
-  JOIN "item_2" AS "iss"
+  JOIN "date_dim" AS "d1"
+    ON "d1"."d_year" <= 2001
+    AND "d1"."d_year" >= 1999
+    AND "store_sales"."ss_sold_date_sk" = "d1"."d_date_sk"
+  JOIN "item" AS "iss"
     ON "store_sales"."ss_item_sk" = "iss"."i_item_sk"
-  JOIN "d1" AS "d1"
-    ON "store_sales"."ss_sold_date_sk" = "d1"."d_date_sk"
   INTERSECT
   SELECT
     "cte_4"."i_brand_id" AS "i_brand_id",
     "cte_4"."i_class_id" AS "i_class_id",
     "cte_4"."i_category_id" AS "i_category_id"
   FROM "cte_4" AS "cte_4"
+), "date_dim_2" AS (
+  SELECT
+    "date_dim"."d_date_sk" AS "d_date_sk",
+    "date_dim"."d_year" AS "d_year"
+  FROM "date_dim" AS "date_dim"
+  WHERE
+    "date_dim"."d_year" <= 2001 AND "date_dim"."d_year" >= 1999
 ), "cte_8" AS (
   SELECT
     "catalog_sales"."cs_quantity" AS "quantity",
     "catalog_sales"."cs_list_price" AS "list_price"
   FROM "catalog_sales" AS "catalog_sales"
-  JOIN "d1" AS "date_dim"
+  JOIN "date_dim_2" AS "date_dim"
     ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk"
   UNION ALL
   SELECT
     "web_sales"."ws_quantity" AS "quantity",
     "web_sales"."ws_list_price" AS "list_price"
   FROM "web_sales" AS "web_sales"
-  JOIN "d1" AS "date_dim"
+  JOIN "date_dim_2" AS "date_dim"
     ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk"
 ), "x" AS (
   SELECT
     "store_sales"."ss_quantity" AS "quantity",
     "store_sales"."ss_list_price" AS "list_price"
   FROM "store_sales" AS "store_sales"
-  JOIN "d1" AS "date_dim"
+  JOIN "date_dim_2" AS "date_dim"
     ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
   UNION ALL
   SELECT
@@ -2238,14 +2244,10 @@ WITH "item_2" AS (
   SELECT
     AVG("x"."quantity" * "x"."list_price") AS "average_sales"
   FROM "x" AS "x"
-), "date_dim_2" AS (
+), "_u_1" AS (
   SELECT
-    "date_dim"."d_date_sk" AS "d_date_sk",
-    "date_dim"."d_year" AS "d_year",
-    "date_dim"."d_moy" AS "d_moy"
-  FROM "date_dim" AS "date_dim"
-  WHERE
-    "date_dim"."d_moy" = 11 AND "date_dim"."d_year" = 2001
+    "avg_sales"."average_sales" AS "average_sales"
+  FROM "avg_sales"
 ), "_u_0" AS (
   SELECT
     "item"."i_item_sk" AS "ss_item_sk"
@@ -2256,10 +2258,14 @@ WITH "item_2" AS (
     AND "item"."i_class_id" = "_q_0"."class_id"
   GROUP BY
     "item"."i_item_sk"
-), "_u_1" AS (
+), "date_dim_3" AS (
   SELECT
-    "avg_sales"."average_sales" AS "average_sales"
-  FROM "avg_sales"
+    "date_dim"."d_date_sk" AS "d_date_sk",
+    "date_dim"."d_year" AS "d_year",
+    "date_dim"."d_moy" AS "d_moy"
+  FROM "date_dim" AS "date_dim"
+  WHERE
+    "date_dim"."d_moy" = 11 AND "date_dim"."d_year" = 2001
 ), "cte_9" AS (
   SELECT
     'store' AS "channel",
@@ -2269,13 +2275,13 @@ WITH "item_2" AS (
     SUM("store_sales"."ss_quantity" * "store_sales"."ss_list_price") AS "sales",
     COUNT(*) AS "number_sales"
   FROM "store_sales" AS "store_sales"
-  JOIN "item_2" AS "item"
-    ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
-  JOIN "date_dim_2" AS "date_dim"
-    ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
+  CROSS JOIN "_u_1" AS "_u_1"
   LEFT JOIN "_u_0" AS "_u_0"
     ON "store_sales"."ss_item_sk" = "_u_0"."ss_item_sk"
-  CROSS JOIN "_u_1" AS "_u_1"
+  JOIN "date_dim_3" AS "date_dim"
+    ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
+  JOIN "item_2" AS "item"
+    ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
   WHERE
     NOT "_u_0"."ss_item_sk" IS NULL
   GROUP BY
@@ -2293,13 +2299,13 @@ WITH "item_2" AS (
     SUM("catalog_sales"."cs_quantity" * "catalog_sales"."cs_list_price") AS "sales",
     COUNT(*) AS "number_sales"
   FROM "catalog_sales" AS "catalog_sales"
-  JOIN "item_2" AS "item"
-    ON "catalog_sales"."cs_item_sk" = "item"."i_item_sk"
-  JOIN "date_dim_2" AS "date_dim"
-    ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk"
+  CROSS JOIN "_u_1" AS "_u_3"
   LEFT JOIN "_u_0" AS "_u_2"
     ON "catalog_sales"."cs_item_sk" = "_u_2"."ss_item_sk"
-  CROSS JOIN "_u_1" AS "_u_3"
+  JOIN "date_dim_3" AS "date_dim"
+    ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk"
+  JOIN "item_2" AS "item"
+    ON "catalog_sales"."cs_item_sk" = "item"."i_item_sk"
   WHERE
     NOT "_u_2"."ss_item_sk" IS NULL
   GROUP BY
@@ -2317,13 +2323,13 @@ WITH "item_2" AS (
     SUM("web_sales"."ws_quantity" * "web_sales"."ws_list_price") AS "sales",
     COUNT(*) AS "number_sales"
   FROM "web_sales" AS "web_sales"
-  JOIN "item_2" AS "item"
-    ON "web_sales"."ws_item_sk" = "item"."i_item_sk"
-  JOIN "date_dim_2" AS "date_dim"
-    ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk"
+  CROSS JOIN "_u_1" AS "_u_5"
   LEFT JOIN "_u_0" AS "_u_4"
     ON "web_sales"."ws_item_sk" = "_u_4"."ss_item_sk"
-  CROSS JOIN "_u_1" AS "_u_5"
+  JOIN "date_dim_3" AS "date_dim"
+    ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk"
+  JOIN "item_2" AS "item"
+    ON "web_sales"."ws_item_sk" = "item"."i_item_sk"
   WHERE
     NOT "_u_4"."ss_item_sk" IS NULL
   GROUP BY
@@ -2421,13 +2427,13 @@ JOIN "customer_address" AS "customer_address"
   ON "catalog_sales"."cs_sales_price" > 500
   OR "customer_address"."ca_state" IN ('CA', 'WA', 'GA')
   OR SUBSTR("customer_address"."ca_zip", 1, 5) IN ('85669', '86197', '88274', '83405', '86475', '85392', '85460', '80348', '81792')
-JOIN "customer" AS "customer"
-  ON "catalog_sales"."cs_bill_customer_sk" = "customer"."c_customer_sk"
-  AND "customer"."c_current_addr_sk" = "customer_address"."ca_address_sk"
 JOIN "date_dim" AS "date_dim"
   ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk"
   AND "date_dim"."d_qoy" = 1
   AND "date_dim"."d_year" = 1998
+JOIN "customer" AS "customer"
+  ON "catalog_sales"."cs_bill_customer_sk" = "customer"."c_customer_sk"
+  AND "customer"."c_current_addr_sk" = "customer_address"."ca_address_sk"
 GROUP BY
   "customer_address"."ca_zip"
 ORDER BY
@@ -2484,32 +2490,32 @@ WITH "_u_0" AS (
     "cr1"."cr_order_number"
 )
 SELECT
-  COUNT(DISTINCT "catalog_sales"."cs_order_number") AS "order count",
-  SUM("catalog_sales"."cs_ext_ship_cost") AS "total shipping cost",
-  SUM("catalog_sales"."cs_net_profit") AS "total net profit"
-FROM "catalog_sales" AS "catalog_sales"
+  COUNT(DISTINCT "cs1"."cs_order_number") AS "order count",
+  SUM("cs1"."cs_ext_ship_cost") AS "total shipping cost",
+  SUM("cs1"."cs_net_profit") AS "total net profit"
+FROM "catalog_sales" AS "cs1"
+LEFT JOIN "_u_0" AS "_u_0"
+  ON "cs1"."cs_order_number" = "_u_0"."_u_1"
+LEFT JOIN "_u_3" AS "_u_3"
+  ON "cs1"."cs_order_number" = "_u_3"."_u_4"
+JOIN "call_center" AS "call_center"
+  ON "call_center"."cc_county" IN ('Williamson County', 'Williamson County', 'Williamson County', 'Williamson County', 'Williamson County')
+  AND "cs1"."cs_call_center_sk" = "call_center"."cc_call_center_sk"
+JOIN "customer_address" AS "customer_address"
+  ON "cs1"."cs_ship_addr_sk" = "customer_address"."ca_address_sk"
+  AND "customer_address"."ca_state" = 'IA'
 JOIN "date_dim" AS "date_dim"
-  ON "catalog_sales"."cs_ship_date_sk" = "date_dim"."d_date_sk"
+  ON "cs1"."cs_ship_date_sk" = "date_dim"."d_date_sk"
   AND "date_dim"."d_date" >= '2002-3-01'
   AND CAST("date_dim"."d_date" AS DATE) <= (
     CAST('2002-3-01' AS DATE) + INTERVAL '60' day
   )
-JOIN "customer_address" AS "customer_address"
-  ON "catalog_sales"."cs_ship_addr_sk" = "customer_address"."ca_address_sk"
-  AND "customer_address"."ca_state" = 'IA'
-JOIN "call_center" AS "call_center"
-  ON "call_center"."cc_county" IN ('Williamson County', 'Williamson County', 'Williamson County', 'Williamson County', 'Williamson County')
-  AND "catalog_sales"."cs_call_center_sk" = "call_center"."cc_call_center_sk"
-LEFT JOIN "_u_0" AS "_u_0"
-  ON "catalog_sales"."cs_order_number" = "_u_0"."_u_1"
-LEFT JOIN "_u_3" AS "_u_3"
-  ON "catalog_sales"."cs_order_number" = "_u_3"."_u_4"
 WHERE
   "_u_3"."_u_4" IS NULL
-  AND ARRAY_ANY("_u_0"."_u_2", "_x" -> "catalog_sales"."cs_warehouse_sk" <> "_x")
+  AND ARRAY_ANY("_u_0"."_u_2", "_x" -> "cs1"."cs_warehouse_sk" <> "_x")
   AND NOT "_u_0"."_u_1" IS NULL
 ORDER BY
-  COUNT(DISTINCT "catalog_sales"."cs_order_number")
+  COUNT(DISTINCT "cs1"."cs_order_number")
 LIMIT 100;
 
 --------------------------------------
@@ -2570,14 +2576,6 @@ ORDER  BY i_item_id,
           i_item_desc,
           s_state
 LIMIT 100;
-WITH "d3" AS (
-  SELECT
-    "date_dim"."d_date_sk" AS "d_date_sk",
-    "date_dim"."d_quarter_name" AS "d_quarter_name"
-  FROM "date_dim" AS "date_dim"
-  WHERE
-    "date_dim"."d_quarter_name" IN ('1999Q1', '1999Q2', '1999Q3')
-)
 SELECT
   "item"."i_item_id" AS "i_item_id",
   "item"."i_item_desc" AS "i_item_desc",
@@ -2595,24 +2593,26 @@ SELECT
   STDDEV_SAMP("catalog_sales"."cs_quantity") / AVG("catalog_sales"."cs_quantity") AS "catalog_sales_quantitystdev",
   STDDEV_SAMP("catalog_sales"."cs_quantity") / AVG("catalog_sales"."cs_quantity") AS "catalog_sales_quantitycov"
 FROM "store_sales" AS "store_sales"
-CROSS JOIN "d3" AS "d3"
-JOIN "catalog_sales" AS "catalog_sales"
-  ON "catalog_sales"."cs_sold_date_sk" = "d3"."d_date_sk"
+JOIN "date_dim" AS "d1"
+  ON "d1"."d_date_sk" = "store_sales"."ss_sold_date_sk"
+  AND "d1"."d_quarter_name" = '1999Q1'
+JOIN "item" AS "item"
+  ON "item"."i_item_sk" = "store_sales"."ss_item_sk"
+JOIN "store" AS "store"
+  ON "store"."s_store_sk" = "store_sales"."ss_store_sk"
 JOIN "store_returns" AS "store_returns"
-  ON "store_returns"."sr_customer_sk" = "catalog_sales"."cs_bill_customer_sk"
-  AND "store_returns"."sr_item_sk" = "catalog_sales"."cs_item_sk"
-  AND "store_sales"."ss_customer_sk" = "store_returns"."sr_customer_sk"
+  ON "store_sales"."ss_customer_sk" = "store_returns"."sr_customer_sk"
   AND "store_sales"."ss_item_sk" = "store_returns"."sr_item_sk"
   AND "store_sales"."ss_ticket_number" = "store_returns"."sr_ticket_number"
-JOIN "date_dim" AS "date_dim"
-  ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk"
-  AND "date_dim"."d_quarter_name" = '1999Q1'
-JOIN "d3" AS "d2"
-  ON "store_returns"."sr_returned_date_sk" = "d2"."d_date_sk"
-JOIN "store" AS "store"
-  ON "store"."s_store_sk" = "store_sales"."ss_store_sk"
-JOIN "item" AS "item"
-  ON "item"."i_item_sk" = "store_sales"."ss_item_sk"
+JOIN "catalog_sales" AS "catalog_sales"
+  ON "store_returns"."sr_customer_sk" = "catalog_sales"."cs_bill_customer_sk"
+  AND "store_returns"."sr_item_sk" = "catalog_sales"."cs_item_sk"
+JOIN "date_dim" AS "d2"
+  ON "d2"."d_quarter_name" IN ('1999Q1', '1999Q2', '1999Q3')
+  AND "store_returns"."sr_returned_date_sk" = "d2"."d_date_sk"
+JOIN "date_dim" AS "d3"
+  ON "catalog_sales"."cs_sold_date_sk" = "d3"."d_date_sk"
+  AND "d3"."d_quarter_name" IN ('1999Q1', '1999Q2', '1999Q3')
 GROUP BY
   "item"."i_item_id",
   "item"."i_item_desc",
@@ -2674,25 +2674,25 @@ SELECT
   AVG(CAST("catalog_sales"."cs_sales_price" AS DECIMAL(12, 2))) AS "agg4",
   AVG(CAST("catalog_sales"."cs_net_profit" AS DECIMAL(12, 2))) AS "agg5",
   AVG(CAST("customer"."c_birth_year" AS DECIMAL(12, 2))) AS "agg6",
-  AVG(CAST("customer_demographics"."cd_dep_count" AS DECIMAL(12, 2))) AS "agg7"
+  AVG(CAST("cd1"."cd_dep_count" AS DECIMAL(12, 2))) AS "agg7"
 FROM "catalog_sales" AS "catalog_sales"
-JOIN "customer_demographics" AS "customer_demographics"
-  ON "catalog_sales"."cs_bill_cdemo_sk" = "customer_demographics"."cd_demo_sk"
-  AND "customer_demographics"."cd_education_status" = 'Secondary'
-  AND "customer_demographics"."cd_gender" = 'F'
+JOIN "customer_demographics" AS "cd1"
+  ON "catalog_sales"."cs_bill_cdemo_sk" = "cd1"."cd_demo_sk"
+  AND "cd1"."cd_education_status" = 'Secondary'
+  AND "cd1"."cd_gender" = 'F'
 JOIN "customer" AS "customer"
   ON "catalog_sales"."cs_bill_customer_sk" = "customer"."c_customer_sk"
   AND "customer"."c_birth_month" IN (8, 4, 2, 5, 11, 9)
-JOIN "customer_demographics" AS "customer_demographics_2"
-  ON "customer"."c_current_cdemo_sk" = "customer_demographics_2"."cd_demo_sk"
-JOIN "customer_address" AS "customer_address"
-  ON "customer"."c_current_addr_sk" = "customer_address"."ca_address_sk"
-  AND "customer_address"."ca_state" IN ('KS', 'IA', 'AL', 'UT', 'VA', 'NC', 'TX')
 JOIN "date_dim" AS "date_dim"
   ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk"
   AND "date_dim"."d_year" = 2001
 JOIN "item" AS "item"
   ON "catalog_sales"."cs_item_sk" = "item"."i_item_sk"
+JOIN "customer_demographics" AS "cd2"
+  ON "customer"."c_current_cdemo_sk" = "cd2"."cd_demo_sk"
+JOIN "customer_address" AS "customer_address"
+  ON "customer"."c_current_addr_sk" = "customer_address"."ca_address_sk"
+  AND "customer_address"."ca_state" IN ('KS', 'IA', 'AL', 'UT', 'VA', 'NC', 'TX')
 GROUP BY
 ROLLUP (
   "item"."i_item_id",
@@ -2708,6 +2708,72 @@ ORDER BY
 LIMIT 100;
 
 --------------------------------------
+-- TPC-DS 19
+--------------------------------------
+SELECT i_brand_id              brand_id,
+               i_brand                 brand,
+               i_manufact_id,
+               i_manufact,
+               Sum(ss_ext_sales_price) ext_price
+FROM   date_dim,
+       store_sales,
+       item,
+       customer,
+       customer_address,
+       store
+WHERE  d_date_sk = ss_sold_date_sk
+       AND ss_item_sk = i_item_sk
+       AND i_manager_id = 38
+       AND d_moy = 12
+       AND d_year = 1998
+       AND ss_customer_sk = c_customer_sk
+       AND c_current_addr_sk = ca_address_sk
+       AND Substr(ca_zip, 1, 5) <> Substr(s_zip, 1, 5)
+       AND ss_store_sk = s_store_sk
+GROUP  BY i_brand,
+          i_brand_id,
+          i_manufact_id,
+          i_manufact
+ORDER  BY ext_price DESC,
+          i_brand,
+          i_brand_id,
+          i_manufact_id,
+          i_manufact
+LIMIT 100;
+SELECT
+  "item"."i_brand_id" AS "brand_id",
+  "item"."i_brand" AS "brand",
+  "item"."i_manufact_id" AS "i_manufact_id",
+  "item"."i_manufact" AS "i_manufact",
+  SUM("store_sales"."ss_ext_sales_price") AS "ext_price"
+FROM "date_dim" AS "date_dim"
+JOIN "store_sales" AS "store_sales"
+  ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk"
+JOIN "item" AS "item"
+  ON "item"."i_manager_id" = 38 AND "store_sales"."ss_item_sk" = "item"."i_item_sk"
+JOIN "store" AS "store"
+  ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
+JOIN "customer_address" AS "customer_address"
+  ON SUBSTR("customer_address"."ca_zip", 1, 5) <> SUBSTR("store"."s_zip", 1, 5)
+JOIN "customer" AS "customer"
+  ON "customer"."c_current_addr_sk" = "customer_address"."ca_address_sk"
+  AND "store_sales"."ss_customer_sk" = "customer"."c_customer_sk"
+WHERE
+  "date_dim"."d_moy" = 12 AND "date_dim"."d_year" = 1998
+GROUP BY
+  "item"."i_brand",
+  "item"."i_brand_id",
+  "item"."i_manufact_id",
+  "item"."i_manufact"
+ORDER BY
+  "ext_price" DESC,
+  "item"."i_brand",
+  "item"."i_brand_id",
+  "i_manufact_id",
+  "i_manufact"
+LIMIT 100;
+
+--------------------------------------
 -- TPC-DS 20
 --------------------------------------
 SELECT
@@ -2748,13 +2814,13 @@ SELECT
   SUM("catalog_sales"."cs_ext_sales_price") AS "itemrevenue",
   SUM("catalog_sales"."cs_ext_sales_price") * 100 / SUM(SUM("catalog_sales"."cs_ext_sales_price")) OVER (PARTITION BY "item"."i_class") AS "revenueratio"
 FROM "catalog_sales" AS "catalog_sales"
-JOIN "item" AS "item"
-  ON "catalog_sales"."cs_item_sk" = "item"."i_item_sk"
-  AND "item"."i_category" IN ('Children', 'Women', 'Electronics')
 JOIN "date_dim" AS "date_dim"
   ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk"
   AND CAST("date_dim"."d_date" AS DATE) <= CAST('2001-03-05' AS DATE)
   AND CAST("date_dim"."d_date" AS DATE) >= CAST('2001-02-03' AS DATE)
+JOIN "item" AS "item"
+  ON "catalog_sales"."cs_item_sk" = "item"."i_item_sk"
+  AND "item"."i_category" IN ('Children', 'Women', 'Electronics')
 GROUP BY
   "item"."i_item_id",
   "item"."i_item_desc",
@@ -2828,16 +2894,16 @@ WITH "x" AS (
       END
     ) AS "inv_after"
   FROM "inventory" AS "inventory"
-  JOIN "warehouse" AS "warehouse"
-    ON "inventory"."inv_warehouse_sk" = "warehouse"."w_warehouse_sk"
-  JOIN "item" AS "item"
-    ON "item"."i_current_price" <= 1.49
-    AND "item"."i_current_price" >= 0.99
-    AND "item"."i_item_sk" = "inventory"."inv_item_sk"
   JOIN "date_dim" AS "date_dim"
     ON "inventory"."inv_date_sk" = "date_dim"."d_date_sk"
     AND CAST("date_dim"."d_date" AS DATE) <= CAST('2000-06-12' AS DATE)
     AND CAST("date_dim"."d_date" AS DATE) >= CAST('2000-04-13' AS DATE)
+  JOIN "item" AS "item"
+    ON "item"."i_current_price" <= 1.49
+    AND "item"."i_current_price" >= 0.99
+    AND "item"."i_item_sk" = "inventory"."inv_item_sk"
+  JOIN "warehouse" AS "warehouse"
+    ON "inventory"."inv_warehouse_sk" = "warehouse"."w_warehouse_sk"
   GROUP BY
     "warehouse"."w_warehouse_name",
     "item"."i_item_id"
@@ -3008,21 +3074,13 @@ WITH "frequent_ss_items" AS (
   SELECT
     "customer"."c_customer_sk" AS "c_customer_sk"
   FROM "store_sales" AS "store_sales"
+  CROSS JOIN "max_store_sales"
   JOIN "customer_2" AS "customer"
     ON "store_sales"."ss_customer_sk" = "customer"."c_customer_sk"
-  CROSS JOIN "max_store_sales"
   GROUP BY
     "customer"."c_customer_sk"
   HAVING
     SUM("store_sales"."ss_quantity" * "store_sales"."ss_sales_price") > 0.95 * MAX("max_store_sales"."tpcds_cmax")
-), "date_dim_4" AS (
-  SELECT
-    "date_dim"."d_date_sk" AS "d_date_sk",
-    "date_dim"."d_year" AS "d_year",
-    "date_dim"."d_moy" AS "d_moy"
-  FROM "date_dim" AS "date_dim"
-  WHERE
-    "date_dim"."d_moy" = 6 AND "date_dim"."d_year" = 1998
 ), "_u_1" AS (
   SELECT
     "frequent_ss_items"."item_sk" AS "item_sk"
@@ -3035,28 +3093,36 @@ WITH "frequent_ss_items" AS (
   FROM "best_ss_customer"
   GROUP BY
     "best_ss_customer"."c_customer_sk"
+), "date_dim_4" AS (
+  SELECT
+    "date_dim"."d_date_sk" AS "d_date_sk",
+    "date_dim"."d_year" AS "d_year",
+    "date_dim"."d_moy" AS "d_moy"
+  FROM "date_dim" AS "date_dim"
+  WHERE
+    "date_dim"."d_moy" = 6 AND "date_dim"."d_year" = 1998
 ), "_q_1" AS (
   SELECT
     "catalog_sales"."cs_quantity" * "catalog_sales"."cs_list_price" AS "sales"
   FROM "catalog_sales" AS "catalog_sales"
-  JOIN "date_dim_4" AS "date_dim"
-    ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk"
   LEFT JOIN "_u_1" AS "_u_1"
     ON "catalog_sales"."cs_item_sk" = "_u_1"."item_sk"
   LEFT JOIN "_u_2" AS "_u_2"
     ON "catalog_sales"."cs_bill_customer_sk" = "_u_2"."c_customer_sk"
+  JOIN "date_dim_4" AS "date_dim"
+    ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk"
   WHERE
     NOT "_u_1"."item_sk" IS NULL AND NOT "_u_2"."c_customer_sk" IS NULL
   UNION ALL
   SELECT
     "web_sales"."ws_quantity" * "web_sales"."ws_list_price" AS "sales"
   FROM "web_sales" AS "web_sales"
-  JOIN "date_dim_4" AS "date_dim"
-    ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk"
   LEFT JOIN "_u_1" AS "_u_3"
     ON "web_sales"."ws_item_sk" = "_u_3"."item_sk"
   LEFT JOIN "_u_2" AS "_u_4"
     ON "web_sales"."ws_bill_customer_sk" = "_u_4"."c_customer_sk"
+  JOIN "date_dim_4" AS "date_dim"
+    ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk"
   WHERE
     NOT "_u_3"."item_sk" IS NULL AND NOT "_u_4"."c_customer_sk" IS NULL
 )
@@ -3123,18 +3189,18 @@ WITH "ssales" AS (
     "item"."i_color" AS "i_color",
     SUM("store_sales"."ss_net_profit") AS "netpaid"
   FROM "store_sales" AS "store_sales"
+  JOIN "item" AS "item"
+    ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
+  JOIN "store" AS "store"
+    ON "store"."s_market_id" = 6 AND "store_sales"."ss_store_sk" = "store"."s_store_sk"
   JOIN "store_returns" AS "store_returns"
     ON "store_sales"."ss_item_sk" = "store_returns"."sr_item_sk"
     AND "store_sales"."ss_ticket_number" = "store_returns"."sr_ticket_number"
-  JOIN "store" AS "store"
-    ON "store"."s_market_id" = 6 AND "store_sales"."ss_store_sk" = "store"."s_store_sk"
-  JOIN "item" AS "item"
-    ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
-  JOIN "customer" AS "customer"
-    ON "store_sales"."ss_customer_sk" = "customer"."c_customer_sk"
   JOIN "customer_address" AS "customer_address"
+    ON "store"."s_zip" = "customer_address"."ca_zip"
+  JOIN "customer" AS "customer"
     ON "customer"."c_birth_country" = UPPER("customer_address"."ca_country")
-    AND "store"."s_zip" = "customer_address"."ca_zip"
+    AND "store_sales"."ss_customer_sk" = "customer"."c_customer_sk"
   GROUP BY
     "customer"."c_last_name",
     "customer"."c_first_name",
@@ -3210,15 +3276,6 @@ ORDER  BY i_item_id,
           s_store_id,
           s_store_name
 LIMIT 100;
-WITH "d3" AS (
-  SELECT
-    "date_dim"."d_date_sk" AS "d_date_sk",
-    "date_dim"."d_year" AS "d_year",
-    "date_dim"."d_moy" AS "d_moy"
-  FROM "date_dim" AS "date_dim"
-  WHERE
-    "date_dim"."d_moy" <= 10 AND "date_dim"."d_moy" >= 4 AND "date_dim"."d_year" = 2001
-)
 SELECT
   "item"."i_item_id" AS "i_item_id",
   "item"."i_item_desc" AS "i_item_desc",
@@ -3228,25 +3285,31 @@ SELECT
   MAX("store_returns"."sr_net_loss") AS "store_returns_loss",
   MAX("catalog_sales"."cs_net_profit") AS "catalog_sales_profit"
 FROM "store_sales" AS "store_sales"
-CROSS JOIN "d3" AS "d3"
-JOIN "catalog_sales" AS "catalog_sales"
-  ON "catalog_sales"."cs_sold_date_sk" = "d3"."d_date_sk"
+JOIN "date_dim" AS "d1"
+  ON "d1"."d_date_sk" = "store_sales"."ss_sold_date_sk"
+  AND "d1"."d_moy" = 4
+  AND "d1"."d_year" = 2001
+JOIN "item" AS "item"
+  ON "item"."i_item_sk" = "store_sales"."ss_item_sk"
+JOIN "store" AS "store"
+  ON "store"."s_store_sk" = "store_sales"."ss_store_sk"
 JOIN "store_returns" AS "store_returns"
-  ON "store_returns"."sr_customer_sk" = "catalog_sales"."cs_bill_customer_sk"
-  AND "store_returns"."sr_item_sk" = "catalog_sales"."cs_item_sk"
-  AND "store_sales"."ss_customer_sk" = "store_returns"."sr_customer_sk"
+  ON "store_sales"."ss_customer_sk" = "store_returns"."sr_customer_sk"
   AND "store_sales"."ss_item_sk" = "store_returns"."sr_item_sk"
   AND "store_sales"."ss_ticket_number" = "store_returns"."sr_ticket_number"
-JOIN "date_dim" AS "date_dim"
-  ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk"
-  AND "date_dim"."d_moy" = 4
-  AND "date_dim"."d_year" = 2001
-JOIN "d3" AS "d2"
-  ON "store_returns"."sr_returned_date_sk" = "d2"."d_date_sk"
-JOIN "store" AS "store"
-  ON "store"."s_store_sk" = "store_sales"."ss_store_sk"
-JOIN "item" AS "item"
-  ON "item"."i_item_sk" = "store_sales"."ss_item_sk"
+JOIN "catalog_sales" AS "catalog_sales"
+  ON "store_returns"."sr_customer_sk" = "catalog_sales"."cs_bill_customer_sk"
+  AND "store_returns"."sr_item_sk" = "catalog_sales"."cs_item_sk"
+JOIN "date_dim" AS "d2"
+  ON "d2"."d_moy" <= 10
+  AND "d2"."d_moy" >= 4
+  AND "d2"."d_year" = 2001
+  AND "store_returns"."sr_returned_date_sk" = "d2"."d_date_sk"
+JOIN "date_dim" AS "d3"
+  ON "catalog_sales"."cs_sold_date_sk" = "d3"."d_date_sk"
+  AND "d3"."d_moy" <= 10
+  AND "d3"."d_moy" >= 4
+  AND "d3"."d_year" = 2001
 GROUP BY
   "item"."i_item_id",
   "item"."i_item_desc",
@@ -3359,11 +3422,11 @@ JOIN "customer_demographics" AS "customer_demographics"
 JOIN "date_dim" AS "date_dim"
   ON "date_dim"."d_year" = 2000
   AND "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
+JOIN "item" AS "item"
+  ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
 JOIN "store" AS "store"
   ON "store"."s_state" IN ('TN', 'TN', 'TN', 'TN', 'TN', 'TN')
   AND "store_sales"."ss_store_sk" = "store"."s_store_sk"
-JOIN "item" AS "item"
-  ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
 GROUP BY
 ROLLUP (
   "item"."i_item_id",
@@ -3590,29 +3653,29 @@ SELECT
   AVG("store_returns"."sr_return_quantity") AS "store_returns_quantity",
   AVG("catalog_sales"."cs_quantity") AS "catalog_sales_quantity"
 FROM "store_sales" AS "store_sales"
-JOIN "date_dim" AS "date_dim"
-  ON "date_dim"."d_year" IN (1998, 1999, 2000)
-JOIN "catalog_sales" AS "catalog_sales"
-  ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk"
+JOIN "date_dim" AS "d1"
+  ON "d1"."d_date_sk" = "store_sales"."ss_sold_date_sk"
+  AND "d1"."d_moy" = 4
+  AND "d1"."d_year" = 1998
+JOIN "item" AS "item"
+  ON "item"."i_item_sk" = "store_sales"."ss_item_sk"
+JOIN "store" AS "store"
+  ON "store"."s_store_sk" = "store_sales"."ss_store_sk"
 JOIN "store_returns" AS "store_returns"
-  ON "store_returns"."sr_customer_sk" = "catalog_sales"."cs_bill_customer_sk"
-  AND "store_returns"."sr_item_sk" = "catalog_sales"."cs_item_sk"
-  AND "store_sales"."ss_customer_sk" = "store_returns"."sr_customer_sk"
+  ON "store_sales"."ss_customer_sk" = "store_returns"."sr_customer_sk"
   AND "store_sales"."ss_item_sk" = "store_returns"."sr_item_sk"
   AND "store_sales"."ss_ticket_number" = "store_returns"."sr_ticket_number"
-JOIN "date_dim" AS "date_dim_2"
-  ON "date_dim_2"."d_date_sk" = "store_sales"."ss_sold_date_sk"
-  AND "date_dim_2"."d_moy" = 4
-  AND "date_dim_2"."d_year" = 1998
-JOIN "date_dim" AS "date_dim_3"
-  ON "date_dim_3"."d_moy" <= 7
-  AND "date_dim_3"."d_moy" >= 4
-  AND "date_dim_3"."d_year" = 1998
-  AND "store_returns"."sr_returned_date_sk" = "date_dim_3"."d_date_sk"
-JOIN "store" AS "store"
-  ON "store"."s_store_sk" = "store_sales"."ss_store_sk"
-JOIN "item" AS "item"
-  ON "item"."i_item_sk" = "store_sales"."ss_item_sk"
+JOIN "catalog_sales" AS "catalog_sales"
+  ON "store_returns"."sr_customer_sk" = "catalog_sales"."cs_bill_customer_sk"
+  AND "store_returns"."sr_item_sk" = "catalog_sales"."cs_item_sk"
+JOIN "date_dim" AS "d2"
+  ON "d2"."d_moy" <= 7
+  AND "d2"."d_moy" >= 4
+  AND "d2"."d_year" = 1998
+  AND "store_returns"."sr_returned_date_sk" = "d2"."d_date_sk"
+JOIN "date_dim" AS "d3"
+  ON "catalog_sales"."cs_sold_date_sk" = "d3"."d_date_sk"
+  AND "d3"."d_year" IN (1998, 1999, 2000)
 GROUP BY
   "item"."i_item_id",
   "item"."i_item_desc",
@@ -3682,11 +3745,11 @@ WITH "customer_total_return" AS (
     "customer_address"."ca_state" AS "ctr_state",
     SUM("web_returns"."wr_return_amt") AS "ctr_total_return"
   FROM "web_returns" AS "web_returns"
+  JOIN "customer_address" AS "customer_address"
+    ON "web_returns"."wr_returning_addr_sk" = "customer_address"."ca_address_sk"
   JOIN "date_dim" AS "date_dim"
     ON "date_dim"."d_year" = 2000
     AND "web_returns"."wr_returned_date_sk" = "date_dim"."d_date_sk"
-  JOIN "customer_address" AS "customer_address"
-    ON "web_returns"."wr_returning_addr_sk" = "customer_address"."ca_address_sk"
   GROUP BY
     "web_returns"."wr_returning_customer_sk",
     "customer_address"."ca_state"
@@ -3713,13 +3776,13 @@ SELECT
   "customer"."c_last_review_date" AS "c_last_review_date",
   "ctr1"."ctr_total_return" AS "ctr_total_return"
 FROM "customer_total_return" AS "ctr1"
+LEFT JOIN "_u_0" AS "_u_0"
+  ON "ctr1"."ctr_state" = "_u_0"."_u_1"
 JOIN "customer" AS "customer"
   ON "ctr1"."ctr_customer_sk" = "customer"."c_customer_sk"
 JOIN "customer_address" AS "customer_address"
   ON "customer_address"."ca_address_sk" = "customer"."c_current_addr_sk"
   AND "customer_address"."ca_state" = 'IN'
-LEFT JOIN "_u_0" AS "_u_0"
-  ON "ctr1"."ctr_state" = "_u_0"."_u_1"
 WHERE
   "ctr1"."ctr_total_return" > "_u_0"."_col_0"
 ORDER BY
@@ -3813,17 +3876,17 @@ WHERE  ss1.d_qoy = 1
                    ELSE NULL
                  END
 ORDER  BY ss1.d_year;
-WITH "date_dim_2" AS (
+WITH "customer_address_2" AS (
+  SELECT
+    "customer_address"."ca_address_sk" AS "ca_address_sk",
+    "customer_address"."ca_county" AS "ca_county"
+  FROM "customer_address" AS "customer_address"
+), "date_dim_2" AS (
   SELECT
     "date_dim"."d_date_sk" AS "d_date_sk",
     "date_dim"."d_year" AS "d_year",
     "date_dim"."d_qoy" AS "d_qoy"
   FROM "date_dim" AS "date_dim"
-), "customer_address_2" AS (
-  SELECT
-    "customer_address"."ca_address_sk" AS "ca_address_sk",
-    "customer_address"."ca_county" AS "ca_county"
-  FROM "customer_address" AS "customer_address"
 ), "ss" AS (
   SELECT
     "customer_address"."ca_county" AS "ca_county",
@@ -3831,10 +3894,10 @@ WITH "date_dim_2" AS (
     "date_dim"."d_year" AS "d_year",
     SUM("store_sales"."ss_ext_sales_price") AS "store_sales"
   FROM "store_sales" AS "store_sales"
-  JOIN "date_dim_2" AS "date_dim"
-    ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
   JOIN "customer_address_2" AS "customer_address"
     ON "store_sales"."ss_addr_sk" = "customer_address"."ca_address_sk"
+  JOIN "date_dim_2" AS "date_dim"
+    ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
   GROUP BY
     "customer_address"."ca_county",
     "date_dim"."d_qoy",
@@ -3846,10 +3909,10 @@ WITH "date_dim_2" AS (
     "date_dim"."d_year" AS "d_year",
     SUM("web_sales"."ws_ext_sales_price") AS "web_sales"
   FROM "web_sales" AS "web_sales"
-  JOIN "date_dim_2" AS "date_dim"
-    ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk"
   JOIN "customer_address_2" AS "customer_address"
     ON "web_sales"."ws_bill_addr_sk" = "customer_address"."ca_address_sk"
+  JOIN "date_dim_2" AS "date_dim"
+    ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk"
   GROUP BY
     "customer_address"."ca_county",
     "date_dim"."d_qoy",
@@ -3865,13 +3928,12 @@ SELECT
 FROM "ss" AS "ss1"
 JOIN "ss" AS "ss2"
   ON "ss1"."ca_county" = "ss2"."ca_county" AND "ss2"."d_qoy" = 2 AND "ss2"."d_year" = 2001
-JOIN "ws" AS "ws2"
-  ON "ws2"."d_qoy" = 2 AND "ws2"."d_year" = 2001
 JOIN "ws" AS "ws1"
-  ON "ss1"."ca_county" = "ws1"."ca_county"
-  AND "ws1"."ca_county" = "ws2"."ca_county"
-  AND "ws1"."d_qoy" = 1
-  AND "ws1"."d_year" = 2001
+  ON "ss1"."ca_county" = "ws1"."ca_county" AND "ws1"."d_qoy" = 1 AND "ws1"."d_year" = 2001
+JOIN "ws" AS "ws2"
+  ON "ws1"."ca_county" = "ws2"."ca_county"
+  AND "ws2"."d_qoy" = 2
+  AND "ws2"."d_year" = 2001
   AND CASE
     WHEN "ws1"."web_sales" > 0
     THEN "ws2"."web_sales" / "ws1"."web_sales"
@@ -3951,10 +4013,10 @@ WITH "catalog_sales_2" AS (
 SELECT
   SUM("catalog_sales"."cs_ext_discount_amt") AS "excess discount amount"
 FROM "catalog_sales_2" AS "catalog_sales"
-JOIN "item" AS "item"
-  ON "item"."i_item_sk" = "catalog_sales"."cs_item_sk" AND "item"."i_manufact_id" = 610
 JOIN "date_dim_2" AS "date_dim"
   ON "date_dim"."d_date_sk" = "catalog_sales"."cs_sold_date_sk"
+JOIN "item" AS "item"
+  ON "item"."i_item_sk" = "catalog_sales"."cs_item_sk" AND "item"."i_manufact_id" = 610
 LEFT JOIN "_u_0" AS "_u_0"
   ON "_u_0"."_u_1" = "item"."i_item_sk"
 WHERE
@@ -4028,7 +4090,14 @@ FROM   (SELECT *
 GROUP  BY i_manufact_id
 ORDER  BY total_sales
 LIMIT 100;
-WITH "date_dim_2" AS (
+WITH "customer_address_2" AS (
+  SELECT
+    "customer_address"."ca_address_sk" AS "ca_address_sk",
+    "customer_address"."ca_gmt_offset" AS "ca_gmt_offset"
+  FROM "customer_address" AS "customer_address"
+  WHERE
+    "customer_address"."ca_gmt_offset" = -5
+), "date_dim_2" AS (
   SELECT
     "date_dim"."d_date_sk" AS "d_date_sk",
     "date_dim"."d_year" AS "d_year",
@@ -4036,13 +4105,6 @@ WITH "date_dim_2" AS (
   FROM "date_dim" AS "date_dim"
   WHERE
     "date_dim"."d_moy" = 3 AND "date_dim"."d_year" = 1999
-), "customer_address_2" AS (
-  SELECT
-    "customer_address"."ca_address_sk" AS "ca_address_sk",
-    "customer_address"."ca_gmt_offset" AS "ca_gmt_offset"
-  FROM "customer_address" AS "customer_address"
-  WHERE
-    "customer_address"."ca_gmt_offset" = -5
 ), "item_2" AS (
   SELECT
     "item"."i_item_sk" AS "i_item_sk",
@@ -4061,10 +4123,10 @@ WITH "date_dim_2" AS (
     "item"."i_manufact_id" AS "i_manufact_id",
     SUM("store_sales"."ss_ext_sales_price") AS "total_sales"
   FROM "store_sales" AS "store_sales"
-  JOIN "date_dim_2" AS "date_dim"
-    ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
   JOIN "customer_address_2" AS "customer_address"
     ON "store_sales"."ss_addr_sk" = "customer_address"."ca_address_sk"
+  JOIN "date_dim_2" AS "date_dim"
+    ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
   JOIN "item_2" AS "item"
     ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
   LEFT JOIN "_u_0" AS "_u_0"
@@ -4078,10 +4140,10 @@ WITH "date_dim_2" AS (
     "item"."i_manufact_id" AS "i_manufact_id",
     SUM("catalog_sales"."cs_ext_sales_price") AS "total_sales"
   FROM "catalog_sales" AS "catalog_sales"
-  JOIN "date_dim_2" AS "date_dim"
-    ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk"
   JOIN "customer_address_2" AS "customer_address"
     ON "catalog_sales"."cs_bill_addr_sk" = "customer_address"."ca_address_sk"
+  JOIN "date_dim_2" AS "date_dim"
+    ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk"
   JOIN "item_2" AS "item"
     ON "catalog_sales"."cs_item_sk" = "item"."i_item_sk"
   LEFT JOIN "_u_0" AS "_u_1"
@@ -4095,10 +4157,10 @@ WITH "date_dim_2" AS (
     "item"."i_manufact_id" AS "i_manufact_id",
     SUM("web_sales"."ws_ext_sales_price") AS "total_sales"
   FROM "web_sales" AS "web_sales"
-  JOIN "date_dim_2" AS "date_dim"
-    ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk"
   JOIN "customer_address_2" AS "customer_address"
     ON "web_sales"."ws_bill_addr_sk" = "customer_address"."ca_address_sk"
+  JOIN "date_dim_2" AS "date_dim"
+    ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk"
   JOIN "item_2" AS "item"
     ON "web_sales"."ws_item_sk" = "item"."i_item_sk"
   LEFT JOIN "_u_0" AS "_u_2"
@@ -4203,9 +4265,6 @@ WITH "dn" AS (
         "date_dim"."d_dom" <= 3 AND "date_dim"."d_dom" >= 1
       )
     )
-  JOIN "store" AS "store"
-    ON "store"."s_county" IN ('Williamson County', 'Williamson County', 'Williamson County', 'Williamson County', 'Williamson County', 'Williamson County', 'Williamson County', 'Williamson County')
-    AND "store_sales"."ss_store_sk" = "store"."s_store_sk"
   JOIN "household_demographics" AS "household_demographics"
     ON (
       "household_demographics"."hd_buy_potential" = '>10000'
@@ -4218,6 +4277,9 @@ WITH "dn" AS (
       THEN "household_demographics"."hd_dep_count" / "household_demographics"."hd_vehicle_count"
       ELSE NULL
     END > 1.2
+  JOIN "store" AS "store"
+    ON "store"."s_county" IN ('Williamson County', 'Williamson County', 'Williamson County', 'Williamson County', 'Williamson County', 'Williamson County', 'Williamson County', 'Williamson County')
+    AND "store_sales"."ss_store_sk" = "store"."s_store_sk"
   GROUP BY
     "store_sales"."ss_ticket_number",
     "store_sales"."ss_customer_sk"
@@ -4359,7 +4421,7 @@ WITH "date_dim_2" AS (
     "catalog_sales"."cs_ship_customer_sk"
 )
 SELECT
-  "customer_address"."ca_state" AS "ca_state",
+  "ca"."ca_state" AS "ca_state",
   "customer_demographics"."cd_gender" AS "cd_gender",
   "customer_demographics"."cd_marital_status" AS "cd_marital_status",
   "customer_demographics"."cd_dep_count" AS "cd_dep_count",
@@ -4377,24 +4439,24 @@ SELECT
   STDDEV_SAMP("customer_demographics"."cd_dep_college_count") AS "_col_15",
   AVG("customer_demographics"."cd_dep_college_count") AS "_col_16",
   MAX("customer_demographics"."cd_dep_college_count") AS "_col_17"
-FROM "customer" AS "customer"
-JOIN "customer_address" AS "customer_address"
-  ON "customer"."c_current_addr_sk" = "customer_address"."ca_address_sk"
-JOIN "customer_demographics" AS "customer_demographics"
-  ON "customer_demographics"."cd_demo_sk" = "customer"."c_current_cdemo_sk"
+FROM "customer" AS "c"
 LEFT JOIN "_u_0" AS "_u_0"
-  ON "customer"."c_customer_sk" = "_u_0"."_u_1"
+  ON "c"."c_customer_sk" = "_u_0"."_u_1"
 LEFT JOIN "_u_2" AS "_u_2"
-  ON "customer"."c_customer_sk" = "_u_2"."_u_3"
+  ON "c"."c_customer_sk" = "_u_2"."_u_3"
 LEFT JOIN "_u_4" AS "_u_4"
-  ON "customer"."c_customer_sk" = "_u_4"."_u_5"
+  ON "c"."c_customer_sk" = "_u_4"."_u_5"
+JOIN "customer_address" AS "ca"
+  ON "c"."c_current_addr_sk" = "ca"."ca_address_sk"
+JOIN "customer_demographics" AS "customer_demographics"
+  ON "customer_demographics"."cd_demo_sk" = "c"."c_current_cdemo_sk"
 WHERE
   NOT "_u_0"."_u_1" IS NULL
   AND (
     NOT "_u_2"."_u_3" IS NULL OR NOT "_u_4"."_u_5" IS NULL
   )
 GROUP BY
-  "customer_address"."ca_state",
+  "ca"."ca_state",
   "customer_demographics"."cd_gender",
   "customer_demographics"."cd_marital_status",
   "customer_demographics"."cd_dep_count",
@@ -4449,9 +4511,8 @@ SELECT
   GROUPING("item"."i_category") + GROUPING("item"."i_class") AS "lochierarchy",
   RANK() OVER (PARTITION BY GROUPING("item"."i_category") + GROUPING("item"."i_class"), CASE WHEN GROUPING("item"."i_class") = 0 THEN "item"."i_category" END ORDER BY SUM("store_sales"."ss_net_profit") / SUM("store_sales"."ss_ext_sales_price")) AS "rank_within_parent"
 FROM "store_sales" AS "store_sales"
-JOIN "date_dim" AS "date_dim"
-  ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk"
-  AND "date_dim"."d_year" = 2000
+JOIN "date_dim" AS "d1"
+  ON "d1"."d_date_sk" = "store_sales"."ss_sold_date_sk" AND "d1"."d_year" = 2000
 JOIN "item" AS "item"
   ON "item"."i_item_sk" = "store_sales"."ss_item_sk"
 JOIN "store" AS "store"
@@ -4497,6 +4558,8 @@ SELECT
   "item"."i_item_desc" AS "i_item_desc",
   "item"."i_current_price" AS "i_current_price"
 FROM "item" AS "item"
+JOIN "catalog_sales" AS "catalog_sales"
+  ON "catalog_sales"."cs_item_sk" = "item"."i_item_sk"
 JOIN "inventory" AS "inventory"
   ON "inventory"."inv_item_sk" = "item"."i_item_sk"
   AND "inventory"."inv_quantity_on_hand" <= 500
@@ -4505,8 +4568,6 @@ JOIN "date_dim" AS "date_dim"
   ON "date_dim"."d_date_sk" = "inventory"."inv_date_sk"
   AND CAST("date_dim"."d_date" AS DATE) <= CAST('1999-05-05' AS DATE)
   AND CAST("date_dim"."d_date" AS DATE) >= CAST('1999-03-06' AS DATE)
-JOIN "catalog_sales" AS "catalog_sales"
-  ON "catalog_sales"."cs_item_sk" = "item"."i_item_sk"
 WHERE
   "item"."i_current_price" <= 50
   AND "item"."i_current_price" >= 20
@@ -4553,7 +4614,13 @@ FROM   (SELECT DISTINCT c_last_name,
                AND web_sales.ws_bill_customer_sk = customer.c_customer_sk
                AND d_month_seq BETWEEN 1188 AND 1188 + 11) hot_cust
 LIMIT 100;
-WITH "date_dim_2" AS (
+WITH "customer_2" AS (
+  SELECT
+    "customer"."c_customer_sk" AS "c_customer_sk",
+    "customer"."c_first_name" AS "c_first_name",
+    "customer"."c_last_name" AS "c_last_name"
+  FROM "customer" AS "customer"
+), "date_dim_2" AS (
   SELECT
     "date_dim"."d_date_sk" AS "d_date_sk",
     "date_dim"."d_date" AS "d_date",
@@ -4561,42 +4628,36 @@ WITH "date_dim_2" AS (
   FROM "date_dim" AS "date_dim"
   WHERE
     "date_dim"."d_month_seq" <= 1199 AND "date_dim"."d_month_seq" >= 1188
-), "customer_2" AS (
-  SELECT
-    "customer"."c_customer_sk" AS "c_customer_sk",
-    "customer"."c_first_name" AS "c_first_name",
-    "customer"."c_last_name" AS "c_last_name"
-  FROM "customer" AS "customer"
 ), "cte" AS (
   SELECT DISTINCT
     "customer"."c_last_name" AS "c_last_name",
     "customer"."c_first_name" AS "c_first_name",
     "date_dim"."d_date" AS "d_date"
   FROM "store_sales" AS "store_sales"
-  JOIN "date_dim_2" AS "date_dim"
-    ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
   JOIN "customer_2" AS "customer"
     ON "store_sales"."ss_customer_sk" = "customer"."c_customer_sk"
+  JOIN "date_dim_2" AS "date_dim"
+    ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
 ), "cte_2" AS (
   SELECT DISTINCT
     "customer"."c_last_name" AS "c_last_name",
     "customer"."c_first_name" AS "c_first_name",
     "date_dim"."d_date" AS "d_date"
   FROM "catalog_sales" AS "catalog_sales"
-  JOIN "date_dim_2" AS "date_dim"
-    ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk"
   JOIN "customer_2" AS "customer"
     ON "catalog_sales"."cs_bill_customer_sk" = "customer"."c_customer_sk"
+  JOIN "date_dim_2" AS "date_dim"
+    ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk"
 ), "cte_3" AS (
   SELECT DISTINCT
     "customer"."c_last_name" AS "c_last_name",
     "customer"."c_first_name" AS "c_first_name",
     "date_dim"."d_date" AS "d_date"
   FROM "web_sales" AS "web_sales"
-  JOIN "date_dim_2" AS "date_dim"
-    ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk"
   JOIN "customer_2" AS "customer"
     ON "web_sales"."ws_bill_customer_sk" = "customer"."c_customer_sk"
+  JOIN "date_dim_2" AS "date_dim"
+    ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk"
 ), "cte_4" AS (
   SELECT
     "cte_2"."c_last_name" AS "c_last_name",
@@ -4695,12 +4756,12 @@ WITH "foo" AS (
     STDDEV_SAMP("inventory"."inv_quantity_on_hand") AS "stdev",
     AVG("inventory"."inv_quantity_on_hand") AS "mean"
   FROM "inventory" AS "inventory"
+  JOIN "date_dim" AS "date_dim"
+    ON "date_dim"."d_year" = 2002 AND "inventory"."inv_date_sk" = "date_dim"."d_date_sk"
   JOIN "item" AS "item"
     ON "inventory"."inv_item_sk" = "item"."i_item_sk"
   JOIN "warehouse" AS "warehouse"
     ON "inventory"."inv_warehouse_sk" = "warehouse"."w_warehouse_sk"
-  JOIN "date_dim" AS "date_dim"
-    ON "date_dim"."d_year" = 2002 AND "inventory"."inv_date_sk" = "date_dim"."d_date_sk"
   GROUP BY
     "warehouse"."w_warehouse_name",
     "warehouse"."w_warehouse_sk",
@@ -4803,16 +4864,16 @@ FROM "catalog_sales" AS "catalog_sales"
 LEFT JOIN "catalog_returns" AS "catalog_returns"
   ON "catalog_sales"."cs_item_sk" = "catalog_returns"."cr_item_sk"
   AND "catalog_sales"."cs_order_number" = "catalog_returns"."cr_order_number"
-JOIN "warehouse" AS "warehouse"
-  ON "catalog_sales"."cs_warehouse_sk" = "warehouse"."w_warehouse_sk"
-JOIN "item" AS "item"
-  ON "item"."i_current_price" <= 1.49
-  AND "item"."i_current_price" >= 0.99
-  AND "item"."i_item_sk" = "catalog_sales"."cs_item_sk"
 JOIN "date_dim" AS "date_dim"
   ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk"
   AND CAST("date_dim"."d_date" AS DATE) <= CAST('2002-07-01' AS DATE)
   AND CAST("date_dim"."d_date" AS DATE) >= CAST('2002-05-02' AS DATE)
+JOIN "item" AS "item"
+  ON "item"."i_current_price" <= 1.49
+  AND "item"."i_current_price" >= 0.99
+  AND "item"."i_item_sk" = "catalog_sales"."cs_item_sk"
+JOIN "warehouse" AS "warehouse"
+  ON "catalog_sales"."cs_warehouse_sk" = "warehouse"."w_warehouse_sk"
 GROUP BY
   "warehouse"."w_state",
   "item"."i_item_id"
@@ -4890,7 +4951,7 @@ WHERE  i_manufact_id BETWEEN 765 AND 765 + 40
 ORDER  BY i_product_name
 LIMIT 100;
 SELECT DISTINCT
-  "i1"."i_product_name" AS "_col_0"
+  "i1"."i_product_name" AS "i_product_name"
 FROM "item" AS "i1"
 WHERE
   "i1"."i_manufact_id" <= 805
@@ -5035,24 +5096,24 @@ ORDER  BY Sum(ss_ext_sales_price) DESC,
           item.i_category
 LIMIT 100;
 SELECT
-  "date_dim"."d_year" AS "d_year",
+  "dt"."d_year" AS "d_year",
   "item"."i_category_id" AS "i_category_id",
   "item"."i_category" AS "i_category",
   SUM("store_sales"."ss_ext_sales_price") AS "_col_3"
-FROM "date_dim" AS "date_dim"
+FROM "date_dim" AS "dt"
 JOIN "store_sales" AS "store_sales"
-  ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk"
+  ON "dt"."d_date_sk" = "store_sales"."ss_sold_date_sk"
 JOIN "item" AS "item"
   ON "item"."i_manager_id" = 1 AND "store_sales"."ss_item_sk" = "item"."i_item_sk"
 WHERE
-  "date_dim"."d_moy" = 12 AND "date_dim"."d_year" = 2000
+  "dt"."d_moy" = 12 AND "dt"."d_year" = 2000
 GROUP BY
-  "date_dim"."d_year",
+  "dt"."d_year",
   "item"."i_category_id",
   "item"."i_category"
 ORDER BY
   SUM("store_sales"."ss_ext_sales_price") DESC,
-  "date_dim"."d_year",
+  "dt"."d_year",
   "item"."i_category_id",
   "item"."i_category"
 LIMIT 100;
@@ -5278,11 +5339,6 @@ WITH "_u_0" AS (
     "v2"."item_sk" AS "item_sk",
     RANK() OVER (ORDER BY "v2"."rank_col" DESC) AS "rnk"
   FROM "v2" AS "v2"
-), "i1" AS (
-  SELECT
-    "item"."i_item_sk" AS "i_item_sk",
-    "item"."i_product_name" AS "i_product_name"
-  FROM "item" AS "item"
 )
 SELECT
   "v11"."rnk" AS "rnk",
@@ -5291,9 +5347,9 @@ SELECT
 FROM "v11" AS "v11"
 JOIN "v21" AS "v21"
   ON "v11"."rnk" = "v21"."rnk" AND "v21"."rnk" < 11
-JOIN "i1" AS "i1"
+JOIN "item" AS "i1"
   ON "i1"."i_item_sk" = "v11"."item_sk"
-JOIN "i1" AS "i2"
+JOIN "item" AS "i2"
   ON "i2"."i_item_sk" = "v21"."item_sk"
 WHERE
   "v11"."rnk" < 11
@@ -5347,8 +5403,6 @@ SELECT
 FROM "web_sales" AS "web_sales"
 JOIN "customer" AS "customer"
   ON "web_sales"."ws_bill_customer_sk" = "customer"."c_customer_sk"
-JOIN "customer_address" AS "customer_address"
-  ON "customer"."c_current_addr_sk" = "customer_address"."ca_address_sk"
 JOIN "date_dim" AS "date_dim"
   ON "date_dim"."d_qoy" = 1
   AND "date_dim"."d_year" = 2000
@@ -5357,6 +5411,8 @@ JOIN "item" AS "item"
   ON "web_sales"."ws_item_sk" = "item"."i_item_sk"
 LEFT JOIN "_u_0" AS "_u_0"
   ON "item"."i_item_id" = "_u_0"."i_item_id"
+JOIN "customer_address" AS "customer_address"
+  ON "customer"."c_current_addr_sk" = "customer_address"."ca_address_sk"
 WHERE
   NOT "_u_0"."i_item_id" IS NULL
   OR SUBSTR("customer_address"."ca_zip", 1, 5) IN ('85669', '86197', '88274', '83405', '86475', '85392', '85460', '80348', '81792')
@@ -5414,12 +5470,7 @@ ORDER  BY c_last_name,
           bought_city,
           ss_ticket_number
 LIMIT 100;
-WITH "customer_address_2" AS (
-  SELECT
-    "customer_address"."ca_address_sk" AS "ca_address_sk",
-    "customer_address"."ca_city" AS "ca_city"
-  FROM "customer_address" AS "customer_address"
-), "dn" AS (
+WITH "dn" AS (
   SELECT
     "store_sales"."ss_ticket_number" AS "ss_ticket_number",
     "store_sales"."ss_customer_sk" AS "ss_customer_sk",
@@ -5427,21 +5478,21 @@ WITH "customer_address_2" AS (
     SUM("store_sales"."ss_coupon_amt") AS "amt",
     SUM("store_sales"."ss_net_profit") AS "profit"
   FROM "store_sales" AS "store_sales"
+  JOIN "customer_address" AS "customer_address"
+    ON "store_sales"."ss_addr_sk" = "customer_address"."ca_address_sk"
   JOIN "date_dim" AS "date_dim"
     ON "date_dim"."d_dow" IN (6, 0)
     AND "date_dim"."d_year" IN (2000, 2001, 2002)
     AND "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
-  JOIN "store" AS "store"
-    ON "store"."s_city" IN ('Midway', 'Fairview', 'Fairview', 'Fairview', 'Fairview')
-    AND "store_sales"."ss_store_sk" = "store"."s_store_sk"
   JOIN "household_demographics" AS "household_demographics"
     ON (
       "household_demographics"."hd_dep_count" = 6
       OR "household_demographics"."hd_vehicle_count" = 0
     )
     AND "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk"
-  JOIN "customer_address_2" AS "customer_address"
-    ON "store_sales"."ss_addr_sk" = "customer_address"."ca_address_sk"
+  JOIN "store" AS "store"
+    ON "store"."s_city" IN ('Midway', 'Fairview', 'Fairview', 'Fairview', 'Fairview')
+    AND "store_sales"."ss_store_sk" = "store"."s_store_sk"
   GROUP BY
     "store_sales"."ss_ticket_number",
     "store_sales"."ss_customer_sk",
@@ -5457,11 +5508,11 @@ SELECT
   "dn"."amt" AS "amt",
   "dn"."profit" AS "profit"
 FROM "dn" AS "dn"
-JOIN "customer_address_2" AS "current_addr"
-  ON "current_addr"."ca_city" <> "dn"."bought_city"
 JOIN "customer" AS "customer"
-  ON "customer"."c_current_addr_sk" = "current_addr"."ca_address_sk"
-  AND "dn"."ss_customer_sk" = "customer"."c_customer_sk"
+  ON "dn"."ss_customer_sk" = "customer"."c_customer_sk"
+JOIN "customer_address" AS "current_addr"
+  ON "current_addr"."ca_city" <> "dn"."bought_city"
+  AND "customer"."c_current_addr_sk" = "current_addr"."ca_address_sk"
 ORDER BY
   "c_last_name",
   "c_first_name",
@@ -5655,30 +5706,6 @@ WHERE  s_store_sk = ss_store_sk
 SELECT
   SUM("store_sales"."ss_quantity") AS "_col_0"
 FROM "store_sales" AS "store_sales"
-JOIN "store" AS "store"
-  ON "store"."s_store_sk" = "store_sales"."ss_store_sk"
-JOIN "customer_demographics" AS "customer_demographics"
-  ON (
-    "customer_demographics"."cd_demo_sk" = "store_sales"."ss_cdemo_sk"
-    AND "customer_demographics"."cd_education_status" = '2 yr Degree'
-    AND "customer_demographics"."cd_marital_status" = 'D'
-    AND "store_sales"."ss_sales_price" <= 200.00
-    AND "store_sales"."ss_sales_price" >= 150.00
-  )
-  OR (
-    "customer_demographics"."cd_demo_sk" = "store_sales"."ss_cdemo_sk"
-    AND "customer_demographics"."cd_education_status" = 'Advanced Degree'
-    AND "customer_demographics"."cd_marital_status" = 'M'
-    AND "store_sales"."ss_sales_price" <= 100.00
-    AND "store_sales"."ss_sales_price" >= 50.00
-  )
-  OR (
-    "customer_demographics"."cd_demo_sk" = "store_sales"."ss_cdemo_sk"
-    AND "customer_demographics"."cd_education_status" = 'Secondary'
-    AND "customer_demographics"."cd_marital_status" = 'W'
-    AND "store_sales"."ss_sales_price" <= 150.00
-    AND "store_sales"."ss_sales_price" >= 100.00
-  )
 JOIN "customer_address" AS "customer_address"
   ON (
     "customer_address"."ca_country" = 'United States'
@@ -5701,9 +5728,33 @@ JOIN "customer_address" AS "customer_address"
     AND "store_sales"."ss_net_profit" <= 2000
     AND "store_sales"."ss_net_profit" >= 0
   )
+JOIN "customer_demographics" AS "customer_demographics"
+  ON (
+    "customer_demographics"."cd_demo_sk" = "store_sales"."ss_cdemo_sk"
+    AND "customer_demographics"."cd_education_status" = '2 yr Degree'
+    AND "customer_demographics"."cd_marital_status" = 'D'
+    AND "store_sales"."ss_sales_price" <= 200.00
+    AND "store_sales"."ss_sales_price" >= 150.00
+  )
+  OR (
+    "customer_demographics"."cd_demo_sk" = "store_sales"."ss_cdemo_sk"
+    AND "customer_demographics"."cd_education_status" = 'Advanced Degree'
+    AND "customer_demographics"."cd_marital_status" = 'M'
+    AND "store_sales"."ss_sales_price" <= 100.00
+    AND "store_sales"."ss_sales_price" >= 50.00
+  )
+  OR (
+    "customer_demographics"."cd_demo_sk" = "store_sales"."ss_cdemo_sk"
+    AND "customer_demographics"."cd_education_status" = 'Secondary'
+    AND "customer_demographics"."cd_marital_status" = 'W'
+    AND "store_sales"."ss_sales_price" <= 150.00
+    AND "store_sales"."ss_sales_price" >= 100.00
+  )
 JOIN "date_dim" AS "date_dim"
   ON "date_dim"."d_year" = 1999
-  AND "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk";
+  AND "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
+JOIN "store" AS "store"
+  ON "store"."s_store_sk" = "store_sales"."ss_store_sk";
 
 --------------------------------------
 -- TPC-DS 49
@@ -5850,22 +5901,22 @@ WITH "date_dim_2" AS (
     "date_dim"."d_moy" = 12 AND "date_dim"."d_year" = 1999
 ), "in_web" AS (
   SELECT
-    "web_sales"."ws_item_sk" AS "item",
-    CAST(SUM(COALESCE("web_returns"."wr_return_quantity", 0)) AS DECIMAL(15, 4)) / CAST(SUM(COALESCE("web_sales"."ws_quantity", 0)) AS DECIMAL(15, 4)) AS "return_ratio",
-    CAST(SUM(COALESCE("web_returns"."wr_return_amt", 0)) AS DECIMAL(15, 4)) / CAST(SUM(COALESCE("web_sales"."ws_net_paid", 0)) AS DECIMAL(15, 4)) AS "currency_ratio"
-  FROM "web_sales" AS "web_sales"
-  LEFT JOIN "web_returns" AS "web_returns"
-    ON "web_sales"."ws_item_sk" = "web_returns"."wr_item_sk"
-    AND "web_sales"."ws_order_number" = "web_returns"."wr_order_number"
+    "ws"."ws_item_sk" AS "item",
+    CAST(SUM(COALESCE("wr"."wr_return_quantity", 0)) AS DECIMAL(15, 4)) / CAST(SUM(COALESCE("ws"."ws_quantity", 0)) AS DECIMAL(15, 4)) AS "return_ratio",
+    CAST(SUM(COALESCE("wr"."wr_return_amt", 0)) AS DECIMAL(15, 4)) / CAST(SUM(COALESCE("ws"."ws_net_paid", 0)) AS DECIMAL(15, 4)) AS "currency_ratio"
+  FROM "web_sales" AS "ws"
   JOIN "date_dim_2" AS "date_dim"
-    ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk"
+    ON "ws"."ws_sold_date_sk" = "date_dim"."d_date_sk"
+  LEFT JOIN "web_returns" AS "wr"
+    ON "ws"."ws_item_sk" = "wr"."wr_item_sk"
+    AND "ws"."ws_order_number" = "wr"."wr_order_number"
   WHERE
-    "web_returns"."wr_return_amt" > 10000
-    AND "web_sales"."ws_net_paid" > 0
-    AND "web_sales"."ws_net_profit" > 1
-    AND "web_sales"."ws_quantity" > 0
+    "wr"."wr_return_amt" > 10000
+    AND "ws"."ws_net_paid" > 0
+    AND "ws"."ws_net_profit" > 1
+    AND "ws"."ws_quantity" > 0
   GROUP BY
-    "web_sales"."ws_item_sk"
+    "ws"."ws_item_sk"
 ), "web" AS (
   SELECT
     "in_web"."item" AS "item",
@@ -5875,22 +5926,22 @@ WITH "date_dim_2" AS (
   FROM "in_web" AS "in_web"
 ), "in_cat" AS (
   SELECT
-    "catalog_sales"."cs_item_sk" AS "item",
-    CAST(SUM(COALESCE("catalog_returns"."cr_return_quantity", 0)) AS DECIMAL(15, 4)) / CAST(SUM(COALESCE("catalog_sales"."cs_quantity", 0)) AS DECIMAL(15, 4)) AS "return_ratio",
-    CAST(SUM(COALESCE("catalog_returns"."cr_return_amount", 0)) AS DECIMAL(15, 4)) / CAST(SUM(COALESCE("catalog_sales"."cs_net_paid", 0)) AS DECIMAL(15, 4)) AS "currency_ratio"
-  FROM "catalog_sales" AS "catalog_sales"
-  LEFT JOIN "catalog_returns" AS "catalog_returns"
-    ON "catalog_sales"."cs_item_sk" = "catalog_returns"."cr_item_sk"
-    AND "catalog_sales"."cs_order_number" = "catalog_returns"."cr_order_number"
+    "cs"."cs_item_sk" AS "item",
+    CAST(SUM(COALESCE("cr"."cr_return_quantity", 0)) AS DECIMAL(15, 4)) / CAST(SUM(COALESCE("cs"."cs_quantity", 0)) AS DECIMAL(15, 4)) AS "return_ratio",
+    CAST(SUM(COALESCE("cr"."cr_return_amount", 0)) AS DECIMAL(15, 4)) / CAST(SUM(COALESCE("cs"."cs_net_paid", 0)) AS DECIMAL(15, 4)) AS "currency_ratio"
+  FROM "catalog_sales" AS "cs"
+  LEFT JOIN "catalog_returns" AS "cr"
+    ON "cs"."cs_item_sk" = "cr"."cr_item_sk"
+    AND "cs"."cs_order_number" = "cr"."cr_order_number"
   JOIN "date_dim_2" AS "date_dim"
-    ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk"
+    ON "cs"."cs_sold_date_sk" = "date_dim"."d_date_sk"
   WHERE
-    "catalog_returns"."cr_return_amount" > 10000
-    AND "catalog_sales"."cs_net_paid" > 0
-    AND "catalog_sales"."cs_net_profit" > 1
-    AND "catalog_sales"."cs_quantity" > 0
+    "cr"."cr_return_amount" > 10000
+    AND "cs"."cs_net_paid" > 0
+    AND "cs"."cs_net_profit" > 1
+    AND "cs"."cs_quantity" > 0
   GROUP BY
-    "catalog_sales"."cs_item_sk"
+    "cs"."cs_item_sk"
 ), "catalog" AS (
   SELECT
     "in_cat"."item" AS "item",
@@ -5900,22 +5951,22 @@ WITH "date_dim_2" AS (
   FROM "in_cat" AS "in_cat"
 ), "in_store" AS (
   SELECT
-    "store_sales"."ss_item_sk" AS "item",
-    CAST(SUM(COALESCE("store_returns"."sr_return_quantity", 0)) AS DECIMAL(15, 4)) / CAST(SUM(COALESCE("store_sales"."ss_quantity", 0)) AS DECIMAL(15, 4)) AS "return_ratio",
-    CAST(SUM(COALESCE("store_returns"."sr_return_amt", 0)) AS DECIMAL(15, 4)) / CAST(SUM(COALESCE("store_sales"."ss_net_paid", 0)) AS DECIMAL(15, 4)) AS "currency_ratio"
-  FROM "store_sales" AS "store_sales"
-  LEFT JOIN "store_returns" AS "store_returns"
-    ON "store_sales"."ss_item_sk" = "store_returns"."sr_item_sk"
-    AND "store_sales"."ss_ticket_number" = "store_returns"."sr_ticket_number"
+    "sts"."ss_item_sk" AS "item",
+    CAST(SUM(COALESCE("sr"."sr_return_quantity", 0)) AS DECIMAL(15, 4)) / CAST(SUM(COALESCE("sts"."ss_quantity", 0)) AS DECIMAL(15, 4)) AS "return_ratio",
+    CAST(SUM(COALESCE("sr"."sr_return_amt", 0)) AS DECIMAL(15, 4)) / CAST(SUM(COALESCE("sts"."ss_net_paid", 0)) AS DECIMAL(15, 4)) AS "currency_ratio"
+  FROM "store_sales" AS "sts"
   JOIN "date_dim_2" AS "date_dim"
-    ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
+    ON "sts"."ss_sold_date_sk" = "date_dim"."d_date_sk"
+  LEFT JOIN "store_returns" AS "sr"
+    ON "sts"."ss_item_sk" = "sr"."sr_item_sk"
+    AND "sts"."ss_ticket_number" = "sr"."sr_ticket_number"
   WHERE
-    "store_returns"."sr_return_amt" > 10000
-    AND "store_sales"."ss_net_paid" > 0
-    AND "store_sales"."ss_net_profit" > 1
-    AND "store_sales"."ss_quantity" > 0
+    "sr"."sr_return_amt" > 10000
+    AND "sts"."ss_net_paid" > 0
+    AND "sts"."ss_net_profit" > 1
+    AND "sts"."ss_quantity" > 0
   GROUP BY
-    "store_sales"."ss_item_sk"
+    "sts"."ss_item_sk"
 ), "store" AS (
   SELECT
     "in_store"."item" AS "item",
@@ -6098,18 +6149,18 @@ SELECT
     END
   ) AS ">120 days"
 FROM "store_sales" AS "store_sales"
+JOIN "date_dim" AS "d1"
+  ON "store_sales"."ss_sold_date_sk" = "d1"."d_date_sk"
+JOIN "store" AS "store"
+  ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
 JOIN "store_returns" AS "store_returns"
   ON "store_sales"."ss_customer_sk" = "store_returns"."sr_customer_sk"
   AND "store_sales"."ss_item_sk" = "store_returns"."sr_item_sk"
   AND "store_sales"."ss_ticket_number" = "store_returns"."sr_ticket_number"
-JOIN "store" AS "store"
-  ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
-JOIN "date_dim" AS "date_dim"
-  ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
-JOIN "date_dim" AS "date_dim_2"
-  ON "date_dim_2"."d_moy" = 9
-  AND "date_dim_2"."d_year" = 2002
-  AND "store_returns"."sr_returned_date_sk" = "date_dim_2"."d_date_sk"
+JOIN "date_dim" AS "d2"
+  ON "d2"."d_moy" = 9
+  AND "d2"."d_year" = 2002
+  AND "store_returns"."sr_returned_date_sk" = "d2"."d_date_sk"
 GROUP BY
   "store"."s_store_name",
   "store"."s_company_id",
@@ -6286,23 +6337,23 @@ ORDER  BY dt.d_year,
           brand_id
 LIMIT 100;
 SELECT
-  "date_dim"."d_year" AS "d_year",
+  "dt"."d_year" AS "d_year",
   "item"."i_brand_id" AS "brand_id",
   "item"."i_brand" AS "brand",
   SUM("store_sales"."ss_ext_sales_price") AS "ext_price"
-FROM "date_dim" AS "date_dim"
+FROM "date_dim" AS "dt"
 JOIN "store_sales" AS "store_sales"
-  ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk"
+  ON "dt"."d_date_sk" = "store_sales"."ss_sold_date_sk"
 JOIN "item" AS "item"
   ON "item"."i_manager_id" = 1 AND "store_sales"."ss_item_sk" = "item"."i_item_sk"
 WHERE
-  "date_dim"."d_moy" = 11 AND "date_dim"."d_year" = 1999
+  "dt"."d_moy" = 11 AND "dt"."d_year" = 1999
 GROUP BY
-  "date_dim"."d_year",
+  "dt"."d_year",
   "item"."i_brand",
   "item"."i_brand_id"
 ORDER BY
-  "date_dim"."d_year",
+  "dt"."d_year",
   "ext_price" DESC,
   "brand_id"
 LIMIT 100;
@@ -6502,16 +6553,16 @@ WITH "cs_or_ws_sales" AS (
     "customer"."c_customer_sk" AS "c_customer_sk",
     "customer"."c_current_addr_sk" AS "c_current_addr_sk"
   FROM "cs_or_ws_sales" AS "cs_or_ws_sales"
-  JOIN "item" AS "item"
-    ON "cs_or_ws_sales"."item_sk" = "item"."i_item_sk"
-    AND "item"."i_category" = 'Sports'
-    AND "item"."i_class" = 'fitness'
+  JOIN "customer" AS "customer"
+    ON "customer"."c_customer_sk" = "cs_or_ws_sales"."customer_sk"
   JOIN "date_dim" AS "date_dim"
     ON "cs_or_ws_sales"."sold_date_sk" = "date_dim"."d_date_sk"
     AND "date_dim"."d_moy" = 5
     AND "date_dim"."d_year" = 2000
-  JOIN "customer" AS "customer"
-    ON "customer"."c_customer_sk" = "cs_or_ws_sales"."customer_sk"
+  JOIN "item" AS "item"
+    ON "cs_or_ws_sales"."item_sk" = "item"."i_item_sk"
+    AND "item"."i_category" = 'Sports'
+    AND "item"."i_class" = 'fitness'
 ), "_u_0" AS (
   SELECT DISTINCT
     "date_dim"."d_month_seq" + 1 AS "_col_0"
@@ -6528,12 +6579,12 @@ WITH "cs_or_ws_sales" AS (
   SELECT
     SUM("store_sales"."ss_ext_sales_price") AS "revenue"
   FROM "my_customers"
-  CROSS JOIN "date_dim" AS "date_dim"
-  JOIN "store_sales" AS "store_sales"
-    ON "my_customers"."c_customer_sk" = "store_sales"."ss_customer_sk"
-    AND "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
   JOIN "customer_address" AS "customer_address"
     ON "my_customers"."c_current_addr_sk" = "customer_address"."ca_address_sk"
+  JOIN "store_sales" AS "store_sales"
+    ON "my_customers"."c_customer_sk" = "store_sales"."ss_customer_sk"
+  JOIN "date_dim" AS "date_dim"
+    ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
   JOIN "store" AS "store"
     ON "customer_address"."ca_county" = "store"."s_county"
     AND "customer_address"."ca_state" = "store"."s_state"
@@ -6670,7 +6721,14 @@ FROM   (SELECT *
 GROUP  BY i_item_id
 ORDER  BY total_sales
 LIMIT 100;
-WITH "date_dim_2" AS (
+WITH "customer_address_2" AS (
+  SELECT
+    "customer_address"."ca_address_sk" AS "ca_address_sk",
+    "customer_address"."ca_gmt_offset" AS "ca_gmt_offset"
+  FROM "customer_address" AS "customer_address"
+  WHERE
+    "customer_address"."ca_gmt_offset" = -6
+), "date_dim_2" AS (
   SELECT
     "date_dim"."d_date_sk" AS "d_date_sk",
     "date_dim"."d_year" AS "d_year",
@@ -6678,13 +6736,6 @@ WITH "date_dim_2" AS (
   FROM "date_dim" AS "date_dim"
   WHERE
     "date_dim"."d_moy" = 3 AND "date_dim"."d_year" = 1998
-), "customer_address_2" AS (
-  SELECT
-    "customer_address"."ca_address_sk" AS "ca_address_sk",
-    "customer_address"."ca_gmt_offset" AS "ca_gmt_offset"
-  FROM "customer_address" AS "customer_address"
-  WHERE
-    "customer_address"."ca_gmt_offset" = -6
 ), "item_2" AS (
   SELECT
     "item"."i_item_sk" AS "i_item_sk",
@@ -6703,10 +6754,10 @@ WITH "date_dim_2" AS (
     "item"."i_item_id" AS "i_item_id",
     SUM("store_sales"."ss_ext_sales_price") AS "total_sales"
   FROM "store_sales" AS "store_sales"
-  JOIN "date_dim_2" AS "date_dim"
-    ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
   JOIN "customer_address_2" AS "customer_address"
     ON "store_sales"."ss_addr_sk" = "customer_address"."ca_address_sk"
+  JOIN "date_dim_2" AS "date_dim"
+    ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
   JOIN "item_2" AS "item"
     ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
   LEFT JOIN "_u_0" AS "_u_0"
@@ -6720,10 +6771,10 @@ WITH "date_dim_2" AS (
     "item"."i_item_id" AS "i_item_id",
     SUM("catalog_sales"."cs_ext_sales_price") AS "total_sales"
   FROM "catalog_sales" AS "catalog_sales"
-  JOIN "date_dim_2" AS "date_dim"
-    ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk"
   JOIN "customer_address_2" AS "customer_address"
     ON "catalog_sales"."cs_bill_addr_sk" = "customer_address"."ca_address_sk"
+  JOIN "date_dim_2" AS "date_dim"
+    ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk"
   JOIN "item_2" AS "item"
     ON "catalog_sales"."cs_item_sk" = "item"."i_item_sk"
   LEFT JOIN "_u_0" AS "_u_1"
@@ -6737,10 +6788,10 @@ WITH "date_dim_2" AS (
     "item"."i_item_id" AS "i_item_id",
     SUM("web_sales"."ws_ext_sales_price") AS "total_sales"
   FROM "web_sales" AS "web_sales"
-  JOIN "date_dim_2" AS "date_dim"
-    ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk"
   JOIN "customer_address_2" AS "customer_address"
     ON "web_sales"."ws_bill_addr_sk" = "customer_address"."ca_address_sk"
+  JOIN "date_dim_2" AS "date_dim"
+    ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk"
   JOIN "item_2" AS "item"
     ON "web_sales"."ws_item_sk" = "item"."i_item_sk"
   LEFT JOIN "_u_0" AS "_u_2"
@@ -6860,6 +6911,8 @@ WITH "v1" AS (
   FROM "item" AS "item"
   JOIN "catalog_sales" AS "catalog_sales"
     ON "catalog_sales"."cs_item_sk" = "item"."i_item_sk"
+  JOIN "call_center" AS "call_center"
+    ON "call_center"."cc_call_center_sk" = "catalog_sales"."cs_call_center_sk"
   JOIN "date_dim" AS "date_dim"
     ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk"
     AND (
@@ -6874,8 +6927,6 @@ WITH "v1" AS (
     AND (
       "date_dim"."d_year" = 1999 OR "date_dim"."d_year" = 2000 OR "date_dim"."d_year" = 2001
     )
-  JOIN "call_center" AS "call_center"
-    ON "call_center"."cc_call_center_sk" = "catalog_sales"."cs_call_center_sk"
   GROUP BY
     "item"."i_category",
     "item"."i_brand",
@@ -6915,6 +6966,205 @@ ORDER BY
 LIMIT 100;
 
 --------------------------------------
+-- TPC-DS 58
+--------------------------------------
+WITH ss_items
+     AS (SELECT i_item_id               item_id,
+                Sum(ss_ext_sales_price) ss_item_rev
+         FROM   store_sales,
+                item,
+                date_dim
+         WHERE  ss_item_sk = i_item_sk
+                AND d_date IN (SELECT d_date
+                               FROM   date_dim
+                               WHERE  d_week_seq = (SELECT d_week_seq
+                                                    FROM   date_dim
+                                                    WHERE  d_date = '2002-02-25'
+                                                   ))
+                AND ss_sold_date_sk = d_date_sk
+         GROUP  BY i_item_id),
+     cs_items
+     AS (SELECT i_item_id               item_id,
+                Sum(cs_ext_sales_price) cs_item_rev
+         FROM   catalog_sales,
+                item,
+                date_dim
+         WHERE  cs_item_sk = i_item_sk
+                AND d_date IN (SELECT d_date
+                               FROM   date_dim
+                               WHERE  d_week_seq = (SELECT d_week_seq
+                                                    FROM   date_dim
+                                                    WHERE  d_date = '2002-02-25'
+                                                   ))
+                AND cs_sold_date_sk = d_date_sk
+         GROUP  BY i_item_id),
+     ws_items
+     AS (SELECT i_item_id               item_id,
+                Sum(ws_ext_sales_price) ws_item_rev
+         FROM   web_sales,
+                item,
+                date_dim
+         WHERE  ws_item_sk = i_item_sk
+                AND d_date IN (SELECT d_date
+                               FROM   date_dim
+                               WHERE  d_week_seq = (SELECT d_week_seq
+                                                    FROM   date_dim
+                                                    WHERE  d_date = '2002-02-25'
+                                                   ))
+                AND ws_sold_date_sk = d_date_sk
+         GROUP  BY i_item_id)
+SELECT ss_items.item_id,
+               ss_item_rev,
+               ss_item_rev / ( ss_item_rev + cs_item_rev + ws_item_rev ) / 3 *
+               100 ss_dev,
+               cs_item_rev,
+               cs_item_rev / ( ss_item_rev + cs_item_rev + ws_item_rev ) / 3 *
+               100 cs_dev,
+               ws_item_rev,
+               ws_item_rev / ( ss_item_rev + cs_item_rev + ws_item_rev ) / 3 *
+               100 ws_dev,
+               ( ss_item_rev + cs_item_rev + ws_item_rev ) / 3
+               average
+FROM   ss_items,
+       cs_items,
+       ws_items
+WHERE  ss_items.item_id = cs_items.item_id
+       AND ss_items.item_id = ws_items.item_id
+       AND ss_item_rev BETWEEN 0.9 * cs_item_rev AND 1.1 * cs_item_rev
+       AND ss_item_rev BETWEEN 0.9 * ws_item_rev AND 1.1 * ws_item_rev
+       AND cs_item_rev BETWEEN 0.9 * ss_item_rev AND 1.1 * ss_item_rev
+       AND cs_item_rev BETWEEN 0.9 * ws_item_rev AND 1.1 * ws_item_rev
+       AND ws_item_rev BETWEEN 0.9 * ss_item_rev AND 1.1 * ss_item_rev
+       AND ws_item_rev BETWEEN 0.9 * cs_item_rev AND 1.1 * cs_item_rev
+ORDER  BY item_id,
+          ss_item_rev
+LIMIT 100;
+WITH "date_dim_2" AS (
+  SELECT
+    "date_dim"."d_date_sk" AS "d_date_sk",
+    "date_dim"."d_date" AS "d_date"
+  FROM "date_dim" AS "date_dim"
+), "item_2" AS (
+  SELECT
+    "item"."i_item_sk" AS "i_item_sk",
+    "item"."i_item_id" AS "i_item_id"
+  FROM "item" AS "item"
+), "_u_0" AS (
+  SELECT
+    "date_dim"."d_week_seq" AS "d_week_seq"
+  FROM "date_dim" AS "date_dim"
+  WHERE
+    "date_dim"."d_date" = '2002-02-25'
+), "_u_1" AS (
+  SELECT
+    "date_dim"."d_date" AS "d_date"
+  FROM "date_dim" AS "date_dim"
+  JOIN "_u_0" AS "_u_0"
+    ON "date_dim"."d_week_seq" = "_u_0"."d_week_seq"
+  GROUP BY
+    "date_dim"."d_date"
+), "ss_items" AS (
+  SELECT
+    "item"."i_item_id" AS "item_id",
+    SUM("store_sales"."ss_ext_sales_price") AS "ss_item_rev"
+  FROM "store_sales" AS "store_sales"
+  JOIN "date_dim_2" AS "date_dim"
+    ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
+  JOIN "item_2" AS "item"
+    ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
+  LEFT JOIN "_u_1" AS "_u_1"
+    ON "date_dim"."d_date" = "_u_1"."d_date"
+  WHERE
+    NOT "_u_1"."d_date" IS NULL
+  GROUP BY
+    "item"."i_item_id"
+), "_u_3" AS (
+  SELECT
+    "date_dim"."d_date" AS "d_date"
+  FROM "date_dim" AS "date_dim"
+  JOIN "_u_0" AS "_u_2"
+    ON "date_dim"."d_week_seq" = "_u_2"."d_week_seq"
+  GROUP BY
+    "date_dim"."d_date"
+), "cs_items" AS (
+  SELECT
+    "item"."i_item_id" AS "item_id",
+    SUM("catalog_sales"."cs_ext_sales_price") AS "cs_item_rev"
+  FROM "catalog_sales" AS "catalog_sales"
+  JOIN "date_dim_2" AS "date_dim"
+    ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk"
+  JOIN "item_2" AS "item"
+    ON "catalog_sales"."cs_item_sk" = "item"."i_item_sk"
+  LEFT JOIN "_u_3" AS "_u_3"
+    ON "date_dim"."d_date" = "_u_3"."d_date"
+  WHERE
+    NOT "_u_3"."d_date" IS NULL
+  GROUP BY
+    "item"."i_item_id"
+), "_u_5" AS (
+  SELECT
+    "date_dim"."d_date" AS "d_date"
+  FROM "date_dim" AS "date_dim"
+  JOIN "_u_0" AS "_u_4"
+    ON "date_dim"."d_week_seq" = "_u_4"."d_week_seq"
+  GROUP BY
+    "date_dim"."d_date"
+), "ws_items" AS (
+  SELECT
+    "item"."i_item_id" AS "item_id",
+    SUM("web_sales"."ws_ext_sales_price") AS "ws_item_rev"
+  FROM "web_sales" AS "web_sales"
+  JOIN "date_dim_2" AS "date_dim"
+    ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk"
+  JOIN "item_2" AS "item"
+    ON "web_sales"."ws_item_sk" = "item"."i_item_sk"
+  LEFT JOIN "_u_5" AS "_u_5"
+    ON "date_dim"."d_date" = "_u_5"."d_date"
+  WHERE
+    NOT "_u_5"."d_date" IS NULL
+  GROUP BY
+    "item"."i_item_id"
+)
+SELECT
+  "ss_items"."item_id" AS "item_id",
+  "ss_items"."ss_item_rev" AS "ss_item_rev",
+  "ss_items"."ss_item_rev" / (
+    "ss_items"."ss_item_rev" + "cs_items"."cs_item_rev" + "ws_items"."ws_item_rev"
+  ) / 3 * 100 AS "ss_dev",
+  "cs_items"."cs_item_rev" AS "cs_item_rev",
+  "cs_items"."cs_item_rev" / (
+    "ss_items"."ss_item_rev" + "cs_items"."cs_item_rev" + "ws_items"."ws_item_rev"
+  ) / 3 * 100 AS "cs_dev",
+  "ws_items"."ws_item_rev" AS "ws_item_rev",
+  "ws_items"."ws_item_rev" / (
+    "ss_items"."ss_item_rev" + "cs_items"."cs_item_rev" + "ws_items"."ws_item_rev"
+  ) / 3 * 100 AS "ws_dev",
+  (
+    "ss_items"."ss_item_rev" + "cs_items"."cs_item_rev" + "ws_items"."ws_item_rev"
+  ) / 3 AS "average"
+FROM "ss_items"
+JOIN "ws_items"
+  ON "ss_items"."item_id" = "ws_items"."item_id"
+  AND "ss_items"."ss_item_rev" <= 1.1 * "ws_items"."ws_item_rev"
+  AND "ss_items"."ss_item_rev" >= 0.9 * "ws_items"."ws_item_rev"
+  AND "ws_items"."ws_item_rev" <= 1.1 * "ss_items"."ss_item_rev"
+  AND "ws_items"."ws_item_rev" >= 0.9 * "ss_items"."ss_item_rev"
+JOIN "cs_items"
+  ON "cs_items"."cs_item_rev" <= 1.1 * "ss_items"."ss_item_rev"
+  AND "cs_items"."cs_item_rev" <= 1.1 * "ws_items"."ws_item_rev"
+  AND "cs_items"."cs_item_rev" >= 0.9 * "ss_items"."ss_item_rev"
+  AND "cs_items"."cs_item_rev" >= 0.9 * "ws_items"."ws_item_rev"
+  AND "ss_items"."item_id" = "cs_items"."item_id"
+  AND "ss_items"."ss_item_rev" <= 1.1 * "cs_items"."cs_item_rev"
+  AND "ss_items"."ss_item_rev" >= 0.9 * "cs_items"."cs_item_rev"
+  AND "ws_items"."ws_item_rev" <= 1.1 * "cs_items"."cs_item_rev"
+  AND "ws_items"."ws_item_rev" >= 0.9 * "cs_items"."cs_item_rev"
+ORDER BY
+  "item_id",
+  "ss_item_rev"
+LIMIT 100;
+
+--------------------------------------
 -- TPC-DS 59
 --------------------------------------
 WITH wss
@@ -7072,12 +7322,12 @@ WITH "wss" AS (
     "wss"."fri_sales" AS "fri_sales2",
     "wss"."sat_sales" AS "sat_sales2"
   FROM "wss"
+  JOIN "date_dim" AS "d"
+    ON "d"."d_month_seq" <= 1219
+    AND "d"."d_month_seq" >= 1208
+    AND "d"."d_week_seq" = "wss"."d_week_seq"
   JOIN "store" AS "store"
     ON "wss"."ss_store_sk" = "store"."s_store_sk"
-  JOIN "date_dim" AS "date_dim"
-    ON "date_dim"."d_month_seq" <= 1219
-    AND "date_dim"."d_month_seq" >= 1208
-    AND "date_dim"."d_week_seq" = "wss"."d_week_seq"
 )
 SELECT
   "store"."s_store_name" AS "s_store_name1",
@@ -7091,12 +7341,12 @@ SELECT
   "wss"."fri_sales" / "x"."fri_sales2" AS "_col_8",
   "wss"."sat_sales" / "x"."sat_sales2" AS "_col_9"
 FROM "wss"
+JOIN "date_dim" AS "d"
+  ON "d"."d_month_seq" <= 1207
+  AND "d"."d_month_seq" >= 1196
+  AND "d"."d_week_seq" = "wss"."d_week_seq"
 JOIN "store" AS "store"
   ON "wss"."ss_store_sk" = "store"."s_store_sk"
-JOIN "date_dim" AS "date_dim"
-  ON "date_dim"."d_month_seq" <= 1207
-  AND "date_dim"."d_month_seq" >= 1196
-  AND "date_dim"."d_week_seq" = "wss"."d_week_seq"
 JOIN "x" AS "x"
   ON "store"."s_store_id" = "x"."s_store_id2"
   AND "wss"."d_week_seq" = "x"."d_week_seq2" - 52
@@ -7174,7 +7424,14 @@ GROUP  BY i_item_id
 ORDER  BY i_item_id,
           total_sales
 LIMIT 100;
-WITH "date_dim_2" AS (
+WITH "customer_address_2" AS (
+  SELECT
+    "customer_address"."ca_address_sk" AS "ca_address_sk",
+    "customer_address"."ca_gmt_offset" AS "ca_gmt_offset"
+  FROM "customer_address" AS "customer_address"
+  WHERE
+    "customer_address"."ca_gmt_offset" = -6
+), "date_dim_2" AS (
   SELECT
     "date_dim"."d_date_sk" AS "d_date_sk",
     "date_dim"."d_year" AS "d_year",
@@ -7182,13 +7439,6 @@ WITH "date_dim_2" AS (
   FROM "date_dim" AS "date_dim"
   WHERE
     "date_dim"."d_moy" = 8 AND "date_dim"."d_year" = 1999
-), "customer_address_2" AS (
-  SELECT
-    "customer_address"."ca_address_sk" AS "ca_address_sk",
-    "customer_address"."ca_gmt_offset" AS "ca_gmt_offset"
-  FROM "customer_address" AS "customer_address"
-  WHERE
-    "customer_address"."ca_gmt_offset" = -6
 ), "item_2" AS (
   SELECT
     "item"."i_item_sk" AS "i_item_sk",
@@ -7207,10 +7457,10 @@ WITH "date_dim_2" AS (
     "item"."i_item_id" AS "i_item_id",
     SUM("store_sales"."ss_ext_sales_price") AS "total_sales"
   FROM "store_sales" AS "store_sales"
-  JOIN "date_dim_2" AS "date_dim"
-    ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
   JOIN "customer_address_2" AS "customer_address"
     ON "store_sales"."ss_addr_sk" = "customer_address"."ca_address_sk"
+  JOIN "date_dim_2" AS "date_dim"
+    ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
   JOIN "item_2" AS "item"
     ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
   LEFT JOIN "_u_0" AS "_u_0"
@@ -7224,10 +7474,10 @@ WITH "date_dim_2" AS (
     "item"."i_item_id" AS "i_item_id",
     SUM("catalog_sales"."cs_ext_sales_price") AS "total_sales"
   FROM "catalog_sales" AS "catalog_sales"
-  JOIN "date_dim_2" AS "date_dim"
-    ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk"
   JOIN "customer_address_2" AS "customer_address"
     ON "catalog_sales"."cs_bill_addr_sk" = "customer_address"."ca_address_sk"
+  JOIN "date_dim_2" AS "date_dim"
+    ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk"
   JOIN "item_2" AS "item"
     ON "catalog_sales"."cs_item_sk" = "item"."i_item_sk"
   LEFT JOIN "_u_0" AS "_u_1"
@@ -7241,10 +7491,10 @@ WITH "date_dim_2" AS (
     "item"."i_item_id" AS "i_item_id",
     SUM("web_sales"."ws_ext_sales_price") AS "total_sales"
   FROM "web_sales" AS "web_sales"
-  JOIN "date_dim_2" AS "date_dim"
-    ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk"
   JOIN "customer_address_2" AS "customer_address"
     ON "web_sales"."ws_bill_addr_sk" = "customer_address"."ca_address_sk"
+  JOIN "date_dim_2" AS "date_dim"
+    ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk"
   JOIN "item_2" AS "item"
     ON "web_sales"."ws_item_sk" = "item"."i_item_sk"
   LEFT JOIN "_u_0" AS "_u_2"
@@ -7334,13 +7584,11 @@ FROM   (SELECT Sum(ss_ext_sales_price) promotions
 ORDER  BY promotions,
           total
 LIMIT 100;
-WITH "store_2" AS (
+WITH "customer_2" AS (
   SELECT
-    "store"."s_store_sk" AS "s_store_sk",
-    "store"."s_gmt_offset" AS "s_gmt_offset"
-  FROM "store" AS "store"
-  WHERE
-    "store"."s_gmt_offset" = -7
+    "customer"."c_customer_sk" AS "c_customer_sk",
+    "customer"."c_current_addr_sk" AS "c_current_addr_sk"
+  FROM "customer" AS "customer"
 ), "date_dim_2" AS (
   SELECT
     "date_dim"."d_date_sk" AS "d_date_sk",
@@ -7349,11 +7597,20 @@ WITH "store_2" AS (
   FROM "date_dim" AS "date_dim"
   WHERE
     "date_dim"."d_moy" = 12 AND "date_dim"."d_year" = 2001
-), "customer_2" AS (
+), "item_2" AS (
   SELECT
-    "customer"."c_customer_sk" AS "c_customer_sk",
-    "customer"."c_current_addr_sk" AS "c_current_addr_sk"
-  FROM "customer" AS "customer"
+    "item"."i_item_sk" AS "i_item_sk",
+    "item"."i_category" AS "i_category"
+  FROM "item" AS "item"
+  WHERE
+    "item"."i_category" = 'Books'
+), "store_2" AS (
+  SELECT
+    "store"."s_store_sk" AS "s_store_sk",
+    "store"."s_gmt_offset" AS "s_gmt_offset"
+  FROM "store" AS "store"
+  WHERE
+    "store"."s_gmt_offset" = -7
 ), "customer_address_2" AS (
   SELECT
     "customer_address"."ca_address_sk" AS "ca_address_sk",
@@ -7361,19 +7618,16 @@ WITH "store_2" AS (
   FROM "customer_address" AS "customer_address"
   WHERE
     "customer_address"."ca_gmt_offset" = -7
-), "item_2" AS (
-  SELECT
-    "item"."i_item_sk" AS "i_item_sk",
-    "item"."i_category" AS "i_category"
-  FROM "item" AS "item"
-  WHERE
-    "item"."i_category" = 'Books'
 ), "promotional_sales" AS (
   SELECT
     SUM("store_sales"."ss_ext_sales_price") AS "promotions"
   FROM "store_sales" AS "store_sales"
-  JOIN "store_2" AS "store"
-    ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
+  JOIN "customer_2" AS "customer"
+    ON "store_sales"."ss_customer_sk" = "customer"."c_customer_sk"
+  JOIN "date_dim_2" AS "date_dim"
+    ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
+  JOIN "item_2" AS "item"
+    ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
   JOIN "promotion" AS "promotion"
     ON (
       "promotion"."p_channel_dmail" = 'Y'
@@ -7381,28 +7635,24 @@ WITH "store_2" AS (
       OR "promotion"."p_channel_tv" = 'Y'
     )
     AND "store_sales"."ss_promo_sk" = "promotion"."p_promo_sk"
-  JOIN "date_dim_2" AS "date_dim"
-    ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
-  JOIN "customer_2" AS "customer"
-    ON "store_sales"."ss_customer_sk" = "customer"."c_customer_sk"
+  JOIN "store_2" AS "store"
+    ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
   JOIN "customer_address_2" AS "customer_address"
     ON "customer_address"."ca_address_sk" = "customer"."c_current_addr_sk"
-  JOIN "item_2" AS "item"
-    ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
 ), "all_sales" AS (
   SELECT
     SUM("store_sales"."ss_ext_sales_price") AS "total"
   FROM "store_sales" AS "store_sales"
-  JOIN "store_2" AS "store"
-    ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
-  JOIN "date_dim_2" AS "date_dim"
-    ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
   JOIN "customer_2" AS "customer"
     ON "store_sales"."ss_customer_sk" = "customer"."c_customer_sk"
-  JOIN "customer_address_2" AS "customer_address"
-    ON "customer_address"."ca_address_sk" = "customer"."c_current_addr_sk"
+  JOIN "date_dim_2" AS "date_dim"
+    ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
   JOIN "item_2" AS "item"
     ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
+  JOIN "store_2" AS "store"
+    ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
+  JOIN "customer_address_2" AS "customer_address"
+    ON "customer_address"."ca_address_sk" = "customer"."c_current_addr_sk"
 )
 SELECT
   "promotional_sales"."promotions" AS "promotions",
@@ -7505,16 +7755,16 @@ SELECT
     END
   ) AS ">120 days"
 FROM "web_sales" AS "web_sales"
-JOIN "warehouse" AS "warehouse"
-  ON "web_sales"."ws_warehouse_sk" = "warehouse"."w_warehouse_sk"
-JOIN "ship_mode" AS "ship_mode"
-  ON "web_sales"."ws_ship_mode_sk" = "ship_mode"."sm_ship_mode_sk"
-JOIN "web_site" AS "web_site"
-  ON "web_sales"."ws_web_site_sk" = "web_site"."web_site_sk"
 JOIN "date_dim" AS "date_dim"
   ON "date_dim"."d_month_seq" <= 1233
   AND "date_dim"."d_month_seq" >= 1222
   AND "web_sales"."ws_ship_date_sk" = "date_dim"."d_date_sk"
+JOIN "ship_mode" AS "ship_mode"
+  ON "web_sales"."ws_ship_mode_sk" = "ship_mode"."sm_ship_mode_sk"
+JOIN "warehouse" AS "warehouse"
+  ON "web_sales"."ws_warehouse_sk" = "warehouse"."w_warehouse_sk"
+JOIN "web_site" AS "web_site"
+  ON "web_sales"."ws_web_site_sk" = "web_site"."web_site_sk"
 GROUP BY
   SUBSTR("warehouse"."w_warehouse_name", 1, 20),
   "ship_mode"."sm_type",
@@ -7780,33 +8030,6 @@ WITH "cs_ui" AS (
     SUM("catalog_sales"."cs_ext_list_price") > 2 * SUM(
       "catalog_returns"."cr_refunded_cash" + "catalog_returns"."cr_reversed_charge" + "catalog_returns"."cr_store_credit"
     )
-), "d1" AS (
-  SELECT
-    "date_dim"."d_date_sk" AS "d_date_sk",
-    "date_dim"."d_year" AS "d_year"
-  FROM "date_dim" AS "date_dim"
-), "ib2" AS (
-  SELECT
-    "income_band"."ib_income_band_sk" AS "ib_income_band_sk"
-  FROM "income_band" AS "income_band"
-), "hd2" AS (
-  SELECT
-    "household_demographics"."hd_demo_sk" AS "hd_demo_sk",
-    "household_demographics"."hd_income_band_sk" AS "hd_income_band_sk"
-  FROM "household_demographics" AS "household_demographics"
-), "cd1" AS (
-  SELECT
-    "customer_demographics"."cd_demo_sk" AS "cd_demo_sk",
-    "customer_demographics"."cd_marital_status" AS "cd_marital_status"
-  FROM "customer_demographics" AS "customer_demographics"
-), "ad1" AS (
-  SELECT
-    "customer_address"."ca_address_sk" AS "ca_address_sk",
-    "customer_address"."ca_street_number" AS "ca_street_number",
-    "customer_address"."ca_street_name" AS "ca_street_name",
-    "customer_address"."ca_city" AS "ca_city",
-    "customer_address"."ca_zip" AS "ca_zip"
-  FROM "customer_address" AS "customer_address"
 ), "cross_sales" AS (
   SELECT
     "item"."i_product_name" AS "product_name",
@@ -7827,45 +8050,45 @@ WITH "cs_ui" AS (
     SUM("store_sales"."ss_list_price") AS "s2",
     SUM("store_sales"."ss_coupon_amt") AS "s3"
   FROM "store_sales" AS "store_sales"
-  JOIN "store_returns" AS "store_returns"
-    ON "store_sales"."ss_item_sk" = "store_returns"."sr_item_sk"
-    AND "store_sales"."ss_ticket_number" = "store_returns"."sr_ticket_number"
+  CROSS JOIN "income_band" AS "ib2"
+  JOIN "customer_address" AS "ad1"
+    ON "store_sales"."ss_addr_sk" = "ad1"."ca_address_sk"
   JOIN "cs_ui"
     ON "store_sales"."ss_item_sk" = "cs_ui"."cs_item_sk"
-  JOIN "d1" AS "d1"
+  JOIN "date_dim" AS "d1"
     ON "store_sales"."ss_sold_date_sk" = "d1"."d_date_sk"
-  CROSS JOIN "ib2" AS "ib2"
-  JOIN "hd2" AS "hd2"
+  JOIN "household_demographics" AS "hd1"
+    ON "store_sales"."ss_hdemo_sk" = "hd1"."hd_demo_sk"
+  JOIN "household_demographics" AS "hd2"
     ON "hd2"."hd_income_band_sk" = "ib2"."ib_income_band_sk"
+  JOIN "item" AS "item"
+    ON "item"."i_color" IN ('cyan', 'peach', 'blush', 'frosted', 'powder', 'orange')
+    AND "item"."i_current_price" <= 68
+    AND "item"."i_current_price" >= 59
+    AND "store_sales"."ss_item_sk" = "item"."i_item_sk"
+  JOIN "promotion" AS "promotion"
+    ON "store_sales"."ss_promo_sk" = "promotion"."p_promo_sk"
+  JOIN "store" AS "store"
+    ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
+  JOIN "store_returns" AS "store_returns"
+    ON "store_sales"."ss_item_sk" = "store_returns"."sr_item_sk"
+    AND "store_sales"."ss_ticket_number" = "store_returns"."sr_ticket_number"
   JOIN "customer" AS "customer"
     ON "customer"."c_current_hdemo_sk" = "hd2"."hd_demo_sk"
     AND "store_sales"."ss_customer_sk" = "customer"."c_customer_sk"
-  JOIN "d1" AS "d2"
+  JOIN "income_band" AS "ib1"
+    ON "hd1"."hd_income_band_sk" = "ib1"."ib_income_band_sk"
+  JOIN "customer_address" AS "ad2"
+    ON "customer"."c_current_addr_sk" = "ad2"."ca_address_sk"
+  JOIN "customer_demographics" AS "cd2"
+    ON "customer"."c_current_cdemo_sk" = "cd2"."cd_demo_sk"
+  JOIN "date_dim" AS "d2"
     ON "customer"."c_first_sales_date_sk" = "d2"."d_date_sk"
-  JOIN "d1" AS "d3"
+  JOIN "date_dim" AS "d3"
     ON "customer"."c_first_shipto_date_sk" = "d3"."d_date_sk"
-  JOIN "store" AS "store"
-    ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
-  JOIN "cd1" AS "cd1"
-    ON "store_sales"."ss_cdemo_sk" = "cd1"."cd_demo_sk"
-  JOIN "cd1" AS "cd2"
+  JOIN "customer_demographics" AS "cd1"
     ON "cd1"."cd_marital_status" <> "cd2"."cd_marital_status"
-    AND "customer"."c_current_cdemo_sk" = "cd2"."cd_demo_sk"
-  JOIN "promotion" AS "promotion"
-    ON "store_sales"."ss_promo_sk" = "promotion"."p_promo_sk"
-  JOIN "hd2" AS "hd1"
-    ON "store_sales"."ss_hdemo_sk" = "hd1"."hd_demo_sk"
-  JOIN "ad1" AS "ad1"
-    ON "store_sales"."ss_addr_sk" = "ad1"."ca_address_sk"
-  JOIN "ad1" AS "ad2"
-    ON "customer"."c_current_addr_sk" = "ad2"."ca_address_sk"
-  JOIN "ib2" AS "ib1"
-    ON "hd1"."hd_income_band_sk" = "ib1"."ib_income_band_sk"
-  JOIN "item" AS "item"
-    ON "item"."i_color" IN ('cyan', 'peach', 'blush', 'frosted', 'powder', 'orange')
-    AND "item"."i_current_price" <= 68
-    AND "item"."i_current_price" >= 59
-    AND "store_sales"."ss_item_sk" = "item"."i_item_sk"
+    AND "store_sales"."ss_cdemo_sk" = "cd1"."cd_demo_sk"
   GROUP BY
     "item"."i_product_name",
     "item"."i_item_sk",
@@ -7920,6 +8143,107 @@ ORDER BY
   "cs2"."cnt";
 
 --------------------------------------
+-- TPC-DS 65
+--------------------------------------
+SELECT s_store_name,
+               i_item_desc,
+               sc.revenue,
+               i_current_price,
+               i_wholesale_cost,
+               i_brand
+FROM   store,
+       item,
+       (SELECT ss_store_sk,
+               Avg(revenue) AS ave
+        FROM   (SELECT ss_store_sk,
+                       ss_item_sk,
+                       Sum(ss_sales_price) AS revenue
+                FROM   store_sales,
+                       date_dim
+                WHERE  ss_sold_date_sk = d_date_sk
+                       AND d_month_seq BETWEEN 1199 AND 1199 + 11
+                GROUP  BY ss_store_sk,
+                          ss_item_sk) sa
+        GROUP  BY ss_store_sk) sb,
+       (SELECT ss_store_sk,
+               ss_item_sk,
+               Sum(ss_sales_price) AS revenue
+        FROM   store_sales,
+               date_dim
+        WHERE  ss_sold_date_sk = d_date_sk
+               AND d_month_seq BETWEEN 1199 AND 1199 + 11
+        GROUP  BY ss_store_sk,
+                  ss_item_sk) sc
+WHERE  sb.ss_store_sk = sc.ss_store_sk
+       AND sc.revenue <= 0.1 * sb.ave
+       AND s_store_sk = sc.ss_store_sk
+       AND i_item_sk = sc.ss_item_sk
+ORDER  BY s_store_name,
+          i_item_desc
+LIMIT 100;
+WITH "store_sales_2" AS (
+  SELECT
+    "store_sales"."ss_sold_date_sk" AS "ss_sold_date_sk",
+    "store_sales"."ss_item_sk" AS "ss_item_sk",
+    "store_sales"."ss_store_sk" AS "ss_store_sk",
+    "store_sales"."ss_sales_price" AS "ss_sales_price"
+  FROM "store_sales" AS "store_sales"
+), "date_dim_2" AS (
+  SELECT
+    "date_dim"."d_date_sk" AS "d_date_sk",
+    "date_dim"."d_month_seq" AS "d_month_seq"
+  FROM "date_dim" AS "date_dim"
+  WHERE
+    "date_dim"."d_month_seq" <= 1210 AND "date_dim"."d_month_seq" >= 1199
+), "sc" AS (
+  SELECT
+    "store_sales"."ss_store_sk" AS "ss_store_sk",
+    "store_sales"."ss_item_sk" AS "ss_item_sk",
+    SUM("store_sales"."ss_sales_price") AS "revenue"
+  FROM "store_sales_2" AS "store_sales"
+  JOIN "date_dim_2" AS "date_dim"
+    ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
+  GROUP BY
+    "store_sales"."ss_store_sk",
+    "store_sales"."ss_item_sk"
+), "sa" AS (
+  SELECT
+    "store_sales"."ss_store_sk" AS "ss_store_sk",
+    SUM("store_sales"."ss_sales_price") AS "revenue"
+  FROM "store_sales_2" AS "store_sales"
+  JOIN "date_dim_2" AS "date_dim"
+    ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
+  GROUP BY
+    "store_sales"."ss_store_sk",
+    "store_sales"."ss_item_sk"
+), "sb" AS (
+  SELECT
+    "sa"."ss_store_sk" AS "ss_store_sk",
+    AVG("sa"."revenue") AS "ave"
+  FROM "sa" AS "sa"
+  GROUP BY
+    "sa"."ss_store_sk"
+)
+SELECT
+  "store"."s_store_name" AS "s_store_name",
+  "item"."i_item_desc" AS "i_item_desc",
+  "sc"."revenue" AS "revenue",
+  "item"."i_current_price" AS "i_current_price",
+  "item"."i_wholesale_cost" AS "i_wholesale_cost",
+  "item"."i_brand" AS "i_brand"
+FROM "store" AS "store"
+JOIN "sc" AS "sc"
+  ON "store"."s_store_sk" = "sc"."ss_store_sk"
+JOIN "item" AS "item"
+  ON "item"."i_item_sk" = "sc"."ss_item_sk"
+JOIN "sb" AS "sb"
+  ON "sb"."ss_store_sk" = "sc"."ss_store_sk" AND "sc"."revenue" <= 0.1 * "sb"."ave"
+ORDER BY
+  "s_store_name",
+  "i_item_desc"
+LIMIT 100;
+
+--------------------------------------
 -- TPC-DS 66
 --------------------------------------
 SELECT w_warehouse_name,
@@ -8227,17 +8551,7 @@ GROUP  BY w_warehouse_name,
           year1
 ORDER  BY w_warehouse_name
 LIMIT 100;
-WITH "warehouse_2" AS (
-  SELECT
-    "warehouse"."w_warehouse_sk" AS "w_warehouse_sk",
-    "warehouse"."w_warehouse_name" AS "w_warehouse_name",
-    "warehouse"."w_warehouse_sq_ft" AS "w_warehouse_sq_ft",
-    "warehouse"."w_city" AS "w_city",
-    "warehouse"."w_county" AS "w_county",
-    "warehouse"."w_state" AS "w_state",
-    "warehouse"."w_country" AS "w_country"
-  FROM "warehouse" AS "warehouse"
-), "date_dim_2" AS (
+WITH "date_dim_2" AS (
   SELECT
     "date_dim"."d_date_sk" AS "d_date_sk",
     "date_dim"."d_year" AS "d_year",
@@ -8245,6 +8559,13 @@ WITH "warehouse_2" AS (
   FROM "date_dim" AS "date_dim"
   WHERE
     "date_dim"."d_year" = 1998
+), "ship_mode_2" AS (
+  SELECT
+    "ship_mode"."sm_ship_mode_sk" AS "sm_ship_mode_sk",
+    "ship_mode"."sm_carrier" AS "sm_carrier"
+  FROM "ship_mode" AS "ship_mode"
+  WHERE
+    "ship_mode"."sm_carrier" IN ('ZOUROS', 'ZHOU')
 ), "time_dim_2" AS (
   SELECT
     "time_dim"."t_time_sk" AS "t_time_sk",
@@ -8252,13 +8573,16 @@ WITH "warehouse_2" AS (
   FROM "time_dim" AS "time_dim"
   WHERE
     "time_dim"."t_time" <= 36049 AND "time_dim"."t_time" >= 7249
-), "ship_mode_2" AS (
+), "warehouse_2" AS (
   SELECT
-    "ship_mode"."sm_ship_mode_sk" AS "sm_ship_mode_sk",
-    "ship_mode"."sm_carrier" AS "sm_carrier"
-  FROM "ship_mode" AS "ship_mode"
-  WHERE
-    "ship_mode"."sm_carrier" IN ('ZOUROS', 'ZHOU')
+    "warehouse"."w_warehouse_sk" AS "w_warehouse_sk",
+    "warehouse"."w_warehouse_name" AS "w_warehouse_name",
+    "warehouse"."w_warehouse_sq_ft" AS "w_warehouse_sq_ft",
+    "warehouse"."w_city" AS "w_city",
+    "warehouse"."w_county" AS "w_county",
+    "warehouse"."w_state" AS "w_state",
+    "warehouse"."w_country" AS "w_country"
+  FROM "warehouse" AS "warehouse"
 ), "cte" AS (
   SELECT
     "warehouse"."w_warehouse_name" AS "w_warehouse_name",
@@ -8438,14 +8762,14 @@ WITH "warehouse_2" AS (
       END
     ) AS "dec_net"
   FROM "web_sales" AS "web_sales"
-  JOIN "warehouse_2" AS "warehouse"
-    ON "web_sales"."ws_warehouse_sk" = "warehouse"."w_warehouse_sk"
   JOIN "date_dim_2" AS "date_dim"
     ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk"
-  JOIN "time_dim_2" AS "time_dim"
-    ON "web_sales"."ws_sold_time_sk" = "time_dim"."t_time_sk"
   JOIN "ship_mode_2" AS "ship_mode"
     ON "web_sales"."ws_ship_mode_sk" = "ship_mode"."sm_ship_mode_sk"
+  JOIN "time_dim_2" AS "time_dim"
+    ON "web_sales"."ws_sold_time_sk" = "time_dim"."t_time_sk"
+  JOIN "warehouse_2" AS "warehouse"
+    ON "web_sales"."ws_warehouse_sk" = "warehouse"."w_warehouse_sk"
   GROUP BY
     "warehouse"."w_warehouse_name",
     "warehouse"."w_warehouse_sq_ft",
@@ -8633,14 +8957,14 @@ WITH "warehouse_2" AS (
       END
     ) AS "dec_net"
   FROM "catalog_sales" AS "catalog_sales"
-  JOIN "warehouse_2" AS "warehouse"
-    ON "catalog_sales"."cs_warehouse_sk" = "warehouse"."w_warehouse_sk"
   JOIN "date_dim_2" AS "date_dim"
     ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk"
-  JOIN "time_dim_2" AS "time_dim"
-    ON "catalog_sales"."cs_sold_time_sk" = "time_dim"."t_time_sk"
   JOIN "ship_mode_2" AS "ship_mode"
     ON "catalog_sales"."cs_ship_mode_sk" = "ship_mode"."sm_ship_mode_sk"
+  JOIN "time_dim_2" AS "time_dim"
+    ON "catalog_sales"."cs_sold_time_sk" = "time_dim"."t_time_sk"
+  JOIN "warehouse_2" AS "warehouse"
+    ON "catalog_sales"."cs_warehouse_sk" = "warehouse"."w_warehouse_sk"
   GROUP BY
     "warehouse"."w_warehouse_name",
     "warehouse"."w_warehouse_sq_ft",
@@ -8840,10 +9164,10 @@ WITH "dw1" AS (
     ON "date_dim"."d_month_seq" <= 1192
     AND "date_dim"."d_month_seq" >= 1181
     AND "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
-  JOIN "store" AS "store"
-    ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
   JOIN "item" AS "item"
     ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
+  JOIN "store" AS "store"
+    ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
   GROUP BY
   ROLLUP (
     "item"."i_category",
@@ -8939,12 +9263,7 @@ WHERE  ss_customer_sk = c_customer_sk
 ORDER  BY c_last_name,
           ss_ticket_number
 LIMIT 100;
-WITH "customer_address_2" AS (
-  SELECT
-    "customer_address"."ca_address_sk" AS "ca_address_sk",
-    "customer_address"."ca_city" AS "ca_city"
-  FROM "customer_address" AS "customer_address"
-), "dn" AS (
+WITH "dn" AS (
   SELECT
     "store_sales"."ss_ticket_number" AS "ss_ticket_number",
     "store_sales"."ss_customer_sk" AS "ss_customer_sk",
@@ -8953,22 +9272,22 @@ WITH "customer_address_2" AS (
     SUM("store_sales"."ss_ext_list_price") AS "list_price",
     SUM("store_sales"."ss_ext_tax") AS "extended_tax"
   FROM "store_sales" AS "store_sales"
+  JOIN "customer_address" AS "customer_address"
+    ON "store_sales"."ss_addr_sk" = "customer_address"."ca_address_sk"
   JOIN "date_dim" AS "date_dim"
     ON "date_dim"."d_dom" <= 2
     AND "date_dim"."d_dom" >= 1
     AND "date_dim"."d_year" IN (1998, 1999, 2000)
     AND "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
-  JOIN "store" AS "store"
-    ON "store"."s_city" IN ('Fairview', 'Midway')
-    AND "store_sales"."ss_store_sk" = "store"."s_store_sk"
   JOIN "household_demographics" AS "household_demographics"
     ON (
       "household_demographics"."hd_dep_count" = 8
       OR "household_demographics"."hd_vehicle_count" = 3
     )
     AND "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk"
-  JOIN "customer_address_2" AS "customer_address"
-    ON "store_sales"."ss_addr_sk" = "customer_address"."ca_address_sk"
+  JOIN "store" AS "store"
+    ON "store"."s_city" IN ('Fairview', 'Midway')
+    AND "store_sales"."ss_store_sk" = "store"."s_store_sk"
   GROUP BY
     "store_sales"."ss_ticket_number",
     "store_sales"."ss_customer_sk",
@@ -8985,11 +9304,11 @@ SELECT
   "dn"."extended_tax" AS "extended_tax",
   "dn"."list_price" AS "list_price"
 FROM "dn" AS "dn"
-JOIN "customer_address_2" AS "current_addr"
-  ON "current_addr"."ca_city" <> "dn"."bought_city"
 JOIN "customer" AS "customer"
-  ON "customer"."c_current_addr_sk" = "current_addr"."ca_address_sk"
-  AND "dn"."ss_customer_sk" = "customer"."c_customer_sk"
+  ON "dn"."ss_customer_sk" = "customer"."c_customer_sk"
+JOIN "customer_address" AS "current_addr"
+  ON "current_addr"."ca_city" <> "dn"."bought_city"
+  AND "customer"."c_current_addr_sk" = "current_addr"."ca_address_sk"
 ORDER BY
   "c_last_name",
   "ss_ticket_number"
@@ -9111,18 +9430,18 @@ SELECT
   COUNT(*) AS "cnt2",
   "customer_demographics"."cd_credit_rating" AS "cd_credit_rating",
   COUNT(*) AS "cnt3"
-FROM "customer" AS "customer"
-JOIN "customer_address" AS "customer_address"
-  ON "customer"."c_current_addr_sk" = "customer_address"."ca_address_sk"
-  AND "customer_address"."ca_state" IN ('KS', 'AZ', 'NE')
-JOIN "customer_demographics" AS "customer_demographics"
-  ON "customer_demographics"."cd_demo_sk" = "customer"."c_current_cdemo_sk"
+FROM "customer" AS "c"
 LEFT JOIN "_u_0" AS "_u_0"
-  ON "customer"."c_customer_sk" = "_u_0"."_u_1"
+  ON "c"."c_customer_sk" = "_u_0"."_u_1"
 LEFT JOIN "_u_2" AS "_u_2"
-  ON "customer"."c_customer_sk" = "_u_2"."_u_3"
+  ON "c"."c_customer_sk" = "_u_2"."_u_3"
 LEFT JOIN "_u_4" AS "_u_4"
-  ON "customer"."c_customer_sk" = "_u_4"."_u_5"
+  ON "c"."c_customer_sk" = "_u_4"."_u_5"
+JOIN "customer_address" AS "ca"
+  ON "c"."c_current_addr_sk" = "ca"."ca_address_sk"
+  AND "ca"."ca_state" IN ('KS', 'AZ', 'NE')
+JOIN "customer_demographics" AS "customer_demographics"
+  ON "customer_demographics"."cd_demo_sk" = "c"."c_current_cdemo_sk"
 WHERE
   "_u_2"."_u_3" IS NULL AND "_u_4"."_u_5" IS NULL AND NOT "_u_0"."_u_1" IS NULL
 GROUP BY
@@ -9187,22 +9506,17 @@ WITH "store_sales_2" AS (
     "store_sales"."ss_store_sk" AS "ss_store_sk",
     "store_sales"."ss_net_profit" AS "ss_net_profit"
   FROM "store_sales" AS "store_sales"
-), "d1" AS (
-  SELECT
-    "date_dim"."d_date_sk" AS "d_date_sk",
-    "date_dim"."d_month_seq" AS "d_month_seq"
-  FROM "date_dim" AS "date_dim"
-  WHERE
-    "date_dim"."d_month_seq" <= 1211 AND "date_dim"."d_month_seq" >= 1200
 ), "tmp1" AS (
   SELECT
     "store"."s_state" AS "s_state",
     RANK() OVER (PARTITION BY "store"."s_state" ORDER BY SUM("store_sales"."ss_net_profit") DESC) AS "ranking"
   FROM "store_sales_2" AS "store_sales"
+  JOIN "date_dim" AS "date_dim"
+    ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk"
+    AND "date_dim"."d_month_seq" <= 1211
+    AND "date_dim"."d_month_seq" >= 1200
   JOIN "store" AS "store"
     ON "store"."s_store_sk" = "store_sales"."ss_store_sk"
-  JOIN "d1" AS "date_dim"
-    ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk"
   GROUP BY
     "store"."s_state"
 ), "_u_0" AS (
@@ -9221,8 +9535,10 @@ SELECT
   GROUPING("store"."s_state") + GROUPING("store"."s_county") AS "lochierarchy",
   RANK() OVER (PARTITION BY GROUPING("store"."s_state") + GROUPING("store"."s_county"), CASE WHEN GROUPING("store"."s_county") = 0 THEN "store"."s_state" END ORDER BY SUM("store_sales"."ss_net_profit") DESC) AS "rank_within_parent"
 FROM "store_sales_2" AS "store_sales"
-JOIN "d1" AS "d1"
+JOIN "date_dim" AS "d1"
   ON "d1"."d_date_sk" = "store_sales"."ss_sold_date_sk"
+  AND "d1"."d_month_seq" <= 1211
+  AND "d1"."d_month_seq" >= 1200
 JOIN "store" AS "store"
   ON "store"."s_store_sk" = "store_sales"."ss_store_sk"
 LEFT JOIN "_u_0" AS "_u_0"
@@ -9408,42 +9724,42 @@ LIMIT 100;
 SELECT
   "item"."i_item_desc" AS "i_item_desc",
   "warehouse"."w_warehouse_name" AS "w_warehouse_name",
-  "date_dim_2"."d_week_seq" AS "d_week_seq",
+  "d1"."d_week_seq" AS "d_week_seq",
   SUM(CASE WHEN "promotion"."p_promo_sk" IS NULL THEN 1 ELSE 0 END) AS "no_promo",
   SUM(CASE WHEN NOT "promotion"."p_promo_sk" IS NULL THEN 1 ELSE 0 END) AS "promo",
   COUNT(*) AS "total_cnt"
 FROM "catalog_sales" AS "catalog_sales"
-JOIN "inventory" AS "inventory"
-  ON "catalog_sales"."cs_item_sk" = "inventory"."inv_item_sk"
-  AND "inventory"."inv_quantity_on_hand" < "catalog_sales"."cs_quantity"
-JOIN "warehouse" AS "warehouse"
-  ON "warehouse"."w_warehouse_sk" = "inventory"."inv_warehouse_sk"
-JOIN "item" AS "item"
-  ON "item"."i_item_sk" = "catalog_sales"."cs_item_sk"
+LEFT JOIN "catalog_returns" AS "catalog_returns"
+  ON "catalog_returns"."cr_item_sk" = "catalog_sales"."cs_item_sk"
+  AND "catalog_returns"."cr_order_number" = "catalog_sales"."cs_order_number"
 JOIN "customer_demographics" AS "customer_demographics"
   ON "catalog_sales"."cs_bill_cdemo_sk" = "customer_demographics"."cd_demo_sk"
   AND "customer_demographics"."cd_marital_status" = 'M'
+JOIN "date_dim" AS "d3"
+  ON "catalog_sales"."cs_ship_date_sk" = "d3"."d_date_sk"
 JOIN "household_demographics" AS "household_demographics"
   ON "catalog_sales"."cs_bill_hdemo_sk" = "household_demographics"."hd_demo_sk"
   AND "household_demographics"."hd_buy_potential" = '501-1000'
-JOIN "date_dim" AS "date_dim"
-  ON "inventory"."inv_date_sk" = "date_dim"."d_date_sk"
-JOIN "date_dim" AS "date_dim_2"
-  ON "catalog_sales"."cs_sold_date_sk" = "date_dim_2"."d_date_sk"
-  AND "date_dim_2"."d_week_seq" = "date_dim"."d_week_seq"
-  AND "date_dim_2"."d_year" = 2002
-JOIN "date_dim" AS "date_dim_3"
-  ON "catalog_sales"."cs_ship_date_sk" = "date_dim_3"."d_date_sk"
-  AND "date_dim_3"."d_date" > CONCAT("date_dim_2"."d_date", INTERVAL '5' day)
+JOIN "inventory" AS "inventory"
+  ON "catalog_sales"."cs_item_sk" = "inventory"."inv_item_sk"
+  AND "inventory"."inv_quantity_on_hand" < "catalog_sales"."cs_quantity"
+JOIN "item" AS "item"
+  ON "item"."i_item_sk" = "catalog_sales"."cs_item_sk"
 LEFT JOIN "promotion" AS "promotion"
   ON "catalog_sales"."cs_promo_sk" = "promotion"."p_promo_sk"
-LEFT JOIN "catalog_returns" AS "catalog_returns"
-  ON "catalog_returns"."cr_item_sk" = "catalog_sales"."cs_item_sk"
-  AND "catalog_returns"."cr_order_number" = "catalog_sales"."cs_order_number"
+JOIN "date_dim" AS "d2"
+  ON "inventory"."inv_date_sk" = "d2"."d_date_sk"
+JOIN "warehouse" AS "warehouse"
+  ON "warehouse"."w_warehouse_sk" = "inventory"."inv_warehouse_sk"
+JOIN "date_dim" AS "d1"
+  ON "catalog_sales"."cs_sold_date_sk" = "d1"."d_date_sk"
+  AND "d1"."d_week_seq" = "d2"."d_week_seq"
+  AND "d1"."d_year" = 2002
+  AND "d3"."d_date" > CONCAT("d1"."d_date", INTERVAL '5' day)
 GROUP BY
   "item"."i_item_desc",
   "warehouse"."w_warehouse_name",
-  "date_dim_2"."d_week_seq"
+  "d1"."d_week_seq"
 ORDER BY
   "total_cnt" DESC,
   "i_item_desc",
@@ -9503,9 +9819,6 @@ WITH "dj" AS (
     AND "date_dim"."d_dom" >= 1
     AND "date_dim"."d_year" IN (2000, 2001, 2002)
     AND "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
-  JOIN "store" AS "store"
-    ON "store"."s_county" IN ('Williamson County', 'Williamson County', 'Williamson County', 'Williamson County')
-    AND "store_sales"."ss_store_sk" = "store"."s_store_sk"
   JOIN "household_demographics" AS "household_demographics"
     ON (
       "household_demographics"."hd_buy_potential" = '0-500'
@@ -9518,6 +9831,9 @@ WITH "dj" AS (
       THEN "household_demographics"."hd_dep_count" / "household_demographics"."hd_vehicle_count"
       ELSE NULL
     END > 1
+  JOIN "store" AS "store"
+    ON "store"."s_county" IN ('Williamson County', 'Williamson County', 'Williamson County', 'Williamson County')
+    AND "store_sales"."ss_store_sk" = "store"."s_store_sk"
   GROUP BY
     "store_sales"."ss_ticket_number",
     "store_sales"."ss_customer_sk"
@@ -9683,19 +9999,19 @@ SELECT
   "t_s_secyear"."customer_first_name" AS "customer_first_name",
   "t_s_secyear"."customer_last_name" AS "customer_last_name"
 FROM "year_total" AS "t_s_firstyear"
-JOIN "year_total" AS "t_s_secyear"
-  ON "t_s_secyear"."customer_id" = "t_s_firstyear"."customer_id"
-  AND "t_s_secyear"."sale_type" = 's'
-  AND "t_s_secyear"."year1" = 2000
-JOIN "year_total" AS "t_w_secyear"
-  ON "t_s_firstyear"."customer_id" = "t_w_secyear"."customer_id"
-  AND "t_w_secyear"."sale_type" = 'w'
-  AND "t_w_secyear"."year1" = 2000
 JOIN "year_total" AS "t_w_firstyear"
   ON "t_s_firstyear"."customer_id" = "t_w_firstyear"."customer_id"
   AND "t_w_firstyear"."sale_type" = 'w'
   AND "t_w_firstyear"."year1" = 1999
   AND "t_w_firstyear"."year_total" > 0
+JOIN "year_total" AS "t_w_secyear"
+  ON "t_s_firstyear"."customer_id" = "t_w_secyear"."customer_id"
+  AND "t_w_secyear"."sale_type" = 'w'
+  AND "t_w_secyear"."year1" = 2000
+JOIN "year_total" AS "t_s_secyear"
+  ON "t_s_secyear"."customer_id" = "t_s_firstyear"."customer_id"
+  AND "t_s_secyear"."sale_type" = 's'
+  AND "t_s_secyear"."year1" = 2000
   AND CASE
     WHEN "t_w_firstyear"."year_total" > 0
     THEN "t_w_secyear"."year_total" / "t_w_firstyear"."year_total"
@@ -9810,7 +10126,12 @@ WHERE  curr_yr.i_brand_id = prev_yr.i_brand_id
            < 0.9
 ORDER  BY sales_cnt_diff
 LIMIT 100;
-WITH "item_2" AS (
+WITH "date_dim_2" AS (
+  SELECT
+    "date_dim"."d_date_sk" AS "d_date_sk",
+    "date_dim"."d_year" AS "d_year"
+  FROM "date_dim" AS "date_dim"
+), "item_2" AS (
   SELECT
     "item"."i_item_sk" AS "i_item_sk",
     "item"."i_brand_id" AS "i_brand_id",
@@ -9821,11 +10142,6 @@ WITH "item_2" AS (
   FROM "item" AS "item"
   WHERE
     "item"."i_category" = 'Men'
-), "date_dim_2" AS (
-  SELECT
-    "date_dim"."d_date_sk" AS "d_date_sk",
-    "date_dim"."d_year" AS "d_year"
-  FROM "date_dim" AS "date_dim"
 ), "cte_4" AS (
   SELECT
     "date_dim"."d_year" AS "d_year",
@@ -9836,10 +10152,10 @@ WITH "item_2" AS (
     "store_sales"."ss_quantity" - COALESCE("store_returns"."sr_return_quantity", 0) AS "sales_cnt",
     "store_sales"."ss_ext_sales_price" - COALESCE("store_returns"."sr_return_amt", 0.0) AS "sales_amt"
   FROM "store_sales" AS "store_sales"
-  JOIN "item_2" AS "item"
-    ON "item"."i_item_sk" = "store_sales"."ss_item_sk"
   JOIN "date_dim_2" AS "date_dim"
     ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk"
+  JOIN "item_2" AS "item"
+    ON "item"."i_item_sk" = "store_sales"."ss_item_sk"
   LEFT JOIN "store_returns" AS "store_returns"
     ON "store_sales"."ss_item_sk" = "store_returns"."sr_item_sk"
     AND "store_sales"."ss_ticket_number" = "store_returns"."sr_ticket_number"
@@ -9853,10 +10169,10 @@ WITH "item_2" AS (
     "web_sales"."ws_quantity" - COALESCE("web_returns"."wr_return_quantity", 0) AS "sales_cnt",
     "web_sales"."ws_ext_sales_price" - COALESCE("web_returns"."wr_return_amt", 0.0) AS "sales_amt"
   FROM "web_sales" AS "web_sales"
-  JOIN "item_2" AS "item"
-    ON "item"."i_item_sk" = "web_sales"."ws_item_sk"
   JOIN "date_dim_2" AS "date_dim"
     ON "date_dim"."d_date_sk" = "web_sales"."ws_sold_date_sk"
+  JOIN "item_2" AS "item"
+    ON "item"."i_item_sk" = "web_sales"."ws_item_sk"
   LEFT JOIN "web_returns" AS "web_returns"
     ON "web_sales"."ws_item_sk" = "web_returns"."wr_item_sk"
     AND "web_sales"."ws_order_number" = "web_returns"."wr_order_number"
@@ -9870,13 +10186,13 @@ WITH "item_2" AS (
     "catalog_sales"."cs_quantity" - COALESCE("catalog_returns"."cr_return_quantity", 0) AS "sales_cnt",
     "catalog_sales"."cs_ext_sales_price" - COALESCE("catalog_returns"."cr_return_amount", 0.0) AS "sales_amt"
   FROM "catalog_sales" AS "catalog_sales"
-  JOIN "item_2" AS "item"
-    ON "item"."i_item_sk" = "catalog_sales"."cs_item_sk"
-  JOIN "date_dim_2" AS "date_dim"
-    ON "date_dim"."d_date_sk" = "catalog_sales"."cs_sold_date_sk"
   LEFT JOIN "catalog_returns" AS "catalog_returns"
     ON "catalog_sales"."cs_item_sk" = "catalog_returns"."cr_item_sk"
     AND "catalog_sales"."cs_order_number" = "catalog_returns"."cr_order_number"
+  JOIN "date_dim_2" AS "date_dim"
+    ON "date_dim"."d_date_sk" = "catalog_sales"."cs_sold_date_sk"
+  JOIN "item_2" AS "item"
+    ON "item"."i_item_sk" = "catalog_sales"."cs_item_sk"
   UNION
   SELECT
     "cte_4"."d_year" AS "d_year",
@@ -9988,17 +10304,17 @@ ORDER  BY channel,
           d_qoy,
           i_category
 LIMIT 100;
-WITH "item_2" AS (
-  SELECT
-    "item"."i_item_sk" AS "i_item_sk",
-    "item"."i_category" AS "i_category"
-  FROM "item" AS "item"
-), "date_dim_2" AS (
+WITH "date_dim_2" AS (
   SELECT
     "date_dim"."d_date_sk" AS "d_date_sk",
     "date_dim"."d_year" AS "d_year",
     "date_dim"."d_qoy" AS "d_qoy"
   FROM "date_dim" AS "date_dim"
+), "item_2" AS (
+  SELECT
+    "item"."i_item_sk" AS "i_item_sk",
+    "item"."i_category" AS "i_category"
+  FROM "item" AS "item"
 ), "cte_4" AS (
   SELECT
     'web' AS "channel",
@@ -10008,10 +10324,10 @@ WITH "item_2" AS (
     "item"."i_category" AS "i_category",
     "web_sales"."ws_ext_sales_price" AS "ext_sales_price"
   FROM "web_sales" AS "web_sales"
-  JOIN "item_2" AS "item"
-    ON "web_sales"."ws_item_sk" = "item"."i_item_sk"
   JOIN "date_dim_2" AS "date_dim"
     ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk"
+  JOIN "item_2" AS "item"
+    ON "web_sales"."ws_item_sk" = "item"."i_item_sk"
   WHERE
     "web_sales"."ws_ship_hdemo_sk" IS NULL
   UNION ALL
@@ -10023,10 +10339,10 @@ WITH "item_2" AS (
     "item"."i_category" AS "i_category",
     "catalog_sales"."cs_ext_sales_price" AS "ext_sales_price"
   FROM "catalog_sales" AS "catalog_sales"
-  JOIN "item_2" AS "item"
-    ON "catalog_sales"."cs_item_sk" = "item"."i_item_sk"
   JOIN "date_dim_2" AS "date_dim"
     ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk"
+  JOIN "item_2" AS "item"
+    ON "catalog_sales"."cs_item_sk" = "item"."i_item_sk"
   WHERE
     "catalog_sales"."cs_warehouse_sk" IS NULL
 ), "foo" AS (
@@ -10038,10 +10354,10 @@ WITH "item_2" AS (
     "item"."i_category" AS "i_category",
     "store_sales"."ss_ext_sales_price" AS "ext_sales_price"
   FROM "store_sales" AS "store_sales"
-  JOIN "item_2" AS "item"
-    ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
   JOIN "date_dim_2" AS "date_dim"
     ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
+  JOIN "item_2" AS "item"
+    ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
   WHERE
     "store_sales"."ss_hdemo_sk" IS NULL
   UNION ALL
@@ -10426,11 +10742,11 @@ WITH "date_dim_2" AS (
     SUM("web_sales"."ws_wholesale_cost") AS "ws_wc",
     SUM("web_sales"."ws_sales_price") AS "ws_sp"
   FROM "web_sales" AS "web_sales"
+  JOIN "date_dim_2" AS "date_dim"
+    ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk"
   LEFT JOIN "web_returns" AS "web_returns"
     ON "web_returns"."wr_order_number" = "web_sales"."ws_order_number"
     AND "web_sales"."ws_item_sk" = "web_returns"."wr_item_sk"
-  JOIN "date_dim_2" AS "date_dim"
-    ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk"
   WHERE
     "web_returns"."wr_order_number" IS NULL
   GROUP BY
@@ -10466,11 +10782,11 @@ WITH "date_dim_2" AS (
     SUM("store_sales"."ss_wholesale_cost") AS "ss_wc",
     SUM("store_sales"."ss_sales_price") AS "ss_sp"
   FROM "store_sales" AS "store_sales"
+  JOIN "date_dim_2" AS "date_dim"
+    ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
   LEFT JOIN "store_returns" AS "store_returns"
     ON "store_returns"."sr_ticket_number" = "store_sales"."ss_ticket_number"
     AND "store_sales"."ss_item_sk" = "store_returns"."sr_item_sk"
-  JOIN "date_dim_2" AS "date_dim"
-    ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
   WHERE
     "store_returns"."sr_ticket_number" IS NULL
   GROUP BY
@@ -10488,14 +10804,14 @@ SELECT
   COALESCE("ws"."ws_wc", 0) + COALESCE("cs"."cs_wc", 0) AS "other_chan_wholesale_cost",
   COALESCE("ws"."ws_sp", 0) + COALESCE("cs"."cs_sp", 0) AS "other_chan_sales_price"
 FROM "ss"
-LEFT JOIN "ws"
-  ON "ws"."ws_customer_sk" = "ss"."ss_customer_sk"
-  AND "ws"."ws_item_sk" = "ss"."ss_item_sk"
-  AND "ws"."ws_sold_year" = "ss"."ss_sold_year"
 LEFT JOIN "cs"
   ON "cs"."cs_customer_sk" = "ss"."ss_customer_sk"
   AND "cs"."cs_item_sk" = "cs"."cs_item_sk"
   AND "cs"."cs_sold_year" = "ss"."ss_sold_year"
+LEFT JOIN "ws"
+  ON "ws"."ws_customer_sk" = "ss"."ss_customer_sk"
+  AND "ws"."ws_item_sk" = "ss"."ss_item_sk"
+  AND "ws"."ws_sold_year" = "ss"."ss_sold_year"
 WHERE
   "ss"."ss_sold_year" = 1999
   AND COALESCE("cs"."cs_qty", 0) > 0
@@ -10560,16 +10876,16 @@ WITH "ms" AS (
     ON "date_dim"."d_dow" = 1
     AND "date_dim"."d_year" IN (2000, 2001, 2002)
     AND "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
-  JOIN "store" AS "store"
-    ON "store"."s_number_employees" <= 295
-    AND "store"."s_number_employees" >= 200
-    AND "store_sales"."ss_store_sk" = "store"."s_store_sk"
   JOIN "household_demographics" AS "household_demographics"
     ON (
       "household_demographics"."hd_dep_count" = 8
       OR "household_demographics"."hd_vehicle_count" > 4
     )
     AND "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk"
+  JOIN "store" AS "store"
+    ON "store"."s_number_employees" <= 295
+    AND "store"."s_number_employees" >= 200
+    AND "store_sales"."ss_store_sk" = "store"."s_store_sk"
   GROUP BY
     "store_sales"."ss_ticket_number",
     "store_sales"."ss_customer_sk",
@@ -10729,17 +11045,17 @@ WITH "date_dim_2" AS (
     SUM(COALESCE("store_returns"."sr_return_amt", 0)) AS "returns1",
     SUM("store_sales"."ss_net_profit" - COALESCE("store_returns"."sr_net_loss", 0)) AS "profit"
   FROM "store_sales" AS "store_sales"
-  LEFT JOIN "store_returns" AS "store_returns"
-    ON "store_sales"."ss_item_sk" = "store_returns"."sr_item_sk"
-    AND "store_sales"."ss_ticket_number" = "store_returns"."sr_ticket_number"
   JOIN "date_dim_2" AS "date_dim"
     ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
-  JOIN "store" AS "store"
-    ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
   JOIN "item_2" AS "item"
     ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
   JOIN "promotion_2" AS "promotion"
     ON "store_sales"."ss_promo_sk" = "promotion"."p_promo_sk"
+  JOIN "store" AS "store"
+    ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
+  LEFT JOIN "store_returns" AS "store_returns"
+    ON "store_sales"."ss_item_sk" = "store_returns"."sr_item_sk"
+    AND "store_sales"."ss_ticket_number" = "store_returns"."sr_ticket_number"
   GROUP BY
     "store"."s_store_id"
 ), "csr" AS (
@@ -10749,13 +11065,13 @@ WITH "date_dim_2" AS (
     SUM(COALESCE("catalog_returns"."cr_return_amount", 0)) AS "returns1",
     SUM("catalog_sales"."cs_net_profit" - COALESCE("catalog_returns"."cr_net_loss", 0)) AS "profit"
   FROM "catalog_sales" AS "catalog_sales"
+  JOIN "catalog_page" AS "catalog_page"
+    ON "catalog_sales"."cs_catalog_page_sk" = "catalog_page"."cp_catalog_page_sk"
   LEFT JOIN "catalog_returns" AS "catalog_returns"
     ON "catalog_sales"."cs_item_sk" = "catalog_returns"."cr_item_sk"
     AND "catalog_sales"."cs_order_number" = "catalog_returns"."cr_order_number"
   JOIN "date_dim_2" AS "date_dim"
     ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk"
-  JOIN "catalog_page" AS "catalog_page"
-    ON "catalog_sales"."cs_catalog_page_sk" = "catalog_page"."cp_catalog_page_sk"
   JOIN "item_2" AS "item"
     ON "catalog_sales"."cs_item_sk" = "item"."i_item_sk"
   JOIN "promotion_2" AS "promotion"
@@ -10769,17 +11085,17 @@ WITH "date_dim_2" AS (
     SUM(COALESCE("web_returns"."wr_return_amt", 0)) AS "returns1",
     SUM("web_sales"."ws_net_profit" - COALESCE("web_returns"."wr_net_loss", 0)) AS "profit"
   FROM "web_sales" AS "web_sales"
-  LEFT JOIN "web_returns" AS "web_returns"
-    ON "web_sales"."ws_item_sk" = "web_returns"."wr_item_sk"
-    AND "web_sales"."ws_order_number" = "web_returns"."wr_order_number"
   JOIN "date_dim_2" AS "date_dim"
     ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk"
-  JOIN "web_site" AS "web_site"
-    ON "web_sales"."ws_web_site_sk" = "web_site"."web_site_sk"
   JOIN "item_2" AS "item"
     ON "web_sales"."ws_item_sk" = "item"."i_item_sk"
   JOIN "promotion_2" AS "promotion"
     ON "web_sales"."ws_promo_sk" = "promotion"."p_promo_sk"
+  LEFT JOIN "web_returns" AS "web_returns"
+    ON "web_sales"."ws_item_sk" = "web_returns"."wr_item_sk"
+    AND "web_sales"."ws_order_number" = "web_returns"."wr_order_number"
+  JOIN "web_site" AS "web_site"
+    ON "web_sales"."ws_web_site_sk" = "web_site"."web_site_sk"
   GROUP BY
     "web_site"."web_site_id"
 ), "cte_4" AS (
@@ -10896,11 +11212,11 @@ WITH "customer_total_return" AS (
     "customer_address"."ca_state" AS "ctr_state",
     SUM("catalog_returns"."cr_return_amt_inc_tax") AS "ctr_total_return"
   FROM "catalog_returns" AS "catalog_returns"
+  JOIN "customer_address" AS "customer_address"
+    ON "catalog_returns"."cr_returning_addr_sk" = "customer_address"."ca_address_sk"
   JOIN "date_dim" AS "date_dim"
     ON "catalog_returns"."cr_returned_date_sk" = "date_dim"."d_date_sk"
     AND "date_dim"."d_year" = 1999
-  JOIN "customer_address" AS "customer_address"
-    ON "catalog_returns"."cr_returning_addr_sk" = "customer_address"."ca_address_sk"
   GROUP BY
     "catalog_returns"."cr_returning_customer_sk",
     "customer_address"."ca_state"
@@ -10930,13 +11246,13 @@ SELECT
   "customer_address"."ca_location_type" AS "ca_location_type",
   "ctr1"."ctr_total_return" AS "ctr_total_return"
 FROM "customer_total_return" AS "ctr1"
+LEFT JOIN "_u_0" AS "_u_0"
+  ON "ctr1"."ctr_state" = "_u_0"."_u_1"
 JOIN "customer" AS "customer"
   ON "ctr1"."ctr_customer_sk" = "customer"."c_customer_sk"
 JOIN "customer_address" AS "customer_address"
   ON "customer_address"."ca_address_sk" = "customer"."c_current_addr_sk"
   AND "customer_address"."ca_state" = 'TX'
-LEFT JOIN "_u_0" AS "_u_0"
-  ON "ctr1"."ctr_state" = "_u_0"."_u_1"
 WHERE
   "ctr1"."ctr_total_return" > "_u_0"."_col_0"
 ORDER BY
@@ -10992,12 +11308,12 @@ JOIN "inventory" AS "inventory"
   ON "inventory"."inv_item_sk" = "item"."i_item_sk"
   AND "inventory"."inv_quantity_on_hand" <= 500
   AND "inventory"."inv_quantity_on_hand" >= 100
+JOIN "store_sales" AS "store_sales"
+  ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
 JOIN "date_dim" AS "date_dim"
   ON "date_dim"."d_date_sk" = "inventory"."inv_date_sk"
   AND CAST("date_dim"."d_date" AS DATE) <= CAST('1998-06-26' AS DATE)
   AND CAST("date_dim"."d_date" AS DATE) >= CAST('1998-04-27' AS DATE)
-JOIN "store_sales" AS "store_sales"
-  ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
 WHERE
   "item"."i_current_price" <= 93
   AND "item"."i_current_price" >= 63
@@ -11087,16 +11403,16 @@ WHERE  sr_items.item_id = cr_items.item_id
 ORDER  BY sr_items.item_id,
           sr_item_qty
 LIMIT 100;
-WITH "item_2" AS (
-  SELECT
-    "item"."i_item_sk" AS "i_item_sk",
-    "item"."i_item_id" AS "i_item_id"
-  FROM "item" AS "item"
-), "date_dim_2" AS (
+WITH "date_dim_2" AS (
   SELECT
     "date_dim"."d_date_sk" AS "d_date_sk",
     "date_dim"."d_date" AS "d_date"
   FROM "date_dim" AS "date_dim"
+), "item_2" AS (
+  SELECT
+    "item"."i_item_sk" AS "i_item_sk",
+    "item"."i_item_id" AS "i_item_id"
+  FROM "item" AS "item"
 ), "_u_0" AS (
   SELECT
     "date_dim"."d_week_seq" AS "d_week_seq"
@@ -11120,10 +11436,10 @@ WITH "item_2" AS (
     "item"."i_item_id" AS "item_id",
     SUM("store_returns"."sr_return_quantity") AS "sr_item_qty"
   FROM "store_returns" AS "store_returns"
-  JOIN "item_2" AS "item"
-    ON "store_returns"."sr_item_sk" = "item"."i_item_sk"
   JOIN "date_dim_2" AS "date_dim"
     ON "store_returns"."sr_returned_date_sk" = "date_dim"."d_date_sk"
+  JOIN "item_2" AS "item"
+    ON "store_returns"."sr_item_sk" = "item"."i_item_sk"
   LEFT JOIN "_u_1" AS "_u_1"
     ON "date_dim"."d_date" = "_u_1"."d_date"
   WHERE
@@ -11145,10 +11461,10 @@ WITH "item_2" AS (
     "item"."i_item_id" AS "item_id",
     SUM("catalog_returns"."cr_return_quantity") AS "cr_item_qty"
   FROM "catalog_returns" AS "catalog_returns"
-  JOIN "item_2" AS "item"
-    ON "catalog_returns"."cr_item_sk" = "item"."i_item_sk"
   JOIN "date_dim_2" AS "date_dim"
     ON "catalog_returns"."cr_returned_date_sk" = "date_dim"."d_date_sk"
+  JOIN "item_2" AS "item"
+    ON "catalog_returns"."cr_item_sk" = "item"."i_item_sk"
   LEFT JOIN "_u_3" AS "_u_3"
     ON "date_dim"."d_date" = "_u_3"."d_date"
   WHERE
@@ -11170,10 +11486,10 @@ WITH "item_2" AS (
     "item"."i_item_id" AS "item_id",
     SUM("web_returns"."wr_return_quantity") AS "wr_item_qty"
   FROM "web_returns" AS "web_returns"
-  JOIN "item_2" AS "item"
-    ON "web_returns"."wr_item_sk" = "item"."i_item_sk"
   JOIN "date_dim_2" AS "date_dim"
     ON "web_returns"."wr_returned_date_sk" = "date_dim"."d_date_sk"
+  JOIN "item_2" AS "item"
+    ON "web_returns"."wr_item_sk" = "item"."i_item_sk"
   LEFT JOIN "_u_5" AS "_u_5"
     ON "date_dim"."d_date" = "_u_5"."d_date"
   WHERE
@@ -11306,27 +11622,46 @@ ORDER  BY Substr(r_reason_desc, 1, 20),
           Avg(wr_refunded_cash),
           Avg(wr_fee)
 LIMIT 100;
-WITH "cd2" AS (
-  SELECT
-    "customer_demographics"."cd_demo_sk" AS "cd_demo_sk",
-    "customer_demographics"."cd_marital_status" AS "cd_marital_status",
-    "customer_demographics"."cd_education_status" AS "cd_education_status"
-  FROM "customer_demographics" AS "customer_demographics"
-)
 SELECT
   SUBSTR("reason"."r_reason_desc", 1, 20) AS "_col_0",
   AVG("web_sales"."ws_quantity") AS "_col_1",
   AVG("web_returns"."wr_refunded_cash") AS "_col_2",
   AVG("web_returns"."wr_fee") AS "_col_3"
 FROM "web_sales" AS "web_sales"
+JOIN "date_dim" AS "date_dim"
+  ON "date_dim"."d_year" = 2001 AND "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk"
+JOIN "web_page" AS "web_page"
+  ON "web_sales"."ws_web_page_sk" = "web_page"."wp_web_page_sk"
 JOIN "web_returns" AS "web_returns"
   ON "web_sales"."ws_item_sk" = "web_returns"."wr_item_sk"
   AND "web_sales"."ws_order_number" = "web_returns"."wr_order_number"
-JOIN "web_page" AS "web_page"
-  ON "web_sales"."ws_web_page_sk" = "web_page"."wp_web_page_sk"
-JOIN "cd2" AS "cd2"
+JOIN "customer_demographics" AS "cd2"
   ON "cd2"."cd_demo_sk" = "web_returns"."wr_returning_cdemo_sk"
-JOIN "cd2" AS "cd1"
+JOIN "customer_address" AS "customer_address"
+  ON "customer_address"."ca_address_sk" = "web_returns"."wr_refunded_addr_sk"
+  AND (
+    (
+      "customer_address"."ca_country" = 'United States'
+      AND "customer_address"."ca_state" IN ('FL', 'WI', 'KS')
+      AND "web_sales"."ws_net_profit" <= 250
+      AND "web_sales"."ws_net_profit" >= 50
+    )
+    OR (
+      "customer_address"."ca_country" = 'United States'
+      AND "customer_address"."ca_state" IN ('KY', 'ME', 'IL')
+      AND "web_sales"."ws_net_profit" <= 200
+      AND "web_sales"."ws_net_profit" >= 100
+    )
+    OR (
+      "customer_address"."ca_country" = 'United States'
+      AND "customer_address"."ca_state" IN ('OK', 'NE', 'MN')
+      AND "web_sales"."ws_net_profit" <= 300
+      AND "web_sales"."ws_net_profit" >= 150
+    )
+  )
+JOIN "reason" AS "reason"
+  ON "reason"."r_reason_sk" = "web_returns"."wr_reason_sk"
+JOIN "customer_demographics" AS "cd1"
   ON "cd1"."cd_demo_sk" = "web_returns"."wr_refunded_cdemo_sk"
   AND (
     (
@@ -11354,32 +11689,6 @@ JOIN "cd2" AS "cd1"
       AND "web_sales"."ws_sales_price" >= 50.00
     )
   )
-JOIN "customer_address" AS "customer_address"
-  ON "customer_address"."ca_address_sk" = "web_returns"."wr_refunded_addr_sk"
-  AND (
-    (
-      "customer_address"."ca_country" = 'United States'
-      AND "customer_address"."ca_state" IN ('FL', 'WI', 'KS')
-      AND "web_sales"."ws_net_profit" <= 250
-      AND "web_sales"."ws_net_profit" >= 50
-    )
-    OR (
-      "customer_address"."ca_country" = 'United States'
-      AND "customer_address"."ca_state" IN ('KY', 'ME', 'IL')
-      AND "web_sales"."ws_net_profit" <= 200
-      AND "web_sales"."ws_net_profit" >= 100
-    )
-    OR (
-      "customer_address"."ca_country" = 'United States'
-      AND "customer_address"."ca_state" IN ('OK', 'NE', 'MN')
-      AND "web_sales"."ws_net_profit" <= 300
-      AND "web_sales"."ws_net_profit" >= 150
-    )
-  )
-JOIN "date_dim" AS "date_dim"
-  ON "date_dim"."d_year" = 2001 AND "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk"
-JOIN "reason" AS "reason"
-  ON "reason"."r_reason_sk" = "web_returns"."wr_reason_sk"
 GROUP BY
   "reason"."r_reason_desc"
 ORDER BY
@@ -11422,10 +11731,10 @@ SELECT
   GROUPING("item"."i_category") + GROUPING("item"."i_class") AS "lochierarchy",
   RANK() OVER (PARTITION BY GROUPING("item"."i_category") + GROUPING("item"."i_class"), CASE WHEN GROUPING("item"."i_class") = 0 THEN "item"."i_category" END ORDER BY SUM("web_sales"."ws_net_paid") DESC) AS "rank_within_parent"
 FROM "web_sales" AS "web_sales"
-JOIN "date_dim" AS "date_dim"
-  ON "date_dim"."d_date_sk" = "web_sales"."ws_sold_date_sk"
-  AND "date_dim"."d_month_seq" <= 1194
-  AND "date_dim"."d_month_seq" >= 1183
+JOIN "date_dim" AS "d1"
+  ON "d1"."d_date_sk" = "web_sales"."ws_sold_date_sk"
+  AND "d1"."d_month_seq" <= 1194
+  AND "d1"."d_month_seq" >= 1183
 JOIN "item" AS "item"
   ON "item"."i_item_sk" = "web_sales"."ws_item_sk"
 GROUP BY
@@ -11462,7 +11771,13 @@ from ((select distinct c_last_name, c_first_name, d_date
          and d_month_seq between 1188 and 1188+11)
 ) cool_cust
 ;
-WITH "date_dim_2" AS (
+WITH "customer_2" AS (
+  SELECT
+    "customer"."c_customer_sk" AS "c_customer_sk",
+    "customer"."c_first_name" AS "c_first_name",
+    "customer"."c_last_name" AS "c_last_name"
+  FROM "customer" AS "customer"
+), "date_dim_2" AS (
   SELECT
     "date_dim"."d_date_sk" AS "d_date_sk",
     "date_dim"."d_date" AS "d_date",
@@ -11470,42 +11785,36 @@ WITH "date_dim_2" AS (
   FROM "date_dim" AS "date_dim"
   WHERE
     "date_dim"."d_month_seq" <= 1199 AND "date_dim"."d_month_seq" >= 1188
-), "customer_2" AS (
-  SELECT
-    "customer"."c_customer_sk" AS "c_customer_sk",
-    "customer"."c_first_name" AS "c_first_name",
-    "customer"."c_last_name" AS "c_last_name"
-  FROM "customer" AS "customer"
 ), "cte" AS (
   SELECT DISTINCT
     "customer"."c_last_name" AS "c_last_name",
     "customer"."c_first_name" AS "c_first_name",
     "date_dim"."d_date" AS "d_date"
   FROM "store_sales" AS "store_sales"
-  JOIN "date_dim_2" AS "date_dim"
-    ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
   JOIN "customer_2" AS "customer"
     ON "store_sales"."ss_customer_sk" = "customer"."c_customer_sk"
+  JOIN "date_dim_2" AS "date_dim"
+    ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
 ), "cte_2" AS (
   SELECT DISTINCT
     "customer"."c_last_name" AS "c_last_name",
     "customer"."c_first_name" AS "c_first_name",
     "date_dim"."d_date" AS "d_date"
   FROM "catalog_sales" AS "catalog_sales"
-  JOIN "date_dim_2" AS "date_dim"
-    ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk"
   JOIN "customer_2" AS "customer"
     ON "catalog_sales"."cs_bill_customer_sk" = "customer"."c_customer_sk"
+  JOIN "date_dim_2" AS "date_dim"
+    ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk"
 ), "cte_3" AS (
   SELECT DISTINCT
     "customer"."c_last_name" AS "c_last_name",
     "customer"."c_first_name" AS "c_first_name",
     "date_dim"."d_date" AS "d_date"
   FROM "web_sales" AS "web_sales"
-  JOIN "date_dim_2" AS "date_dim"
-    ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk"
   JOIN "customer_2" AS "customer"
     ON "web_sales"."ws_bill_customer_sk" = "customer"."c_customer_sk"
+  JOIN "date_dim_2" AS "date_dim"
+    ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk"
 ), "cte_4" AS (
   (
     SELECT
@@ -11676,96 +11985,96 @@ WITH "store_sales_2" AS (
   FROM "store_sales_2" AS "store_sales"
   JOIN "household_demographics_2" AS "household_demographics"
     ON "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk"
+  JOIN "store_2" AS "store"
+    ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
   JOIN "time_dim" AS "time_dim"
     ON "store_sales"."ss_sold_time_sk" = "time_dim"."t_time_sk"
     AND "time_dim"."t_hour" = 8
     AND "time_dim"."t_minute" >= 30
-  JOIN "store_2" AS "store"
-    ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
 ), "s2" AS (
   SELECT
     COUNT(*) AS "h9_to_9_30"
   FROM "store_sales_2" AS "store_sales"
   JOIN "household_demographics_2" AS "household_demographics"
     ON "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk"
+  JOIN "store_2" AS "store"
+    ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
   JOIN "time_dim" AS "time_dim"
     ON "store_sales"."ss_sold_time_sk" = "time_dim"."t_time_sk"
     AND "time_dim"."t_hour" = 9
     AND "time_dim"."t_minute" < 30
-  JOIN "store_2" AS "store"
-    ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
 ), "s3" AS (
   SELECT
     COUNT(*) AS "h9_30_to_10"
   FROM "store_sales_2" AS "store_sales"
   JOIN "household_demographics_2" AS "household_demographics"
     ON "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk"
+  JOIN "store_2" AS "store"
+    ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
   JOIN "time_dim" AS "time_dim"
     ON "store_sales"."ss_sold_time_sk" = "time_dim"."t_time_sk"
     AND "time_dim"."t_hour" = 9
     AND "time_dim"."t_minute" >= 30
-  JOIN "store_2" AS "store"
-    ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
 ), "s4" AS (
   SELECT
     COUNT(*) AS "h10_to_10_30"
   FROM "store_sales_2" AS "store_sales"
   JOIN "household_demographics_2" AS "household_demographics"
     ON "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk"
+  JOIN "store_2" AS "store"
+    ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
   JOIN "time_dim" AS "time_dim"
     ON "store_sales"."ss_sold_time_sk" = "time_dim"."t_time_sk"
     AND "time_dim"."t_hour" = 10
     AND "time_dim"."t_minute" < 30
-  JOIN "store_2" AS "store"
-    ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
 ), "s5" AS (
   SELECT
     COUNT(*) AS "h10_30_to_11"
   FROM "store_sales_2" AS "store_sales"
   JOIN "household_demographics_2" AS "household_demographics"
     ON "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk"
+  JOIN "store_2" AS "store"
+    ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
   JOIN "time_dim" AS "time_dim"
     ON "store_sales"."ss_sold_time_sk" = "time_dim"."t_time_sk"
     AND "time_dim"."t_hour" = 10
     AND "time_dim"."t_minute" >= 30
-  JOIN "store_2" AS "store"
-    ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
 ), "s6" AS (
   SELECT
     COUNT(*) AS "h11_to_11_30"
   FROM "store_sales_2" AS "store_sales"
   JOIN "household_demographics_2" AS "household_demographics"
     ON "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk"
+  JOIN "store_2" AS "store"
+    ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
   JOIN "time_dim" AS "time_dim"
     ON "store_sales"."ss_sold_time_sk" = "time_dim"."t_time_sk"
     AND "time_dim"."t_hour" = 11
     AND "time_dim"."t_minute" < 30
-  JOIN "store_2" AS "store"
-    ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
 ), "s7" AS (
   SELECT
     COUNT(*) AS "h11_30_to_12"
   FROM "store_sales_2" AS "store_sales"
   JOIN "household_demographics_2" AS "household_demographics"
     ON "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk"
+  JOIN "store_2" AS "store"
+    ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
   JOIN "time_dim" AS "time_dim"
     ON "store_sales"."ss_sold_time_sk" = "time_dim"."t_time_sk"
     AND "time_dim"."t_hour" = 11
     AND "time_dim"."t_minute" >= 30
-  JOIN "store_2" AS "store"
-    ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
 ), "s8" AS (
   SELECT
     COUNT(*) AS "h12_to_12_30"
   FROM "store_sales_2" AS "store_sales"
   JOIN "household_demographics_2" AS "household_demographics"
     ON "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk"
+  JOIN "store_2" AS "store"
+    ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
   JOIN "time_dim" AS "time_dim"
     ON "store_sales"."ss_sold_time_sk" = "time_dim"."t_time_sk"
     AND "time_dim"."t_hour" = 12
     AND "time_dim"."t_minute" < 30
-  JOIN "store_2" AS "store"
-    ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
 )
 SELECT
   "s1"."h8_30_to_9" AS "h8_30_to_9",
@@ -12016,16 +12325,19 @@ SELECT
   "call_center"."cc_manager" AS "manager",
   SUM("catalog_returns"."cr_net_loss") AS "returns_loss"
 FROM "call_center" AS "call_center"
-JOIN "catalog_returns" AS "catalog_returns"
-  ON "catalog_returns"."cr_call_center_sk" = "call_center"."cc_call_center_sk"
-JOIN "date_dim" AS "date_dim"
-  ON "catalog_returns"."cr_returned_date_sk" = "date_dim"."d_date_sk"
-  AND "date_dim"."d_moy" = 12
-  AND "date_dim"."d_year" = 1999
 JOIN "household_demographics" AS "household_demographics"
   ON "household_demographics"."hd_buy_potential" LIKE 'Unknown%'
+JOIN "customer" AS "customer"
+  ON "household_demographics"."hd_demo_sk" = "customer"."c_current_hdemo_sk"
+JOIN "catalog_returns" AS "catalog_returns"
+  ON "catalog_returns"."cr_call_center_sk" = "call_center"."cc_call_center_sk"
+  AND "catalog_returns"."cr_returning_customer_sk" = "customer"."c_customer_sk"
+JOIN "customer_address" AS "customer_address"
+  ON "customer_address"."ca_address_sk" = "customer"."c_current_addr_sk"
+  AND "customer_address"."ca_gmt_offset" = -7
 JOIN "customer_demographics" AS "customer_demographics"
-  ON (
+  ON "customer_demographics"."cd_demo_sk" = "customer"."c_current_cdemo_sk"
+  AND (
     "customer_demographics"."cd_education_status" = 'Advanced Degree'
     OR "customer_demographics"."cd_education_status" = 'Unknown'
   )
@@ -12041,13 +12353,10 @@ JOIN "customer_demographics" AS "customer_demographics"
     "customer_demographics"."cd_marital_status" = 'M'
     OR "customer_demographics"."cd_marital_status" = 'W'
   )
-JOIN "customer_address" AS "customer_address"
-  ON "customer_address"."ca_gmt_offset" = -7
-JOIN "customer" AS "customer"
-  ON "catalog_returns"."cr_returning_customer_sk" = "customer"."c_customer_sk"
-  AND "customer_address"."ca_address_sk" = "customer"."c_current_addr_sk"
-  AND "customer_demographics"."cd_demo_sk" = "customer"."c_current_cdemo_sk"
-  AND "household_demographics"."hd_demo_sk" = "customer"."c_current_hdemo_sk"
+JOIN "date_dim" AS "date_dim"
+  ON "catalog_returns"."cr_returned_date_sk" = "date_dim"."d_date_sk"
+  AND "date_dim"."d_moy" = 12
+  AND "date_dim"."d_year" = 1999
 GROUP BY
   "call_center"."cc_call_center_id",
   "call_center"."cc_name",
@@ -12108,10 +12417,10 @@ WITH "web_sales_2" AS (
 SELECT
   SUM("web_sales"."ws_ext_discount_amt") AS "Excess Discount Amount"
 FROM "web_sales_2" AS "web_sales"
-JOIN "item" AS "item"
-  ON "item"."i_item_sk" = "web_sales"."ws_item_sk" AND "item"."i_manufact_id" = 718
 JOIN "date_dim_2" AS "date_dim"
   ON "date_dim"."d_date_sk" = "web_sales"."ws_sold_date_sk"
+JOIN "item" AS "item"
+  ON "item"."i_item_sk" = "web_sales"."ws_item_sk" AND "item"."i_manufact_id" = 718
 LEFT JOIN "_u_0" AS "_u_0"
   ON "_u_0"."_u_1" = "item"."i_item_sk"
 WHERE
@@ -12158,11 +12467,11 @@ SELECT
     END
   ) AS "sumsales"
 FROM "store_sales" AS "store_sales"
+JOIN "reason" AS "reason"
+  ON "reason"."r_reason_desc" = 'reason 38'
 LEFT JOIN "store_returns" AS "store_returns"
   ON "store_returns"."sr_item_sk" = "store_sales"."ss_item_sk"
   AND "store_returns"."sr_ticket_number" = "store_sales"."ss_ticket_number"
-JOIN "reason" AS "reason"
-  ON "reason"."r_reason_desc" = 'reason 38'
 WHERE
   "store_returns"."sr_reason_sk" = "reason"."r_reason_sk"
 GROUP BY
@@ -12218,32 +12527,32 @@ WITH "_u_0" AS (
     "wr1"."wr_order_number"
 )
 SELECT
-  COUNT(DISTINCT "web_sales"."ws_order_number") AS "order count",
-  SUM("web_sales"."ws_ext_ship_cost") AS "total shipping cost",
-  SUM("web_sales"."ws_net_profit") AS "total net profit"
-FROM "web_sales" AS "web_sales"
+  COUNT(DISTINCT "ws1"."ws_order_number") AS "order count",
+  SUM("ws1"."ws_ext_ship_cost") AS "total shipping cost",
+  SUM("ws1"."ws_net_profit") AS "total net profit"
+FROM "web_sales" AS "ws1"
+LEFT JOIN "_u_0" AS "_u_0"
+  ON "ws1"."ws_order_number" = "_u_0"."_u_1"
+LEFT JOIN "_u_3" AS "_u_3"
+  ON "ws1"."ws_order_number" = "_u_3"."_u_4"
+JOIN "customer_address" AS "customer_address"
+  ON "customer_address"."ca_state" = 'MT'
+  AND "ws1"."ws_ship_addr_sk" = "customer_address"."ca_address_sk"
 JOIN "date_dim" AS "date_dim"
   ON "date_dim"."d_date" >= '2000-3-01'
-  AND "web_sales"."ws_ship_date_sk" = "date_dim"."d_date_sk"
+  AND "ws1"."ws_ship_date_sk" = "date_dim"."d_date_sk"
   AND CAST("date_dim"."d_date" AS DATE) <= (
     CAST('2000-3-01' AS DATE) + INTERVAL '60' day
   )
-JOIN "customer_address" AS "customer_address"
-  ON "customer_address"."ca_state" = 'MT'
-  AND "web_sales"."ws_ship_addr_sk" = "customer_address"."ca_address_sk"
 JOIN "web_site" AS "web_site"
-  ON "web_sales"."ws_web_site_sk" = "web_site"."web_site_sk"
-  AND "web_site"."web_company_name" = 'pri'
-LEFT JOIN "_u_0" AS "_u_0"
-  ON "web_sales"."ws_order_number" = "_u_0"."_u_1"
-LEFT JOIN "_u_3" AS "_u_3"
-  ON "web_sales"."ws_order_number" = "_u_3"."_u_4"
+  ON "web_site"."web_company_name" = 'pri'
+  AND "ws1"."ws_web_site_sk" = "web_site"."web_site_sk"
 WHERE
   "_u_3"."_u_4" IS NULL
-  AND ARRAY_ANY("_u_0"."_u_2", "_x" -> "web_sales"."ws_warehouse_sk" <> "_x")
+  AND ARRAY_ANY("_u_0"."_u_2", "_x" -> "ws1"."ws_warehouse_sk" <> "_x")
   AND NOT "_u_0"."_u_1" IS NULL
 ORDER BY
-  COUNT(DISTINCT "web_sales"."ws_order_number")
+  COUNT(DISTINCT "ws1"."ws_order_number")
 LIMIT 100;
 
 --------------------------------------
@@ -12285,16 +12594,11 @@ AND      ws1.ws_order_number IN
                 WHERE  wr_order_number = ws_wh.ws_order_number)
 ORDER BY count(DISTINCT ws_order_number)
 LIMIT 100;
-WITH "ws1" AS (
-  SELECT
-    "web_sales"."ws_warehouse_sk" AS "ws_warehouse_sk",
-    "web_sales"."ws_order_number" AS "ws_order_number"
-  FROM "web_sales" AS "web_sales"
-), "ws_wh" AS (
+WITH "ws_wh" AS (
   SELECT
     "ws1"."ws_order_number" AS "ws_order_number"
-  FROM "ws1" AS "ws1"
-  JOIN "ws1" AS "ws2"
+  FROM "web_sales" AS "ws1"
+  JOIN "web_sales" AS "ws2"
     ON "ws1"."ws_order_number" = "ws2"."ws_order_number"
     AND "ws1"."ws_warehouse_sk" <> "ws2"."ws_warehouse_sk"
 ), "_u_0" AS (
@@ -12313,30 +12617,30 @@ WITH "ws1" AS (
     "web_returns"."wr_order_number"
 )
 SELECT
-  COUNT(DISTINCT "web_sales"."ws_order_number") AS "order count",
-  SUM("web_sales"."ws_ext_ship_cost") AS "total shipping cost",
-  SUM("web_sales"."ws_net_profit") AS "total net profit"
-FROM "web_sales" AS "web_sales"
+  COUNT(DISTINCT "ws1"."ws_order_number") AS "order count",
+  SUM("ws1"."ws_ext_ship_cost") AS "total shipping cost",
+  SUM("ws1"."ws_net_profit") AS "total net profit"
+FROM "web_sales" AS "ws1"
+LEFT JOIN "_u_0" AS "_u_0"
+  ON "ws1"."ws_order_number" = "_u_0"."ws_order_number"
+LEFT JOIN "_u_1" AS "_u_1"
+  ON "ws1"."ws_order_number" = "_u_1"."wr_order_number"
+JOIN "customer_address" AS "customer_address"
+  ON "customer_address"."ca_state" = 'IN'
+  AND "ws1"."ws_ship_addr_sk" = "customer_address"."ca_address_sk"
 JOIN "date_dim" AS "date_dim"
   ON "date_dim"."d_date" >= '2000-4-01'
-  AND "web_sales"."ws_ship_date_sk" = "date_dim"."d_date_sk"
+  AND "ws1"."ws_ship_date_sk" = "date_dim"."d_date_sk"
   AND CAST("date_dim"."d_date" AS DATE) <= (
     CAST('2000-4-01' AS DATE) + INTERVAL '60' day
   )
-JOIN "customer_address" AS "customer_address"
-  ON "customer_address"."ca_state" = 'IN'
-  AND "web_sales"."ws_ship_addr_sk" = "customer_address"."ca_address_sk"
 JOIN "web_site" AS "web_site"
-  ON "web_sales"."ws_web_site_sk" = "web_site"."web_site_sk"
-  AND "web_site"."web_company_name" = 'pri'
-LEFT JOIN "_u_0" AS "_u_0"
-  ON "web_sales"."ws_order_number" = "_u_0"."ws_order_number"
-LEFT JOIN "_u_1" AS "_u_1"
-  ON "web_sales"."ws_order_number" = "_u_1"."wr_order_number"
+  ON "web_site"."web_company_name" = 'pri'
+  AND "ws1"."ws_web_site_sk" = "web_site"."web_site_sk"
 WHERE
   NOT "_u_0"."ws_order_number" IS NULL AND NOT "_u_1"."wr_order_number" IS NULL
 ORDER BY
-  COUNT(DISTINCT "web_sales"."ws_order_number")
+  COUNT(DISTINCT "ws1"."ws_order_number")
 LIMIT 100;
 
 --------------------------------------
@@ -12362,12 +12666,12 @@ FROM "store_sales" AS "store_sales"
 JOIN "household_demographics" AS "household_demographics"
   ON "household_demographics"."hd_dep_count" = 7
   AND "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk"
+JOIN "store" AS "store"
+  ON "store"."s_store_name" = 'ese' AND "store_sales"."ss_store_sk" = "store"."s_store_sk"
 JOIN "time_dim" AS "time_dim"
   ON "store_sales"."ss_sold_time_sk" = "time_dim"."t_time_sk"
   AND "time_dim"."t_hour" = 15
   AND "time_dim"."t_minute" >= 30
-JOIN "store" AS "store"
-  ON "store"."s_store_name" = 'ese' AND "store_sales"."ss_store_sk" = "store"."s_store_sk"
 ORDER BY
   COUNT(*)
 LIMIT 100;
@@ -12509,13 +12813,13 @@ SELECT
   SUM("store_sales"."ss_ext_sales_price") AS "itemrevenue",
   SUM("store_sales"."ss_ext_sales_price") * 100 / SUM(SUM("store_sales"."ss_ext_sales_price")) OVER (PARTITION BY "item"."i_class") AS "revenueratio"
 FROM "store_sales" AS "store_sales"
-JOIN "item" AS "item"
-  ON "item"."i_category" IN ('Men', 'Home', 'Electronics')
-  AND "store_sales"."ss_item_sk" = "item"."i_item_sk"
 JOIN "date_dim" AS "date_dim"
   ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
   AND CAST("date_dim"."d_date" AS DATE) <= CAST('2000-06-17' AS DATE)
   AND CAST("date_dim"."d_date" AS DATE) >= CAST('2000-05-18' AS DATE)
+JOIN "item" AS "item"
+  ON "item"."i_category" IN ('Men', 'Home', 'Electronics')
+  AND "store_sales"."ss_item_sk" = "item"."i_item_sk"
 GROUP BY
   "item"."i_item_id",
   "item"."i_item_desc",
@@ -12621,16 +12925,16 @@ SELECT
     END
   ) AS ">120 days"
 FROM "catalog_sales" AS "catalog_sales"
-JOIN "warehouse" AS "warehouse"
-  ON "catalog_sales"."cs_warehouse_sk" = "warehouse"."w_warehouse_sk"
-JOIN "ship_mode" AS "ship_mode"
-  ON "catalog_sales"."cs_ship_mode_sk" = "ship_mode"."sm_ship_mode_sk"
 JOIN "call_center" AS "call_center"
   ON "catalog_sales"."cs_call_center_sk" = "call_center"."cc_call_center_sk"
 JOIN "date_dim" AS "date_dim"
   ON "catalog_sales"."cs_ship_date_sk" = "date_dim"."d_date_sk"
   AND "date_dim"."d_month_seq" <= 1211
   AND "date_dim"."d_month_seq" >= 1200
+JOIN "ship_mode" AS "ship_mode"
+  ON "catalog_sales"."cs_ship_mode_sk" = "ship_mode"."sm_ship_mode_sk"
+JOIN "warehouse" AS "warehouse"
+  ON "catalog_sales"."cs_warehouse_sk" = "warehouse"."w_warehouse_sk"
 GROUP BY
   SUBSTR("warehouse"."w_warehouse_name", 1, 20),
   "ship_mode"."sm_type",
diff --git a/tests/fixtures/optimizer/tpc-h/tpc-h.sql b/tests/fixtures/optimizer/tpc-h/tpc-h.sql
index a25e247..942295e 100644
--- a/tests/fixtures/optimizer/tpc-h/tpc-h.sql
+++ b/tests/fixtures/optimizer/tpc-h/tpc-h.sql
@@ -117,12 +117,12 @@ WITH "region_2" AS (
     MIN("partsupp"."ps_supplycost") AS "_col_0",
     "partsupp"."ps_partkey" AS "_u_1"
   FROM "partsupp_2" AS "partsupp"
-  CROSS JOIN "region_2" AS "region"
-  JOIN "nation" AS "nation"
-    ON "nation"."n_regionkey" = "region"."r_regionkey"
   JOIN "supplier" AS "supplier"
+    ON "supplier"."s_suppkey" = "partsupp"."ps_suppkey"
+  JOIN "nation" AS "nation"
     ON "supplier"."s_nationkey" = "nation"."n_nationkey"
-    AND "supplier"."s_suppkey" = "partsupp"."ps_suppkey"
+  JOIN "region_2" AS "region"
+    ON "nation"."n_regionkey" = "region"."r_regionkey"
   GROUP BY
     "partsupp"."ps_partkey"
 )
@@ -137,6 +137,8 @@ SELECT
   "supplier"."s_comment" AS "s_comment"
 FROM "part" AS "part"
 CROSS JOIN "region_2" AS "region"
+LEFT JOIN "_u_0" AS "_u_0"
+  ON "part"."p_partkey" = "_u_0"."_u_1"
 JOIN "nation" AS "nation"
   ON "nation"."n_regionkey" = "region"."r_regionkey"
 JOIN "partsupp_2" AS "partsupp"
@@ -144,8 +146,6 @@ JOIN "partsupp_2" AS "partsupp"
 JOIN "supplier" AS "supplier"
   ON "supplier"."s_nationkey" = "nation"."n_nationkey"
   AND "supplier"."s_suppkey" = "partsupp"."ps_suppkey"
-LEFT JOIN "_u_0" AS "_u_0"
-  ON "part"."p_partkey" = "_u_0"."_u_1"
 WHERE
   "part"."p_size" = 15
   AND "part"."p_type" LIKE '%BRASS'
@@ -294,16 +294,15 @@ JOIN "orders" AS "orders"
   ON "customer"."c_custkey" = "orders"."o_custkey"
   AND CAST("orders"."o_orderdate" AS DATE) < CAST('1995-01-01' AS DATE)
   AND CAST("orders"."o_orderdate" AS DATE) >= CAST('1994-01-01' AS DATE)
-JOIN "region" AS "region"
-  ON "region"."r_name" = 'ASIA'
-JOIN "nation" AS "nation"
-  ON "nation"."n_regionkey" = "region"."r_regionkey"
 JOIN "supplier" AS "supplier"
   ON "customer"."c_nationkey" = "supplier"."s_nationkey"
-  AND "supplier"."s_nationkey" = "nation"."n_nationkey"
 JOIN "lineitem" AS "lineitem"
   ON "lineitem"."l_orderkey" = "orders"."o_orderkey"
   AND "lineitem"."l_suppkey" = "supplier"."s_suppkey"
+JOIN "nation" AS "nation"
+  ON "supplier"."s_nationkey" = "nation"."n_nationkey"
+JOIN "region" AS "region"
+  ON "nation"."n_regionkey" = "region"."r_regionkey" AND "region"."r_name" = 'ASIA'
 GROUP BY
   "nation"."n_name"
 ORDER BY
@@ -373,14 +372,6 @@ order by
         supp_nation,
         cust_nation,
         l_year;
-WITH "n1" AS (
-  SELECT
-    "nation"."n_nationkey" AS "n_nationkey",
-    "nation"."n_name" AS "n_name"
-  FROM "nation" AS "nation"
-  WHERE
-    "nation"."n_name" = 'FRANCE' OR "nation"."n_name" = 'GERMANY'
-)
 SELECT
   "n1"."n_name" AS "supp_nation",
   "n2"."n_name" AS "cust_nation",
@@ -393,20 +384,26 @@ JOIN "lineitem" AS "lineitem"
   ON "supplier"."s_suppkey" = "lineitem"."l_suppkey"
   AND CAST("lineitem"."l_shipdate" AS DATE) <= CAST('1996-12-31' AS DATE)
   AND CAST("lineitem"."l_shipdate" AS DATE) >= CAST('1995-01-01' AS DATE)
-JOIN "orders" AS "orders"
-  ON "orders"."o_orderkey" = "lineitem"."l_orderkey"
-JOIN "customer" AS "customer"
-  ON "customer"."c_custkey" = "orders"."o_custkey"
-JOIN "n1" AS "n1"
-  ON "supplier"."s_nationkey" = "n1"."n_nationkey"
-JOIN "n1" AS "n2"
-  ON "customer"."c_nationkey" = "n2"."n_nationkey"
-  AND (
+JOIN "nation" AS "n1"
+  ON (
+    "n1"."n_name" = 'FRANCE' OR "n1"."n_name" = 'GERMANY'
+  )
+  AND "supplier"."s_nationkey" = "n1"."n_nationkey"
+JOIN "nation" AS "n2"
+  ON (
     "n1"."n_name" = 'FRANCE' OR "n2"."n_name" = 'FRANCE'
   )
   AND (
     "n1"."n_name" = 'GERMANY' OR "n2"."n_name" = 'GERMANY'
   )
+  AND (
+    "n2"."n_name" = 'FRANCE' OR "n2"."n_name" = 'GERMANY'
+  )
+JOIN "customer" AS "customer"
+  ON "customer"."c_nationkey" = "n2"."n_nationkey"
+JOIN "orders" AS "orders"
+  ON "customer"."c_custkey" = "orders"."o_custkey"
+  AND "orders"."o_orderkey" = "lineitem"."l_orderkey"
 GROUP BY
   "n1"."n_name",
   "n2"."n_name",
@@ -460,7 +457,7 @@ SELECT
   EXTRACT(year FROM CAST("orders"."o_orderdate" AS DATE)) AS "o_year",
   SUM(
     CASE
-      WHEN "nation_2"."n_name" = 'BRAZIL'
+      WHEN "n2"."n_name" = 'BRAZIL'
       THEN "lineitem"."l_extendedprice" * (
         1 - "lineitem"."l_discount"
       )
@@ -472,21 +469,21 @@ SELECT
 FROM "part" AS "part"
 JOIN "region" AS "region"
   ON "region"."r_name" = 'AMERICA'
-JOIN "nation" AS "nation"
-  ON "nation"."n_regionkey" = "region"."r_regionkey"
+JOIN "lineitem" AS "lineitem"
+  ON "part"."p_partkey" = "lineitem"."l_partkey"
+JOIN "nation" AS "n1"
+  ON "n1"."n_regionkey" = "region"."r_regionkey"
 JOIN "customer" AS "customer"
-  ON "customer"."c_nationkey" = "nation"."n_nationkey"
+  ON "customer"."c_nationkey" = "n1"."n_nationkey"
+JOIN "supplier" AS "supplier"
+  ON "supplier"."s_suppkey" = "lineitem"."l_suppkey"
+JOIN "nation" AS "n2"
+  ON "supplier"."s_nationkey" = "n2"."n_nationkey"
 JOIN "orders" AS "orders"
-  ON "orders"."o_custkey" = "customer"."c_custkey"
+  ON "lineitem"."l_orderkey" = "orders"."o_orderkey"
+  AND "orders"."o_custkey" = "customer"."c_custkey"
   AND CAST("orders"."o_orderdate" AS DATE) <= CAST('1996-12-31' AS DATE)
   AND CAST("orders"."o_orderdate" AS DATE) >= CAST('1995-01-01' AS DATE)
-JOIN "lineitem" AS "lineitem"
-  ON "lineitem"."l_orderkey" = "orders"."o_orderkey"
-  AND "part"."p_partkey" = "lineitem"."l_partkey"
-JOIN "supplier" AS "supplier"
-  ON "supplier"."s_suppkey" = "lineitem"."l_suppkey"
-JOIN "nation" AS "nation_2"
-  ON "supplier"."s_nationkey" = "nation_2"."n_nationkey"
 WHERE
   "part"."p_type" = 'ECONOMY ANODIZED STEEL'
 GROUP BY
@@ -540,13 +537,13 @@ SELECT
 FROM "part" AS "part"
 JOIN "lineitem" AS "lineitem"
   ON "part"."p_partkey" = "lineitem"."l_partkey"
-JOIN "supplier" AS "supplier"
-  ON "supplier"."s_suppkey" = "lineitem"."l_suppkey"
+JOIN "orders" AS "orders"
+  ON "orders"."o_orderkey" = "lineitem"."l_orderkey"
 JOIN "partsupp" AS "partsupp"
   ON "partsupp"."ps_partkey" = "lineitem"."l_partkey"
   AND "partsupp"."ps_suppkey" = "lineitem"."l_suppkey"
-JOIN "orders" AS "orders"
-  ON "orders"."o_orderkey" = "lineitem"."l_orderkey"
+JOIN "supplier" AS "supplier"
+  ON "supplier"."s_suppkey" = "lineitem"."l_suppkey"
 JOIN "nation" AS "nation"
   ON "supplier"."s_nationkey" = "nation"."n_nationkey"
 WHERE
@@ -606,14 +603,14 @@ SELECT
   "customer"."c_phone" AS "c_phone",
   "customer"."c_comment" AS "c_comment"
 FROM "customer" AS "customer"
+JOIN "nation" AS "nation"
+  ON "customer"."c_nationkey" = "nation"."n_nationkey"
 JOIN "orders" AS "orders"
   ON "customer"."c_custkey" = "orders"."o_custkey"
   AND CAST("orders"."o_orderdate" AS DATE) < CAST('1994-01-01' AS DATE)
   AND CAST("orders"."o_orderdate" AS DATE) >= CAST('1993-10-01' AS DATE)
 JOIN "lineitem" AS "lineitem"
   ON "lineitem"."l_orderkey" = "orders"."o_orderkey" AND "lineitem"."l_returnflag" = 'R'
-JOIN "nation" AS "nation"
-  ON "customer"."c_nationkey" = "nation"."n_nationkey"
 GROUP BY
   "customer"."c_custkey",
   "customer"."c_name",
@@ -681,11 +678,11 @@ SELECT
   "partsupp"."ps_partkey" AS "ps_partkey",
   SUM("partsupp"."ps_supplycost" * "partsupp"."ps_availqty") AS "value"
 FROM "partsupp" AS "partsupp"
+CROSS JOIN "_u_0" AS "_u_0"
 JOIN "supplier_2" AS "supplier"
   ON "partsupp"."ps_suppkey" = "supplier"."s_suppkey"
 JOIN "nation_2" AS "nation"
   ON "supplier"."s_nationkey" = "nation"."n_nationkey"
-CROSS JOIN "_u_0" AS "_u_0"
 GROUP BY
   "partsupp"."ps_partkey"
 HAVING
@@ -950,13 +947,13 @@ SELECT
   "part"."p_size" AS "p_size",
   COUNT(DISTINCT "partsupp"."ps_suppkey") AS "supplier_cnt"
 FROM "partsupp" AS "partsupp"
+LEFT JOIN "_u_0" AS "_u_0"
+  ON "partsupp"."ps_suppkey" = "_u_0"."s_suppkey"
 JOIN "part" AS "part"
   ON "part"."p_brand" <> 'Brand#45'
   AND "part"."p_partkey" = "partsupp"."ps_partkey"
   AND "part"."p_size" IN (49, 14, 23, 45, 19, 3, 36, 9)
   AND NOT "part"."p_type" LIKE 'MEDIUM POLISHED%'
-LEFT JOIN "_u_0" AS "_u_0"
-  ON "partsupp"."ps_suppkey" = "_u_0"."s_suppkey"
 WHERE
   "_u_0"."s_suppkey" IS NULL
 GROUP BY
@@ -1066,10 +1063,10 @@ SELECT
 FROM "customer" AS "customer"
 JOIN "orders" AS "orders"
   ON "customer"."c_custkey" = "orders"."o_custkey"
-JOIN "lineitem" AS "lineitem"
-  ON "orders"."o_orderkey" = "lineitem"."l_orderkey"
 LEFT JOIN "_u_0" AS "_u_0"
   ON "orders"."o_orderkey" = "_u_0"."l_orderkey"
+JOIN "lineitem" AS "lineitem"
+  ON "orders"."o_orderkey" = "lineitem"."l_orderkey"
 WHERE
   NOT "_u_0"."l_orderkey" IS NULL
 GROUP BY
@@ -1260,10 +1257,10 @@ SELECT
   "supplier"."s_name" AS "s_name",
   "supplier"."s_address" AS "s_address"
 FROM "supplier" AS "supplier"
-JOIN "nation" AS "nation"
-  ON "nation"."n_name" = 'CANADA' AND "supplier"."s_nationkey" = "nation"."n_nationkey"
 LEFT JOIN "_u_4" AS "_u_4"
   ON "supplier"."s_suppkey" = "_u_4"."ps_suppkey"
+JOIN "nation" AS "nation"
+  ON "nation"."n_name" = 'CANADA' AND "supplier"."s_nationkey" = "nation"."n_nationkey"
 WHERE
   NOT "_u_4"."ps_suppkey" IS NULL
 ORDER BY
@@ -1334,24 +1331,24 @@ SELECT
   "supplier"."s_name" AS "s_name",
   COUNT(*) AS "numwait"
 FROM "supplier" AS "supplier"
-JOIN "lineitem" AS "lineitem"
-  ON "lineitem"."l_receiptdate" > "lineitem"."l_commitdate"
-  AND "supplier"."s_suppkey" = "lineitem"."l_suppkey"
-JOIN "orders" AS "orders"
-  ON "orders"."o_orderkey" = "lineitem"."l_orderkey" AND "orders"."o_orderstatus" = 'F'
+JOIN "lineitem" AS "l1"
+  ON "l1"."l_receiptdate" > "l1"."l_commitdate"
+  AND "supplier"."s_suppkey" = "l1"."l_suppkey"
 JOIN "nation" AS "nation"
   ON "nation"."n_name" = 'SAUDI ARABIA'
   AND "supplier"."s_nationkey" = "nation"."n_nationkey"
 LEFT JOIN "_u_0" AS "_u_0"
-  ON "_u_0"."l_orderkey" = "lineitem"."l_orderkey"
+  ON "_u_0"."l_orderkey" = "l1"."l_orderkey"
 LEFT JOIN "_u_2" AS "_u_2"
-  ON "_u_2"."l_orderkey" = "lineitem"."l_orderkey"
+  ON "_u_2"."l_orderkey" = "l1"."l_orderkey"
+JOIN "orders" AS "orders"
+  ON "orders"."o_orderkey" = "l1"."l_orderkey" AND "orders"."o_orderstatus" = 'F'
 WHERE
   (
     "_u_2"."l_orderkey" IS NULL
-    OR NOT ARRAY_ANY("_u_2"."_u_3", "_x" -> "_x" <> "lineitem"."l_suppkey")
+    OR NOT ARRAY_ANY("_u_2"."_u_3", "_x" -> "_x" <> "l1"."l_suppkey")
   )
-  AND ARRAY_ANY("_u_0"."_u_1", "_x" -> "_x" <> "lineitem"."l_suppkey")
+  AND ARRAY_ANY("_u_0"."_u_1", "_x" -> "_x" <> "l1"."l_suppkey")
   AND NOT "_u_0"."l_orderkey" IS NULL
 GROUP BY
   "supplier"."s_name"
@@ -1430,3 +1427,4 @@ GROUP BY
   SUBSTRING("customer"."c_phone", 1, 2)
 ORDER BY
   "cntrycode";
+
diff --git a/tests/test_build.py b/tests/test_build.py
index 1e28689..f354640 100644
--- a/tests/test_build.py
+++ b/tests/test_build.py
@@ -128,6 +128,16 @@ class TestBuild(unittest.TestCase):
                 "postgres",
             ),
             (
+                lambda: select("x").from_("tbl").hint("repartition(100)"),
+                "SELECT /*+ REPARTITION(100) */ x FROM tbl",
+                "spark",
+            ),
+            (
+                lambda: select("x").from_("tbl").hint("coalesce(3)", "broadcast(x)"),
+                "SELECT /*+ COALESCE(3), BROADCAST(x) */ x FROM tbl",
+                "spark",
+            ),
+            (
                 lambda: select("x", "y").from_("tbl").group_by("x"),
                 "SELECT x, y FROM tbl GROUP BY x",
             ),
diff --git a/tests/test_executor.py b/tests/test_executor.py
index a121dea..bb01dee 100644
--- a/tests/test_executor.py
+++ b/tests/test_executor.py
@@ -1,6 +1,7 @@
 import datetime
 import unittest
 from datetime import date
+from multiprocessing import Pool
 
 import duckdb
 import pandas as pd
@@ -76,13 +77,21 @@ class TestExecutor(unittest.TestCase):
                 )
             return expression
 
-        for i, (sql, _) in enumerate(self.sqls):
-            with self.subTest(f"tpch-h {i + 1}"):
-                a = self.cached_execute(sql)
-                sql = parse_one(sql).transform(to_csv).sql(pretty=True)
-                table = execute(sql, TPCH_SCHEMA)
-                b = pd.DataFrame(table.rows, columns=table.columns)
-                assert_frame_equal(a, b, check_dtype=False, check_index_type=False)
+        with Pool() as pool:
+            for i, table in enumerate(
+                pool.starmap(
+                    execute,
+                    (
+                        (parse_one(sql).transform(to_csv).sql(pretty=True), TPCH_SCHEMA)
+                        for sql, _ in self.sqls
+                    ),
+                )
+            ):
+                with self.subTest(f"tpch-h {i + 1}"):
+                    sql, _ = self.sqls[i]
+                    a = self.cached_execute(sql)
+                    b = pd.DataFrame(table.rows, columns=table.columns)
+                    assert_frame_equal(a, b, check_dtype=False, check_index_type=False)
 
     def test_execute_callable(self):
         tables = {
@@ -496,6 +505,7 @@ class TestExecutor(unittest.TestCase):
             ("SELECT 1", ["1"], [(1,)]),
             ("SELECT 1 + 2 AS x", ["x"], [(3,)]),
             ("SELECT CONCAT('a', 'b') AS x", ["x"], [("ab",)]),
+            ("SELECT CONCAT('a', 1) AS x", ["x"], [("a1",)]),
             ("SELECT 1 AS x, 2 AS y", ["x", "y"], [(1, 2)]),
             ("SELECT 'foo' LIMIT 1", ["foo"], [("foo",)]),
             (
diff --git a/tests/test_expressions.py b/tests/test_expressions.py
index 7735e78..c9b5279 100644
--- a/tests/test_expressions.py
+++ b/tests/test_expressions.py
@@ -534,6 +534,7 @@ class TestExpressions(unittest.TestCase):
         self.assertIsInstance(parse_one("HLL(a)"), exp.Hll)
         self.assertIsInstance(parse_one("ARRAY(time, foo)"), exp.Array)
         self.assertIsInstance(parse_one("STANDARD_HASH('hello', 'sha256')"), exp.StandardHash)
+        self.assertIsInstance(parse_one("DATE(foo)"), exp.Date)
 
     def test_column(self):
         column = parse_one("a.b.c.d")
@@ -590,7 +591,7 @@ class TestExpressions(unittest.TestCase):
         unit = parse_one("timestamp_trunc(current_timestamp, week(thursday))")
         self.assertIsNotNone(unit.find(exp.CurrentTimestamp))
         week = unit.find(exp.Week)
-        self.assertEqual(week.this, exp.Var(this="thursday"))
+        self.assertEqual(week.this, exp.var("thursday"))
 
     def test_identifier(self):
         self.assertTrue(exp.to_identifier('"x"').quoted)
@@ -601,7 +602,7 @@ class TestExpressions(unittest.TestCase):
     def test_function_normalizer(self):
         self.assertEqual(parse_one("HELLO()").sql(normalize_functions="lower"), "hello()")
         self.assertEqual(parse_one("hello()").sql(normalize_functions="upper"), "HELLO()")
-        self.assertEqual(parse_one("heLLO()").sql(normalize_functions=None), "heLLO()")
+        self.assertEqual(parse_one("heLLO()").sql(normalize_functions=False), "heLLO()")
         self.assertEqual(parse_one("SUM(x)").sql(normalize_functions="lower"), "sum(x)")
         self.assertEqual(parse_one("sum(x)").sql(normalize_functions="upper"), "SUM(x)")
 
@@ -786,7 +787,7 @@ FROM foo""",
         self.assertEqual(exp.DataType.build("DECIMAL").sql(), "DECIMAL")
         self.assertEqual(exp.DataType.build("BOOLEAN").sql(), "BOOLEAN")
         self.assertEqual(exp.DataType.build("JSON").sql(), "JSON")
-        self.assertEqual(exp.DataType.build("JSONB").sql(), "JSONB")
+        self.assertEqual(exp.DataType.build("JSONB", dialect="postgres").sql(), "JSONB")
         self.assertEqual(exp.DataType.build("INTERVAL").sql(), "INTERVAL")
         self.assertEqual(exp.DataType.build("TIME").sql(), "TIME")
         self.assertEqual(exp.DataType.build("TIMESTAMP").sql(), "TIMESTAMP")
@@ -801,22 +802,17 @@ FROM foo""",
         self.assertEqual(exp.DataType.build("GEOMETRY").sql(), "GEOMETRY")
         self.assertEqual(exp.DataType.build("STRUCT").sql(), "STRUCT")
         self.assertEqual(exp.DataType.build("NULLABLE").sql(), "NULLABLE")
-        self.assertEqual(exp.DataType.build("HLLSKETCH").sql(), "HLLSKETCH")
-        self.assertEqual(exp.DataType.build("HSTORE").sql(), "HSTORE")
-        self.assertEqual(exp.DataType.build("SUPER").sql(), "SUPER")
-        self.assertEqual(exp.DataType.build("SERIAL").sql(), "SERIAL")
-        self.assertEqual(exp.DataType.build("SMALLSERIAL").sql(), "SMALLSERIAL")
-        self.assertEqual(exp.DataType.build("BIGSERIAL").sql(), "BIGSERIAL")
-        self.assertEqual(exp.DataType.build("XML").sql(), "XML")
-        self.assertEqual(exp.DataType.build("UNIQUEIDENTIFIER").sql(), "UNIQUEIDENTIFIER")
-        self.assertEqual(exp.DataType.build("MONEY").sql(), "MONEY")
-        self.assertEqual(exp.DataType.build("SMALLMONEY").sql(), "SMALLMONEY")
-        self.assertEqual(exp.DataType.build("ROWVERSION").sql(), "ROWVERSION")
-        self.assertEqual(exp.DataType.build("IMAGE").sql(), "IMAGE")
-        self.assertEqual(exp.DataType.build("VARIANT").sql(), "VARIANT")
-        self.assertEqual(exp.DataType.build("OBJECT").sql(), "OBJECT")
+        self.assertEqual(exp.DataType.build("HLLSKETCH", dialect="redshift").sql(), "HLLSKETCH")
+        self.assertEqual(exp.DataType.build("HSTORE", dialect="postgres").sql(), "HSTORE")
         self.assertEqual(exp.DataType.build("NULL").sql(), "NULL")
+        self.assertEqual(exp.DataType.build("NULL", dialect="bigquery").sql(), "NULL")
         self.assertEqual(exp.DataType.build("UNKNOWN").sql(), "UNKNOWN")
+        self.assertEqual(exp.DataType.build("UNKNOWN", dialect="bigquery").sql(), "UNKNOWN")
+        self.assertEqual(exp.DataType.build("UNKNOWN", dialect="snowflake").sql(), "UNKNOWN")
+        self.assertEqual(exp.DataType.build("TIMESTAMP", dialect="bigquery").sql(), "TIMESTAMPTZ")
+        self.assertEqual(
+            exp.DataType.build("struct<x int>", dialect="spark").sql(), "STRUCT<x INT>"
+        )
 
     def test_rename_table(self):
         self.assertEqual(
diff --git a/tests/test_helper.py b/tests/test_helper.py
index 82d917e..7d63c34 100644
--- a/tests/test_helper.py
+++ b/tests/test_helper.py
@@ -6,17 +6,16 @@ from sqlglot.helper import name_sequence, tsort
 
 class TestHelper(unittest.TestCase):
     def test_tsort(self):
-        self.assertEqual(tsort({"a": []}), ["a"])
-        self.assertEqual(tsort({"a": ["b", "b"]}), ["b", "a"])
-        self.assertEqual(tsort({"a": ["b"]}), ["b", "a"])
-        self.assertEqual(tsort({"a": ["c"], "b": [], "c": []}), ["c", "a", "b"])
+        self.assertEqual(tsort({"a": set()}), ["a"])
+        self.assertEqual(tsort({"a": {"b"}}), ["b", "a"])
+        self.assertEqual(tsort({"a": {"c"}, "b": set(), "c": set()}), ["b", "c", "a"])
         self.assertEqual(
             tsort(
                 {
-                    "a": ["b", "c"],
-                    "b": ["c"],
-                    "c": [],
-                    "d": ["a"],
+                    "a": {"b", "c"},
+                    "b": {"c"},
+                    "c": set(),
+                    "d": {"a"},
                 }
             ),
             ["c", "b", "a", "d"],
@@ -25,9 +24,9 @@ class TestHelper(unittest.TestCase):
         with self.assertRaises(ValueError):
             tsort(
                 {
-                    "a": ["b", "c"],
-                    "b": ["a"],
-                    "c": [],
+                    "a": {"b", "c"},
+                    "b": {"a"},
+                    "c": set(),
                 }
             )
 
diff --git a/tests/test_optimizer.py b/tests/test_optimizer.py
index 2ae6da9..94bd0ba 100644
--- a/tests/test_optimizer.py
+++ b/tests/test_optimizer.py
@@ -200,6 +200,15 @@ class TestOptimizer(unittest.TestCase):
     def test_qualify_columns(self):
         self.assertEqual(
             optimizer.qualify_columns.qualify_columns(
+                parse_one("WITH x AS (SELECT a FROM db.y) SELECT z FROM db.x"),
+                schema={"db": {"x": {"z": "int"}, "y": {"a": "int"}}},
+                infer_schema=False,
+            ).sql(),
+            "WITH x AS (SELECT y.a AS a FROM db.y) SELECT x.z AS z FROM db.x",
+        )
+
+        self.assertEqual(
+            optimizer.qualify_columns.qualify_columns(
                 parse_one("select y from x"),
                 schema={},
                 infer_schema=False,
@@ -544,9 +553,10 @@ FROM READ_CSV('tests/fixtures/optimizer/tpc-h/nation.csv.gz', 'delimiter', '|')
 
     def test_function_annotation(self):
         schema = {"x": {"cola": "VARCHAR", "colb": "CHAR"}}
-        sql = "SELECT x.cola || TRIM(x.colb) AS col FROM x AS x"
+        sql = "SELECT x.cola || TRIM(x.colb) AS col, DATE(x.colb) FROM x AS x"
 
-        concat_expr_alias = annotate_types(parse_one(sql), schema=schema).expressions[0]
+        expression = annotate_types(parse_one(sql), schema=schema)
+        concat_expr_alias = expression.expressions[0]
         self.assertEqual(concat_expr_alias.type.this, exp.DataType.Type.VARCHAR)
 
         concat_expr = concat_expr_alias.this
@@ -555,6 +565,9 @@ FROM READ_CSV('tests/fixtures/optimizer/tpc-h/nation.csv.gz', 'delimiter', '|')
         self.assertEqual(concat_expr.right.type.this, exp.DataType.Type.VARCHAR)  # TRIM(x.colb)
         self.assertEqual(concat_expr.right.this.type.this, exp.DataType.Type.CHAR)  # x.colb
 
+        date_expr = expression.expressions[1]
+        self.assertEqual(date_expr.type.this, exp.DataType.Type.DATE)
+
         sql = "SELECT CASE WHEN 1=1 THEN x.cola ELSE x.colb END AS col FROM x AS x"
 
         case_expr_alias = annotate_types(parse_one(sql), schema=schema).expressions[0]
diff --git a/tests/test_parser.py b/tests/test_parser.py
index 897357f..96192cd 100644
--- a/tests/test_parser.py
+++ b/tests/test_parser.py
@@ -81,6 +81,11 @@ class TestParser(unittest.TestCase):
     def test_float(self):
         self.assertEqual(parse_one(".2"), parse_one("0.2"))
 
+    def test_unnest_projection(self):
+        expr = parse_one("SELECT foo IN UNNEST(bla) AS bar")
+        self.assertIsInstance(expr.selects[0], exp.Alias)
+        self.assertEqual(expr.selects[0].output_name, "bar")
+
     def test_unary_plus(self):
         self.assertEqual(parse_one("+15"), exp.Literal.number(15))
 
diff --git a/tests/test_tokens.py b/tests/test_tokens.py
index 30af34f..d5a2b7f 100644
--- a/tests/test_tokens.py
+++ b/tests/test_tokens.py
@@ -1,5 +1,6 @@
 import unittest
 
+from sqlglot.dialects import BigQuery
 from sqlglot.tokens import Tokenizer, TokenType
 
 
@@ -68,7 +69,8 @@ x"""
             Tokenizer().tokenize("select /*")
 
     def test_jinja(self):
-        tokenizer = Tokenizer()
+        # Check that {#, #} are treated as token delimiters, even though BigQuery overrides COMMENTS
+        tokenizer = BigQuery.Tokenizer()
 
         tokens = tokenizer.tokenize(
             """
diff --git a/tests/test_transpile.py b/tests/test_transpile.py
index 1085b09..8d762d3 100644
--- a/tests/test_transpile.py
+++ b/tests/test_transpile.py
@@ -280,6 +280,11 @@ FROM v""",
             "select * from t where ((condition = 1)/*test*/)",
             "SELECT * FROM t WHERE ((condition = 1) /* test */)",
         )
+        self.validate(
+            "SELECT 1 // hi this is a comment",
+            "SELECT 1 /* hi this is a comment */",
+            read="snowflake",
+        )
 
     def test_types(self):
         self.validate("INT 1", "CAST(1 AS INT)")
diff --git a/tests/tpch.py b/tests/tpch.py
index 0b6de63..ef2b666 100644
--- a/tests/tpch.py
+++ b/tests/tpch.py
@@ -2,11 +2,89 @@ import time
 
 from sqlglot.optimizer import optimize
 
-INPUT = ""
-OUTPUT = ""
-NUM = 99
-SCHEMA = {}
-KIND = "DS"
+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):
@@ -17,7 +95,7 @@ with open(OUTPUT, "w", encoding="UTF-8") as fixture:
                 for line in file.read().split(";")[0].split("\n")
                 if not line.startswith("--")
             )
-            original = original.replace("`", '"')
+            original = original.replace("`", '"').strip()
             now = time.time()
             try:
                 optimized = optimize(original, schema=SCHEMA)