Anomaly Detection in SQL — Z-Scores and IQR

Flag the day that doesn't belong, in one query, no Python. FIND_VIDEO: search 'SQL anomaly detection z-score window' — recommended channel: Modern SQL. Aim for 11 min or under.

16 minutesVideo LessonPDF notes

Watch Anomaly Detection in SQL — Z-Scores and IQR on YouTube

🎯 Free Guest Mode: You are learning for free. Sign in to save your completion progress and quiz answers.

Loading progress...

Key moments

Outlier Importance and Context — The instructor outlines business contexts where outliers represent critical fraud events versus erroneous data.
Bivariate Outlier Intuition — A height and weight scenario demonstrates how rare deviations separate from standard correlations.
Z-Score Mechanics — The instructor explains normal distribution standardization and the three standard deviation threshold.
Percentiles and IQR Theory — Sorted distribution rankings illustrate how 25th and 75th percentiles define the interquartile range.
Visual Outlier Detection — The lesson contrasts scatter plots and box plot boundaries for spotting anomalous observations.
Z-Score Implementation — A numeric demonstration walks through calculating standard deviations to isolate extreme values.
IQR Boundary Calculations — The instructor outlines computing lower and upper bounds using the 1.5 IQR formula.

PDF notes

Bottom line: You will identify and extract data anomalies using parametric Z-scores and non-parametric Interquartile Range (IQR) methods in SQL and data processing pipelines. You will evaluate distribution properties to isolate legitimate fraud or data quality errors without corrupting baseline metrics.

This lesson on Anomaly Detection in SQL — Z-Scores and IQR is hands-on and example-driven. You will identify and extract data anomalies using parametric Z-scores and non-parametric Interquartile Range (IQR) methods in SQL and data processing pipelines. You will evaluate distribution properties to isolate legitimate fraud or data quality errors without corrupting baseline metrics.

What You'll Be Able To Do

Differentiate critical domain outliers like credit fraud from measurement errors.
Compute Z-scores across numerical distributions to identify observations beyond 3 standard deviations.
Calculate percentile distributions and IQR thresholds to build non-parametric outlier boundaries.
Formulate lower and upper bounds using the 1.5 multiplier rule on first and third quartiles.
Select appropriate visual inspection tools like scatter plots and box plots to validate anomalies.

Detailed Concept Walkthrough

1. Outlier Definition and Business Context

An outlier is an observation that lies distant from the overall distribution of values in a dataset. Outliers can signal either critical business events or corrupted measurements.

Mechanism: In domain-specific datasets like fraud detection, rare extreme transactions are the high-value target observations required for risk assessment.
Under the Hood: In standard operational datasets, extreme values frequently arise from human input errors, transmission bugs, or sensor measurement drift.
Impact on Metrics: Skewed anomalies distort core parametric statistics, pulling the sample mean and inflating the standard deviation away from the true population center.

-- Inspect distribution summary stats sensitive to extreme outliers
SELECT 
    AVG(transaction_amount) AS mean_amount,
    STDDEV(transaction_amount) AS stddev_amount,
    MIN(transaction_amount) AS min_amount,
    MAX(transaction_amount) AS max_amount
FROM transactions;

Key Takeaway: Outlier handling strategy depends entirely on whether extreme values represent fraudulent signals or data entry noise.

2. Z-Score Anomaly Detection

The Z-score standardizes a distribution to a mean of 0 and a standard deviation of 1 to determine how many standard deviations an observation is from the mean.

Mathematical Formula: Z-score is computed as (X - Mean) / Standard_Deviation for each individual data point X.
Execution Flow: Observations with an absolute Z-score value greater than 3.0 fall beyond the third standard deviation threshold and are flagged as outliers.
Parametric Limitation: The technique assumes the underlying data follows a standard normal distribution, making extreme values pull the baseline mean before detection.

-- Calculate Z-scores and filter anomalies exceeding 3 standard deviations
WITH stats AS (
    SELECT 
        AVG(val) AS mean_val,
        STDDEV(val) AS std_val
    FROM dataset
)
SELECT 
    d.val,
    (d.val - s.mean_val) / s.std_val AS z_score
