Pipeline Run History
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
- ESPN
Problem
For each pipeline in data_pipes, show when it first ran, when it most recently ran, and in how many different months it was active.
The lineage trail.
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
| pipe_name | first_run | last_run | active_months |
|---|---|---|---|
| agg_daily | 2025-02-15 14:00:00 | 2026-09-05 20:00:00 | 15 |
| agg_weekly | 2026-01-18 21:00:00 | 2026-09-08 11:00:00 | 9 |
| dbt_build | 2025-01-22 01:00:00 | 2026-09-06 17:00:00 | 15 |
| etl_events | 2023-02-03 02:00:00 | 2026-09-07 14:00:00 | 15 |
| etl_orders | 2026-01-07 18:00:00 | 2026-09-07 18:00:00 | 9 |
Worked solution and explanation
What this problem is really asking
Under the pipeline-monitoring costume, this is a distinct-count over a truncated timestamp. The real question: across how many separate months was each pipeline active? Anyone can GROUP BY pipe_name and hang a COUNT(*) off it, but that counts every run, not months. The skill being probed is seeing that start_at is a timestamp, bucketing it to a month, and counting the DISTINCT buckets. Miss it and etl_orders reads as dozens of months active when it actually ran across only 9.
Why COUNT(*) lies here
Each row in data_pipes is one run, and every run carries its own start_at instant. So COUNT(*) and COUNT(DISTINCT start_at) are effectively the SAME number: the run count. Neither one answers 'how many months'. Two runs that both happened in '2026-09' are two rows but one active month, and the only way to collapse them is to truncate the timestamp to its year-month first, then dedup.
Building it
Step 1: Group by pipeline
Collapse the runs to one row per pipeline with GROUP BY pipe_name. Everything else is an aggregate over that pipeline's runs.
Step 2: Bound the run window
MIN(start_at) is the first run and MAX(start_at) is the most recent. These stay full timestamps: the run window is an instant, not a date.
Step 3: Count distinct active months
COUNT(DISTINCT strftime('%Y-%m', start_at)) gives the number of separate months the pipeline was active, folding repeated runs in a month into one.
SELECT
pipe_name,
MIN(start_at) AS first_run,
MAX(start_at) AS last_run,
COUNT(DISTINCT strftime('%Y-%m', start_at)) AS active_months
FROM data_pipes
GROUP BY pipe_nameCommon follow-up questions
- If `start_at` is `NULL` for some runs, how does that affect `active_months`? (Tests whether the candidate knows COUNT ignores NULLs and that a truncated NULL stays NULL, so runs with no `start_at` never form a bucket.)
- How would you find pipelines that had a month with no runs between their first and last active month? (Probes moving from distinct months to a contiguous-gap analysis, a harder time-series shape.)
- What changes in your query if the metric became distinct active weeks instead of months? (Tests generalizing the truncation granularity from month to week or day.)