Longest Running Pipeline

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

An on-call engineer chasing a slow batch window is going through data_pipes, which records every pipeline run along with how long it took. Return the name of the one pipeline behind the longest recorded run.

One pipeline outlasted them all.

Tables

data_pipes

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

data_pipes: 10 of 200 rows
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 1 of 1 rows
pipe_name
dbt_build

Worked solution and explanation

Why this problem exists in real interviews

Against the data_pipes table, top-N selection and running total on pipe_name values is the key operation. Interviewers favor this in mid-level screens because it exposes whether candidates handle ties, NULLs, and ordering edge cases correctly.

Break down the requirements

Step 1: Read from `data_pipes`

The query targets data_pipes with 7 columns. Identify which columns are needed for the output.

Step 2: Order and limit the output

Sort by the target metric and apply LIMIT to return the requested number of rows. Ensure the sort is deterministic to produce reproducible results.

Step 3: Return the result set

Select the required columns with any necessary aliasing or formatting.

The solution

Direct sort for longest pipeline

SELECT pipe_name
FROM data_pipes
ORDER BY dur_secs DESC
LIMIT 1

Common follow-up questions

What happens to your result if `data_pipes`.start_at contains NULLs for some rows? (Tests whether the candidate accounts for NULL behavior in aggregates and comparisons on `start_at`.)
How would you verify that your aggregation on `data_pipes`.pipe_id is not double-counting due to duplicate rows? (Tests data quality awareness and deduplication strategies.)
If `data_pipes` grows to hundreds of millions of rows, how would you partition or index on `start_at` to maintain performance? (Tests partitioning strategy for time-series data in `start_at`.)