resources/tpcds-v2.7.0/q57.sql

0001 WITH v1 AS (
0002   SELECT
0003     i_category,
0004     i_brand,
0005     cc_name,
0006     d_year,
0007     d_moy,
0008     sum(cs_sales_price) sum_sales,
0009     avg(sum(cs_sales_price))
0010     OVER
0011     (PARTITION BY i_category, i_brand, cc_name, d_year)
0012     avg_monthly_sales,
0013     rank()
0014     OVER
0015     (PARTITION BY i_category, i_brand, cc_name
0016       ORDER BY d_year, d_moy) rn
0017   FROM item, catalog_sales, date_dim, call_center
0018   WHERE cs_item_sk = i_item_sk AND
0019     cs_sold_date_sk = d_date_sk AND
0020     cc_call_center_sk = cs_call_center_sk AND
0021     (
0022       d_year = 1999 OR
0023         (d_year = 1999 - 1 AND d_moy = 12) OR
0024         (d_year = 1999 + 1 AND d_moy = 1)
0025     )
0026   GROUP BY i_category, i_brand,
0027     cc_name, d_year, d_moy),
0028     v2 AS (
0029     SELECT
0030       v1.i_category,
0031       v1.i_brand,
0032       -- q57 in TPCDS v1.4 had a column below:
0033       -- v1.cc_name,
0034       v1.d_year,
0035       v1.d_moy,
0036       v1.avg_monthly_sales,
0037       v1.sum_sales,
0038       v1_lag.sum_sales psum,
0039       v1_lead.sum_sales nsum
0040     FROM v1, v1 v1_lag, v1 v1_lead
0041     WHERE v1.i_category = v1_lag.i_category AND
0042       v1.i_category = v1_lead.i_category AND
0043       v1.i_brand = v1_lag.i_brand AND
0044       v1.i_brand = v1_lead.i_brand AND
0045       v1.cc_name = v1_lag.cc_name AND
0046       v1.cc_name = v1_lead.cc_name AND
0047       v1.rn = v1_lag.rn + 1 AND
0048       v1.rn = v1_lead.rn - 1)
0049 SELECT *
0050 FROM v2
0051 WHERE d_year = 1999 AND
0052   avg_monthly_sales > 0 AND
0053   CASE WHEN avg_monthly_sales > 0
0054     THEN abs(sum_sales - avg_monthly_sales) / avg_monthly_sales
0055   ELSE NULL END > 0.1
0056 ORDER BY sum_sales - avg_monthly_sales, 3
0057 LIMIT 100