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

   1 -- start query 1 in stream 0 using template query63.tpl and seed 1819994127
   2 select  *
   3 from (select i_manager_id
   4              ,sum(ss_sales_price) sum_sales
   5              ,avg(sum(ss_sales_price)) over (partition by i_manager_id) avg_monthly_sales
   6       from item
   7           ,store_sales
   8           ,date_dim
   9           ,store
  10       where ss_item_sk = i_item_sk
  11         and ss_sold_date_sk = d_date_sk
  12         and ss_store_sk = s_store_sk
  13         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)
  14         and ((    i_category in ('Books','Children','Electronics')
  15               and i_class in ('personal','portable','reference','self-help')
  16               and i_brand in ('scholaramalgamalg #14','scholaramalgamalg #7',
  17                                   'exportiunivamalg #9','scholaramalgamalg #9'))
  18            or(    i_category in ('Women','Music','Men')
  19               and i_class in ('accessories','classical','fragrances','pants')
  20               and i_brand in ('amalgimporto #1','edu packscholar #1','exportiimporto #1',
  21                                  'importoamalg #1')))
  22 group by i_manager_id, d_moy) tmp1
  23 where case when avg_monthly_sales > 0 then abs (sum_sales - avg_monthly_sales) / avg_monthly_sales else null end > 0.1
  24 order by i_manager_id
  25         ,avg_monthly_sales
  26         ,sum_sales
  27 limit 100;
  28
  29 -- end query 1 in stream 0 using template query63.tpl