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

In the previous lesson, we eliminated potential anomalies with less than 10 entries. Using thresholds, we were able to remove some non-interesting anomalies.

Let's have a look at the data for status code `400` after applying the threshold:



The first alert happened at 17:59, and a minute later, the z-score was still high with a large number of entries, and so we classified the next rows at 18:00 as an anomaly as well.

If you think of an alerting system, we want to send an alert only when an anomaly first happens. We do not want to send an alert every minute until the z-score comes back below the threshold. In this case, we only want to send one alert at 17:59. We do not want to send another alert a minute later at 18:00.

Let's remove alerts where the previous period was also classified as an alert:



Learn how to eliminate repeating alerts in the given data.

Eliminating Repeating Alerts