# The Days That Line Up > No key ties these tables but the calendar. Read it right. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L3 ## 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. ## 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 **Same-day calendar join, grouped by priority** ```sql 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 ``` > **DATE() vs SUBSTR** > > `SUBSTR(start_at, 1, 10)` extracts `YYYY-MM-DD` from the ISO-8601 timestamp prefix. `DATE(start_at)` does the same in SQLite. Either works; SUBSTR is portable to engines without a DATE function but breaks if your timestamp format is not ISO. **Prefer `DATE()` when available**, fall back to SUBSTR for raw text columns. > **Why the planner can't use indexes here** > > Both sides have a function on the join column, which kills index seeks. The planner will full-scan both tables, hash on the derived date, and join. With 80k pipes and 400k jobs that is fine. At 100x scale it is not: the right fix is materializing `start_date AS DATE(start_at)` as a generated column on each table and indexing it. Then the join becomes a plain equality on indexed columns. > **What interviewers actually score** > > Strong candidates ask three things up front. (1) 'Is there a real foreign key I am missing?' If yes, use it; same-day is a smell. (2) 'What does same-day mean for jobs that span midnight?' SUBSTR uses wall-clock date at the storage timezone, which can split a single logical run across two days. (3) 'If priority is on the job and duration is on the pipe, am I really measuring pipeline cost by job priority, or just correlating two unrelated things?' That last question is the senior-engineer move. > **Mixed-case status gotcha** > > The `status` column is mixed case: the same table carries 'success' and 'Success', 'failed' and 'FAILED'. 'Finished successfully' means every run that succeeded regardless of casing, so normalize before you compare: `LOWER(dp.status) = 'success'`. A bare `dp.status = 'success'` silently drops the capital-S 'Success' runs and quietly shifts your averages, since fewer runs of a pipeline feed the fanout-weighted mean. **Inspect the distinct values of any column you filter on before writing the WHERE clause** (`SELECT DISTINCT status FROM data_pipes`). --- ## 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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the_days_that_line_up) - [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.