Need to display a positive and a negative message in a column when a number deviates from the average

Question

Basically, I need to create a table that will show the avg users per day of the week and if there is a 20% positive or negative deviation in the numbers from the average I need it to say 'Positive outlier' or 'negative outlier'. I have currently calculated the avg users but I do not know how to write the case statement which will show what I need to derive from the data. This is what I have so far:

SELECT
  EXTRACT(DAYOFWEEK FROM date) AS dayOfWeek,
  AVG(users) AS average_users,
FROM
(
  SELECT
    PARSE_DATE('%Y%m%d', date) AS date,
    COUNT(DISTINCT(fullVisitorId)) AS users
  FROM `table`
  WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE ('%Y%m%d', DATE_SUB(CURRENT_DATE, INTERVAL 14 DAY))
    AND FORMAT_DATE ('%Y%m%d', DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY))
  GROUP BY date
)
GROUP BY dayOfWeek
ORDER BY dayOfWeek ASC

Answer 1

put this code in CTE eg,

with cte as (SELECT
  EXTRACT(DAYOFWEEK FROM date) AS dayOfWeek,
  AVG(users) AS average_users,
FROM
(
  SELECT
    PARSE_DATE('%Y%m%d', date) AS date,
    COUNT(DISTINCT(fullVisitorId)) AS users
  FROM `table`
  WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE ('%Y%m%d', DATE_SUB(CURRENT_DATE, INTERVAL 14 DAY))
    AND FORMAT_DATE ('%Y%m%d', DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY))
  GROUP BY date
)
GROUP BY dayOfWeek
ORDER BY dayOfWeek ASC)

select dayOfWeek,average_users,
case when average_users > 20 then "positive'
else 'negative' end as outlier
 from cte;