Which Days Are Missing?

The interview question: “A file should land for every day. Some never arrived. Which days do we need to backfill?” Each day's file is a partition of the data, and to backfill a day means to load it late, once the file is found. A missing day doesn't make anything fail: its reports just show no sales, and nobody notices for weeks.

The trap is to ask the data you loaded, for example by counting orders per day and looking for a 0. A day that never loaded has no rows, so it has no group and never shows up. Here the first and last loaded days are 05-01 and 05-07, so the range looks whole, but only 5 of the 7 days are there.

So you build the list of days that should exist, a calendar, with generate_series(start, end, step). It makes one row per value, with the end included, in a column named generate_series. A DuckDB query may start with FROM, and then it keeps every column:

FROM generate_series(10, 40, 10)   →   10, 20, 30, 40

Stepping dates by INTERVAL 1 DAY gives timestamps such as 2026-05-01 00:00:00, so cast them to DATE. Then an anti-join keeps the rows of one table that have no match in the other. A LEFT JOIN from the calendar to the loaded days keeps every calendar day and gives the missing ones NULL on the loaded side, and WHERE … IS NULL keeps just those rows. The same move finds products that never sold.

A hidden step copied 5 of the 7 daily files into the folder landing/. The starting code reads them with filename = true and keeps one row per file in loaded_days: parse_filename(filename, true) turns landing/2026-05-04.csv into 2026-05-04, which is then cast to a DATE. The check prints calendar, then your query:

day
2026-05-01
2026-05-02
2026-05-03
2026-05-04
2026-05-05
2026-05-06
2026-05-07
(7 rows)
missing_day
2026-05-03
2026-05-06
(2 rows)

Your task: make calendar hold one DATE named day for each day from 2026-05-01 to 2026-05-07. Then list the calendar days that no file loaded, as missing_day.

Assignment
Follow the Coding Tutorial to build the calendar with generate_series, then anti-join loaded_days to find the days to backfill.

Hint
generate_series includes its end. Join from calendar c to loaded_days l with a LEFT JOIN, so no calendar day is lost. Then keep the rows where l.day, the loaded side, is NULL.