summaryrefslogtreecommitdiffstats
path: root/src/s3select/TPCDS/sample-queries-tpcds/query89.sql
diff options
context:
space:
mode:
Diffstat (limited to 'src/s3select/TPCDS/sample-queries-tpcds/query89.sql')
-rw-r--r--src/s3select/TPCDS/sample-queries-tpcds/query89.sql28
1 files changed, 28 insertions, 0 deletions
diff --git a/src/s3select/TPCDS/sample-queries-tpcds/query89.sql b/src/s3select/TPCDS/sample-queries-tpcds/query89.sql
new file mode 100644
index 000000000..3a275d4e0
--- /dev/null
+++ b/src/s3select/TPCDS/sample-queries-tpcds/query89.sql
@@ -0,0 +1,28 @@
+-- start query 1 in stream 0 using template query89.tpl and seed 1719819282
+select *
+from(
+select i_category, i_class, i_brand,
+ s_store_name, s_company_name,
+ d_moy,
+ sum(ss_sales_price) sum_sales,
+ avg(sum(ss_sales_price)) over
+ (partition by i_category, i_brand, s_store_name, s_company_name)
+ avg_monthly_sales
+from item, store_sales, date_dim, store
+where ss_item_sk = i_item_sk and
+ ss_sold_date_sk = d_date_sk and
+ ss_store_sk = s_store_sk and
+ d_year in (2000) and
+ ((i_category in ('Home','Music','Books') and
+ i_class in ('glassware','classical','fiction')
+ )
+ or (i_category in ('Jewelry','Sports','Women') and
+ i_class in ('semi-precious','baseball','dresses')
+ ))
+group by i_category, i_class, i_brand,
+ s_store_name, s_company_name, d_moy) tmp1
+where 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, s_store_name
+limit 100;
+
+-- end query 1 in stream 0 using template query89.tpl