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