Learn to identify and address irregularities in data using SQL. This module covers detecting anomalies, backtesting techniques, and server log analysis to improve your database's efficiency.

Become a Database Professional with SQL

In this module, we’ll focus on detecting anomalies in data using SQL. We’ll explore how to apply mathematical concepts like mean, standard deviation, and z-scores to identify anomalies. Additionally, we’ll delve into backtesting techniques to investigate past data and refine our detection models. We’ll also cover server log analysis to improve and optimize the overall efficiency and scalability of databases.

Anomaly Detection

Using a z-score for detecting anomalies is an easy way to get started with anomaly detection and see results right away. But this method is not always the best choice, and if you do not get good alerts using this method, there are some improvements and other methods you can try using just SQL.

Our system uses a mean to determine a reasonable value and a lookback period to determine how far back to calculate the mean. In our case, we calculated the mean based on data from 1 hour ago.

Using this method of calculating mean gives the same weight to entries that happened 1 hour ago and to entries that just happened. Give more weight to recent entries at the expense of previous entries. The new weighted mean should become more sensitive to recent entries, and you may be able to identify anomalies quicker.
To give more weight to recent entries, you can use a weighted average:


Learn how to use weighted mean to improve the accuracy of anomaly detection.

Weighted Mean