CUME_DIST() can be used to identify outliers in your dataset in BigQuery. The CUME_DIST() function computes the cumulative distribution of a value in a set of values. Here's how you can use it to identify outliers:
SELECT * FROM ( SELECT value, CUME_DIST() OVER (ORDER BY value) AS cume_dist FROM student_grades ) WHERE ROUND(cume_dist, 2) > 0.83 OR ROUND(cume_dist, 2) < 0.33
In this query, values significantly higher or lower than the majority can be considered outliers. We round the cumulative distribution to two decimal places and filter out values that fall in the top 17% or bottom 33% of the distribution.
However, it's important to note that we can use this function in the SELECT block but cannot use it in the WHERE or HAVING blocks directly. We can use it in WHERE or HAVING only after calculating the metric beforehand. Therefore, the query is structured with a subquery to calculate the cumulative distribution first and then apply the filtering condition.