SQL-Based Data Cleaning and Anomaly Detection Questions

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.

Given a table of timestamped events per entity (for example sensor readings per sensor, or activity dates per customer), write an efficient SQL query using LAG/LEAD or window functions that identifies gaps between consecutive events longer than an expected interval, and returns the gap start, end, and duration per entity. Discuss how this scales when there are millions of distinct entities.

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.

Write a single SQL query over a transactions table that computes BOTH a z-score flag (more than 3 standard deviations from the mean) and an IQR flag (outside Q1 - 1.5*IQR / Q3 + 1.5*IQR) for the same numeric column, in one pass, so the two methods can be compared side by side on the same data.

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?