diff options
Diffstat (limited to 'src/s3select/TPCDS/sample-queries-tpcds/query36.sql')
| -rw-r--r-- | src/s3select/TPCDS/sample-queries-tpcds/query36.sql | 30 |
1 files changed, 30 insertions, 0 deletions
diff --git a/src/s3select/TPCDS/sample-queries-tpcds/query36.sql b/src/s3select/TPCDS/sample-queries-tpcds/query36.sql new file mode 100644 index 000000000..8456fc8c4 --- /dev/null +++ b/src/s3select/TPCDS/sample-queries-tpcds/query36.sql @@ -0,0 +1,30 @@ +-- start query 1 in stream 0 using template query36.tpl and seed 1544728811 +select + sum(ss_net_profit)/sum(ss_ext_sales_price) as gross_margin + ,i_category + ,i_class + ,grouping(i_category)+grouping(i_class) as lochierarchy + ,rank() over ( + partition by grouping(i_category)+grouping(i_class), + case when grouping(i_class) = 0 then i_category end + order by sum(ss_net_profit)/sum(ss_ext_sales_price) asc) as rank_within_parent + from + store_sales + ,date_dim d1 + ,item + ,store + where + d1.d_year = 1999 + and d1.d_date_sk = ss_sold_date_sk + and i_item_sk = ss_item_sk + and s_store_sk = ss_store_sk + and s_state in ('IN','AL','MI','MN', + 'TN','LA','FL','NM') + group by rollup(i_category,i_class) + order by + lochierarchy desc + ,case when lochierarchy = 0 then i_category end + ,rank_within_parent + limit 100; + +-- end query 1 in stream 0 using template query36.tpl |