FROM dataset d
CROSS JOIN stats s
WHERE ABS((d.val - s.mean_val) / s.std_val) > 3;

Key Takeaway: Z-score flags any point falling outside three standard deviations under a normal distribution assumption.

3. Interquartile Range (IQR) Detection

IQR measures the statistical dispersion of the middle 50% of sorted data, defining robust non-parametric boundaries.

Sorting Mechanism: Data is sorted in ascending order to divide points into percentiles, identifying the 25th percentile (Q1) and 75th percentile (Q3).
Boundary Formulation: IQR is calculated as Q3 - Q1, defining the lower bound as Q1 - (1.5 * IQR) and the upper bound as Q3 + (1.5 * IQR).
Robust Behavior: Because quartiles rely on ordered rank rather than aggregate sums, IQR limits are resistant to extreme data distortions.

-- Calculate IQR boundaries using percentiles and flag anomalies
WITH quartiles AS (
    SELECT 
        PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY val) AS q1,
        PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY val) AS q3
    FROM dataset
),
bounds AS (
    SELECT 
        q1,
        q3,
        (q3 - q1) AS iqr,
        q1 - (1.5 * (q3 - q1)) AS lower_bound,
        q3 + (1.5 * (q3 - q1)) AS upper_bound
    FROM quartiles
)
SELECT d.val
FROM dataset d
CROSS JOIN bounds b
WHERE d.val < b.lower_bound OR d.val > b.upper_bound;

Key Takeaway: IQR establishes robust upper and lower cutoffs by scaling the spread between the 25th and 75th percentiles.

4. Visual Outlier Inspection Methods

Visual plotting provides an immediate geometric representation of bivariate and univariate distributions to confirm statistical outliers.

Scatter Plots: Plot paired variables (e.g., Height vs. Weight) across Cartesian axes to expose isolated points that violate bivariate correlations.
Box Plots: Display summary distributions displaying Q1, Median (Q2), Q3, and extending whiskers to upper and lower bounds.
Inspection Rule: Any point plotted beyond the box plot whiskers is categorized directly as an outlier for data cleansing.

# Generate visual checks for outlier distributions
import matplotlib.pyplot as plt

# Boxplot visualization to spot values outside 1.5 * IQR
plt.figure(figsize=(8, 4))
plt.boxplot(data_values, vert=False)
plt.title('Outlier Inspection via Box Plot')
plt.show()

Key Takeaway: Box plots map Q1, median, Q3, and whisker limits to render extreme points visible.

Topics Covered in Anomaly Detection in SQL — Z-Scores and IQR

Outlier Importance and Context (0:00 - 0:45) — The instructor outlines business contexts where outliers represent critical fraud events versus erroneous data.
Bivariate Outlier Intuition (0:46 - 1:35) — A height and weight scenario demonstrates how rare deviations separate from standard correlations.
Z-Score Mechanics (1:36 - 2:30) — The instructor explains normal distribution standardization and the three standard deviation threshold.
Percentiles and IQR Theory (2:31 - 3:40) — Sorted distribution rankings illustrate how 25th and 75th percentiles define the interquartile range.
Visual Outlier Detection (3:41 - 4:45) — The lesson contrasts scatter plots and box plot boundaries for spotting anomalous observations.
Z-Score Implementation (4:46 - 5:40) — A numeric demonstration walks through calculating standard deviations to isolate extreme values.
IQR Boundary Calculations (5:41 - 6:30) — The instructor outlines computing lower and upper bounds using the 1.5 IQR formula.

SQL Cheat Sheet

(X - AVG(X)) / STDDEV(X) — Computes Z-score standard deviation distance
```
SELECT (val - AVG(val) OVER()) / STDDEV(val) OVER() FROM t;
```

PERCENTILE_CONT(0.25) — Calculates 25th percentile value Q1

SELECT PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY val) FROM t;

PERCENTILE_CONT(0.75) — Calculates 75th percentile value Q3

SELECT PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY val) FROM t;

