# Pipeline Run History > The lineage trail. Canonical URL: 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. ## Tables ### data_pipes - pipe_id INTEGER - pipe_name TEXT - status TEXT - rows_in INTEGER - rows_out INTEGER - start_at TEXT - dur_secs INTEGER Sample rows (10 of 200): | 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 First 5 of 10 rows: | 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. > **Truncate, then distinct** > > Cast each timestamp down to its year-month with `strftime('%Y-%m', start_at)`, then `COUNT(DISTINCT ...)` over that. Every run in `'2026-09'` becomes the same bucket, so a busy month is counted once. This is the whole problem in one expression: truncate, then distinct. --- ### 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. **Distinct active months per pipeline** ```sql 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_name ``` > **Distinct on the wrong column** > > The tempting shortcut is `COUNT(DISTINCT start_at)`, reasoning that distinct start times are distinct runs. On this data every `start_at` is already unique, so that expression silently equals `COUNT(*)`: you get the raw run count back and never notice the months requirement went unmet. The distinct has to sit on the month bucket, not the raw timestamp. > **Bucketing the timestamp is the tell** > > The tell is whether you truncate `start_at` before counting. A candidate who writes `COUNT(*)` or `COUNT(DISTINCT start_at)` has not registered that a timestamp needs bucketing to answer a 'per month' question. The strong signal is reaching for `strftime`/`date_trunc` unprompted and putting the DISTINCT on the bucket. > **In production** > > With `data_pipes` this is a single grouped scan, cheap at any realistic volume. At production scale, active-months-per-pipeline is the kind of metric you would precompute into a rollup keyed by (`pipe_name`, month) rather than recompute the distinct over raw run history on every dashboard load. --- ## Common 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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/pipeline_run_history) - [SQL Interview Questions](https://datadriven.io/sql-interview-questions) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.