# Early 2026 Data Pipelines > Which pipelines ran before the year turned mid. Canonical URL: Domain: SQL · Difficulty: easy · Seniority: L3 ## Problem The data governance team is auditing which pipelines ran early in 2026. List the unique pipeline names whose runs started before July 1. ## Worked solution and explanation ### What they're actually testing A 'historical audit' sounds heavy, but strip the costume and this is a filtered projection: keep the runs that started before a cutoff, then hand back the pipeline names without repeats. Only two decisions carry weight here: where the timestamp boundary sits, and whether you remember to collapse duplicate names. Everything else in the table is bait. That noisy status column with 'failed', 'FAILED', 'Success' and 'success' all jumbled together is there to tempt you into filtering on something the question never asked for. > **The boundary is a timestamp, not a date** > > start_at is a full timestamp, so 'before July 1' means before midnight on July 1. Compare against '2026-07-01' with a strict less-than and every run on July 1, at any hour, falls outside the window. That is exactly why export_s3 at 2026-07-07 06:00:00 is out, while agg_weekly at 2026-06-06 stays in. ### Building it #### Step 1: Filter to the window WHERE start_at < '2026-07-01'. Because the timestamps are stored in ISO order (year, then month, then day), the comparison sorts correctly whether the engine reads it as text or as a timestamp. No cast, no date function, no EXTRACT needed. #### Step 2: Collapse to one name per pipeline SELECT DISTINCT pipe_name. Even though this sample happens to have unique names, the audit asks for names, not runs. A pipeline that ran three times before the cutoff must appear once, so DISTINCT is the contract you are promising, not a coincidence you are relying on. **Distinct pipelines that ran before July 1** ```sql SELECT DISTINCT pipe_name FROM data_pipes WHERE start_at < '2026-07-01'; ``` *Filter on the timestamp, then de-duplicate the projected name.* > **The two ways people miss this** > > First, using <= '2026-07-01' quietly pulls in any run stamped exactly at midnight on July 1, which is one day past the intended window. Second, reaching for the status column: nothing in the prompt scopes to failed or successful runs, so a WHERE on status drops rows that belong in the answer. **Over-engineered** WHERE CAST(start_at AS DATE) < '2026-07-01' AND status = 'success'. A cast that buys nothing on an ISO timestamp, plus a status filter the question never mentioned. It returns the wrong set and signals you did not read the ask. **What the prompt asks** WHERE start_at < '2026-07-01'. One predicate on the one column that matters, DISTINCT on the one column requested. Reads in two seconds and is exactly what was asked. > **The tell they're watching for** > > On an easy filter like this, seniority shows in two small moves: reaching for DISTINCT before anyone points out duplicate names could exist, and treating a timestamp boundary as an exclusive midnight cut rather than fuzzing it with a date cast. Candidates who ask 'is July 1 itself included?' out loud score higher than candidates who guess. > **In production this stays cheap** > > A B-tree index on start_at turns the WHERE into a range scan that touches only the early-year rows, and the DISTINCT dedups a small projected set. On a pipeline-runs table with tens of millions of rows, this is a bounded scan, not a full-table read. ## Common follow-up questions - Now return each pipeline's most recent run time within that window alongside the name. _(Moves from a flat DISTINCT to a grouped aggregate (MAX(start_at) GROUP BY pipe_name).)_ - The timestamps are stored in UTC but the audit is defined in US Pacific time. How does the cutoff change? _(Tests timezone awareness and whether the candidate shifts the boundary rather than the data.)_ - How would you also exclude pipelines whose only early-year runs were 'skipped'? _(Introduces a HAVING-style condition after grouping, distinct from a naive row filter.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/early_year_data_pipelines) - [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.