The Legacy Hunt
An easy SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.
- Domain
- SQL
- Difficulty
- easy
- Seniority
- L3
- Asked in
Problem
The platform team is retiring the legacy scheduler and needs its run calendar from before the May 1, 2026 migration. List every calendar day on which at least one pipeline started a run.
Old data. Still matters.
Tables
data_pipes
Columns: pipe_id INTEGER, pipe_name TEXT, status TEXT, rows_in INTEGER, rows_out INTEGER, start_at TEXT, dur_secs INTEGER
| pipe_id | pipe_name | status | rows_in | rows_out | start_at | dur_secs |
|---|---|---|---|---|---|---|
| 131 | etl_users | failed | 137 | 97 | 2026-02-02 01:00:00 | 28 |
| 162 | etl_events | running | 274 | 194 | 2026-03-03 02:00:00 | 51 |
| 193 | sync_crm | queued | 411 | 291 | 2026-04-04 03:00:00 | 74 |
| 224 | agg_daily | skipped | 548 | 388 | 2026-05-05 04:00:00 | 97 |
| 255 | agg_weekly | Success | 685 | 485 | 2026-06-06 05:00:00 | NULL |
| 286 | export_s3 | FAILED | 822 | 582 | 2026-07-07 06:00:00 | 143 |
| 317 | import_csv | success | NULL | 679 | 2026-08-08 07:00:00 | 166 |
| 348 | ml_train | failed | 1096 | 776 | 2026-09-09 08:00:00 | 189 |
| 379 | dbt_build | running | 1233 | NULL | 2026-01-10 09:00:00 | 212 |
| 410 | etl_orders | queued | 1370 | 970 | 2026-02-11 10:00:00 | NULL |
Expected output
| run_date |
|---|
| 2026-02-02 |
| 2026-03-03 |
| 2026-04-04 |
| 2026-01-10 |
| 2026-02-11 |
Worked solution and explanation
What this really is
Under the sunset framing this is a plain set-reduction: turn a one-row-per-run log into the set of calendar days that had activity, cut off at the migration date. Anyone can filter by date. What separates candidates is seeing that data_pipes stores one row per RUN, not per day, so a bare SELECT DATE(start_at) hands back the same day dozens of times, and that start_at is a TIMESTAMP: use <= against '2026-05-01' and every run at midnight on migration day leaks into a calendar that is supposed to end before it.
The two moves
Step 1: Collapse runs into days
Each row is a single run, and a busy pipeline fires many times a day. DATE(start_at) throws away the clock time so runs at 02:00 and 22:00 on the same day become one value, and DISTINCT keeps a single copy of each day. Skip it and the audit team gets tens of thousands of rows where they expected a tidy calendar.
Step 2: Cut strictly before the cutoff
start_at is a TIMESTAMP and the migration boundary is the date '2026-05-01'. Strict less-than keeps every run through the last instant of April 30 and drops the first instant of May 1 onward. <= '2026-05-01' would let midnight on migration day slip in; <= '2026-04-30' would silently drop all of April 30's runs. Strict-less-than against the start of the day after the window is the bug-free pattern.
The solution
SELECT DISTINCT DATE(start_at) AS run_date
FROM data_pipes
WHERE start_at < '2026-05-01';Common follow-up questions
- Return the days that had NO pipeline runs before the cutoff. What do you need besides this table? (Forces a generated calendar left-joined to the table: this query only knows the days that DID run, so the empty days need an external date spine.)
- Now count runs per day instead of just listing the days. Which clause changes? (DISTINCT gives way to GROUP BY plus COUNT(*). Tests whether the candidate reads DISTINCT as existence and GROUP BY as aggregation.)
- If `start_at` can be NULL, how does DATE(`start_at`) behave and do those rows appear? (DATE(NULL) is NULL and a NULL `start_at` fails the strict comparison, so those runs never reach the result. Good check of NULL semantics.)
- Extend this to distinct (pipeline, day) pairs so you can see which pipeline was active each day. (Multi-column DISTINCT over (`pipe_name`, DATE(`start_at`)); useful for spotting which pipeline was active on which legacy day.)