Q3 - Q1 — Calculates interquartile range spread

SELECT (q3 - q1) AS iqr FROM quartile_table;

Q1 - (1.5 * IQR) — Calculates lower boundary for outliers

SELECT q1 - 1.5 * (q3 - q1) AS lower_bound FROM bounds;

Q3 + (1.5 * IQR) — Calculates upper boundary for outliers

SELECT q3 + 1.5 * (q3 - q1) AS upper_bound FROM bounds;

Comparison Table

Feature	Z-Score Method	IQR Method
Distribution Assumption	Assumes normal distribution	Non-parametric distribution agnostic
Threshold Metric	3 Standard Deviations	1.5 Multiplier of IQR
Sensitivity to Skew	Highly sensitive to extremes	Robust against extreme skews

Common Pitfalls

Mistake: Applying Z-score filters to heavily skewed data. Avoid: Use non-parametric IQR boundaries when data deviates from normal distribution.
Mistake: Blindly dropping all detected outliers across every domain. Avoid: Inspect whether extreme records represent valid fraudulent signals before deleting.
Mistake: Forgetting to sort values prior to computing percentiles manually. Avoid: Order data sequentially before calculating quartile ranks and cutoffs.

FAQs

Why is 3 standard deviations chosen as the Z-score cutoff? In a standard normal distribution, 99.7% of all data points fall within 3 standard deviations of the mean.
Why do outliers distort the Z-score calculation itself? Extreme values directly inflate the mean and standard deviation, which dampens the resulting Z-score of other points.
What causes outliers in production datasets? Outliers stem from natural population variability, manual data entry mistakes, or sensor and experimental measurement errors.

Common pitfalls

Mistake: Applying Z-score filters to heavily skewed data. Avoid: Use non-parametric IQR boundaries when data deviates from normal distribution.
Mistake: Blindly dropping all detected outliers across every domain. Avoid: Inspect whether extreme records represent valid fraudulent signals before deleting.
Mistake: Forgetting to sort values prior to computing percentiles manually. Avoid: Order data sequentially before calculating quartile ranks and cutoffs.

