Languages

SQL for Data Analytics

Advanced SQL patterns for data analysis and reporting.

Driptanil DattaSoftware Developer

Mar 202514 min read

Page 6: Anomaly Detection — Finding What Doesn't Belong 🚨

Anomaly detection is the process of identifying data points that deviate significantly from the "norm." In data analytics, this is critical for spotting fraud, tracking system errors, or identifying massive shifts in user behavior.

What SQL Can (and Can't) Do

SQL is excellent for Statistical Outlier Detection but lacks the capability for complex multidimensional pattern recognition (Clustering) that ML models provide.

Surfacing Outliers with Sorting

The simplest form of anomaly detection:

• Sort by value descending to find the highest earners or biggest spenders.
• Sort by date to find the oldest or newest records.

Using Percentiles and Standard Deviations

A more rigorous approach involves math:

• Z-Score: How many standard deviations is a point away from the mean?
• Interquartile Range (IQR): Calculating the 25th and 75th percentiles to define a "normal" range.

-- Identifying values outside 3 Standard Deviations
SELECT * FROM metrics
WHERE ABS(val - AVG(val) OVER()) > 3 * STDDEV(val) OVER();

Visualizing Anomalies

Transforming query results into simple flags for BI tools:

• CASE WHEN is_anomaly THEN 1 ELSE 0 END as anomaly_flag

Types of Anomalies

1. Value Anomalies: A single transaction for $1,000,000 in a system where the average is $10.
2. Frequency Anomalies: A user logging in 500 times in 1 minute.
3. Absence of Data: A critical drop to ZERO in a metric like "Checkout Start" (usually indicates a bug).

Handling Anomalies