resources/tpcds-modifiedQueries/q63.sql

0001 -- start query 63 in stream 0 using template query63.tpl
0002 select  *
0003 from (select i_manager_id
0004              ,sum(ss_sales_price) sum_sales
0005              ,avg(sum(ss_sales_price)) over (partition by i_manager_id) avg_monthly_sales
0006       from item
0007           ,store_sales
0008           ,date_dim
0009           ,store
0010       where ss_item_sk = i_item_sk
0011         and ss_sold_date_sk = d_date_sk
0012         and ss_sold_date_sk between 2452123 and 2452487
0013         and ss_store_sk = s_store_sk
0014         and d_month_seq in (1219,1219+1,1219+2,1219+3,1219+4,1219+5,1219+6,1219+7,1219+8,1219+9,1219+10,1219+11)
0015         and ((    i_category in ('Books','Children','Electronics')
0016               and i_class in ('personal','portable','reference','self-help')
0017               and i_brand in ('scholaramalgamalg #14','scholaramalgamalg #7',
0018                                   'exportiunivamalg #9','scholaramalgamalg #9'))
0019            or(    i_category in ('Women','Music','Men')
0020               and i_class in ('accessories','classical','fragrances','pants')
0021               and i_brand in ('amalgimporto #1','edu packscholar #1','exportiimporto #1',
0022                                  'importoamalg #1')))
0023 group by i_manager_id, d_moy) tmp1
0024 where case when avg_monthly_sales > 0 then abs (sum_sales - avg_monthly_sales) / avg_monthly_sales else null end > 0.1
0025 order by i_manager_id
0026         ,avg_monthly_sales
0027         ,sum_sales
0028 limit 100
0029 -- end query 63 in stream 0 using template query63.tpl