What to expect in a data engineer SQL interview round

A 45-minute data engineer SQL screen is not evenly split. Aggregation and filtering take the largest slice, the first questions and the ones you clear to reach anything else. Joins are next, and the failure there is a fan-out that returns a wrong number rather than an error. Window functions arrive last, in one or two questions, and they are the tier that decides whether the write-up says senior. Below: that split in detail, one streak question worked from a stakeholder's Slack message to a query that runs, and the capture sequence for when the schema and the question sit on different scrolls.

What the 45 minutes is actually made of

No public dataset breaks a SQL round into minutes, so treat this as a planning split, not a measurement. It matches the shape of the format: questions get harder in order, and the last one exists to separate candidates who all passed the first three.

The traps that live in the biggest tier

People revise window functions and then lose the round on tier one. The recurring traps, in rough order of how often they cost someone an offer:

• Counting after a fan-out. A LEFT JOIN from customers to orders repeats each customer once per order. COUNT(*) now counts orders, and SUM(c.credit_limit) adds the same limit five times. Aggregate the child table in a CTE first, or use COUNT(DISTINCT c.customer_id).
• COUNT(*) against COUNT(col) after a left join. The unmatched row still exists, with NULLs in it. COUNT(*) returns 1 for a customer with no orders, COUNT(o.order_id) returns 0. One of those answers "how many orders", and it is not the first one.
• NOT IN with a NULL in the subquery. It returns zero rows, silently, because the comparison is unknown rather than false. NOT EXISTS does not behave that way. This is a favourite follow-up precisely because it is so quiet.
• WHERE against HAVING. Date filters belong in WHERE, before grouping. A filter on an aggregate belongs in HAVING. Putting the date in HAVING works on some engines and scans far more than it needs to.
• Half-open date ranges. If the column is a timestamp, BETWEEN '2026-04-01' AND '2026-06-30' quietly drops everything after midnight on the last day. Write >= '2026-04-01' AND < '2026-07-01' and say why out loud.

One window function question, from Slack message to running query

Good interviewers phrase the last question the way a stakeholder would, because reading the ask is part of the job. It lands looking like this:

hey, for the retention deck: for each user, what was their longest run of consecutive days they opened the app in Q2? and how many users hit 7 days in a row?

The schema is two tables and a dialect note:

-- PostgreSQL 14
events(user_id BIGINT, event_ts TIMESTAMPTZ, event_name TEXT)
users (user_id BIGINT, signup_date DATE, plan TEXT)

Say the shape of the answer before you type. This is a gaps-and-islands problem: find runs of consecutive values, measure each run, take the longest per user. Three moves get you there.

with days as (
  select distinct user_id, event_ts::date as d
  from events
  where event_name = 'app_open'
    and event_ts >= date '2026-04-01'
    and event_ts <  date '2026-07-01'
),
islands as (
  select user_id, d,
         d - (row_number() over (partition by user_id order by d))::int as grp
  from days
),
streaks as (
  select user_id, grp, count(*) as streak_len,
         min(d) as started, max(d) as ended
  from islands
  group by user_id, grp
)
select user_id, max(streak_len) as longest_streak
from streaks
group by user_id
order by longest_streak desc;

The second question is one line against the same streaks CTE, and it needs no extra grouping because any island of seven or more qualifies the user:

select count(distinct user_id) from streaks where streak_len >= 7;

Trace three days out loud while you write it. If a user's days are 4, 5 and 9 April, the row numbers are 1, 2, 3 and the differences are 3 Apr, 3 Apr, 6 Apr. Two rows in the first island, one in the second, longest streak two. That thirty-second trace convinces an interviewer you derived the trick rather than memorised it.

The follow-ups this question always gets

• "Why ROW_NUMBER and not RANK?" Ties. RANK leaves holes after a tie, which breaks the arithmetic. The DISTINCT removes duplicate dates so it does not bite here, but say that you know the choice is not arbitrary.
• "Does it break across month or year boundaries?" No, and that is the point of subtracting from the date rather than from a day-of-month. To a date type, 30 April and 1 May are one day apart.
• "Users are in different timezones." event_ts::date resolves in the session timezone. If each user carries a timezone column, cast with (event_ts at time zone u.tz)::date, which pulls the join earlier in the query.
• "Now do it per week." Truncate to the week, DISTINCT those, and subtract row_number * interval '7 days' instead of a plain integer.
• "Four hundred million rows. Will this run?" The DISTINCT is the expensive step. A composite index on (user_id, event_ts) where the filter is selective, or a pre-aggregated daily activity table, is the honest answer.

When the schema block and the question are on different scrolls

SQL assessments almost never fit one screen. The DDL or ERD sits at the top, a banner underneath says which engine the queries run against, and the question, its sub-parts and an expected-output table sit below the fold. Crop the question alone and any AI answer invents login_date when the column is event_ts, joins on id when the key is user_id, and picks a dialect at random. The schema shot matters far more here than on an algorithm prompt, where the problem is self-contained.

OfferPilot AI's Code Copilot takes several shots in one session before it sends anything. Expand any collapsed table definitions first, because OCR reads pixels and not the DOM.

Where this goes wrong, starting with the dialect

A generated query can be logically perfect and still refuse to run on the grader's engine, because window and aggregate syntax diverges hard between databases. Check any answer against the dialect banner before you paste it.

The wider limits deserve naming plainly. A SQL round is graded on the narration as much as the query, and the interviewer will change the spec halfway through your answer, which is exactly where a query you did not derive falls apart. The overlay's capture exclusion works at the operating system level, so it is absent from Zoom, Teams, Meet, OBS and screenshots while staying visible on your own monitor, but that covers screen sharing and screen recording only. It does nothing about a webcam, a lockdown browser, eye tracking, or a proctor watching in the room. The app is Windows 10 and 11 only. OCR can misread a column name in a low-contrast ERD image, so read the identifiers back against the schema. And the rules belong to the company: some take-homes allow assistance and some forbid it, so read the instructions and ask when they are ambiguous.

Practise the weighting, not the topic list

Work the tiers in the order the round uses them. Spend most of your reps on aggregation with joins underneath it, because that is where offers are lost quietly, then drill four window patterns until they are automatic: ROW_NUMBER for deduplication, LAG for period-over-period, SUM ... OVER with an explicit frame for running totals, and the row-number subtraction above. Take a real assessment page, run the three-drag capture on it, read the intuition before the code, then rebuild the query in a blank editor while narrating it. For the modelling conversation at the end, the company Q&A generator builds a bank around the pipeline stack the role uses, and the question banks cover the rest. The free tier is 5 minutes a day with no card; paid plans start at $39 a month and meter listening time only.

Related guides

• AI Interview Copilot for New Grad SWE: Which Rounds It Helps
• ML Engineer vs Data Scientist Interview: Every Round Compared
• API Design Interview vs System Design Interview: The Tells
• SRE Interview Linux Troubleshooting: Triage Order to Narrate
• Data Scientist Product Sense Interview Questions: 4-Move Answer