Highest Throughput Pipelines

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

City and County of San Francisco

Problem

How many rows did the biggest single run in data_pipes put out in each quarter of 2025? A capacity analyst is sizing hardware against those quarterly peaks, and every run counts whatever its status.

The pipes that carry the most water.

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 calendar-bucket peak dressed up as capacity planning. Anyone writes MAX(rows_out). The bucket is what separates candidates: start_at is a timestamp, and the quarter has to be built from it, inside a one-year fence. Format the run's own month instead of truncating to the quarter and every quarter splits into three rows. Drop the fence and you get every quarter the table has ever seen, each one looking perfectly plausible.

Build it

Step 1: Fence the runs to 2025

EXTRACT(YEAR FROM start_at) = 2025 keeps only the year the analyst is sizing from. It is a row predicate, so it lives in WHERE and runs before anything is bucketed. Without it, the runs from every other year in data_pipes land in the output too.

Step 2: Snap each run to its quarter

DATE_TRUNC('quarter', start_at) maps a run on May 17 to April 1. Every run in April, May and June lands on the same value, which is exactly the bucket you want in the GROUP BY. Group on the truncated timestamp, not on a string built from the raw one.

Step 3: Take the peak, then label the bucket

MAX(rows_out) is the single biggest run in the bucket, and it skips NULL, so a run with no logged output never drags a peak down. TO_CHAR(DATE_TRUNC('quarter', start_at), 'YYYY-MM') turns the quarter start into the label the preview shows; it is a function of the grouped bucket, so it is legal in the SELECT. Leave status alone: a failed run still hit the system.

The solution

SELECT
    TO_CHAR(DATE_TRUNC('quarter', start_at), 'YYYY-MM') AS quarter_start,
    MAX(rows_out) AS peak_rows_out
FROM data_pipes
WHERE EXTRACT(YEAR FROM start_at) = 2025
GROUP BY DATE_TRUNC('quarter', start_at)

Format, then group

Grouping on TO_CHAR(start_at, 'YYYY-MM') buckets by the run's own month. The output looks like quarter labels, but there are up to twelve of them and each peak covers a third of a quarter.

Truncate, then format

DATE_TRUNC('quarter', start_at) collapses the three months first, and TO_CHAR only dresses the result. Four buckets, each holding every run of its quarter.

SELECT
    TO_CHAR(start_at, 'YYYY-MM') AS quarter_start,
    MAX(rows_out) AS peak_rows_out
FROM data_pipes
WHERE EXTRACT(YEAR FROM start_at) = 2025
GROUP BY TO_CHAR(start_at, 'YYYY-MM')

Common follow-up questions

• Product wants to know which pipeline produced each quarter's peak. How does the query change? (Tests knowing when an aggregate is not enough: `ROW_NUMBER() OVER (PARTITION BY DATE_TRUNC('quarter', start_at) ORDER BY rows_out DESC)` keeps the peak run with its `pipe_name`.)
• `start_at` is about 1% `NULL`. What happens to those runs under your filter? (Tests null handling: `EXTRACT` of a `NULL` is `NULL`, the comparison is not true, and the run drops out, which is right for a run that cannot be placed in a quarter.)
• Should only successful runs count toward the peak? (Tests product reasoning: a failed run may have written partial output, and the casing mix means any filter needs `LOWER(status)`.)