Busiest Pipeline Month

A medium SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

medium

Seniority

L3

Asked in

ESPN

Problem

Each row of data_pipes is one pipeline execution with its start_at timestamp and, when the run's duration was captured, its dur_secs. With every year pooled together, which calendar month had the most executions with a captured duration, and how many did it have?

One month, more pipeline runs than any other.

Tables

data_pipes

Columns: pipe_id INTEGER, pipe_name TEXT, status TEXT, rows_in INTEGER, rows_out INTEGER, start_at TEXT, dur_secs INTEGER

Expected output

Worked solution and explanation

What this really is

This is a month-of-year tally with one quiet filter, and the filter is where candidates split. Anyone can pull the month out of start_at and count rows. The real question is which column you guard. start_at is filled on every row of data_pipes, while dur_secs is missing on 40 of the 200 runs. Count every row and February comes back with 22. Count only the runs with a captured duration and it is 17. The month is the same but the number is wrong, and the grade compares the number.

Step 1: Read the grain off the preview

The preview shows '02', not '2026-02'. That means month of the year with every year pooled, so STRFTIME('%m', start_at) is the key. If you reach for '%Y-%m', February of one year lands in a different bucket from February of the next, and you have answered a question nobody asked.

Step 2: Drop the untimed runs before counting

Only runs whose duration was captured count, so WHERE dur_secs IS NOT NULL runs first and those rows never reach a bucket. Put the guard on dur_secs, the column that actually has gaps. start_at has none on this table.

Step 3: Count executions, not pipelines

Each row is one execution, so the metric is COUNT(*) per month. COUNT(DISTINCT pipe_name) collapses to at most 10 per month, because there are only ten pipelines. SUM(rows_in) measures data volume, not runs.

Step 4: Keep only the busiest month

ORDER BY pipeline_runs DESC LIMIT 1 keeps the top bucket. On this data February leads at 17 and the next months sit at 16, so the cut never falls inside a tie.

The solution

SELECT
    STRFTIME('%m', start_at) AS month,
    COUNT(*) AS pipeline_runs
FROM data_pipes
WHERE dur_secs IS NOT NULL
GROUP BY STRFTIME('%m', start_at)
ORDER BY pipeline_runs DESC
LIMIT 1

Count every row

With no filter, COUNT(*) puts February at 22. The 5 February runs with no dur_secs are counted as if they happened like the rest.

Count timed runs

With WHERE dur_secs IS NOT NULL, those 5 rows are gone before the grouping and February reports 17, which is the number the grade expects.

Common follow-up questions

• Two months tie for first next quarter. How do you return both instead of an arbitrary one? (Tests tie handling: rank the monthly totals with `RANK()` and keep rank 1, instead of using `LIMIT 1`.)
• The team now wants the month that moves the most data. What changes? (Swap `COUNT(*)` for `SUM(rows_in)` and decide what a missing `rows_in` should mean.)
• Only successful runs should count. Where does that filter go, given `status` holds both 'success' and 'Success'? (Tests normalizing case with `LOWER(status)` before comparing.)
• `start_at` is stored in UTC but the team plans in Pacific time. Which runs change month? (Tests whether the timezone shift happens before the month is extracted.)
• `data_pipes` grows to billions of rows. What dominates the cost? (The scan. The aggregate never holds more than 12 groups.)