SQL data engineering interview problem. Difficulty: advanced. Pattern: Date Functions. About 18 minutes. Part of the Pro drill bank.
Generate every date from 2024-01-01 through 2024-01-08 inclusive. Return dates that do not appear in transactions.transaction_date as missing_date. Order by missing_date.
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
Interview-style drill: Generate 2024-01-01..2024-01-08 and find dates with no transactions.
Generate every date from 2024-01-01 through 2024-01-08 inclusive. Return dates that do not appear in `transactions.transaction_date` as `missing_date`. Order by `missing_date`.