TPC-DS Q63

SELECT
TOP 100
  *
FROM (
  SELECT
    i_manager_id AS i_manager_id,
    SUM(ss_sales_price) AS sum_sales,
    AVG(SUM(ss_sales_price)) OVER (PARTITION BY i_manager_id) AS 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_month_seq IN (
      1203,
      1203 + 1,
      1203 + 2,
      1203 + 3,
      1203 + 4,
      1203 + 5,
      1203 + 6,
      1203 + 7,
      1203 + 8,
      1203 + 9,
      1203 + 10,
      1203 + 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_manager_id,
    d_moy
) AS 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
  i_manager_id,
  avg_monthly_sales,
  sum_sales;