diff options
Diffstat (limited to 'src/s3select/TPCDS/sample-queries-tpcds/query51.sql')
-rw-r--r-- | src/s3select/TPCDS/sample-queries-tpcds/query51.sql | 45 |
1 files changed, 45 insertions, 0 deletions
diff --git a/src/s3select/TPCDS/sample-queries-tpcds/query51.sql b/src/s3select/TPCDS/sample-queries-tpcds/query51.sql new file mode 100644 index 000000000..dbf9807f5 --- /dev/null +++ b/src/s3select/TPCDS/sample-queries-tpcds/query51.sql @@ -0,0 +1,45 @@ +-- start query 1 in stream 0 using template query51.tpl and seed 1819994127 +WITH web_v1 as ( +select + ws_item_sk item_sk, d_date, + sum(sum(ws_sales_price)) + over (partition by ws_item_sk order by d_date rows between unbounded preceding and current row) cume_sales +from web_sales + ,date_dim +where ws_sold_date_sk=d_date_sk + and d_month_seq between 1214 and 1214+11 + and ws_item_sk is not NULL +group by ws_item_sk, d_date), +store_v1 as ( +select + ss_item_sk item_sk, d_date, + sum(sum(ss_sales_price)) + over (partition by ss_item_sk order by d_date rows between unbounded preceding and current row) cume_sales +from store_sales + ,date_dim +where ss_sold_date_sk=d_date_sk + and d_month_seq between 1214 and 1214+11 + and ss_item_sk is not NULL +group by ss_item_sk, d_date) + select * +from (select item_sk + ,d_date + ,web_sales + ,store_sales + ,max(web_sales) + over (partition by item_sk order by d_date rows between unbounded preceding and current row) web_cumulative + ,max(store_sales) + over (partition by item_sk order by d_date rows between unbounded preceding and current row) store_cumulative + from (select case when web.item_sk is not null then web.item_sk else store.item_sk end item_sk + ,case when web.d_date is not null then web.d_date else store.d_date end d_date + ,web.cume_sales web_sales + ,store.cume_sales store_sales + from web_v1 web full outer join store_v1 store on (web.item_sk = store.item_sk + and web.d_date = store.d_date) + )x )y +where web_cumulative > store_cumulative +order by item_sk + ,d_date +limit 100; + +-- end query 1 in stream 0 using template query51.tpl |