Simple Anomaly Detection using SQL

Gain insights into using SQL for anomaly detection. Delve into applying mean, standard deviation, and z-score to identify and investigate data anomalies, enhancing your analytical skills.

4.7

19 Lessons

Updated 1 month ago

Join 3 million developers at

LEARNING OBJECTIVES

Identify anomalies in time-series data by computing mean and standard deviation in SQL
Calculate z-scores in SQL to flag data points outside defined acceptable ranges
Tune z-score thresholds in SQL to balance sensitivity and false alerts
Prepare server log data by filling time gaps with zeros using PostgreSQL functions
Backtest anomaly detection with SQL window functions and dynamic thresholds on past data
Reduce repeating alerts by detecting first-occurrence anomalies using SQL thresholds
Apply weighted mean, median, and MAD in SQL to improve anomaly detection accuracy

Learning Roadmap

19 Lessons4 Quizzes

Detecting Anomalies

Learn how to use SQL and z-scores to detect and optimize anomaly detection.

Introduction

What are Anomalies?

Understanding Z-score

Optimizing Z-score

Quiz 1

Analyzing A Server Log

Walk through server log analysis, data preparation, and anomaly detection to pinpoint issues.

Getting a Sense of the Data

Preparing the Data

Identifying Anomalies

Quiz 2

Backtesting

5 Lessons

Work your way through backtesting anomalies with thresholds, eliminating repeats, and experimenting for optimal results.

Improving Accuracy

5 Lessons

Apply your skills to improve anomaly detection accuracy using weighted mean, median, and MAD.

Certificate of Completion

Showcase your accomplishment by sharing your certificate of completion.

Developed by MAANG Engineers

ABOUT THIS COURSE

Learn how to identify anomalies in your data using SQL. Understand how to apply mathematical concepts such as mean, standard deviation, and z-score to detect when an anomaly occurred in your data and how to investigate past data to improve and refine the model.

ABOUT THE AUTHOR

Haki Benita

Haki is a technical lead and a tech blogger interested in databases, web development, software design and performance tuning.

Learn more about Haki

Trusted by 3 million developers working at companies

These are high-quality courses. Trust me the price is worth it for the content quality. Educative came at the right time in my career. I'm understanding topics better than with any book or online video tutorial I've done. Truly made for developers. Thanks

Anthony Walker

@_webarchitect_

Just finished my first full #ML course: Machine learning for Software Engineers from Educative, Inc. ... Highly recommend!

Evan Dunbar

ML Engineer

You guys are the gold standard of crash-courses... Narrow enough that it doesn't need years of study or a full blown book to get the gist, but broad enough that an afternoon of Googling doesn't cut it.

Software Developer

Carlos Matias La Borde

I spend my days and nights on Educative. It is indispensable. It is such a unique and reader-friendly site

Souvik Kundu

Front-end Developer

Your courses are simply awesome, the depth they go into and the breadth of coverage is so good that I don't have to refer to 10 different websites looking for interview topics and content.

Vinay Krishnaiah

Software Developer