The Days That Line Up

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

General Assembly

Problem

For each pipeline run that finished successfully after January 1, 2026, match it to every batch job that started on the same calendar day. Report each pipeline name paired with a batch job priority, and for that pairing give the shortest, average, and longest pipeline duration in seconds.

No key ties these tables but the calendar. Read it right.

Tables

data_pipes

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

batch_jobs

Columns: job_id INTEGER, job_name TEXT, status TEXT, rows_done INTEGER, started TEXT, ended TEXT, priority INTEGER, retries INTEGER

Expected output

Worked solution and explanation

The mental model

Infra teams ask this to see if you spot a missing foreign key and name it. data_pipes and batch_jobs share no real relationship, just a calendar day. They want to hear you say 'there is no FK here, I am inferring a same-day join' out loud, then handle the many-to-many cartesian fanout without misreading your aggregates. Silent assumption equals silent rejection.

What the aggregates actually measure

Here is the part that catches everyone. This is not a foreign-key join; it is a same-calendar-day match, so it is many-to-many. If one successful agg_daily run on a given day matches three batch jobs at priority 2, the join emits three copies of that run's dur_secs, all tagged priority 2. MIN and MAX shrug this off (a duplicated value never changes the extremes), but AVG does NOT: when several runs of the same pipeline match different numbers of jobs, each run's duration gets weighted by its own fanout, so the average is a fanout-weighted mean, not the plain average of the runs. SUM(dur_secs) or COUNT(*) would be inflated the same way. Being able to explain WHY the average is what it is, rather than just trusting it, is the senior signal here.

The solution

SELECT
    dp.pipe_name,
    bj.priority,
    MIN(dp.dur_secs) AS min_duration,
    AVG(dp.dur_secs) AS avg_duration,
    MAX(dp.dur_secs) AS max_duration
FROM data_pipes dp
INNER JOIN batch_jobs bj
    ON SUBSTR(dp.start_at, 1, 10) = SUBSTR(bj.started, 1, 10)
WHERE dp.start_at > '2026-01-01'
  AND LOWER(dp.status) = 'success'
GROUP BY dp.pipe_name, bj.priority

Common follow-up questions

• Two pipes ran successfully on the same Tuesday. Five batch jobs ran that Tuesday: 3 at priority HIGH, 2 at LOW. How many rows does the join produce, and how many output rows after GROUP BY? (Tests cardinality understanding. Answer: 2 pipes x 5 jobs = 10 rows from the join, then grouped to 2 (`pipe_name`) x 2 (priority) = up to 4 output rows. Each pipe's `dur_secs` appears in the aggregate as many times as jobs at that priority that day.)
• How would you sanity-check whether the same-day join is inflating any of your aggregates? (Tests the candidate's debugging instinct. Right answer: try SUM(`dur_secs`) too. If SUM is wildly off but you expected a clean per-run total, you are catching cardinality inflation from the fanout. The fix is usually deduplicating the right side before the join.)
• Production runs in three timezones. A pipeline starts at 11:55pm PT and a batch job starts at 12:05am ET. Do they match? Should they? (Tests timezone awareness. Real-world answer: store timestamps in UTC, do the SUBSTR there, and surface the user's local date in the presentation layer. Doing date math in mixed timezones causes off-by-one bugs that are nearly impossible to debug.)
• Your VP says this query is slow and asks you to make it 10x faster without scaling hardware. What do you change? (Tests practical schema design. Best answer: add a `run_id` or `schedule_id` linking pipes and jobs at the application layer. Failing that, materialize a generated column `run_date` on both tables and index it. SUBSTR-on-string is the worst option in production.)