testdata/workloads/tpcds/queries/tpcds-q53.test

====
---- 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.63
30,247.07,340.63
30,627.63,340.63
619,158.76,348.64
619,210.81,348.64
619,464.26,348.64
619,560.73,348.64
271,79.26,354.33
271,86.87,354.33
271,179.61,354.33
271,1071.58,354.33
827,82.44,356.59
827,666.52,356.59
296,188.61,369.12
296,265.76,369.12
296,655.24,369.12
308,200.28,385.98
308,214.07,385.98
308,489.17,385.98
308,640.41,385.98
486,178.80,400.53
486,455.08,400.53
486,468.01,400.53
486,500.23,400.53
554,191.48,407.97
554,346.80,407.97
554,660.97,407.97
208,151.84,410.38
208,207.02,410.38
208,533.75,410.38
208,748.93,410.38
662,199.83,412.13
662,300.61,412.13
662,460.94,412.13
662,687.16,412.13
394,264.65,413.40
394,272.02,413.40
394,674.12,413.40
221,183.63,416.08
221,534.62,416.08
221,552.44,416.08
621,232.20,418.11
621,624.80,418.11
507,76.98,419.46
507,505.36,419.46
507,644.24,419.46
316,36.25,423.03
316,158.85,423.03
316,697.32,423.03
316,799.72,423.03
56,110.81,423.09
56,371.77,423.09
56,500.77,423.09
56,709.04,423.09
517,194.87,426.28
517,313.41,426.28
517,482.41,426.28
517,714.46,426.28
411,364.09,426.89
411,519.60,426.89
247,313.42,427.68
247,353.08,427.68
247,628.37,427.68
652,228.75,434.75
652,314.98,434.75
652,365.90,434.75
652,829.38,434.75
129,301.69,436.45
129,325.26,436.45
129,550.47,436.45
129,568.39,436.45
99,164.24,438.66
99,183.75,438.66
99,1013.10,438.66
235,111.64,441.03
235,179.51,441.03
235,493.83,441.03
235,979.14,441.03
360,143.83,445.41
360,293.99,445.41
360,928.51,445.41
732,106.56,447.06
732,300.77,447.06
732,498.49,447.06
732,882.44,447.06
190,202.77,451.98
190,233.10,451.98
190,358.97,451.98
190,1013.09,451.98
147,212.53,455.89
147,366.10,455.89
147,553.92,455.89
147,691.02,455.89
665,283.25,456.08
665,357.22,456.08
665,691.20,456.08
85,325.28,456.28
85,350.10,456.28
85,387.62,456.28
85,762.12,456.28
---- TYPES
INT, DECIMAL, DECIMAL
====