diff options
Diffstat (limited to 'src/s3select/TPCDS/sample-queries-tpcds/query63.sql')
| -rw-r--r-- | src/s3select/TPCDS/sample-queries-tpcds/query63.sql | 29 |
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 |