SQL-Based Data Cleaning and Anomaly Detection Questions

Write a query that flags outliers in a numeric column using the IQR (interquartile range) method: compute Q1 and Q3 (for example with PERCENTILE_CONT), derive the IQR, and flag values outside [Q1 - 1.5*IQR, Q3 + 1.5*IQR]. Show how you would compute this PER CATEGORY (for example per country or per product) rather than globally, and discuss how you would handle a category with very few rows where the IQR is not a reliable signal.

What is the difference between an 'outlier' and an 'anomaly' in a data-quality context? Give an example of a legitimate outlier that should be kept in the data for modeling or reporting, and an example of an anomaly that indicates a genuine data-quality issue and should be quarantined or removed. What instrumentation failure modes (clock resets, sentinel defaults, timestamp misalignment) commonly produce the second kind, and how would you triage between the two quickly?

Write a query that computes the daily null rate for a required column, grouped by source, over the last 60 days, and flags days where that day's null rate is a statistical outlier relative to a trailing baseline (mean plus three standard deviations over the preceding 28 days) rather than against a fixed threshold. Discuss how you'd avoid false positives when the sample size on a given day is small.

Standard deviation-based outlier detection breaks down when the underlying data is skewed, because the mean and standard deviation themselves get pulled by the outliers. Write a SQL-based robust alternative using the median and median absolute deviation (MAD), computed with window functions over a rolling window, and explain the threshold rule you'd use to call a day anomalous.

Write a query that compares today's row count (or a daily metric like DAU or transaction count) against a rolling baseline, such as the trailing 28-day median for the same weekday, and flags an alert if today's value is outside a defined tolerance band (for example below 70% or above 130% of the baseline). Discuss how you'd choose the tolerance band and baseline window, and how the check should behave on days with unusually low absolute volume.