summaryrefslogtreecommitdiffstats
path: root/src/s3select/TPCDS/sample-queries-tpcds/query57.sql
diff options
context:
space:
mode:
Diffstat (limited to 'src/s3select/TPCDS/sample-queries-tpcds/query57.sql')
-rw-r--r--src/s3select/TPCDS/sample-queries-tpcds/query57.sql48
1 files changed, 48 insertions, 0 deletions
diff --git a/src/s3select/TPCDS/sample-queries-tpcds/query57.sql b/src/s3select/TPCDS/sample-queries-tpcds/query57.sql
new file mode 100644
index 000000000..10602da67
--- /dev/null
+++ b/src/s3select/TPCDS/sample-queries-tpcds/query57.sql
@@ -0,0 +1,48 @@
+-- start query 1 in stream 0 using template query57.tpl and seed 2031708268
+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, d_moy) rn
+ from item, catalog_sales, date_dim, 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.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 > 0.1
+ order by sum_sales - avg_monthly_sales, avg_monthly_sales
+ limit 100;
+
+-- end query 1 in stream 0 using template query57.tpl