ceph/src/s3select/TPCDS/sample-queries-tpcds/query70.sql

   1 -- start query 1 in stream 0 using template query70.tpl and seed 1819994127
   2 select
   3     sum(ss_net_profit) as total_sum
   4    ,s_state
   5    ,s_county
   6    ,grouping(s_state)+grouping(s_county) as lochierarchy
   7    ,rank() over (
   8         partition by grouping(s_state)+grouping(s_county),
   9         case when grouping(s_county) = 0 then s_state end
  10         order by sum(ss_net_profit) desc) as rank_within_parent
  11  from
  12     store_sales
  13    ,date_dim       d1
  14    ,store
  15  where
  16     d1.d_month_seq between 1218 and 1218+11
  17  and d1.d_date_sk = ss_sold_date_sk
  18  and s_store_sk  = ss_store_sk
  19  and s_state in
  20              ( select s_state
  21                from  (select s_state as s_state,
  22                             rank() over ( partition by s_state order by sum(ss_net_profit) desc) as ranking
  23                       from   store_sales, store, date_dim
  24                       where  d_month_seq between 1218 and 1218+11
  25                             and d_date_sk = ss_sold_date_sk
  26                             and s_store_sk  = ss_store_sk
  27                       group by s_state
  28                      ) tmp1
  29                where ranking <= 5
  30              )
  31  group by rollup(s_state,s_county)
  32  order by
  33    lochierarchy desc
  34   ,case when lochierarchy = 0 then s_state end
  35   ,rank_within_parent
  36  limit 100;
  37
  38 -- end query 1 in stream 0 using template query70.tpl