Missing dates in early January 2024

SQL data engineering interview problem. Difficulty: advanced. Pattern: Date Functions. About 18 minutes. Part of the Pro drill bank.

A data quality check wants to know on which days there were no transactions. Days with no data do not show up in a normal query, because there is no row to show. To find them, you first need a list of all the days, and then see which ones are missing from the table. Generate every date from 2024-01-01 to 2024-01-08, both included. Return the dates that do not appear in transactions.transaction_date, in a column called missing_date. Sort by missing_date.

Requirements

• Create a list of all dates from 2024-01-01 to 2024-01-08.
• Return the dates that have no row in transactions.
• Name the column missing_date.
• Sort by missing_date.

Constraints

• The table is transactions.
• Generate the days with generate_series (date, date, 1-day interval) in this database.
• The series must stop at January 8, not run for the whole year.

Examples

Input: transactions (Jan 1-8 window) transaction_id | transaction_date | amount 1 | 2024-01-01 | 100 2 | 2024-01-02 | 50 3 | 2024-01-04 | 75 4 | 2024-01-07 | 20 5 | 2024-01-08 | 30 Output: missing_date | 2024-01-03 | 2024-01-05 | 2024-01-06 | Why this passes: Calendar fill then anti-join reveals the holes between 2024-01-01 and 2024-01-08.

Topics: lakebench, sql, generate_series, gaps.

More SQL interview questions · All interview problems · Learn data engineering