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

28 practiced

Write a query that reconciles a source table against its downstream warehouse counterpart on a daily basis: for each day, compute the row counts (and/or sums) on both sides, the absolute and percentage difference, and flag any day where the difference exceeds a tolerance (for example greater than 1%). Discuss how you would schedule this, alert on it, and store the reconciliation history for trend analysis.

Sample Answer

HardTechnical

26 practiced

You suspect a cumulative divergence has been building between two systems tracking the same numbers over time. Write SQL that computes the running (cumulative) difference day by day and finds the FIRST date at which the cumulative divergence crosses a given threshold (percentage or absolute), so you can narrow an investigation to a specific starting point rather than re-checking the whole history.

Sample Answer

Instead of re-scanning the whole history every time you suspect drift, track the CUMULATIVE difference day by day and find the first date where that running total crosses your tolerance, which pinpoints where an investigation should start rather than requiring you to eyeball a long time series. Approach (verified by execution) ```sql WITH diffs AS (SELECT d, source_amt, target_amt, source_amt - target_amt AS daily_diff FROM recon), cum AS (SELECT d, daily_diff, SUM(daily_diff) OVER (ORDER BY d ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_diff, SUM(source_amt) OVER (ORDER BY d ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_source FROM diffs) SELECT d, cum_diff, cum_source, ROUND(cum_diff * 100.0 / cum_source, 2) AS pct_cum_diff FROM cum WHERE cum_diff * 1.0 / cum_source > 0.02 ORDER BY d LIMIT 1; ``` Worked example (verified by execution) Over five days with small, growing daily discrepancies (0, 1, 2, 50, 100 out of roughly 1000 per day), the cumulative percentage difference crosses a 2% threshold for the first time on day five (3.06%), even though day four's own daily discrepancy (50) was already the visibly larger jump; `LIMIT 1` on the ordered result correctly returns exactly that first crossing date rather than every subsequent day that also exceeds the threshold. Trade-offs and pitfalls A cumulative running total is sensitive to the STARTING point of the window: if you run this over "all history," a genuinely resolved discrepancy from months ago can still show up as inflating today's cumulative percentage, even though the two systems have agreed perfectly every day since; consider resetting the cumulative sum at a meaningful reconciliation checkpoint (like the last date both systems were confirmed to agree) rather than always summing from the dawn of time. Once you have the first-crossing date, that becomes the natural starting point for a narrower, partition-level investigation into what changed on or just before that specific day.

MediumTechnical

31 practiced

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.

MediumTechnical

33 practiced

Write a query that verifies an aggregate invariant holds across related tables: for example, that an order's recorded total_amount equals the sum of its order_items.amount, or that the sum of hourly metric values for a day equals the recorded daily total. Return rows where the invariant is violated beyond a small floating-point tolerance, and discuss the considerations for doing this efficiently over large joins.

MediumTechnical

30 practiced

Given a table of daily per-entity metrics (for example, a metric value per product per day), write a query that finds entities with missing expected data points over a recent window (for example, the last 90 days), returning each entity and a count of its missing dates. State your assumptions about the expected date range and any business calendar effects.

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.