From 67578a7602a5be7eb51f324086c8d49bcf8b7498 Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Fri, 16 Jun 2023 11:41:18 +0200 Subject: Merging upstream version 16.2.1. Signed-off-by: Daniel Baumann --- tests/dataframe/unit/test_functions.py | 2 +- tests/dialects/test_bigquery.py | 24 +- tests/dialects/test_clickhouse.py | 14 + tests/dialects/test_dialect.py | 23 +- tests/dialects/test_duckdb.py | 18 +- tests/dialects/test_mysql.py | 2 + tests/dialects/test_oracle.py | 2 +- tests/dialects/test_postgres.py | 56 + tests/dialects/test_presto.py | 2 + tests/dialects/test_snowflake.py | 8 +- tests/dialects/test_teradata.py | 12 +- tests/fixtures/identity.sql | 15 + tests/fixtures/optimizer/isolate_table_selects.sql | 4 +- tests/fixtures/optimizer/optimizer.sql | 35 +- tests/fixtures/optimizer/pushdown_predicates.sql | 4 +- tests/fixtures/optimizer/qualify_columns.sql | 4 + tests/fixtures/optimizer/tpc-ds/tpc-ds.sql | 2110 +++++++++++--------- tests/fixtures/optimizer/tpc-h/tpc-h.sql | 122 +- tests/test_build.py | 10 + tests/test_executor.py | 24 +- tests/test_expressions.py | 30 +- tests/test_helper.py | 21 +- tests/test_optimizer.py | 17 +- tests/test_parser.py | 5 + tests/test_tokens.py | 4 +- tests/test_transpile.py | 5 + tests/tpch.py | 90 +- 27 files changed, 1633 insertions(+), 1030 deletions(-) (limited to 'tests') 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)") @@ -27,6 +30,9 @@ class TestBigQuery(Validator): self.validate_identity("SELECT * FROM q UNPIVOT(values FOR quarter IN (b, c))") self.validate_identity("""CREATE TABLE x (a STRUCT>)""") self.validate_identity("""CREATE TABLE x (a STRUCT)""") + 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)""" ) @@ -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,7 +45,21 @@ 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)'"} ) 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 @@ -1084,6 +1084,14 @@ class TestDialect(Validator): self.validate_identity("some.column LIKE 'foo' + another.column + 'bar'") 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={ @@ -1177,10 +1185,21 @@ 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={ @@ -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 @@ -9,6 +9,20 @@ class TestDuckDB(Validator): self.validate_identity("SELECT CURRENT_DATE") 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={ @@ -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", @@ -2707,6 +2707,72 @@ ORDER BY "i_item_id" 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 -------------------------------------- @@ -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", @@ -6914,6 +6965,205 @@ ORDER BY "v1"."avg_monthly_sales" 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 -------------------------------------- @@ -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" - ON "hd2"."hd_income_band_sk" = "ib2"."ib_income_band_sk" - 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" - ON "customer"."c_first_sales_date_sk" = "d2"."d_date_sk" - JOIN "d1" 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" - 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" + JOIN "household_demographics" 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 "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 "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 "date_dim" AS "d3" + ON "customer"."c_first_shipto_date_sk" = "d3"."d_date_sk" + JOIN "customer_demographics" AS "cd1" + ON "cd1"."cd_marital_status" <> "cd2"."cd_marital_status" + AND "store_sales"."ss_cdemo_sk" = "cd1"."cd_demo_sk" GROUP BY "item"."i_product_name", "item"."i_item_sk", @@ -7919,6 +8142,107 @@ ORDER BY "cs1"."store_name", "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 -------------------------------------- @@ -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 @@ -10557,19 +10873,19 @@ WITH "ms" AS ( SUM("store_sales"."ss_net_profit") AS "profit" FROM "store_sales" AS "store_sales" JOIN "date_dim" AS "date_dim" - ON "date_dim"."d_dow" = 1 - 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" + 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 "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 @@ -127,6 +127,16 @@ class TestBuild(unittest.TestCase): "SELECT x FROM tbl WHERE x > 0 FOR SHARE", "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", dialect="spark").sql(), "STRUCT" + ) 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 @@ -198,6 +198,15 @@ class TestOptimizer(unittest.TestCase): self.check_file("normalize", normalize) 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"), @@ -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) -- cgit v1.2.3