resources/tpcds-modifiedQueries/q89.sql

0001 -- start query 89 in stream 0 using template query89.tpl
0002 select
0003   *
0004 from
0005   (select
0006     i_category,
0007     i_class,
0008     i_brand,
0009     s_store_name,
0010     s_company_name,
0011     d_moy,
0012     sum(ss_sales_price) sum_sales,
0013     avg(sum(ss_sales_price)) over (partition by i_category, i_brand, s_store_name, s_company_name) avg_monthly_sales
0014   from
0015     item,
0016     store_sales,
0017     date_dim,
0018     store
0019   where
0020     ss_item_sk = i_item_sk
0021     and ss_sold_date_sk = d_date_sk
0022     and ss_store_sk = s_store_sk
0023     and d_year in (2000)
0024     and ((i_category in ('Home', 'Books', 'Electronics')
0025         and i_class in ('wallpaper', 'parenting', 'musical'))
0026       or (i_category in ('Shoes', 'Jewelry', 'Men')
0027         and i_class in ('womens', 'birdal', 'pants')))
0028     and ss_sold_date_sk between 2451545 and 2451910  -- partition key filter
0029   group by
0030     i_category,
0031     i_class,
0032     i_brand,
0033     s_store_name,
0034     s_company_name,
0035     d_moy
0036   ) tmp1
0037 where
0038   case when (avg_monthly_sales <> 0) then (abs(sum_sales - avg_monthly_sales) / avg_monthly_sales) else null end > 0.1
0039 order by
0040   sum_sales - avg_monthly_sales,
0041   s_store_name
0042 limit 100
0043 -- end query 89 in stream 0 using template query89.tpl