Inevitably, the unexpected happens. A historically low-traffic channel brings in 10x the normal amount of users. Or your user login rate drops by half. In either case, these are important events that are easy to miss in a sea of data.



In this post we’ll cover a few SQL queries for detecting unusually high or low values in a set of data.



<h2 class="wp-block-heading">Static Thresholds</h2>



We’ll start by using a static threshold in the query below to find rows above or below that threshold. This works well for key numbers that behave in predictable ways, and is trivial to implement.



 Let’s define a&nbsp;with&nbsp;table of user data and see what it looks like: 



<pre class="wp-block-code"><code>with user_count as (
 select
 date_trunc('day', created_at)::date as day,
 count(1) as value
 from users
 group by 1
)
select * from user_count</code></pre>



<figure class="wp-block-image fancybox"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/image-01-8.png" alt="With table" class="wp-image-78070"/></figure>



Let’s define outliers as any day with more than a thousand new users:



<pre class="wp-block-code"><code>select *
from user_count
where value > 1000</code></pre>



<figure class="wp-block-image fancybox"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/image-02-9.png" alt="Outlier table" class="wp-image-78064"/></figure>



When we plot the outliers on top of the full data we see:



<figure class="wp-block-image fancybox"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/image-03-9.png" alt="New users and outliers" class="wp-image-78058"/></figure>



Static thresholds are simple and effective for basic use cases, but run into problems when data varies month to month and the old fixed threshold no longer applies.



<h2 class="wp-block-heading">Percentage Thresholds</h2>



Percentage thresholds work well for data with a growth trend. A thousand new users per day may be unexpected in January, but typical by July.



With percentage thresholds, our alerts will continually adjust to recent trends. Rather than setting a threshold at a thousand new users, we can set one at 2x the current average.



 First let’s add another&nbsp;with&nbsp;table to include the percentage difference vs. the mean for each data point: 



<pre class="wp-block-code"><code>with user_count as (
 select
 date_trunc('day', created_at)::date as day,
 count(1) as value
 from users
 group by 1
), user_count_with_pct as (
 select
 day,
 value,
 value / (avg(value) over ()) as pct_of_mean
 from user_count
 order by 1
)
select * from user_count_with_pct</code></pre>





The line&nbsp;value / (avg(value) over ())&nbsp;uses a window function to divide each row’s value by the average value for the entire table. The results are:





<figure class="wp-block-image fancybox"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/image-04-9.png" alt="Average value table" class="wp-image-78052"/></figure>



 If we limit to outliers: 



<pre class="wp-block-code"><code>select *
from user_count_with_pct
where pct >= 2.0</code></pre>



 We see the days where we had 200%+ of the average signup rate: 



<figure class="wp-block-image fancybox"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/image-05-7.png" alt="200 percent chart" class="wp-image-78046"/></figure>



 Plotted together: 



<figure class="wp-block-image fancybox"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/image-06-4.png" alt="New users and outliers graph" class="wp-image-78040"/></figure>



 We could make this more robust by limiting the&nbsp;user_count&nbsp;table to just recent users. This prevents skewing the average with data from several months ago.



<h2 class="wp-block-heading">Standard Deviation Thresholds</h2>



While the percentage thresholds are flexible, they still manually picking the threshold. It would convenient if we had a query that would automatically pick a threshold for rare events.



Fortunately, we can use <a href="https://en.wikipedia.org/wiki/Standard_deviation" target="_blank" rel="noreferrer noopener" aria-label=" (opens in a new tab)">standard deviations</a>. We can automatically define thresholds appropriate for data with either low variance (30% disk usage +/- 1%) or high variance (30% signup rate +/- 10%).



We then choose how sensitive we want to be to outliers. Do we want to detect events with a 5% chance, or a 0.1% chance? Additionally, we have to choose if we care about both high and low values (<a href="https://en.wikipedia.org/wiki/One-_and_two-tailed_tests" target="_blank" rel="noreferrer noopener" aria-label=" (opens in a new tab)">a two-tailed test</a>), or just one of the two (<a href="https://en.wikipedia.org/wiki/One-_and_two-tailed_tests" target="_blank" rel="noreferrer noopener" aria-label=" (opens in a new tab)">a one-tailed test</a>).



First, we need to pick a z score (number of standard deviations) threshold. <a href="http://sphweb.bumc.bu.edu/otlt/MPH-Modules/BS/BS704_HypothesisTest-Means-Proportions/BS704_HypothesisTest-Means-Proportions3.html" target="_blank" rel="noreferrer noopener" aria-label=" (opens in a new tab)">This page from Boston University</a> has a good explanation and z-scores for different probabilities.



For example, if we care about high or low values that occur only 5% of the time by random chance, we’d use a z score threshold of +/- 1.645. If we want a 5% threshold exclusively for high values, we’d pick 1.96.



Here’s the SQL for calculating z-scores for the low variance disk usage data:



<pre class="wp-block-code"><code>with data as (
 select
 date_trunc('day', created_at)::date as day,
 count(1) as value
 from disk_usage
 group by 1
), data_with_stddev as (
 select
 day,
 value,
 (value - avg(value) over ())
 / (stddev(value) over ()) as zscore
 from data
 order by 1
)
select * from data_with_stddev</code></pre>



<figure class="wp-block-image fancybox"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/image-07-1.png" alt="low variance" class="wp-image-78094"/></figure>



This is the same query as the percent threshold, but we calculate zscore instead of percent deviation from the mean. The first part of the calculation is&nbsp;(value &#8211; avg(value) over ())&nbsp;which calculates how much a single datapoint deviates from the mean.



The second part&nbsp;/ (stddev(value) over ())&nbsp;divides the deviation by the standard deviation, to measure how many standard deviations the data point is from the mean.



Here’s the outlier query for a two-tailed 5% threshold:



<pre class="wp-block-code"><code>select *
from data_with_stddev
where abs(zscore) >= 1.645</code></pre>



<figure class="wp-block-image fancybox"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/image-081.png" alt="Low variance table" class="wp-image-78088"/></figure>



 Plotting the disk usage data and outliers together: 



<figure class="wp-block-image fancybox"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/image-091.png" alt="Disk usage" class="wp-image-78082"/></figure>



 And swapping in the high variance signup rate data: 



<figure class="wp-block-image fancybox"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/image-101.png" alt="Signup rate" class="wp-image-78076"/></figure>



We’ve applied the same standard deviation threshold to queries with very different data, and can still detect the outliers.



<h2 class="wp-block-heading">Well, that was unexpected!</h2>



We now have a few techniques for finding unusual results. Enjoy discovering the unpredictable in your data!

Outlier Detection in SQL

LinkedIn

Twitter

GitHub

curve-image-unique-image-unique

curve

3-dark-2-image-unique-image-unique

3 DARK 2

Get the latest in analytics right in your inbox.

Article