SQL-Based Data Cleaning and Anomaly Detection Questions

Using SQL to profile, clean, and validate data: detecting duplicates, nulls, outliers, and referential-integrity breaks; writing production-safe diagnostic queries; and root-cause debugging of missing or incorrect data. Covers anomaly-detection queries and data cleaning for analytics. The hands-on SQL data-quality skill.

MediumTechnical

34 practiced

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.

Sample Answer

\text{MAD} = \text{median}(|x_i - \text{median}(x)|), \qquad M_i = \frac{0.6745 (x_i - \text{median}(x))}{\text{MAD}}

MediumTechnical

26 practiced

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.

HardTechnical

37 practiced

You need to validate a data-quality metric (for example, a duplicate rate or a null rate) on a table with tens of billions of rows, where a full scan is too slow or too expensive to run regularly. Describe a statistically sound sampling strategy: how you would choose the sample size and stratification (for example by partition or by day), compute a confidence interval around the estimate, and decide when the sample result is close enough to a threshold that you should escalate to a full scan instead.

Sample Answer

Replace a full scan with a random or stratified sample, compute the metric on the sample plus a confidence interval, and only pay for a full scan when the sampled estimate is close enough to your threshold that the uncertainty itself matters. Approach sqlWITH sampled AS ( SELECT * FROM clicks TABLESAMPLE BERNOULLI(1) -- roughly 1% sample (some engines, e.g. DuckDB, require BERNOULLI(1%)) ) SELECT partition_day, COUNT(*) AS sample_rows, COUNT(*) - COUNT(DISTINCT (user_id, session_id)) AS sample_dup_rows, (COUNT(*) - COUNT(DISTINCT (user_id, session_id))) * 1.0 / COUNT(*) AS sample_dup_rate FROM sampled GROUP BY partition_day; Stratifying by partition (here, by day) rather than sampling uniformly across the whole table avoids a sample that's accidentally skewed toward a few large partitions; report the sampled rate alongside a confidence interval (derived from the sample size and observed proportion via a standard proportion-confidence formula) so the estimate is presented honestly as a range, not a false-precision point value. Note that a multi-column DISTINCT needs the row-constructor form, COUNT(DISTINCT (col1, col2)), not COUNT(DISTINCT col1, col2); the latter is not valid syntax in PostgreSQL or DuckDB and will fail with a function-not-found error. Worked example A 100-billion-row table sampled at roughly 1% (about 1 billion rows) still gives a duplicate-rate estimate with a narrow confidence interval, at a small fraction of the cost of scanning every row; if that estimate sits comfortably below your alert threshold with the interval fully below it too, you can trust the sampled result without a full scan. Trade-offs and pitfalls When the sampled estimate's confidence interval STRADDLES your threshold (you can't tell if the true rate is above or below the line you care about), that's exactly the situation that justifies paying for a full, expensive scan rather than the sample being "good enough"; the sample's job is to save you from unnecessary full scans, not to force a decision when the uncertainty is genuinely too high to trust. An incremental, watermark-based check (only re-examining rows updated since the last successful run, tracked via a stored watermark column, and designed to be idempotent across retries) is a complementary technique for keeping routine checks cheap, distinct from sampling: it reduces the SCOPE of what's scanned rather than the FRACTION sampled within that scope, and the two combine well on a genuinely enormous, frequently-updated table.

EasyTechnical

31 practiced

Write a query to flag rows where a timestamp is logically impossible: it is set in the future relative to now, or it predates a sane earliest-possible date for the business (for example, before the year 2000). Explain common causes of this kind of error (client clock skew, default/sentinel dates, timezone mistakes) and how you'd prevent it upstream.

HardTechnical

37 practiced

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.

Sample Answer

Unlock Full Question Bank

Get access to all SQL-Based Data Cleaning and Anomaly Detection interview questions and detailed answers.

Join thousands of developers preparing for their dream job.

SQL-Based Data Cleaning and Anomaly Detection Questions

Sample Answer

Approach (verified by execution)

Worked example (verified by execution)

Trade-offs and pitfalls

Sample Answer

Approach (verified by execution)

Worked example (verified by execution)

Trade-offs and pitfalls

Sample Answer

Approach

Worked example

Trade-offs and pitfalls

Sample Answer

Approach

Worked example

Trade-offs and pitfalls

Sample Answer

Approach

Worked example

Trade-offs and pitfalls

Unlock Full Question Bank