SQL · medium

Write a query to find gaps in a daily sequence — dates with no rows in a metrics table.

Asked in Analytics Engineer interviews, in the SQL round.

Short answer

Generate a full date spine (a calendar table or generate_series) and LEFT JOIN the metrics table onto it; the days where the metric side is NULL are the missing ones. Never infer no-data from absent rows without a spine — you cannot otherwise tell zero from missing.

How to answer it

You cannot find missing rows by looking at the rows that exist. Build the full list of days first, then see which ones the table cannot match.

WITH spine AS (
  SELECT generate_series(
    DATE '2026-01-01', DATE '2026-08-27', INTERVAL '1 day'
  )::date AS day
)
SELECT s.day AS missing_day
FROM spine s
LEFT JOIN daily_metrics m ON m.metric_date = s.day
WHERE m.metric_date IS NULL
ORDER BY s.day;

Take the bounds from the table itself (MIN and MAX of metric_date) when the range is not given. Databases without generate_series use a calendar table or a recursive CTE for the spine; most warehouses keep a calendar table anyway.

The reason this matters beyond the puzzle: a chart built from the metrics table alone draws a straight line across a missing day, so nobody sees the gap. With a spine, a missing day reads as NULL, which is visibly different from zero.

• What they are checking: that you know absence is not a value, and reach for a date spine.
• Common mistake: LAG(metric_date) and looking for differences greater than one day. It finds where gaps start but not which days are missing, and misses a gap at either end.

Related questions

• Write a query to find the second highest salary from an Employees table.
• Given orders(user_id, order_date, amount), compute 7-day rolling revenue per day.
• Explain the difference between INNER, LEFT, and FULL OUTER JOIN.
• Calculate the median order value using SQL.
• Find the top 3 products by revenue within each category.
• Compute month-over-month user retention from an events table.

Practice this for real

• Check your resume against a Analytics Engineer job description — free, no account needed.
• Every data interview question, by role and topic
• Scored AI mock interviews — two free, no card