summaryrefslogtreecommitdiffstats
path: root/tests/fixtures/optimizer/tpc-ds
diff options
context:
space:
mode:
Diffstat (limited to 'tests/fixtures/optimizer/tpc-ds')
-rw-r--r--tests/fixtures/optimizer/tpc-ds/tpc-ds.sql540
1 files changed, 277 insertions, 263 deletions
diff --git a/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql b/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql
index 9908756..a6ee325 100644
--- a/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql
+++ b/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql
@@ -46,12 +46,12 @@ WITH "customer_total_return" AS (
SELECT
"customer"."c_customer_id" AS "c_customer_id"
FROM "customer_total_return" AS "ctr1"
-LEFT JOIN "_u_0" AS "_u_0"
- ON "ctr1"."ctr_store_sk" = "_u_0"."_u_1"
JOIN "store" AS "store"
ON "store"."s_state" = 'TN' AND "store"."s_store_sk" = "ctr1"."ctr_store_sk"
JOIN "customer" AS "customer"
ON "ctr1"."ctr_customer_sk" = "customer"."c_customer_sk"
+LEFT JOIN "_u_0" AS "_u_0"
+ ON "ctr1"."ctr_store_sk" = "_u_0"."_u_1"
WHERE
"ctr1"."ctr_total_return" > "_u_0"."_col_0"
ORDER BY
@@ -211,9 +211,8 @@ JOIN "date_dim" AS "date_dim"
ON "date_dim"."d_week_seq" = "wswscs"."d_week_seq" AND "date_dim"."d_year" = 1998
JOIN "wswscs" AS "wswscs_2"
ON "wswscs"."d_week_seq" = "wswscs_2"."d_week_seq" - 53
-CROSS JOIN "date_dim" AS "date_dim_2"
-WHERE
- "date_dim_2"."d_week_seq" = "wswscs_2"."d_week_seq" AND "date_dim_2"."d_year" = 1999
+JOIN "date_dim" AS "date_dim_2"
+ ON "date_dim_2"."d_week_seq" = "wswscs_2"."d_week_seq" AND "date_dim_2"."d_year" = 1999
ORDER BY
"d_week_seq1";
@@ -953,13 +952,13 @@ JOIN "date_dim" AS "date_dim"
ON "date_dim"."d_month_seq" = "_u_0"."_col_0"
JOIN "store_sales" AS "store_sales"
ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
+JOIN "customer" AS "customer"
+ ON "customer"."c_customer_sk" = "store_sales"."ss_customer_sk"
+ AND "customer_address"."ca_address_sk" = "customer"."c_current_addr_sk"
JOIN "item" AS "item"
ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
LEFT JOIN "_u_1" AS "_u_1"
ON "_u_1"."_u_2" = "item"."i_category"
-JOIN "customer" AS "customer"
- ON "customer"."c_customer_sk" = "store_sales"."ss_customer_sk"
- AND "customer_address"."ca_address_sk" = "customer"."c_current_addr_sk"
WHERE
"item"."i_current_price" > 1.2 * "_u_1"."_col_0"
GROUP BY
@@ -1256,9 +1255,9 @@ LIMIT 100;
WITH "a1" AS (
SELECT
SUBSTR("customer_address"."ca_zip", 1, 5) AS "ca_zip"
- FROM "customer_address" AS "customer_address", "customer" AS "customer"
- WHERE
- "customer"."c_preferred_cust_flag" = 'Y'
+ FROM "customer_address" AS "customer_address"
+ JOIN "customer" AS "customer"
+ ON "customer"."c_preferred_cust_flag" = 'Y'
AND "customer_address"."ca_address_sk" = "customer"."c_current_addr_sk"
GROUP BY
"customer_address"."ca_zip"
@@ -1612,17 +1611,17 @@ SELECT
"customer_demographics"."cd_dep_college_count" AS "cd_dep_college_count",
COUNT(*) AS "cnt6"
FROM "customer" AS "customer"
+JOIN "customer_address" AS "customer_address"
+ ON "customer"."c_current_addr_sk" = "customer_address"."ca_address_sk"
+ AND "customer_address"."ca_county" IN ('Lycoming County', 'Sheridan County', 'Kandiyohi County', 'Pike County', 'Greene County')
+JOIN "customer_demographics" AS "customer_demographics"
+ ON "customer_demographics"."cd_demo_sk" = "customer"."c_current_cdemo_sk"
LEFT JOIN "_u_0" AS "_u_0"
ON "customer"."c_customer_sk" = "_u_0"."_u_1"
LEFT JOIN "_u_2" AS "_u_2"
ON "customer"."c_customer_sk" = "_u_2"."_u_3"
LEFT JOIN "_u_4" AS "_u_4"
ON "customer"."c_customer_sk" = "_u_4"."_u_5"
-JOIN "customer_address" AS "customer_address"
- ON "customer"."c_current_addr_sk" = "customer_address"."ca_address_sk"
- AND "customer_address"."ca_county" IN ('Lycoming County', 'Sheridan County', 'Kandiyohi County', 'Pike County', 'Greene County')
-JOIN "customer_demographics" AS "customer_demographics"
- ON "customer_demographics"."cd_demo_sk" = "customer"."c_current_cdemo_sk"
WHERE
NOT "_u_0"."_u_1" IS NULL
AND (
@@ -2179,33 +2178,30 @@ WITH "item_2" AS (
"ics"."i_class_id" AS "i_class_id",
"ics"."i_category_id" AS "i_category_id"
FROM "catalog_sales" AS "catalog_sales"
- CROSS JOIN "item_2" AS "ics"
- CROSS JOIN "d1" AS "d2"
- WHERE
- "catalog_sales"."cs_item_sk" = "ics"."i_item_sk"
- AND "catalog_sales"."cs_sold_date_sk" = "d2"."d_date_sk"
+ JOIN "item_2" AS "ics"
+ ON "catalog_sales"."cs_item_sk" = "ics"."i_item_sk"
+ JOIN "d1" AS "d2"
+ ON "catalog_sales"."cs_sold_date_sk" = "d2"."d_date_sk"
INTERSECT
SELECT
"iws"."i_brand_id" AS "i_brand_id",
"iws"."i_class_id" AS "i_class_id",
"iws"."i_category_id" AS "i_category_id"
FROM "web_sales" AS "web_sales"
- CROSS JOIN "item_2" AS "iws"
- CROSS JOIN "d1" AS "d3"
- WHERE
- "web_sales"."ws_item_sk" = "iws"."i_item_sk"
- AND "web_sales"."ws_sold_date_sk" = "d3"."d_date_sk"
+ JOIN "item_2" AS "iws"
+ ON "web_sales"."ws_item_sk" = "iws"."i_item_sk"
+ JOIN "d1" AS "d3"
+ ON "web_sales"."ws_sold_date_sk" = "d3"."d_date_sk"
), "_q_0" AS (
SELECT
"iss"."i_brand_id" AS "brand_id",
"iss"."i_class_id" AS "class_id",
"iss"."i_category_id" AS "category_id"
FROM "store_sales" AS "store_sales"
- CROSS JOIN "item_2" AS "iss"
- CROSS JOIN "d1" AS "d1"
- WHERE
- "store_sales"."ss_item_sk" = "iss"."i_item_sk"
- AND "store_sales"."ss_sold_date_sk" = "d1"."d_date_sk"
+ JOIN "item_2" AS "iss"
+ ON "store_sales"."ss_item_sk" = "iss"."i_item_sk"
+ JOIN "d1" AS "d1"
+ ON "store_sales"."ss_sold_date_sk" = "d1"."d_date_sk"
INTERSECT
SELECT
"cte_4"."i_brand_id" AS "i_brand_id",
@@ -2242,6 +2238,14 @@ WITH "item_2" AS (
SELECT
AVG("x"."quantity" * "x"."list_price") AS "average_sales"
FROM "x" AS "x"
+), "date_dim_2" AS (
+ SELECT
+ "date_dim"."d_date_sk" AS "d_date_sk",
+ "date_dim"."d_year" AS "d_year",
+ "date_dim"."d_moy" AS "d_moy"
+ FROM "date_dim" AS "date_dim"
+ WHERE
+ "date_dim"."d_moy" = 11 AND "date_dim"."d_year" = 2001
), "_u_0" AS (
SELECT
"item"."i_item_sk" AS "ss_item_sk"
@@ -2256,14 +2260,6 @@ WITH "item_2" AS (
SELECT
"avg_sales"."average_sales" AS "average_sales"
FROM "avg_sales"
-), "date_dim_2" AS (
- SELECT
- "date_dim"."d_date_sk" AS "d_date_sk",
- "date_dim"."d_year" AS "d_year",
- "date_dim"."d_moy" AS "d_moy"
- FROM "date_dim" AS "date_dim"
- WHERE
- "date_dim"."d_moy" = 11 AND "date_dim"."d_year" = 2001
), "cte_9" AS (
SELECT
'store' AS "channel",
@@ -2273,13 +2269,13 @@ WITH "item_2" AS (
SUM("store_sales"."ss_quantity" * "store_sales"."ss_list_price") AS "sales",
COUNT(*) AS "number_sales"
FROM "store_sales" AS "store_sales"
- LEFT JOIN "_u_0" AS "_u_0"
- ON "store_sales"."ss_item_sk" = "_u_0"."ss_item_sk"
- CROSS JOIN "_u_1" AS "_u_1"
JOIN "item_2" AS "item"
ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
JOIN "date_dim_2" AS "date_dim"
ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
+ LEFT JOIN "_u_0" AS "_u_0"
+ ON "store_sales"."ss_item_sk" = "_u_0"."ss_item_sk"
+ CROSS JOIN "_u_1" AS "_u_1"
WHERE
NOT "_u_0"."ss_item_sk" IS NULL
GROUP BY
@@ -2297,13 +2293,13 @@ WITH "item_2" AS (
SUM("catalog_sales"."cs_quantity" * "catalog_sales"."cs_list_price") AS "sales",
COUNT(*) AS "number_sales"
FROM "catalog_sales" AS "catalog_sales"
- LEFT JOIN "_u_0" AS "_u_2"
- ON "catalog_sales"."cs_item_sk" = "_u_2"."ss_item_sk"
- CROSS JOIN "_u_1" AS "_u_3"
JOIN "item_2" AS "item"
ON "catalog_sales"."cs_item_sk" = "item"."i_item_sk"
JOIN "date_dim_2" AS "date_dim"
ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk"
+ LEFT JOIN "_u_0" AS "_u_2"
+ ON "catalog_sales"."cs_item_sk" = "_u_2"."ss_item_sk"
+ CROSS JOIN "_u_1" AS "_u_3"
WHERE
NOT "_u_2"."ss_item_sk" IS NULL
GROUP BY
@@ -2321,13 +2317,13 @@ WITH "item_2" AS (
SUM("web_sales"."ws_quantity" * "web_sales"."ws_list_price") AS "sales",
COUNT(*) AS "number_sales"
FROM "web_sales" AS "web_sales"
- LEFT JOIN "_u_0" AS "_u_4"
- ON "web_sales"."ws_item_sk" = "_u_4"."ss_item_sk"
- CROSS JOIN "_u_1" AS "_u_5"
JOIN "item_2" AS "item"
ON "web_sales"."ws_item_sk" = "item"."i_item_sk"
JOIN "date_dim_2" AS "date_dim"
ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk"
+ LEFT JOIN "_u_0" AS "_u_4"
+ ON "web_sales"."ws_item_sk" = "_u_4"."ss_item_sk"
+ CROSS JOIN "_u_1" AS "_u_5"
WHERE
NOT "_u_4"."ss_item_sk" IS NULL
GROUP BY
@@ -2492,10 +2488,6 @@ SELECT
SUM("catalog_sales"."cs_ext_ship_cost") AS "total shipping cost",
SUM("catalog_sales"."cs_net_profit") AS "total net profit"
FROM "catalog_sales" AS "catalog_sales"
-LEFT JOIN "_u_0" AS "_u_0"
- ON "catalog_sales"."cs_order_number" = "_u_0"."_u_1"
-LEFT JOIN "_u_3" AS "_u_3"
- ON "catalog_sales"."cs_order_number" = "_u_3"."_u_4"
JOIN "date_dim" AS "date_dim"
ON "catalog_sales"."cs_ship_date_sk" = "date_dim"."d_date_sk"
AND "date_dim"."d_date" >= '2002-3-01'
@@ -2508,6 +2500,10 @@ JOIN "customer_address" AS "customer_address"
JOIN "call_center" AS "call_center"
ON "call_center"."cc_county" IN ('Williamson County', 'Williamson County', 'Williamson County', 'Williamson County', 'Williamson County')
AND "catalog_sales"."cs_call_center_sk" = "call_center"."cc_call_center_sk"
+LEFT JOIN "_u_0" AS "_u_0"
+ ON "catalog_sales"."cs_order_number" = "_u_0"."_u_1"
+LEFT JOIN "_u_3" AS "_u_3"
+ ON "catalog_sales"."cs_order_number" = "_u_3"."_u_4"
WHERE
"_u_3"."_u_4" IS NULL
AND ARRAY_ANY("_u_0"."_u_2", "_x" -> "catalog_sales"."cs_warehouse_sk" <> "_x")
@@ -3012,13 +3008,21 @@ WITH "frequent_ss_items" AS (
SELECT
"customer"."c_customer_sk" AS "c_customer_sk"
FROM "store_sales" AS "store_sales"
- CROSS JOIN "max_store_sales"
JOIN "customer_2" AS "customer"
ON "store_sales"."ss_customer_sk" = "customer"."c_customer_sk"
+ CROSS JOIN "max_store_sales"
GROUP BY
"customer"."c_customer_sk"
HAVING
SUM("store_sales"."ss_quantity" * "store_sales"."ss_sales_price") > 0.95 * MAX("max_store_sales"."tpcds_cmax")
+), "date_dim_4" AS (
+ SELECT
+ "date_dim"."d_date_sk" AS "d_date_sk",
+ "date_dim"."d_year" AS "d_year",
+ "date_dim"."d_moy" AS "d_moy"
+ FROM "date_dim" AS "date_dim"
+ WHERE
+ "date_dim"."d_moy" = 6 AND "date_dim"."d_year" = 1998
), "_u_1" AS (
SELECT
"frequent_ss_items"."item_sk" AS "item_sk"
@@ -3031,36 +3035,28 @@ WITH "frequent_ss_items" AS (
FROM "best_ss_customer"
GROUP BY
"best_ss_customer"."c_customer_sk"
-), "date_dim_4" AS (
- SELECT
- "date_dim"."d_date_sk" AS "d_date_sk",
- "date_dim"."d_year" AS "d_year",
- "date_dim"."d_moy" AS "d_moy"
- FROM "date_dim" AS "date_dim"
- WHERE
- "date_dim"."d_moy" = 6 AND "date_dim"."d_year" = 1998
), "_q_1" AS (
SELECT
"catalog_sales"."cs_quantity" * "catalog_sales"."cs_list_price" AS "sales"
FROM "catalog_sales" AS "catalog_sales"
+ JOIN "date_dim_4" AS "date_dim"
+ ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk"
LEFT JOIN "_u_1" AS "_u_1"
ON "catalog_sales"."cs_item_sk" = "_u_1"."item_sk"
LEFT JOIN "_u_2" AS "_u_2"
ON "catalog_sales"."cs_bill_customer_sk" = "_u_2"."c_customer_sk"
- JOIN "date_dim_4" AS "date_dim"
- ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk"
WHERE
NOT "_u_1"."item_sk" IS NULL AND NOT "_u_2"."c_customer_sk" IS NULL
UNION ALL
SELECT
"web_sales"."ws_quantity" * "web_sales"."ws_list_price" AS "sales"
FROM "web_sales" AS "web_sales"
+ JOIN "date_dim_4" AS "date_dim"
+ ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk"
LEFT JOIN "_u_1" AS "_u_3"
ON "web_sales"."ws_item_sk" = "_u_3"."item_sk"
LEFT JOIN "_u_2" AS "_u_4"
ON "web_sales"."ws_bill_customer_sk" = "_u_4"."c_customer_sk"
- JOIN "date_dim_4" AS "date_dim"
- ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk"
WHERE
NOT "_u_3"."item_sk" IS NULL AND NOT "_u_4"."c_customer_sk" IS NULL
)
@@ -3717,13 +3713,13 @@ SELECT
"customer"."c_last_review_date" AS "c_last_review_date",
"ctr1"."ctr_total_return" AS "ctr_total_return"
FROM "customer_total_return" AS "ctr1"
-LEFT JOIN "_u_0" AS "_u_0"
- ON "ctr1"."ctr_state" = "_u_0"."_u_1"
JOIN "customer" AS "customer"
ON "ctr1"."ctr_customer_sk" = "customer"."c_customer_sk"
JOIN "customer_address" AS "customer_address"
ON "customer_address"."ca_address_sk" = "customer"."c_current_addr_sk"
AND "customer_address"."ca_state" = 'IN'
+LEFT JOIN "_u_0" AS "_u_0"
+ ON "ctr1"."ctr_state" = "_u_0"."_u_1"
WHERE
"ctr1"."ctr_total_return" > "_u_0"."_col_0"
ORDER BY
@@ -3957,10 +3953,10 @@ SELECT
FROM "catalog_sales_2" AS "catalog_sales"
JOIN "item" AS "item"
ON "item"."i_item_sk" = "catalog_sales"."cs_item_sk" AND "item"."i_manufact_id" = 610
-LEFT JOIN "_u_0" AS "_u_0"
- ON "_u_0"."_u_1" = "item"."i_item_sk"
JOIN "date_dim_2" AS "date_dim"
ON "date_dim"."d_date_sk" = "catalog_sales"."cs_sold_date_sk"
+LEFT JOIN "_u_0" AS "_u_0"
+ ON "_u_0"."_u_1" = "item"."i_item_sk"
WHERE
"catalog_sales"."cs_ext_discount_amt" > "_u_0"."_col_0"
LIMIT 100;
@@ -4032,20 +4028,7 @@ FROM (SELECT *
GROUP BY i_manufact_id
ORDER BY total_sales
LIMIT 100;
-WITH "item_2" AS (
- SELECT
- "item"."i_item_sk" AS "i_item_sk",
- "item"."i_manufact_id" AS "i_manufact_id"
- FROM "item" AS "item"
-), "_u_0" AS (
- SELECT
- "item"."i_manufact_id" AS "i_manufact_id"
- FROM "item" AS "item"
- WHERE
- "item"."i_category" IN ('Books')
- GROUP BY
- "item"."i_manufact_id"
-), "date_dim_2" AS (
+WITH "date_dim_2" AS (
SELECT
"date_dim"."d_date_sk" AS "d_date_sk",
"date_dim"."d_year" AS "d_year",
@@ -4060,19 +4043,32 @@ WITH "item_2" AS (
FROM "customer_address" AS "customer_address"
WHERE
"customer_address"."ca_gmt_offset" = -5
+), "item_2" AS (
+ SELECT
+ "item"."i_item_sk" AS "i_item_sk",
+ "item"."i_manufact_id" AS "i_manufact_id"
+ FROM "item" AS "item"
+), "_u_0" AS (
+ SELECT
+ "item"."i_manufact_id" AS "i_manufact_id"
+ FROM "item" AS "item"
+ WHERE
+ "item"."i_category" IN ('Books')
+ GROUP BY
+ "item"."i_manufact_id"
), "ss" AS (
SELECT
"item"."i_manufact_id" AS "i_manufact_id",
SUM("store_sales"."ss_ext_sales_price") AS "total_sales"
FROM "store_sales" AS "store_sales"
- JOIN "item_2" AS "item"
- ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
- LEFT JOIN "_u_0" AS "_u_0"
- ON "item"."i_manufact_id" = "_u_0"."i_manufact_id"
JOIN "date_dim_2" AS "date_dim"
ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
JOIN "customer_address_2" AS "customer_address"
ON "store_sales"."ss_addr_sk" = "customer_address"."ca_address_sk"
+ JOIN "item_2" AS "item"
+ ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
+ LEFT JOIN "_u_0" AS "_u_0"
+ ON "item"."i_manufact_id" = "_u_0"."i_manufact_id"
WHERE
NOT "_u_0"."i_manufact_id" IS NULL
GROUP BY
@@ -4082,14 +4078,14 @@ WITH "item_2" AS (
"item"."i_manufact_id" AS "i_manufact_id",
SUM("catalog_sales"."cs_ext_sales_price") AS "total_sales"
FROM "catalog_sales" AS "catalog_sales"
- JOIN "item_2" AS "item"
- ON "catalog_sales"."cs_item_sk" = "item"."i_item_sk"
- LEFT JOIN "_u_0" AS "_u_1"
- ON "item"."i_manufact_id" = "_u_1"."i_manufact_id"
JOIN "date_dim_2" AS "date_dim"
ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk"
JOIN "customer_address_2" AS "customer_address"
ON "catalog_sales"."cs_bill_addr_sk" = "customer_address"."ca_address_sk"
+ JOIN "item_2" AS "item"
+ ON "catalog_sales"."cs_item_sk" = "item"."i_item_sk"
+ LEFT JOIN "_u_0" AS "_u_1"
+ ON "item"."i_manufact_id" = "_u_1"."i_manufact_id"
WHERE
NOT "_u_1"."i_manufact_id" IS NULL
GROUP BY
@@ -4099,14 +4095,14 @@ WITH "item_2" AS (
"item"."i_manufact_id" AS "i_manufact_id",
SUM("web_sales"."ws_ext_sales_price") AS "total_sales"
FROM "web_sales" AS "web_sales"
- JOIN "item_2" AS "item"
- ON "web_sales"."ws_item_sk" = "item"."i_item_sk"
- LEFT JOIN "_u_0" AS "_u_2"
- ON "item"."i_manufact_id" = "_u_2"."i_manufact_id"
JOIN "date_dim_2" AS "date_dim"
ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk"
JOIN "customer_address_2" AS "customer_address"
ON "web_sales"."ws_bill_addr_sk" = "customer_address"."ca_address_sk"
+ JOIN "item_2" AS "item"
+ ON "web_sales"."ws_item_sk" = "item"."i_item_sk"
+ LEFT JOIN "_u_0" AS "_u_2"
+ ON "item"."i_manufact_id" = "_u_2"."i_manufact_id"
WHERE
NOT "_u_2"."i_manufact_id" IS NULL
GROUP BY
@@ -4382,16 +4378,16 @@ SELECT
AVG("customer_demographics"."cd_dep_college_count") AS "_col_16",
MAX("customer_demographics"."cd_dep_college_count") AS "_col_17"
FROM "customer" AS "customer"
+JOIN "customer_address" AS "customer_address"
+ ON "customer"."c_current_addr_sk" = "customer_address"."ca_address_sk"
+JOIN "customer_demographics" AS "customer_demographics"
+ ON "customer_demographics"."cd_demo_sk" = "customer"."c_current_cdemo_sk"
LEFT JOIN "_u_0" AS "_u_0"
ON "customer"."c_customer_sk" = "_u_0"."_u_1"
LEFT JOIN "_u_2" AS "_u_2"
ON "customer"."c_customer_sk" = "_u_2"."_u_3"
LEFT JOIN "_u_4" AS "_u_4"
ON "customer"."c_customer_sk" = "_u_4"."_u_5"
-JOIN "customer_address" AS "customer_address"
- ON "customer"."c_current_addr_sk" = "customer_address"."ca_address_sk"
-JOIN "customer_demographics" AS "customer_demographics"
- ON "customer_demographics"."cd_demo_sk" = "customer"."c_current_cdemo_sk"
WHERE
NOT "_u_0"."_u_1" IS NULL
AND (
@@ -4468,7 +4464,7 @@ ROLLUP (
)
ORDER BY
"lochierarchy" DESC,
- CASE WHEN "lochierarchy" = 0 THEN "item"."i_category" END,
+ CASE WHEN "lochierarchy" = 0 THEN "i_category" END,
"rank_within_parent"
LIMIT 100;
@@ -5349,10 +5345,6 @@ SELECT
"customer_address"."ca_state" AS "ca_state",
SUM("web_sales"."ws_sales_price") AS "_col_2"
FROM "web_sales" AS "web_sales"
-JOIN "item" AS "item"
- ON "web_sales"."ws_item_sk" = "item"."i_item_sk"
-LEFT JOIN "_u_0" AS "_u_0"
- ON "item"."i_item_id" = "_u_0"."i_item_id"
JOIN "customer" AS "customer"
ON "web_sales"."ws_bill_customer_sk" = "customer"."c_customer_sk"
JOIN "customer_address" AS "customer_address"
@@ -5361,6 +5353,10 @@ JOIN "date_dim" AS "date_dim"
ON "date_dim"."d_qoy" = 1
AND "date_dim"."d_year" = 2000
AND "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk"
+JOIN "item" AS "item"
+ ON "web_sales"."ws_item_sk" = "item"."i_item_sk"
+LEFT JOIN "_u_0" AS "_u_0"
+ ON "item"."i_item_id" = "_u_0"."i_item_id"
WHERE
NOT "_u_0"."i_item_id" IS NULL
OR SUBSTR("customer_address"."ca_zip", 1, 5) IN ('85669', '86197', '88274', '83405', '86475', '85392', '85460', '80348', '81792')
@@ -6145,7 +6141,7 @@ WITH web_v1 AS
(
SELECT ws_item_sk item_sk,
d_date,
- sum(Sum(ws_sales_price)) OVER (partition BY ws_item_sk ORDER BY d_date rows BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) cume_sales
+ sum(Sum(ws_sales_price)) OVER (partition BY ws_item_sk ORDER BY d_date rows BETWEEN UNBOUNDED PRECEDING AND CURRENT row) cume_sales
FROM web_sales ,
date_dim
WHERE ws_sold_date_sk=d_date_sk
@@ -6156,7 +6152,7 @@ WITH web_v1 AS
(
SELECT ss_item_sk item_sk,
d_date,
- sum(sum(ss_sales_price)) OVER (partition BY ss_item_sk ORDER BY d_date rows BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) cume_sales
+ sum(sum(ss_sales_price)) OVER (partition BY ss_item_sk ORDER BY d_date rows BETWEEN UNBOUNDED PRECEDING AND CURRENT row) cume_sales
FROM store_sales ,
date_dim
WHERE ss_sold_date_sk=d_date_sk
@@ -6171,8 +6167,8 @@ FROM (
d_date ,
web_sales ,
store_sales ,
- max(web_sales) OVER (partition BY item_sk ORDER BY d_date rows BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) web_cumulative ,
- max(store_sales) OVER (partition BY item_sk ORDER BY d_date rows BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) store_cumulative
+ max(web_sales) OVER (partition BY item_sk ORDER BY d_date rows BETWEEN UNBOUNDED PRECEDING AND CURRENT row) web_cumulative ,
+ max(store_sales) OVER (partition BY item_sk ORDER BY d_date rows BETWEEN UNBOUNDED PRECEDING AND CURRENT row) store_cumulative
FROM (
SELECT
CASE
@@ -6533,10 +6529,6 @@ WITH "cs_or_ws_sales" AS (
SUM("store_sales"."ss_ext_sales_price") AS "revenue"
FROM "my_customers"
CROSS JOIN "date_dim" AS "date_dim"
- JOIN "_u_0" AS "_u_0"
- ON "date_dim"."d_month_seq" >= "_u_0"."_col_0"
- JOIN "_u_1" AS "_u_1"
- ON "date_dim"."d_month_seq" <= "_u_1"."_col_0"
JOIN "store_sales" AS "store_sales"
ON "my_customers"."c_customer_sk" = "store_sales"."ss_customer_sk"
AND "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
@@ -6545,6 +6537,10 @@ WITH "cs_or_ws_sales" AS (
JOIN "store" AS "store"
ON "customer_address"."ca_county" = "store"."s_county"
AND "customer_address"."ca_state" = "store"."s_state"
+ JOIN "_u_0" AS "_u_0"
+ ON "date_dim"."d_month_seq" >= "_u_0"."_col_0"
+ JOIN "_u_1" AS "_u_1"
+ ON "date_dim"."d_month_seq" <= "_u_1"."_col_0"
GROUP BY
"my_customers"."c_customer_sk"
)
@@ -6674,20 +6670,7 @@ FROM (SELECT *
GROUP BY i_item_id
ORDER BY total_sales
LIMIT 100;
-WITH "item_2" AS (
- SELECT
- "item"."i_item_sk" AS "i_item_sk",
- "item"."i_item_id" AS "i_item_id"
- FROM "item" AS "item"
-), "_u_0" AS (
- SELECT
- "item"."i_item_id" AS "i_item_id"
- FROM "item" AS "item"
- WHERE
- "item"."i_color" IN ('firebrick', 'rosy', 'white')
- GROUP BY
- "item"."i_item_id"
-), "date_dim_2" AS (
+WITH "date_dim_2" AS (
SELECT
"date_dim"."d_date_sk" AS "d_date_sk",
"date_dim"."d_year" AS "d_year",
@@ -6702,19 +6685,32 @@ WITH "item_2" AS (
FROM "customer_address" AS "customer_address"
WHERE
"customer_address"."ca_gmt_offset" = -6
+), "item_2" AS (
+ SELECT
+ "item"."i_item_sk" AS "i_item_sk",
+ "item"."i_item_id" AS "i_item_id"
+ FROM "item" AS "item"
+), "_u_0" AS (
+ SELECT
+ "item"."i_item_id" AS "i_item_id"
+ FROM "item" AS "item"
+ WHERE
+ "item"."i_color" IN ('firebrick', 'rosy', 'white')
+ GROUP BY
+ "item"."i_item_id"
), "ss" AS (
SELECT
"item"."i_item_id" AS "i_item_id",
SUM("store_sales"."ss_ext_sales_price") AS "total_sales"
FROM "store_sales" AS "store_sales"
- JOIN "item_2" AS "item"
- ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
- LEFT JOIN "_u_0" AS "_u_0"
- ON "item"."i_item_id" = "_u_0"."i_item_id"
JOIN "date_dim_2" AS "date_dim"
ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
JOIN "customer_address_2" AS "customer_address"
ON "store_sales"."ss_addr_sk" = "customer_address"."ca_address_sk"
+ JOIN "item_2" AS "item"
+ ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
+ LEFT JOIN "_u_0" AS "_u_0"
+ ON "item"."i_item_id" = "_u_0"."i_item_id"
WHERE
NOT "_u_0"."i_item_id" IS NULL
GROUP BY
@@ -6724,14 +6720,14 @@ WITH "item_2" AS (
"item"."i_item_id" AS "i_item_id",
SUM("catalog_sales"."cs_ext_sales_price") AS "total_sales"
FROM "catalog_sales" AS "catalog_sales"
- JOIN "item_2" AS "item"
- ON "catalog_sales"."cs_item_sk" = "item"."i_item_sk"
- LEFT JOIN "_u_0" AS "_u_1"
- ON "item"."i_item_id" = "_u_1"."i_item_id"
JOIN "date_dim_2" AS "date_dim"
ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk"
JOIN "customer_address_2" AS "customer_address"
ON "catalog_sales"."cs_bill_addr_sk" = "customer_address"."ca_address_sk"
+ JOIN "item_2" AS "item"
+ ON "catalog_sales"."cs_item_sk" = "item"."i_item_sk"
+ LEFT JOIN "_u_0" AS "_u_1"
+ ON "item"."i_item_id" = "_u_1"."i_item_id"
WHERE
NOT "_u_1"."i_item_id" IS NULL
GROUP BY
@@ -6741,14 +6737,14 @@ WITH "item_2" AS (
"item"."i_item_id" AS "i_item_id",
SUM("web_sales"."ws_ext_sales_price") AS "total_sales"
FROM "web_sales" AS "web_sales"
- JOIN "item_2" AS "item"
- ON "web_sales"."ws_item_sk" = "item"."i_item_sk"
- LEFT JOIN "_u_0" AS "_u_2"
- ON "item"."i_item_id" = "_u_2"."i_item_id"
JOIN "date_dim_2" AS "date_dim"
ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk"
JOIN "customer_address_2" AS "customer_address"
ON "web_sales"."ws_bill_addr_sk" = "customer_address"."ca_address_sk"
+ JOIN "item_2" AS "item"
+ ON "web_sales"."ws_item_sk" = "item"."i_item_sk"
+ LEFT JOIN "_u_0" AS "_u_2"
+ ON "item"."i_item_id" = "_u_2"."i_item_id"
WHERE
NOT "_u_2"."i_item_id" IS NULL
GROUP BY
@@ -7075,12 +7071,13 @@ WITH "wss" AS (
"wss"."thu_sales" AS "thu_sales2",
"wss"."fri_sales" AS "fri_sales2",
"wss"."sat_sales" AS "sat_sales2"
- FROM "wss", "store" AS "store", "date_dim" AS "date_dim"
- WHERE
- "date_dim"."d_month_seq" <= 1219
+ FROM "wss"
+ JOIN "store" AS "store"
+ ON "wss"."ss_store_sk" = "store"."s_store_sk"
+ JOIN "date_dim" AS "date_dim"
+ ON "date_dim"."d_month_seq" <= 1219
AND "date_dim"."d_month_seq" >= 1208
AND "date_dim"."d_week_seq" = "wss"."d_week_seq"
- AND "wss"."ss_store_sk" = "store"."s_store_sk"
)
SELECT
"store"."s_store_name" AS "s_store_name1",
@@ -7177,20 +7174,7 @@ GROUP BY i_item_id
ORDER BY i_item_id,
total_sales
LIMIT 100;
-WITH "item_2" AS (
- SELECT
- "item"."i_item_sk" AS "i_item_sk",
- "item"."i_item_id" AS "i_item_id"
- FROM "item" AS "item"
-), "_u_0" AS (
- SELECT
- "item"."i_item_id" AS "i_item_id"
- FROM "item" AS "item"
- WHERE
- "item"."i_category" IN ('Jewelry')
- GROUP BY
- "item"."i_item_id"
-), "date_dim_2" AS (
+WITH "date_dim_2" AS (
SELECT
"date_dim"."d_date_sk" AS "d_date_sk",
"date_dim"."d_year" AS "d_year",
@@ -7205,19 +7189,32 @@ WITH "item_2" AS (
FROM "customer_address" AS "customer_address"
WHERE
"customer_address"."ca_gmt_offset" = -6
+), "item_2" AS (
+ SELECT
+ "item"."i_item_sk" AS "i_item_sk",
+ "item"."i_item_id" AS "i_item_id"
+ FROM "item" AS "item"
+), "_u_0" AS (
+ SELECT
+ "item"."i_item_id" AS "i_item_id"
+ FROM "item" AS "item"
+ WHERE
+ "item"."i_category" IN ('Jewelry')
+ GROUP BY
+ "item"."i_item_id"
), "ss" AS (
SELECT
"item"."i_item_id" AS "i_item_id",
SUM("store_sales"."ss_ext_sales_price") AS "total_sales"
FROM "store_sales" AS "store_sales"
- JOIN "item_2" AS "item"
- ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
- LEFT JOIN "_u_0" AS "_u_0"
- ON "item"."i_item_id" = "_u_0"."i_item_id"
JOIN "date_dim_2" AS "date_dim"
ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
JOIN "customer_address_2" AS "customer_address"
ON "store_sales"."ss_addr_sk" = "customer_address"."ca_address_sk"
+ JOIN "item_2" AS "item"
+ ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
+ LEFT JOIN "_u_0" AS "_u_0"
+ ON "item"."i_item_id" = "_u_0"."i_item_id"
WHERE
NOT "_u_0"."i_item_id" IS NULL
GROUP BY
@@ -7227,14 +7224,14 @@ WITH "item_2" AS (
"item"."i_item_id" AS "i_item_id",
SUM("catalog_sales"."cs_ext_sales_price") AS "total_sales"
FROM "catalog_sales" AS "catalog_sales"
- JOIN "item_2" AS "item"
- ON "catalog_sales"."cs_item_sk" = "item"."i_item_sk"
- LEFT JOIN "_u_0" AS "_u_1"
- ON "item"."i_item_id" = "_u_1"."i_item_id"
JOIN "date_dim_2" AS "date_dim"
ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk"
JOIN "customer_address_2" AS "customer_address"
ON "catalog_sales"."cs_bill_addr_sk" = "customer_address"."ca_address_sk"
+ JOIN "item_2" AS "item"
+ ON "catalog_sales"."cs_item_sk" = "item"."i_item_sk"
+ LEFT JOIN "_u_0" AS "_u_1"
+ ON "item"."i_item_id" = "_u_1"."i_item_id"
WHERE
NOT "_u_1"."i_item_id" IS NULL
GROUP BY
@@ -7244,14 +7241,14 @@ WITH "item_2" AS (
"item"."i_item_id" AS "i_item_id",
SUM("web_sales"."ws_ext_sales_price") AS "total_sales"
FROM "web_sales" AS "web_sales"
- JOIN "item_2" AS "item"
- ON "web_sales"."ws_item_sk" = "item"."i_item_sk"
- LEFT JOIN "_u_0" AS "_u_2"
- ON "item"."i_item_id" = "_u_2"."i_item_id"
JOIN "date_dim_2" AS "date_dim"
ON "web_sales"."ws_sold_date_sk" = "date_dim"."d_date_sk"
JOIN "customer_address_2" AS "customer_address"
ON "web_sales"."ws_bill_addr_sk" = "customer_address"."ca_address_sk"
+ JOIN "item_2" AS "item"
+ ON "web_sales"."ws_item_sk" = "item"."i_item_sk"
+ LEFT JOIN "_u_0" AS "_u_2"
+ ON "item"."i_item_id" = "_u_2"."i_item_id"
WHERE
NOT "_u_2"."i_item_id" IS NULL
GROUP BY
@@ -7395,13 +7392,17 @@ WITH "store_2" AS (
), "all_sales" AS (
SELECT
SUM("store_sales"."ss_ext_sales_price") AS "total"
- FROM "store_sales" AS "store_sales", "store_2" AS "store", "date_dim_2" AS "date_dim", "customer_2" AS "customer", "customer_address_2" AS "customer_address", "item_2" AS "item"
- WHERE
- "customer_address"."ca_address_sk" = "customer"."c_current_addr_sk"
- AND "store_sales"."ss_customer_sk" = "customer"."c_customer_sk"
- AND "store_sales"."ss_item_sk" = "item"."i_item_sk"
- AND "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
- AND "store_sales"."ss_store_sk" = "store"."s_store_sk"
+ FROM "store_sales" AS "store_sales"
+ JOIN "store_2" AS "store"
+ ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
+ JOIN "date_dim_2" AS "date_dim"
+ ON "store_sales"."ss_sold_date_sk" = "date_dim"."d_date_sk"
+ JOIN "customer_2" AS "customer"
+ ON "store_sales"."ss_customer_sk" = "customer"."c_customer_sk"
+ JOIN "customer_address_2" AS "customer_address"
+ ON "customer_address"."ca_address_sk" = "customer"."c_current_addr_sk"
+ JOIN "item_2" AS "item"
+ ON "store_sales"."ss_item_sk" = "item"."i_item_sk"
)
SELECT
"promotional_sales"."promotions" AS "promotions",
@@ -9111,17 +9112,17 @@ SELECT
"customer_demographics"."cd_credit_rating" AS "cd_credit_rating",
COUNT(*) AS "cnt3"
FROM "customer" AS "customer"
+JOIN "customer_address" AS "customer_address"
+ ON "customer"."c_current_addr_sk" = "customer_address"."ca_address_sk"
+ AND "customer_address"."ca_state" IN ('KS', 'AZ', 'NE')
+JOIN "customer_demographics" AS "customer_demographics"
+ ON "customer_demographics"."cd_demo_sk" = "customer"."c_current_cdemo_sk"
LEFT JOIN "_u_0" AS "_u_0"
ON "customer"."c_customer_sk" = "_u_0"."_u_1"
LEFT JOIN "_u_2" AS "_u_2"
ON "customer"."c_customer_sk" = "_u_2"."_u_3"
LEFT JOIN "_u_4" AS "_u_4"
ON "customer"."c_customer_sk" = "_u_4"."_u_5"
-JOIN "customer_address" AS "customer_address"
- ON "customer"."c_current_addr_sk" = "customer_address"."ca_address_sk"
- AND "customer_address"."ca_state" IN ('KS', 'AZ', 'NE')
-JOIN "customer_demographics" AS "customer_demographics"
- ON "customer_demographics"."cd_demo_sk" = "customer"."c_current_cdemo_sk"
WHERE
"_u_2"."_u_3" IS NULL AND "_u_4"."_u_5" IS NULL AND NOT "_u_0"."_u_1" IS NULL
GROUP BY
@@ -9186,7 +9187,7 @@ WITH "store_sales_2" AS (
"store_sales"."ss_store_sk" AS "ss_store_sk",
"store_sales"."ss_net_profit" AS "ss_net_profit"
FROM "store_sales" AS "store_sales"
-), "date_dim_2" AS (
+), "d1" AS (
SELECT
"date_dim"."d_date_sk" AS "d_date_sk",
"date_dim"."d_month_seq" AS "d_month_seq"
@@ -9200,7 +9201,7 @@ WITH "store_sales_2" AS (
FROM "store_sales_2" AS "store_sales"
JOIN "store" AS "store"
ON "store"."s_store_sk" = "store_sales"."ss_store_sk"
- JOIN "date_dim_2" AS "date_dim"
+ JOIN "d1" AS "date_dim"
ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk"
GROUP BY
"store"."s_state"
@@ -9220,12 +9221,12 @@ SELECT
GROUPING("store"."s_state") + GROUPING("store"."s_county") AS "lochierarchy",
RANK() OVER (PARTITION BY GROUPING("store"."s_state") + GROUPING("store"."s_county"), CASE WHEN GROUPING("store"."s_county") = 0 THEN "store"."s_state" END ORDER BY SUM("store_sales"."ss_net_profit") DESC) AS "rank_within_parent"
FROM "store_sales_2" AS "store_sales"
+JOIN "d1" AS "d1"
+ ON "d1"."d_date_sk" = "store_sales"."ss_sold_date_sk"
JOIN "store" AS "store"
ON "store"."s_store_sk" = "store_sales"."ss_store_sk"
LEFT JOIN "_u_0" AS "_u_0"
ON "store"."s_state" = "_u_0"."s_state"
-JOIN "date_dim_2" AS "d1"
- ON "d1"."d_date_sk" = "store_sales"."ss_sold_date_sk"
WHERE
NOT "_u_0"."s_state" IS NULL
GROUP BY
@@ -9235,7 +9236,7 @@ ROLLUP (
)
ORDER BY
"lochierarchy" DESC,
- CASE WHEN "lochierarchy" = 0 THEN "store"."s_state" END,
+ CASE WHEN "lochierarchy" = 0 THEN "s_state" END,
"rank_within_parent"
LIMIT 100;
@@ -9303,27 +9304,24 @@ WITH "date_dim_2" AS (
"catalog_sales"."cs_item_sk" AS "sold_item_sk",
"catalog_sales"."cs_sold_time_sk" AS "time_sk"
FROM "catalog_sales" AS "catalog_sales"
- CROSS JOIN "date_dim_2" AS "date_dim"
- WHERE
- "date_dim"."d_date_sk" = "catalog_sales"."cs_sold_date_sk"
+ JOIN "date_dim_2" AS "date_dim"
+ ON "date_dim"."d_date_sk" = "catalog_sales"."cs_sold_date_sk"
UNION ALL
SELECT
"store_sales"."ss_ext_sales_price" AS "ext_price",
"store_sales"."ss_item_sk" AS "sold_item_sk",
"store_sales"."ss_sold_time_sk" AS "time_sk"
FROM "store_sales" AS "store_sales"
- CROSS JOIN "date_dim_2" AS "date_dim"
- WHERE
- "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk"
+ JOIN "date_dim_2" AS "date_dim"
+ ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk"
), "tmp" AS (
SELECT
"web_sales"."ws_ext_sales_price" AS "ext_price",
"web_sales"."ws_item_sk" AS "sold_item_sk",
"web_sales"."ws_sold_time_sk" AS "time_sk"
FROM "web_sales" AS "web_sales"
- CROSS JOIN "date_dim_2" AS "date_dim"
- WHERE
- "date_dim"."d_date_sk" = "web_sales"."ws_sold_date_sk"
+ JOIN "date_dim_2" AS "date_dim"
+ ON "date_dim"."d_date_sk" = "web_sales"."ws_sold_date_sk"
UNION ALL
SELECT
"cte_4"."ext_price" AS "ext_price",
@@ -10932,13 +10930,13 @@ SELECT
"customer_address"."ca_location_type" AS "ca_location_type",
"ctr1"."ctr_total_return" AS "ctr_total_return"
FROM "customer_total_return" AS "ctr1"
-LEFT JOIN "_u_0" AS "_u_0"
- ON "ctr1"."ctr_state" = "_u_0"."_u_1"
JOIN "customer" AS "customer"
ON "ctr1"."ctr_customer_sk" = "customer"."c_customer_sk"
JOIN "customer_address" AS "customer_address"
ON "customer_address"."ca_address_sk" = "customer"."c_current_addr_sk"
AND "customer_address"."ca_state" = 'TX'
+LEFT JOIN "_u_0" AS "_u_0"
+ ON "ctr1"."ctr_state" = "_u_0"."_u_1"
WHERE
"ctr1"."ctr_total_return" > "_u_0"."_col_0"
ORDER BY
@@ -11089,7 +11087,12 @@ WHERE sr_items.item_id = cr_items.item_id
ORDER BY sr_items.item_id,
sr_item_qty
LIMIT 100;
-WITH "date_dim_2" AS (
+WITH "item_2" AS (
+ SELECT
+ "item"."i_item_sk" AS "i_item_sk",
+ "item"."i_item_id" AS "i_item_id"
+ FROM "item" AS "item"
+), "date_dim_2" AS (
SELECT
"date_dim"."d_date_sk" AS "d_date_sk",
"date_dim"."d_date" AS "d_date"
@@ -11112,22 +11115,17 @@ WITH "date_dim_2" AS (
NOT "_u_0"."d_week_seq" IS NULL
GROUP BY
"date_dim"."d_date"
-), "item_2" AS (
- SELECT
- "item"."i_item_sk" AS "i_item_sk",
- "item"."i_item_id" AS "i_item_id"
- FROM "item" AS "item"
), "sr_items" AS (
SELECT
"item"."i_item_id" AS "item_id",
SUM("store_returns"."sr_return_quantity") AS "sr_item_qty"
FROM "store_returns" AS "store_returns"
+ JOIN "item_2" AS "item"
+ ON "store_returns"."sr_item_sk" = "item"."i_item_sk"
JOIN "date_dim_2" AS "date_dim"
ON "store_returns"."sr_returned_date_sk" = "date_dim"."d_date_sk"
LEFT JOIN "_u_1" AS "_u_1"
ON "date_dim"."d_date" = "_u_1"."d_date"
- JOIN "item_2" AS "item"
- ON "store_returns"."sr_item_sk" = "item"."i_item_sk"
WHERE
NOT "_u_1"."d_date" IS NULL
GROUP BY
@@ -11147,12 +11145,12 @@ WITH "date_dim_2" AS (
"item"."i_item_id" AS "item_id",
SUM("catalog_returns"."cr_return_quantity") AS "cr_item_qty"
FROM "catalog_returns" AS "catalog_returns"
+ JOIN "item_2" AS "item"
+ ON "catalog_returns"."cr_item_sk" = "item"."i_item_sk"
JOIN "date_dim_2" AS "date_dim"
ON "catalog_returns"."cr_returned_date_sk" = "date_dim"."d_date_sk"
LEFT JOIN "_u_3" AS "_u_3"
ON "date_dim"."d_date" = "_u_3"."d_date"
- JOIN "item_2" AS "item"
- ON "catalog_returns"."cr_item_sk" = "item"."i_item_sk"
WHERE
NOT "_u_3"."d_date" IS NULL
GROUP BY
@@ -11172,12 +11170,12 @@ WITH "date_dim_2" AS (
"item"."i_item_id" AS "item_id",
SUM("web_returns"."wr_return_quantity") AS "wr_item_qty"
FROM "web_returns" AS "web_returns"
+ JOIN "item_2" AS "item"
+ ON "web_returns"."wr_item_sk" = "item"."i_item_sk"
JOIN "date_dim_2" AS "date_dim"
ON "web_returns"."wr_returned_date_sk" = "date_dim"."d_date_sk"
LEFT JOIN "_u_5" AS "_u_5"
ON "date_dim"."d_date" = "_u_5"."d_date"
- JOIN "item_2" AS "item"
- ON "web_returns"."wr_item_sk" = "item"."i_item_sk"
WHERE
NOT "_u_5"."d_date" IS NULL
GROUP BY
@@ -11437,7 +11435,7 @@ ROLLUP (
)
ORDER BY
"lochierarchy" DESC,
- CASE WHEN "lochierarchy" = 0 THEN "item"."i_category" END,
+ CASE WHEN "lochierarchy" = 0 THEN "i_category" END,
"rank_within_parent"
LIMIT 100;
@@ -11687,73 +11685,87 @@ WITH "store_sales_2" AS (
), "s2" AS (
SELECT
COUNT(*) AS "h9_to_9_30"
- FROM "store_sales_2" AS "store_sales", "household_demographics_2" AS "household_demographics", "time_dim" AS "time_dim", "store_2" AS "store"
- WHERE
- "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk"
- AND "store_sales"."ss_sold_time_sk" = "time_dim"."t_time_sk"
- AND "store_sales"."ss_store_sk" = "store"."s_store_sk"
+ FROM "store_sales_2" AS "store_sales"
+ JOIN "household_demographics_2" AS "household_demographics"
+ ON "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk"
+ JOIN "time_dim" AS "time_dim"
+ ON "store_sales"."ss_sold_time_sk" = "time_dim"."t_time_sk"
AND "time_dim"."t_hour" = 9
AND "time_dim"."t_minute" < 30
+ JOIN "store_2" AS "store"
+ ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
), "s3" AS (
SELECT
COUNT(*) AS "h9_30_to_10"
- FROM "store_sales_2" AS "store_sales", "household_demographics_2" AS "household_demographics", "time_dim" AS "time_dim", "store_2" AS "store"
- WHERE
- "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk"
- AND "store_sales"."ss_sold_time_sk" = "time_dim"."t_time_sk"
- AND "store_sales"."ss_store_sk" = "store"."s_store_sk"
+ FROM "store_sales_2" AS "store_sales"
+ JOIN "household_demographics_2" AS "household_demographics"
+ ON "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk"
+ JOIN "time_dim" AS "time_dim"
+ ON "store_sales"."ss_sold_time_sk" = "time_dim"."t_time_sk"
AND "time_dim"."t_hour" = 9
AND "time_dim"."t_minute" >= 30
+ JOIN "store_2" AS "store"
+ ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
), "s4" AS (
SELECT
COUNT(*) AS "h10_to_10_30"
- FROM "store_sales_2" AS "store_sales", "household_demographics_2" AS "household_demographics", "time_dim" AS "time_dim", "store_2" AS "store"
- WHERE
- "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk"
- AND "store_sales"."ss_sold_time_sk" = "time_dim"."t_time_sk"
- AND "store_sales"."ss_store_sk" = "store"."s_store_sk"
+ FROM "store_sales_2" AS "store_sales"
+ JOIN "household_demographics_2" AS "household_demographics"
+ ON "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk"
+ JOIN "time_dim" AS "time_dim"
+ ON "store_sales"."ss_sold_time_sk" = "time_dim"."t_time_sk"
AND "time_dim"."t_hour" = 10
AND "time_dim"."t_minute" < 30
+ JOIN "store_2" AS "store"
+ ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
), "s5" AS (
SELECT
COUNT(*) AS "h10_30_to_11"
- FROM "store_sales_2" AS "store_sales", "household_demographics_2" AS "household_demographics", "time_dim" AS "time_dim", "store_2" AS "store"
- WHERE
- "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk"
- AND "store_sales"."ss_sold_time_sk" = "time_dim"."t_time_sk"
- AND "store_sales"."ss_store_sk" = "store"."s_store_sk"
+ FROM "store_sales_2" AS "store_sales"
+ JOIN "household_demographics_2" AS "household_demographics"
+ ON "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk"
+ JOIN "time_dim" AS "time_dim"
+ ON "store_sales"."ss_sold_time_sk" = "time_dim"."t_time_sk"
AND "time_dim"."t_hour" = 10
AND "time_dim"."t_minute" >= 30
+ JOIN "store_2" AS "store"
+ ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
), "s6" AS (
SELECT
COUNT(*) AS "h11_to_11_30"
- FROM "store_sales_2" AS "store_sales", "household_demographics_2" AS "household_demographics", "time_dim" AS "time_dim", "store_2" AS "store"
- WHERE
- "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk"
- AND "store_sales"."ss_sold_time_sk" = "time_dim"."t_time_sk"
- AND "store_sales"."ss_store_sk" = "store"."s_store_sk"
+ FROM "store_sales_2" AS "store_sales"
+ JOIN "household_demographics_2" AS "household_demographics"
+ ON "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk"
+ JOIN "time_dim" AS "time_dim"
+ ON "store_sales"."ss_sold_time_sk" = "time_dim"."t_time_sk"
AND "time_dim"."t_hour" = 11
AND "time_dim"."t_minute" < 30
+ JOIN "store_2" AS "store"
+ ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
), "s7" AS (
SELECT
COUNT(*) AS "h11_30_to_12"
- FROM "store_sales_2" AS "store_sales", "household_demographics_2" AS "household_demographics", "time_dim" AS "time_dim", "store_2" AS "store"
- WHERE
- "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk"
- AND "store_sales"."ss_sold_time_sk" = "time_dim"."t_time_sk"
- AND "store_sales"."ss_store_sk" = "store"."s_store_sk"
+ FROM "store_sales_2" AS "store_sales"
+ JOIN "household_demographics_2" AS "household_demographics"
+ ON "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk"
+ JOIN "time_dim" AS "time_dim"
+ ON "store_sales"."ss_sold_time_sk" = "time_dim"."t_time_sk"
AND "time_dim"."t_hour" = 11
AND "time_dim"."t_minute" >= 30
+ JOIN "store_2" AS "store"
+ ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
), "s8" AS (
SELECT
COUNT(*) AS "h12_to_12_30"
- FROM "store_sales_2" AS "store_sales", "household_demographics_2" AS "household_demographics", "time_dim" AS "time_dim", "store_2" AS "store"
- WHERE
- "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk"
- AND "store_sales"."ss_sold_time_sk" = "time_dim"."t_time_sk"
- AND "store_sales"."ss_store_sk" = "store"."s_store_sk"
+ FROM "store_sales_2" AS "store_sales"
+ JOIN "household_demographics_2" AS "household_demographics"
+ ON "store_sales"."ss_hdemo_sk" = "household_demographics"."hd_demo_sk"
+ JOIN "time_dim" AS "time_dim"
+ ON "store_sales"."ss_sold_time_sk" = "time_dim"."t_time_sk"
AND "time_dim"."t_hour" = 12
AND "time_dim"."t_minute" < 30
+ JOIN "store_2" AS "store"
+ ON "store_sales"."ss_store_sk" = "store"."s_store_sk"
)
SELECT
"s1"."h8_30_to_9" AS "h8_30_to_9",
@@ -11946,13 +11958,15 @@ WITH "web_sales_2" AS (
), "pt" AS (
SELECT
COUNT(*) AS "pmc"
- FROM "web_sales_2" AS "web_sales", "household_demographics_2" AS "household_demographics", "time_dim" AS "time_dim", "web_page_2" AS "web_page"
- WHERE
- "time_dim"."t_hour" <= 21
+ FROM "web_sales_2" AS "web_sales"
+ JOIN "household_demographics_2" AS "household_demographics"
+ ON "web_sales"."ws_ship_hdemo_sk" = "household_demographics"."hd_demo_sk"
+ JOIN "time_dim" AS "time_dim"
+ ON "time_dim"."t_hour" <= 21
AND "time_dim"."t_hour" >= 20
- AND "web_sales"."ws_ship_hdemo_sk" = "household_demographics"."hd_demo_sk"
AND "web_sales"."ws_sold_time_sk" = "time_dim"."t_time_sk"
- AND "web_sales"."ws_web_page_sk" = "web_page"."wp_web_page_sk"
+ JOIN "web_page_2" AS "web_page"
+ ON "web_sales"."ws_web_page_sk" = "web_page"."wp_web_page_sk"
)
SELECT
CAST("at1"."amc" AS DECIMAL(15, 4)) / CAST("pt"."pmc" AS DECIMAL(15, 4)) AS "am_pm_ratio"
@@ -11997,10 +12011,10 @@ GROUP BY cc_call_center_id,
cd_education_status
ORDER BY Sum(cr_net_loss) DESC;
SELECT
- "call_center"."cc_call_center_id" AS Call_Center,
- "call_center"."cc_name" AS Call_Center_Name,
- "call_center"."cc_manager" AS Manager,
- SUM("catalog_returns"."cr_net_loss") AS Returns_Loss
+ "call_center"."cc_call_center_id" AS "call_center",
+ "call_center"."cc_name" AS "call_center_name",
+ "call_center"."cc_manager" AS "manager",
+ SUM("catalog_returns"."cr_net_loss") AS "returns_loss"
FROM "call_center" AS "call_center"
JOIN "catalog_returns" AS "catalog_returns"
ON "catalog_returns"."cr_call_center_sk" = "call_center"."cc_call_center_sk"
@@ -12096,10 +12110,10 @@ SELECT
FROM "web_sales_2" AS "web_sales"
JOIN "item" AS "item"
ON "item"."i_item_sk" = "web_sales"."ws_item_sk" AND "item"."i_manufact_id" = 718
-LEFT JOIN "_u_0" AS "_u_0"
- ON "_u_0"."_u_1" = "item"."i_item_sk"
JOIN "date_dim_2" AS "date_dim"
ON "date_dim"."d_date_sk" = "web_sales"."ws_sold_date_sk"
+LEFT JOIN "_u_0" AS "_u_0"
+ ON "_u_0"."_u_1" = "item"."i_item_sk"
WHERE
"web_sales"."ws_ext_discount_amt" > "_u_0"."_col_0"
ORDER BY
@@ -12208,10 +12222,6 @@ SELECT
SUM("web_sales"."ws_ext_ship_cost") AS "total shipping cost",
SUM("web_sales"."ws_net_profit") AS "total net profit"
FROM "web_sales" AS "web_sales"
-LEFT JOIN "_u_0" AS "_u_0"
- ON "web_sales"."ws_order_number" = "_u_0"."_u_1"
-LEFT JOIN "_u_3" AS "_u_3"
- ON "web_sales"."ws_order_number" = "_u_3"."_u_4"
JOIN "date_dim" AS "date_dim"
ON "date_dim"."d_date" >= '2000-3-01'
AND "web_sales"."ws_ship_date_sk" = "date_dim"."d_date_sk"
@@ -12224,6 +12234,10 @@ JOIN "customer_address" AS "customer_address"
JOIN "web_site" AS "web_site"
ON "web_sales"."ws_web_site_sk" = "web_site"."web_site_sk"
AND "web_site"."web_company_name" = 'pri'
+LEFT JOIN "_u_0" AS "_u_0"
+ ON "web_sales"."ws_order_number" = "_u_0"."_u_1"
+LEFT JOIN "_u_3" AS "_u_3"
+ ON "web_sales"."ws_order_number" = "_u_3"."_u_4"
WHERE
"_u_3"."_u_4" IS NULL
AND ARRAY_ANY("_u_0"."_u_2", "_x" -> "web_sales"."ws_warehouse_sk" <> "_x")
@@ -12303,10 +12317,6 @@ SELECT
SUM("web_sales"."ws_ext_ship_cost") AS "total shipping cost",
SUM("web_sales"."ws_net_profit") AS "total net profit"
FROM "web_sales" AS "web_sales"
-LEFT JOIN "_u_0" AS "_u_0"
- ON "web_sales"."ws_order_number" = "_u_0"."ws_order_number"
-LEFT JOIN "_u_1" AS "_u_1"
- ON "web_sales"."ws_order_number" = "_u_1"."wr_order_number"
JOIN "date_dim" AS "date_dim"
ON "date_dim"."d_date" >= '2000-4-01'
AND "web_sales"."ws_ship_date_sk" = "date_dim"."d_date_sk"
@@ -12319,6 +12329,10 @@ JOIN "customer_address" AS "customer_address"
JOIN "web_site" AS "web_site"
ON "web_sales"."ws_web_site_sk" = "web_site"."web_site_sk"
AND "web_site"."web_company_name" = 'pri'
+LEFT JOIN "_u_0" AS "_u_0"
+ ON "web_sales"."ws_order_number" = "_u_0"."ws_order_number"
+LEFT JOIN "_u_1" AS "_u_1"
+ ON "web_sales"."ws_order_number" = "_u_1"."wr_order_number"
WHERE
NOT "_u_0"."ws_order_number" IS NULL AND NOT "_u_1"."wr_order_number" IS NULL
ORDER BY