====
---- QUERY: TPCDS-Q89
select *
from(
select i_category, i_class, i_brand,
       s_store_name, s_company_name,
       d_moy,
       sum(ss_sales_price) sum_sales,
       avg(sum(ss_sales_price)) over
         (partition by i_category, i_brand, s_store_name, s_company_name)
         avg_monthly_sales
from item, store_sales, date_dim, store
where ss_item_sk = i_item_sk and
      ss_sold_date_sk = d_date_sk and
      ss_store_sk = s_store_sk and
      d_year in (1999) and
        ((i_category in ('Books','Electronics','Sports') and
          i_class in ('computers','stereo','football')
         )
      or (i_category in ('Men','Jewelry','Women') and
          i_class in ('shirts','birdal','dresses')
        ))
group by i_category, i_class, i_brand,
         s_store_name, s_company_name, d_moy) tmp1
where case when (avg_monthly_sales <> 0) then (abs(sum_sales - avg_monthly_sales) / avg_monthly_sales) else null end > 0.1
order by sum_sales - avg_monthly_sales, s_store_name
limit 100
---- RESULTS
'Men','shirts','importoimporto #1','ese','Unknown',3,2070.65,5492.817500
'Men','shirts','importoimporto #1','ought','Unknown',6,2152.15,5045.758333
'Men','shirts','importoimporto #1','able','Unknown',2,2333.43,5194.818333
'Men','shirts','importoimporto #1','ese','Unknown',6,2691.33,5492.817500
'Men','shirts','importoimporto #1','ought','Unknown',2,2253.70,5045.758333
'Women','dresses','amalgamalg #1','able','Unknown',6,1761.81,4507.240000
'Men','shirts','importoimporto #1','ation','Unknown',3,2672.68,5410.916667
'Men','shirts','importoimporto #1','ation','Unknown',5,2677.81,5410.916667
'Men','shirts','importoimporto #1','eing','Unknown',5,2356.88,5074.076667
'Men','shirts','importoimporto #1','ese','Unknown',4,2784.11,5492.817500
'Women','dresses','amalgamalg #1','ought','Unknown',7,1926.67,4619.707500
'Women','dresses','amalgamalg #1','ought','Unknown',1,2065.94,4619.707500
'Men','shirts','importoimporto #1','ation','Unknown',6,2881.34,5410.916667
'Men','shirts','importoimporto #1','able','Unknown',3,2674.91,5194.818333
'Women','dresses','amalgamalg #1','ation','Unknown',2,2192.31,4690.890000
'Men','shirts','importoimporto #1','bar','Unknown',5,2475.90,4973.509167
'Women','dresses','amalgamalg #1','ese','Unknown',2,2146.52,4642.990833
'Men','shirts','importoimporto #1','eing','Unknown',2,2588.02,5074.076667
'Women','dresses','amalgamalg #1','bar','Unknown',7,2428.75,4914.118333
'Women','dresses','amalgamalg #1','bar','Unknown',3,2451.51,4914.118333
'Men','shirts','importoimporto #1','ation','Unknown',7,2951.67,5410.916667
'Men','shirts','importoimporto #1','able','Unknown',1,2765.75,5194.818333
'Women','dresses','amalgamalg #1','able','Unknown',2,2082.54,4507.240000
'Men','shirts','importoimporto #1','ation','Unknown',4,2988.08,5410.916667
'Men','shirts','importoimporto #1','ought','Unknown',7,2667.92,5045.758333
'Women','dresses','amalgamalg #1','bar','Unknown',4,2549.27,4914.118333
'Women','dresses','amalgamalg #1','ese','Unknown',7,2307.00,4642.990833
'Women','dresses','amalgamalg #1','eing','Unknown',4,2204.15,4528.822500
'Men','shirts','importoimporto #1','able','Unknown',7,2880.34,5194.818333
'Men','shirts','importoimporto #1','ese','Unknown',5,3210.74,5492.817500
'Women','dresses','amalgamalg #1','ation','Unknown',4,2418.39,4690.890000
'Men','shirts','importoimporto #1','eing','Unknown',4,2833.40,5074.076667
'Women','dresses','amalgamalg #1','bar','Unknown',2,2692.79,4914.118333
'Men','shirts','importoimporto #1','eing','Unknown',6,2854.62,5074.076667
'Women','dresses','amalgamalg #1','eing','Unknown',6,2314.71,4528.822500
'Men','shirts','importoimporto #1','ought','Unknown',5,2834.94,5045.758333
'Women','dresses','amalgamalg #1','ation','Unknown',5,2480.25,4690.890000
'Women','dresses','amalgamalg #1','able','Unknown',7,2302.21,4507.240000
'Women','dresses','amalgamalg #1','ation','Unknown',6,2501.15,4690.890000
'Men','shirts','importoimporto #1','ese','Unknown',2,3307.78,5492.817500
'Men','shirts','importoimporto #1','able','Unknown',5,3016.43,5194.818333
'Men','shirts','importoimporto #1','able','Unknown',4,3040.23,5194.818333
'Women','dresses','amalgamalg #1','eing','Unknown',5,2420.31,4528.822500
'Women','dresses','amalgamalg #1','ese','Unknown',5,2535.80,4642.990833
'Women','dresses','amalgamalg #1','ought','Unknown',2,2521.70,4619.707500
'Men','shirts','importoimporto #1','bar','Unknown',3,2877.96,4973.509167
'Women','dresses','amalgamalg #1','ese','Unknown',3,2577.15,4642.990833
'Women','dresses','amalgamalg #1','ation','Unknown',7,2637.27,4690.890000
'Men','shirts','importoimporto #1','ation','Unknown',1,3380.16,5410.916667
'Women','dresses','amalgamalg #1','eing','Unknown',7,2524.32,4528.822500
'Men','shirts','importoimporto #1','bar','Unknown',4,2986.70,4973.509167
'Women','dresses','amalgamalg #1','ought','Unknown',4,2648.80,4619.707500
'Men','shirts','importoimporto #1','bar','Unknown',7,3026.41,4973.509167
'Women','dresses','amalgamalg #1','ation','Unknown',3,2751.11,4690.890000
'Men','shirts','importoimporto #1','bar','Unknown',2,3073.45,4973.509167
'Men','shirts','importoimporto #1','eing','Unknown',3,3179.96,5074.076667
'Women','dresses','amalgamalg #1','ation','Unknown',1,2797.07,4690.890000
'Women','dresses','amalgamalg #1','able','Unknown',1,2621.05,4507.240000
'Women','dresses','amalgamalg #1','ese','Unknown',4,2808.36,4642.990833
'Women','dresses','amalgamalg #2','bar','Unknown',6,680.47,2506.963333
'Men','shirts','importoimporto #1','ation','Unknown',2,3591.65,5410.916667
'Women','dresses','amalgamalg #1','able','Unknown',3,2706.15,4507.240000
'Women','dresses','amalgamalg #2','eing','Unknown',2,773.08,2569.346667
'Men','shirts','importoimporto #1','bar','Unknown',6,3199.08,4973.509167
'Men','shirts','importoimporto #1','ese','Unknown',7,3754.33,5492.817500
'Women','dresses','amalgamalg #1','bar','Unknown',5,3190.77,4914.118333
'Women','dresses','amalgamalg #1','eing','Unknown',2,2841.88,4528.822500
'Men','shirts','importoimporto #1','able','Unknown',6,3538.25,5194.818333
'Women','dresses','amalgamalg #1','ese','Unknown',6,3006.94,4642.990833
'Women','dresses','amalgamalg #1','bar','Unknown',1,3284.93,4914.118333
'Women','dresses','amalgamalg #2','bar','Unknown',5,878.42,2506.963333
'Women','dresses','amalgamalg #1','able','Unknown',5,2891.95,4507.240000
'Men','shirts','importoimporto #1','eing','Unknown',7,3465.77,5074.076667
'Women','dresses','amalgamalg #2','eing','Unknown',3,964.29,2569.346667
'Women','dresses','amalgamalg #1','bar','Unknown',6,3324.49,4914.118333
'Men','shirts','importoimporto #1','ought','Unknown',3,3467.09,5045.758333
'Women','dresses','amalgamalg #1','ought','Unknown',5,3049.25,4619.707500
'Women','dresses','amalgamalg #1','eing','Unknown',3,2978.07,4528.822500
'Men','shirts','importoimporto #1','ought','Unknown',4,3544.97,5045.758333
'Men','shirts','importoimporto #1','bar','Unknown',1,3490.62,4973.509167
'Men','shirts','importoimporto #2','able','Unknown',2,628.63,2095.470833
'Men','shirts','importoimporto #1','eing','Unknown',1,3628.21,5074.076667
'Women','dresses','amalgamalg #1','able','Unknown',4,3082.17,4507.240000
'Men','shirts','importoimporto #2','eing','Unknown',5,765.86,2137.656667
'Women','dresses','amalgamalg #2','ation','Unknown',4,1064.70,2431.226667
'Women','dresses','amalgamalg #2','ought','Unknown',3,991.89,2355.441667
'Women','dresses','amalgamalg #2','eing','Unknown',5,1211.13,2569.346667
'Women','dresses','amalgamalg #2','ation','Unknown',7,1078.41,2431.226667
'Men','shirts','importoimporto #2','ation','Unknown',4,807.67,2141.060000
'Women','dresses','amalgamalg #2','ation','Unknown',2,1102.89,2431.226667
'Women','dresses','amalgamalg #2','eing','Unknown',6,1247.86,2569.346667
'Women','dresses','amalgamalg #2','able','Unknown',4,1020.39,2337.278333
'Men','shirts','importoimporto #2','able','Unknown',7,780.91,2095.470833
'Women','dresses','amalgamalg #2','ese','Unknown',2,904.34,2200.820833
'Women','dresses','amalgamalg #2','ought','Unknown',7,1061.87,2355.441667
'Women','dresses','amalgamalg #2','able','Unknown',3,1054.94,2337.278333
'Men','shirts','importoimporto #2','ought','Unknown',6,633.96,1908.056667
'Men','shirts','importoimporto #2','ese','Unknown',6,853.37,2113.573333
'Women','dresses','amalgamalg #2','able','Unknown',5,1084.94,2337.278333
'Women','dresses','amalgamalg #2','eing','Unknown',4,1317.08,2569.346667
---- TYPES
STRING, STRING, STRING, STRING, STRING, INT, DECIMAL, DECIMAL
====