sql.presto.tpcds.q57.sql Maven / Gradle / Ivy
WITH
v1 AS (
SELECT
"i_category"
, "i_brand"
, "cc_name"
, "d_year"
, "d_moy"
, "sum"("cs_sales_price") "sum_sales"
, "avg"("sum"("cs_sales_price")) OVER (PARTITION BY "i_category", "i_brand", "cc_name", "d_year") "avg_monthly_sales"
, "rank"() OVER (PARTITION BY "i_category", "i_brand", "cc_name" ORDER BY "d_year" ASC, "d_moy" ASC) "rn"
FROM
${database}.${schema}.item
, ${database}.${schema}.catalog_sales
, ${database}.${schema}.date_dim
, ${database}.${schema}.call_center
WHERE ("cs_item_sk" = "i_item_sk")
AND ("cs_sold_date_sk" = "d_date_sk")
AND ("cc_call_center_sk" = "cs_call_center_sk")
AND (("d_year" = 1999)
OR (("d_year" = (1999 - 1))
AND ("d_moy" = 12))
OR (("d_year" = (1999 + 1))
AND ("d_moy" = 1)))
GROUP BY "i_category", "i_brand", "cc_name", "d_year", "d_moy"
)
, v2 AS (
SELECT
"v1"."i_category"
, "v1"."i_brand"
, "v1"."cc_name"
, "v1"."d_year"
, "v1"."d_moy"
, "v1"."avg_monthly_sales"
, "v1"."sum_sales"
, "v1_lag"."sum_sales" "psum"
, "v1_lead"."sum_sales" "nsum"
FROM
v1
, v1 v1_lag
, v1 v1_lead
WHERE ("v1"."i_category" = "v1_lag"."i_category")
AND ("v1"."i_category" = "v1_lead"."i_category")
AND ("v1"."i_brand" = "v1_lag"."i_brand")
AND ("v1"."i_brand" = "v1_lead"."i_brand")
AND ("v1"."cc_name" = "v1_lag"."cc_name")
AND ("v1"."cc_name" = "v1_lead"."cc_name")
AND ("v1"."rn" = ("v1_lag"."rn" + 1))
AND ("v1"."rn" = ("v1_lead"."rn" - 1))
)
SELECT *
FROM
v2
WHERE ("d_year" = 1999)
AND ("avg_monthly_sales" > 0)
AND ((CASE WHEN ("avg_monthly_sales" > 0) THEN ("abs"(("sum_sales" - "avg_monthly_sales")) / "avg_monthly_sales") ELSE null END) > DECIMAL '0.1')
ORDER BY ("sum_sales" - "avg_monthly_sales") ASC, 3 ASC
LIMIT 100