Hello, all. Today we'll be discussing how to find an outlier in a particular data set. First of all, to begin with, guys, outliers are important also for some of the use cases, and it may also not be important for some of the use cases. So, let me just take an example. Suppose, if I have an use case which is called as credit card fraudulent data set. Now in that particular data set, you'll be having some kind of data which will act like an outlier, you know, some of the transaction or which will be actually fraudulent. Those information are very, very important. In that case, we can consider that that particular outlier is very, very important. But in the case of normal data set where we have, due to some human errors, that particular data has been captured in a in a in not in a proper way, you know, so that rare value can also be an outlier which is not useful for our data set itself. So, first of all, before I move this uh ahead, make sure you watch this video till the end because I'm also going to show you some practical implementation how to detect an outlier in a data set itself. So, first of all, how to begin with, what is an outlier? See, outlier is a data point in a data set that is distant from all the observation. That basically means that it does not belong to the observation that is actually present in the data set, you know, it it did it has a distant characteristics in short. So, let me just give you an example. Suppose, I have some data set over here. Okay. In my X-axis, I have some value like uh weight, in the Y-axis, I have some value like height. Now, usually, what happens is that you'll be seeing a scenario that as the weight increases, your height also increases. Most of the scenario is like that. But in some scenario, you'll be seeing that even though the weight is somewhere here, our height will be very, very high. So, this is just like an outlier, you know. Uh and this is a very rare condition, very rare condition. Specifically, you'll not have those scenario in a population data set. Okay. So, this is just one example. Now, if I take an example of credit card fraudulent data, you know, credit card fraudulent data, in that you'll be seeing that some of the transaction will be very rare, you know, some of the transaction which is just like an uh you know fraudulent data will be very rare. And that is what is the most important outlier data, right? So, they are various ways to handle this kind of outliers and this kind of outliers that are rarely present if I take the example of height and weight. Okay. So, they are different ways. In this particular video, we will be specifically seeing that suppose in a data set due to human error, they are some data that has got captured as an outlier, how do we find that particular outlier? How do we try to fix that? Okay. So, initially to begin with, I'm going to import some of the libraries like NumPy, pandas and matplotlib. Sorry, pandas is not there. NumPy and matplotlib will be sufficient. Now, what are the criterias to identify an outlier? See, in this particular video, I'm going to show you two different techniques. One is with the help of something called as Z-score. If you know about standard normal distribution, I think Z-score will be very, very easy for you. This again, anyhow, I'll be explaining what is this Z-score. Then, the next thing is something called as interquartile range. Okay. So, we are going to discuss these two techniques. The first one is basically the interquartile range. The second technique is something like a Z-score. Okay. Now, what exactly is Z-score? That we need to understand. Always remember, guys, whenever we have some data set, let me just take one feature, and I have some data set like 12, 13, 14, 15, 17, or let me just clear this. Suppose I'll just take a simple example like 1, 2, 3, 4, 5. Okay. And suppose I calculate the mean of this particular data set as 3, and my standard deviation is 1. Suppose, I'm just considering it, guys. I'm not con- I'm not calculating it. I'm just considering that mean will be 3 and standard deviation will be 1. Now, if I want to convert this distribution into a standard normal distribution, wherein the first property of standard normal distribution is the mean = 0, and standard deviation = 1. So, in order to convert this, we usually apply a Z-score formula which is something like X - this X is basically my all my observation I'll be considering - mean / standard deviation. Okay. This is how the Z-score is basically calculated. And Z-score basically says that in what standard deviation, whether in the first standard deviation or in whether in the second standard deviation, this particular data set actually falls into. Now, always remember, guys, in a standard normal distribution, right, if I have some kind of standard normal distribution, this center point is basically my mean. This is my first standard deviation. This is my second standard deviation. This is my third standard deviation. And anything away from this anything any data that falls after this third standard deviation are basically considered as my outliers. Okay, as my outliers. So, suppose I have this particular data set, and suppose if I apply this Z-score formula in this particular data set, if this Z-score formula fall within the standard deviation of 3, okay, within this three standard deviation, third standard deviation, that time I will be considering that this particular data set is not an outlier. If any data set falls away from this particular third standard deviation, then I will be considering as an outlier. And ha- that is how we are going to perform the second step. Z-score, what we'll do? Suppose I've given given a feature like F1 and this is a having all the features like this, I will go and see that which all features over here, which all values over here fall within the third standard deviation. Suppose if it does not follow or if if it does not fall within the third standard deviation, then I'm going to consider that particular value as my outlier. Simple. Very easy. Okay. So, this is how a Z-score works. Now, the other technique is something called as interquartile range. That is IQR. In interquartile range, there are some steps involved. What we do is that you should need to know what is percentiles. Okay. Before understanding interquartile range. Now, suppose, guys, I have this features like I have some values like 5, 6, 7, and I have 1, 2. Okay. So, in order to find out percentiles, what we do is that we will try to, you know, um uh suppose I have some more numbers like 4, 9, um 8 over here and 10. Okay. So, if I want to find out the percentiles, what we'll do is that, first of all, sorting mechanism will happen in this numbers. So, it will become sorted like 1, 2, 3, 4, 5, 6, 7, 8, 9, 10. After sorting, right, what we do is that suppose if I consider 1, this 1 is nothing but my 0th percentile. This basically indicates that there are no number there are zero number 0 percentage of number less than 1 that are smaller than 1. Oh, sorry. That are lesser than 1. Okay. There are zero numbers. And that is true because I don't have any numbers less than 1 over here. Suppose if I consider 10 uh 2, since I have 10 numbers over here, 2 will basically become my 10 percentile number. That basically means that 10% of the total numbers over here in this particular distribution are less than 2. And uh 10% of this numbers are only 1, right? I have only one number which is less than 2, which is nothing but from this total 10 numbers, it is actually 10%. Okay. Now, if I consider 3 over here, this becomes my 20%. That basically indicates that 20% of my number is less than 3, 80% of number 80% of the number in this distribution is > 3, right? So, similarly 4, 4 will actually be my 30%. That basically indicates that 30% of this distribution in this particular distribution is < 4, 70% is > 4 or greater than or equal to 4. Okay. That is how it is indicated. Now, in interquartile range, the most numbers that we will be focusing on will be 25 percentile, 75 percentile. And then we calculate 75 percentile values in this distribution. Suppose if I'm taking this particular distribution as an example, we subtract it with 25 percentile. And that is basically my IQR. That is basically my interquartile range. That is basically that 75 percentile number whatever I'm get and if I'm subtracting with 25 percentile number, that is basically my interquartile range. Okay. Simple. Now, how to detect an outlier after finding an IQR value? That I'll just discuss in a while. Okay. So, this was the two techniques. One is Z-score and one is interquartile range. After this, what I'm going to explain you is that what are the reasons for an outlier to exist in a data set? First of all, variability in the data. The second reason is that there is an experi- experimental measurement error or it may be some human error. They are not calculated it properly itself. What are the impacts of having an outlier in a data set? There are various impacts because the statistical analysis that you will be doing if there are a lot of outliers that are present, it may affect that particular analysis. Okay. And it may also have some more problems when we are actually trying to calculate the mean and the standard deviation. Always remember, guys, due to the outliers, your mean and standard deviation may change a lot. And that is the reason that these are some of the impacts that may happen if our outlier is present. Various ways of finding the outlier, one way is basically by using scatter plot. The second way is something like box plot. Uh scatter plot is just like distribution of X and Y. If I If I If I just show you X and Y, just remember this. Suppose this is my X-axis and Y-axis. Suppose I had some of the distribution which is somewhere here. Okay. But there will be some points that will be plotted here. So, these are my outliers. And by using a scattered plot, I will be able to see it. Okay. Now, similarly, if I come up with box plot, box plot is nothing but I'll be having this kind of boxes. You know, this this this this will be my 25 percentile, this will be your 50 percentile, this point will be your 75 percentile, and anything above this over here will be your outliers, you know, outliers. Okay. So, this is one example of box plot. But again, I'll explain I just have calculated IQR. After finding IQR, how do I determine the outlier? First of all, let me just try to uh show you how to do with that with the Z-score with the help of a code. Now, suppose imagine that this is my data set. Over here I have some values like 11, 10, 12, 14, 12, 15, 14, 13, 15. And you observe some of the numbers. I have 102, 107, 108. And by just seeing this particular data set, you may come into a conclusion that 102, 107 and 108 may be an outlier because this values are higher when compared to the other distribution. But how to do it mathematically? That we need to understand. How to do with the help of Python, right? Now, detecting outliers using Z-score. First, I'm going to consider Z-score. So, remember Z-score the formula is X - mu / standard deviation. So, here I've created my outlier as my list. Okay. And I'm creating a function which will detect the outlier. And to detect the outlier with the help of Z-score, first of all, I need to find out the Z-score value. Okay. So, uh the threshold value I'm keeping it as 3. That basically means that within third standard deviation if my data is falling, I'm going to consider I'm not going to consider that as an outlier. If it is falling away from uh third standard deviation, then I'm going to consider it as an outlier. So, first I'm going to compute the mean and standard deviation because I need the mean and standard deviation based on this formula. And then I'm going to put a for loop in my data set. For each and every data set, I'm going to subtract I'm going to apply this particular formula and try to find out the Z-score. If that particular value is uh whatever Z-score value is coming over here, once I get this particular Z-score value, if that Z-score value is greater than the threshold value, okay? If the Z-score value is greater than the threshold value, that basically means if it is falling away from the third standard deviation, if you are calculating the Z-score, if it is more than 3, I'm going to append that particular Y value that particular Y value in my outliers. Okay. That is what I'm actually trying to do. Sorry. It should not be Y. I'm going to append my I value in this particular outliers. So, this particular I value indicates that it does not fall within the third standard deviation. So, I am adding that in my outliers list. Okay. And similarly it will go and check for each and every uh you know uh values in this particular data set in this particular data set. It will go and check. Now, uh this this was about this particular function, guys. Uh I'm trying to see for each and every values that are present in that particular data set whether that falls in the outlier or not. If it does not fall in the outlier, I'm going to retrieve that particular record and append it in my outlier list. So, here it is. I am uh applying this particular function. I am giving my data set. And as soon as I see my outlier point, I'm getting 102, 107, 108. Now, you can see perfectly we have got the outlier by just using a simple method of Z-score. If it is falling within the third standard deviation, I'm considering it. If it is not falling, I'm just considering that as an outlier. Okay. Now, the next point, I'm going to go with the interquartile range. Interquartile range is basically my 75 percentile value - 25 percentile value. First of all, there are some steps in interquartile range. First of all, we arrange the data in increasing order, okay, basically in the sorted order. We calculate the first quartile and the third quartile. This is basically my 25 percentile, and this will basically be my 75 percentile. Then we find out the difference from the third con- uh quartile, that is my 75 percentile, and the 25 percentile. And finally, I find out the lower point. Lower bound value basically means that uh below what point below what point in that particular data set I'm going to consider that belongs to an outlier. And higher bound or the upper bound basically means that from the third quartile, okay, what value I have to consider in the top that uh that particular data set does not belong uh within that particular uh interquartile range. So, I'm going to calculate my lower bound value and the upper bound value. In order to calculate, what I have to do is that whatever first quartile I'm getting, I have to multiply with 1.5. Whatever third quartile I'm getting, I'm going to multiply with 1.5. Simple. This many steps are there. And anything that lies outside of lower and upper bound is basically an outlier. Now, how I'm considering 1.5 and 1.5 over here? That is based on the distribution pattern. Most suitable value that we have actually found out for multiplying that has been proved already is 1.5. Okay. So, for that reason, we're basically using. So, anything that lies outside uh of lower and upper bound is basically an outlier. So, this is the way how you can do the sorted. I don't want to do all this step-by-step. What I'll do is that I will basically use a function inside the NumPy which is called as np.percentile. And here I'm going to give my data set, and here I'm specifying that I need the 25 percentile value and the 75 percentile value. As soon as I do this, I'll be getting my lower bound over here and my upper bound. And if you see my lower and upper bound, this is basically 12.0 and 5. 15.0. And then I'll try to find out the difference between this. So, here it is. I have I'll find out quantile 3 - quantile 1. This is basically my three value. Okay. But still I need to find out what is my uh you know uh the lower bound and the higher bound values which I'm considering after my step fourth and fifth. Okay. For that I have to multiply with 1.5. So, I'll go down over here. Uh here is my IQR_value. So, what I'm doing? I'm going to find out my lower bound value and the upper bound value. So, lower bound value as usual I said, quantile 1 - 1.5 * IQR value. And upper bound, I'm saying that quantile 3 + 1.5 * IQR_value. When I do this, this is my lower bound. This is my higher bound. So, anything any values if it is away from this particular lower bound and the upper bound, that is basically considered as an outlier. And this is how you can do a very simple method by using IQR and the Z-score, and you can find out an outlier in a data set. In my next video, I'm going to take a very good example of a data set uh you know, of from a Kaggle competition. And then I am going to show you how you can find out an outlier and how to how you can fix that outlier in that particular problem statement. So, I hope you like this particular videos, guys. Please do subscribe the channel if you're not already subscribed. Uh please share with all your friends whoever require this kind of help. Uh and, yes, I'll see you all in the next video. Have a great day ahead. Uh uh keep on learning, never give up. I'll see you all in the next video. God bless you all. Thank you one and all.

Frequently asked questions

Why is 3 standard deviations chosen as the Z-score cutoff?

In a standard normal distribution, 99.7% of all data points fall within 3 standard deviations of the mean.

Why do outliers distort the Z-score calculation itself?

Extreme values directly inflate the mean and standard deviation, which dampens the resulting Z-score of other points.

What causes outliers in production datasets?

Outliers stem from natural population variability, manual data entry mistakes, or sensor and experimental measurement errors.

How was this lesson?

Your feedback helps us refine explanations and catch bugs.