diff options
Diffstat (limited to 'src/s3select/TPCDS/sample-queries-tpcds/query70.sql')
| -rw-r--r-- | src/s3select/TPCDS/sample-queries-tpcds/query70.sql | 38 |
1 files changed, 38 insertions, 0 deletions
diff --git a/src/s3select/TPCDS/sample-queries-tpcds/query70.sql b/src/s3select/TPCDS/sample-queries-tpcds/query70.sql new file mode 100644 index 000000000..1d44fbd6a --- /dev/null +++ b/src/s3select/TPCDS/sample-queries-tpcds/query70.sql @@ -0,0 +1,38 @@ +-- start query 1 in stream 0 using template query70.tpl and seed 1819994127 +select + sum(ss_net_profit) as total_sum + ,s_state + ,s_county + ,grouping(s_state)+grouping(s_county) as lochierarchy + ,rank() over ( + partition by grouping(s_state)+grouping(s_county), + case when grouping(s_county) = 0 then s_state end + order by sum(ss_net_profit) desc) as rank_within_parent + from + store_sales + ,date_dim d1 + ,store + where + d1.d_month_seq between 1218 and 1218+11 + and d1.d_date_sk = ss_sold_date_sk + and s_store_sk = ss_store_sk + and s_state in + ( select s_state + from (select s_state as s_state, + rank() over ( partition by s_state order by sum(ss_net_profit) desc) as ranking + from store_sales, store, date_dim + where d_month_seq between 1218 and 1218+11 + and d_date_sk = ss_sold_date_sk + and s_store_sk = ss_store_sk + group by s_state + ) tmp1 + where ranking <= 5 + ) + group by rollup(s_state,s_county) + order by + lochierarchy desc + ,case when lochierarchy = 0 then s_state end + ,rank_within_parent + limit 100; + +-- end query 1 in stream 0 using template query70.tpl |