====
---- QUERY: TPCDS-Q53
select
  *
from
  (select
    i_manufact_id,
    sum(ss_sales_price) sum_sales,
    avg(sum(ss_sales_price)) over (partition by i_manufact_id) avg_quarterly_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_month_seq in (1200, 1200 + 1, 1200 + 2, 1200 + 3, 1200 + 4, 1200 + 5, 1200 + 6, 1200 + 7, 1200 + 8, 1200 + 9, 1200 + 10, 1200 + 11)
    and ((i_category in ('Books', 'Children', 'Electronics')
      and i_class in ('personal', 'portable', 'reference', 'self-help')
      and i_brand in ('scholaramalgamalg #14', 'scholaramalgamalg #7', 'exportiunivamalg #9', 'scholaramalgamalg #9'))
    or (i_category in ('Women', 'Music', 'Men')
      and i_class in ('accessories', 'classical', 'fragrances', 'pants')
      and i_brand in ('amalgimporto #1', 'edu packscholar #1', 'exportiimporto #1', 'importoamalg #1')))
  group by
    i_manufact_id,
    d_qoy
  ) tmp1
where
  case when avg_quarterly_sales > 0 then abs (sum_sales - avg_quarterly_sales) / avg_quarterly_sales else null end > 0.1
order by
  avg_quarterly_sales,
  sum_sales,
  i_manufact_id
limit 100
---- RESULTS
30,165.67,340.635000
30,247.07,340.635000
30,627.63,340.635000
619,158.76,348.640000
619,210.81,348.640000
619,464.26,348.640000
619,560.73,348.640000
271,79.26,354.330000
271,86.87,354.330000
271,179.61,354.330000
271,1071.58,354.330000
827,82.44,356.590000
827,320.05,356.590000
827,666.52,356.590000
296,188.61,369.125000
296,265.76,369.125000
296,655.24,369.125000
308,200.28,385.982500
308,214.07,385.982500
308,489.17,385.982500
308,640.41,385.982500
486,178.80,400.530000
486,455.08,400.530000
486,468.01,400.530000
486,500.23,400.530000
554,191.48,407.975000
554,346.80,407.975000
554,660.97,407.975000
208,151.84,410.385000
208,207.02,410.385000
208,533.75,410.385000
208,748.93,410.385000
662,199.83,412.135000
662,300.61,412.135000
662,460.94,412.135000
662,687.16,412.135000
394,264.65,413.405000
394,272.02,413.405000
394,674.12,413.405000
221,183.63,416.082500
221,534.62,416.082500
221,552.44,416.082500
621,232.20,418.115000
621,624.80,418.115000
507,76.98,419.465000
507,505.36,419.465000
507,644.24,419.465000
316,36.25,423.035000
316,158.85,423.035000
316,697.32,423.035000
316,799.72,423.035000
56,110.81,423.097500
56,371.77,423.097500
56,500.77,423.097500
56,709.04,423.097500
517,194.87,426.287500
517,313.41,426.287500
517,482.41,426.287500
517,714.46,426.287500
411,364.09,426.892500
411,519.60,426.892500
247,313.42,427.682500
247,353.08,427.682500
247,628.37,427.682500
652,228.75,434.752500
652,314.98,434.752500
652,365.90,434.752500
652,829.38,434.752500
129,301.69,436.452500
129,325.26,436.452500
129,550.47,436.452500
129,568.39,436.452500
99,164.24,438.667500
99,183.75,438.667500
99,393.58,438.667500
99,1013.10,438.667500
235,111.64,441.030000
235,179.51,441.030000
235,493.83,441.030000
235,979.14,441.030000
360,143.83,445.415000
360,293.99,445.415000
360,928.51,445.415000
732,106.56,447.065000
732,300.77,447.065000
732,498.49,447.065000
732,882.44,447.065000
190,202.77,451.982500
190,233.10,451.982500
190,358.97,451.982500
190,1013.09,451.982500
147,212.53,455.892500
147,366.10,455.892500
147,553.92,455.892500
147,691.02,455.892500
665,283.25,456.087500
665,357.22,456.087500
665,691.20,456.087500
85,325.28,456.280000
85,350.10,456.280000
---- TYPES
INT, DECIMAL, DECIMAL
====