Identify Outliers
Description
Outlier detection is a key step to any analysis. It allows you to quickly spot errors in data collection, or which points you may need to remove before you do any statistical modeling.
There are a number of ways to find outliers, but this notebook goes through the most common ways:
The Data
For this example, we’ll be looking at some data that shows the total number of daily streams for the Top 200 Tracks on Spotify every day since Jan 1 2017:
Data
Method 1: MAD (Median Absolute Deviation)
MAD describes how far away a point is from the median. This method can be preferable to other methods, like the zscore method because you don’t have to assume the data is normally distributed.
“A critical value for the Median Absolute Deviation of 5 has been suggested by workers in Nonparametric statistics, as a Median Absolute Deviation value of just under 1.5 is equivalent to one standard deviation, so MAD = 5 is approximately equal to 3 standard deviations.”
 Using SQL to detect outliers by Robert de Graaf
SELECT
diff_to_med.*,
percentile_cont(diff_to_med.difference, 0.5) over() median_of_diff,
abs(difference/percentile_cont(difference, 0.5) over()) mad,
case
when abs(difference/percentile_cont(difference, 0.5) over()) > <MAD_THRESHOLD> then 'outlier'
else 'not outlier'
end label
FROM (
select
<OTHER_COLUMNS>
<OUTLIER_COLUMN>,
med.median ,
abs(data.<OUTLIER_COLUMN>  med.median) difference
from
<TABLE> as data
cross join
(
select distinct
percentile_cont(<OUTLIER_COLUMN>,.5) over() median
from <TABLE> as data) as med
) as diff_to_med
order by mad desc
where:

<OTHER_COLUMNS>
: Columns selected other than the outlier column 
<OUTLIER_COLUMN>
: Column name for outlier 
<MAD_TRESHOLD>
: The outlier threshold. 5 = 3 standard deviations
MAD Method Outliers
Method 2: Standard Deviations
The most common way outliers are detected is by finding points outside a certain number of sigmas (or standard deviations) from the mean.
If the data is normally distributed, then the following rules apply:
Image from anomoly.io
So, identifying points 2 or 3 sigmas away from the mean should lead us to our outliers.
SELECT
data.*,
case
when <OUTLIER_COLUMN> between mean  3 * stdev and mean + 3 * stdev
then 'not outlier'
else 'outlier'
end label,
mean  <SIGMAS> * stdev lower_bound,
mean + <SIGMAS> * stdev upper_bound
FROM data
CROSS JOIN
(
SELECT
avg(<OUTLIER_COLUMN>) mean,
stddev_samp(data.streams<OUTLIER_COLUMN>
FROM <TABLE> AS data ) mean_sd
ORDER BY label DESC
where:

<OTHER_COLUMNS>
: Columns selected other than the outlier column 
<OUTLIER_COLUMN>
: Column name for outlier 
<SIGMAS>
: Number of standard deviations to identify the outliers (suggested: 3)
Standard Deviation Method
Method 3: ZScore
Similar to Method 2, the zscore method tells us the probability of a point occurring in a normal distribution.
Image from simplypsychology.org
To use zscores to identify outliers, we can set an alpha (significance level) that will determine how ‘likely’ each point will be in order for it to be classified as an outlier.
This method also assumes the data is normally distributed.
SELECT
find_z.*,
case
when abs(z_score) >=abs(<Z_THRESHOLD>) then 'outlier' else 'not outlier'
end label
FROM (
SELECT
<OTHER_COLUMNS>,
<OUTLIER_COLUMN>,
(<OUTLIER_COLUMN>  avg(<OUTLIER_COLUMN>) over ())
/ (stddev(<OUTLIER_COLUMN>) over ()) z_score
FROM <TABLE> AS data) find_z
ORDER BY abs(z_score) DESC
where:

<OTHER_COLUMNS>
: Columns selected other than the outlier column 
<OUTLIER_COLUMN>
: Column name for outlier 
<Z_THRESHOLD>
: zscore threshold to identify outliers (suggested: 1.96)
Z Score Method