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.

Sample data

These tables are loaded into an in-browser engine on this page, so you can run your own answer against them. No account needed.

daily_metrics

How to answer it

Definition

Find the days that are missing. A table cannot report rows it does not have, so the missing days must come from somewhere else: a generated series of every date in the range, left joined to the data.

Example

WITH spine AS (
  SELECT generate_series(DATE '2025-01-01', DATE '2025-03-31', INTERVAL '1 day')::date AS day
)
SELECT s.day
FROM   spine s
LEFT   JOIN metrics m ON m.metric_date = s.day
WHERE  m.metric_date IS NULL
ORDER BY s.day;

Output

    day
------------
 2025-01-19
 2025-02-08
 2025-02-09

Without generate_series

Most warehouses keep a calendar dimension for exactly this. Where neither exists, build the spine from the data's own range and a number series.

WITH bounds AS (SELECT MIN(metric_date) AS lo, MAX(metric_date) AS hi FROM metrics)
SELECT lo + n AS day
FROM   bounds
CROSS  JOIN (SELECT ROW_NUMBER() OVER () - 1 AS n FROM calendar LIMIT 1000) nums
WHERE  lo + n <= hi;

Key points

• The spine is the whole answer. Without it, missing days are invisible by definition.
• Choose the range deliberately. Anchoring to MIN and MAX of the data cannot detect a gap at either end.
• LEFT JOIN plus IS NULL is the anti-join idiom.
• A row that exists but is NULL or zero is a different problem from a row that is absent. Say which you are looking for.
• Per-group gaps need the spine crossed with the groups: spine CROSS JOIN (SELECT DISTINCT region ...).

FAQ

• How do I get runs of missing days rather than single dates? Apply gaps and islands to the missing days.
• Should this be an alert? Usually yes. A freshness check on the last expected day catches it earlier than a report does.
• What about weekends and holidays? Filter the spine to expected days, or every Saturday looks like an outage.

The 30-second answer

I would generate a date spine covering the range, either with generate_series or the warehouse's calendar table, then left join the metrics table onto it and keep the rows where the metrics side is NULL.

The reason a spine is unavoidable is that the table cannot tell you about rows it does not contain, so the days have to be manufactured from somewhere else. Two things I would add: I would pick the range deliberately rather than from MIN and MAX of the data, because that can never find a gap at the start or the end, and I would filter the spine to days we actually expect data, otherwise every weekend is a false alarm.

In the interview

• 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.

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