resources/tpcds-modifiedQueries/q53.sql

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