summaryrefslogtreecommitdiffstats
path: root/src/s3select/TPCDS/sample-queries-tpcds/query63.sql
diff options
context:
space:
mode:
Diffstat (limited to 'src/s3select/TPCDS/sample-queries-tpcds/query63.sql')
-rw-r--r--src/s3select/TPCDS/sample-queries-tpcds/query63.sql29
1 files changed, 29 insertions, 0 deletions
diff --git a/src/s3select/TPCDS/sample-queries-tpcds/query63.sql b/src/s3select/TPCDS/sample-queries-tpcds/query63.sql
new file mode 100644
index 000000000..599a2d023
--- /dev/null
+++ b/src/s3select/TPCDS/sample-queries-tpcds/query63.sql
@@ -0,0 +1,29 @@
+-- start query 1 in stream 0 using template query63.tpl and seed 1819994127
+select *
+from (select i_manager_id
+ ,sum(ss_sales_price) sum_sales
+ ,avg(sum(ss_sales_price)) over (partition by i_manager_id) 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_month_seq in (1211,1211+1,1211+2,1211+3,1211+4,1211+5,1211+6,1211+7,1211+8,1211+9,1211+10,1211+11)
+ and (( i_category in ('Books','Children','Electronics')
+ and i_class in ('personal','portable','reference','self-help')
+ and i_brand in ('scholaramalgamalg #14','scholaramalgamalg #7',
+ 'exportiunivamalg #9','scholaramalgamalg #9'))
+ or( i_category in ('Women','Music','Men')
+ and i_class in ('accessories','classical','fragrances','pants')
+ and i_brand in ('amalgimporto #1','edu packscholar #1','exportiimporto #1',
+ 'importoamalg #1')))
+group by i_manager_id, 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 i_manager_id
+ ,avg_monthly_sales
+ ,sum_sales
+limit 100;
+
+-- end query 1 in stream 0 using template query63.tpl