SQL Interview Questions for Data Engineering — Joins, GROUP BY, Window Functions, CTEs
The four SQL primitives that show up in every data engineering interview: LEFT JOIN anti-joins for orphan detection, GROUP BY with HAVING for duplicates, ROW_NUMBER vs RANK vs DENSE_RANK for top-N ranking, and CTE composition with recursive CTEs — with worked examples and full traces.
After reading: You'll have fluent command of the four SQL primitives that appear in every DE interview loop — ready to write them cold on a whiteboard.
SQL interview questions for data engineering circle around the same four primitives in every loop, regardless of the company name on the JD: JOIN semantics with LEFT JOIN ... IS NULL for anti-joins, GROUP BY with HAVING for aggregate filters and duplicate detection, window functions like ROW_NUMBER, RANK, DENSE_RANK, LAG, and LEAD for ranking and lookback, and CTEs (including recursive CTEs) plus correlated subqueries for multi-step logic and top-N-per-group queries.
Whether the prompt is "find customers who never placed an order", "count duplicate emails", "second-highest salary", or "top 3 salaries per department", the same handful of mental models keeps showing up — interviewers grade fluency with these primitives over memorised syntax.
This guide walks four topic clusters end-to-end. Every example uses PostgreSQL-flavored syntax — the dialect that drives most live-coding environments at product-analytics companies and the bulk of public SQL interview corpora — and every solution ends with a concept-by-concept breakdown.
Top SQL Data Engineering Interview Topics
| # | Topic | Why it shows up |
|---|-------|-----------------|
| 1 | INNER JOIN vs LEFT JOIN and LEFT JOIN ... IS NULL anti-join | Combine rows without inflating cardinality; IS NULL is the canonical "find rows in A with no match in B" pattern |
| 2 | GROUP BY and HAVING for aggregates and duplicate detection | WHERE filters rows before grouping, HAVING filters groups after |
| 3 | Window functions — ROW_NUMBER vs RANK vs DENSE_RANK, LAG, LEAD | Per-partition ranking, top-N-per-group, running totals, lookback deltas without collapsing rows |
| 4 | CTEs, recursive CTEs, and correlated subqueries | WITH clauses for readable multi-step logic; recursive CTEs for sequences and hierarchies |
Beginner-friendly framing: SQL engines process roughly in this order — FROM / JOIN → WHERE → GROUP BY → aggregates → HAVING → window functions → SELECT → ORDER BY → LIMIT. When in doubt, ask: "Am I filtering one row at a time (WHERE) or a whole bucket after summing (HAVING)?" That single question resolves more than half of the parse errors candidates hit on a live screen.
1. SQL Joins — INNER, LEFT, and Anti-Joins
"Find customers who never placed an order" is the signature SQL join interview question — and the cleanest answer is not a NOT IN subquery or a NOT EXISTS correlated query but a LEFT JOIN with WHERE right.key IS NULL. The mental model: INNER JOIN keeps only matching pairs, LEFT JOIN keeps every left row and pads the right side with NULLs when there is no match, and filtering for right.id IS NULL after the LEFT JOIN isolates exactly the left rows that had no match — the anti-join.
💡
Tip
LEFT JOIN ... WHERE right.id IS NULL is generally as fast as or faster than NOT IN (subquery) because NOT IN returns NULL (not FALSE) for any NULL in the subquery and silently drops every outer row. State this gotcha out loud — interviewers grade the candidate who knows why NOT IN can return zero rows when the data has a single NULL.
INNER JOIN: keep only matching rows
The INNER JOIN invariant: a row from the left table is paired with a row from the right table iff the join predicate evaluates to TRUE; rows on either side with no match are discarded.
No padding — unmatched rows on either side are silently dropped
Cardinality risk — 1:N on the right inflates left rows; N:M is a Cartesian-by-key explosion
Result schema — every column from both tables (use aliases to disambiguate)
Worked example.customers(id, name) with rows (1, Alice), (2, Bob), (3, Carol) and orders(order_id, customer_id, amount) with rows (101, 1, 50), (102, 1, 30), (103, 2, 80). Carol has no row — INNER JOIN drops her.
SELECT c.name AS customer, o.order_id, o.amount
FROM customers c
INNER JOIN orders o
ON o.customer_id = c.id;
Rule of thumb: reach for INNER JOIN when the question is "rows where both sides exist"; it is the smallest, fastest, most common join.
LEFT JOIN: keep every left row, pad the right with NULL
The LEFT JOIN invariant: every row from the left table appears in the output; if the join predicate matches at least one right row, the right columns are filled in; otherwise the right columns are NULL.
All left rows preserved — even unmatched ones
Right columns are NULL when there is no match — the key signal for the anti-join trick
Result has at least |left| rows and at most |left| × max_right_match rows
SELECT c.name AS customer, o.order_id, o.amount
FROM customers c
LEFT JOIN orders o
ON o.customer_id = c.id;
Carol appears with NULL, NULL in the order columns — she stays.
Rule of thumb: LEFT JOIN is the right answer whenever the question asks for "every X, with Y when it exists" — a churn report, a coverage report, a left-padded join for downstream pipelines.
LEFT JOIN ... IS NULL anti-join: rows with no match
The anti-join invariant: a LEFT JOIN followed by WHERE right.key IS NULL keeps exactly the left rows for which no right row matched.
SELECT c.name AS customer
FROM customers c
LEFT JOIN orders o
ON o.customer_id = c.id
WHERE o.order_id IS NULL;
Common beginner mistakes with JOINs:
Filtering the right table inside WHERE after a LEFT JOIN — silently turns the LEFT JOIN back into an INNER JOIN because NULL > 0 is NULL
Using NOT IN (subquery) when the subquery can return NULL — drops every outer row
Forgetting to alias both sides — id is ambiguous when both tables have it
Using LEFT JOIN when INNER JOIN is correct — leaves spurious NULL rows
"Find duplicate emails in the users table" and "find the department with the highest average salary" are the two signature aggregation prompts — both reduce to GROUP BY + aggregate function + HAVING filter. The mental model: GROUP BY col collapses rows that share the same col value into a single output row; COUNT(), SUM(...), AVG(...), MIN(...), MAX(...) summarise each bucket.
🔑
Key point
Aggregate predicates belong in HAVING; row predicates belong in WHERE. When the question is "find duplicates", the canonical shape is SELECT key, COUNT(*) FROM t GROUP BY key HAVING COUNT(*) > 1.
COUNT, SUM, AVG, MIN, MAX — NULL-aware aggregates
COUNT(*) — every row in the bucket, regardless of NULLs
COUNT(col) — non-NULL values of col only
COUNT(DISTINCT col) — unique non-NULL values; essential after a JOIN that may have inflated rows
SUM / AVG — skip NULL values entirely; AVG is sum-of-non-null divided by count-of-non-null
Worked example. Three rows in one group: amount = 10, NULL, 30.
SELECT user_id,
COUNT(*) AS n_rows,
COUNT(amount) AS n_known,
SUM(amount) AS total,
AVG(amount) AS mean
FROM orders
GROUP BY user_id;
WHERE vs HAVING — row filter vs group filter
WHERE runs before GROUP BY and references raw row columns only
HAVING runs after grouping and can reference aggregate functions
Trying to use WHERE COUNT(*) > 1 is a parse error
SELECT department, AVG(salary) AS avg_salary
FROM employees
WHERE salary > 30000 -- row filter BEFORE grouping
GROUP BY department
HAVING AVG(salary) > 50000; -- group filter AFTER aggregation
HAVING COUNT(*) > 1 — the universal duplicate finder
SELECT email, COUNT(*) AS n_copies
FROM users
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY n_copies DESC, email;
Step-by-step trace for users with rows alice@ (×3), bob@ (×2), carol@ (×1):
GROUP BY email — three buckets
COUNT(*) — 3, 2, 1
HAVING COUNT(*) > 1 — drops carol
Output: alice (3), bob (2)
Common beginner mistakes with GROUP BY:
Writing WHERE COUNT(*) > 1 — parse error; WHERE cannot reference aggregates
Selecting a non-aggregated, non-GROUP BY column — strict SQL rejects this
Forgetting COUNT(DISTINCT user_id) after a JOIN that inflates rows
3. SQL Window Functions — ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD
"Find the second-highest salary" and "find the top 3 salaries per department" are the two signature window-function prompts — both reduce to DENSE_RANK() OVER (PARTITION BY ... ORDER BY ...) filtered by rank. A window function computes a value across a set of rows related to the current row, without collapsing them like GROUP BY does.
🔑
Key point
When the question is "second-highest salary", reach for DENSE_RANK over RANK — DENSE_RANK = 2 reliably means "second distinct salary" even when ties exist at the top, while RANK = 2 skips entirely if two rows tie for first.
ROW_NUMBER — unique sequential numbering per partition
ROW_NUMBER() OVER (PARTITION BY p ORDER BY o) assigns a unique integer 1, 2, 3, ... to every row inside each partition p, ordered by o. Use it when you need a unique sequence per group regardless of tie semantics — most often for deduplication.
SELECT department, name, salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC, name) AS rn
FROM employees;
Rule of thumb: ROW_NUMBER is the right tool for deduplication (WHERE rn = 1) and ordered streams. Reach for RANK or DENSE_RANK when ties must be honoured.
RANK vs DENSE_RANK — tie semantics
| function | tie result | example |
|----------|-----------|---------|
| ROW_NUMBER | never ties | 1, 2, 3, 4 |
| RANK | skips after ties | 1, 2, 2, 4 |
| DENSE_RANK | no skip | 1, 2, 2, 3 |
SELECT name, salary,
RANK() OVER (ORDER BY salary DESC) AS rnk,
DENSE_RANK() OVER (ORDER BY salary DESC) AS dr,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
FROM employees;
Interview answer pattern for "second-highest distinct salary":
SELECT MAX(salary) AS second_highest_salary
FROM (
SELECT salary,
DENSE_RANK() OVER (ORDER BY salary DESC) AS dr
FROM employees
) ranked
WHERE dr = 2;
MAX(salary) handles the "no second-highest" edge case by returning NULL when no rows match — cleaner than LIMIT 1 OFFSET 1 which errors on empty sets.
LAG and LEAD — lookback and lookahead
LAG(col) OVER (PARTITION BY p ORDER BY o) returns the value of col in the previous row (NULL for the first row). LEAD is symmetric forward. They power month-over-month deltas, retention cohorts, and sessionisation.
SELECT sales_date,
amount,
LAG(amount) OVER (ORDER BY sales_date) AS prev_amount,
amount - LAG(amount) OVER (ORDER BY sales_date) AS mom_delta
FROM sales;
Always PARTITION BY the entity when the table holds multiple series — otherwise the lookback wraps across entities.
Common beginner mistakes with window functions:
Using RANK when the question wants the Nth distinct value — RANK = 2 skips if two rows tie for first
Forgetting PARTITION BY for per-group ranking — produces a global ranking instead
Using WHERE rn = 2 directly without wrapping in a subquery — window functions cannot be referenced in WHERE of the same SELECT
Forgetting ORDER BY inside OVER — required for ROW_NUMBER, RANK, LAG, LEAD
4. CTEs, Recursive CTEs, and Correlated Subqueries
"Find the top 3 salaries per department" and "find employees earning above their department average" are the two signature CTE-and-subquery prompts. The mental model: a CTE (WITH name AS (SELECT ...)) names an intermediate result you reference like a table; a recursive CTE (WITH RECURSIVE) repeatedly evaluates a base case plus a recursive case; a correlated subquery is a subquery whose WHERE clause references the outer query's alias, re-evaluating per outer row.
💡
Tip
When the prompt is "top N per group", reach for the CTE-plus-DENSE_RANK pattern, not LIMIT N — LIMIT does not respect partitions and only works on the global stream.
WITH name AS (SELECT ...) — non-recursive CTEs for readability
WITH high_paying AS (
SELECT department
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000
)
SELECT e.name, e.salary, e.department
FROM employees e
JOIN high_paying h ON h.department = e.department;
Multiple CTEs can be chained in a single WITH clause separated by commas. If you find yourself nesting a subquery three levels deep, refactor to a CTE — the engine produces the same plan but human review takes a fraction of the time.
WITH RECURSIVE — recursive CTEs for sequences and hierarchies
A WITH RECURSIVE CTE has two parts joined by UNION ALL: an anchor query (evaluated once) and a recursive query (refers to the CTE itself, evaluated repeatedly until it returns no new rows).
WITH RECURSIVE nums(n) AS (
SELECT 1 -- anchor
UNION ALL
SELECT n + 1 FROM nums WHERE n < 5 -- recursive step
)
SELECT n FROM nums;
-- output: 1, 2, 3, 4, 5
Always include a termination predicate (WHERE n < N). A missing predicate produces an infinite loop that most planners kill with an out-of-memory error.
Top 3 salaries per department (the canonical interview solution):
WITH ranked AS (
SELECT department, name, salary,
DENSE_RANK() OVER (
PARTITION BY department
ORDER BY salary DESC
) AS dr
FROM employees
)
SELECT department, name, salary, dr
FROM ranked
WHERE dr <= 3
ORDER BY department, dr, name;
Why this works concept-by-concept:
CTE ranked — names the intermediate ranked result; the outer query filters it like a table
PARTITION BY department — restarts the rank at each department boundary; without this, the rank is global and wrong
DENSE_RANK over RANK — the spec wants the top three distinct salaries; RANK would skip after ties and miss the third if there is a two-way tie above it
WHERE dr <= 3 in the outer — window functions cannot be referenced in WHERE of the same SELECT; the CTE provides the materialised column
Correlated subqueries — per-row predicates against the same table
A correlated subquery's WHERE clause references a column of the outer query, so it re-evaluates for every outer row:
SELECT name, department, salary
FROM employees e
WHERE salary > (
SELECT AVG(salary)
FROM employees
WHERE department = e.department
);
Prefer a CTE + window function (AVG(salary) OVER (PARTITION BY department)) for performance on large tables. The correlated subquery is the clearer but slower form. Interviewers grade you on knowing both.
Common beginner mistakes with CTEs:
Forgetting WITH RECURSIVE for a self-referencing CTE — parse error
Missing the termination predicate in WITH RECURSIVE — infinite loop
Using LIMIT N for top-N-per-group — LIMIT is global; it does not respect partitions
Re-materialising the same CTE under different names — the planner may run it twice
Tips to Crack SQL Interviews for Data Engineering Roles
Master the four primitives — joins, aggregates, windows, CTEs
If you can write LEFT JOIN ... IS NULL for orphans, GROUP BY ... HAVING COUNT(*) > 1 for duplicates, DENSE_RANK() OVER (PARTITION BY ...) for top-N-per-group, and a WITH RECURSIVE CTE for sequence generation without thinking — you can pass most fresher and mid-level data-engineering SQL rounds. These four primitives compose into 80% of the questions you will see.
Know the order of evaluation
FROM / JOIN → WHERE (row filter) → GROUP BY → aggregates → HAVING (group filter) → window functions → SELECT → ORDER BY → LIMIT. State this order when asked — interviewers treat it as fundamental literacy. Window functions cannot be referenced in WHERE of the same SELECT — wrap them in a CTE or subquery first.
Pick DENSE_RANK for "Nth distinct"; pick ROW_NUMBER for deduplication
The single most-graded ranking distinction: DENSE_RANK = N is the Nth distinct value; RANK = N is the Nth row in skip-aware ranking order; ROW_NUMBER = N is the Nth row in arbitrary order. State which one and why.
Use LEFT JOIN ... IS NULL over NOT IN for anti-joins
NOT IN (subquery) returns zero rows if the subquery contains a single NULL because x NOT IN (..., NULL, ...) evaluates to NULL. LEFT JOIN ... WHERE right.id IS NULL and NOT EXISTS (...) are both immune. State this gotcha out loud — production engineers who have been bitten by this once never write NOT IN again.
Practice on PostgreSQL
Most live-coding environments and SQL interview corpora use PostgreSQL syntax. Drill EXTRACT(...), INTERVAL '1 month', DATE_TRUNC, ::DATE casting, and COALESCE until they are reflexive.
Always add a tiebreaker to ORDER BY
Window functions, LIMIT N, and "top result" queries require an ORDER BY with a deterministic tiebreaker (e.g., ORDER BY salary DESC, name). Without one, two runs of the same query can return different rows — silently wrong in production and visibly wrong in an interview if the reviewer's reference answer locks an ordering.
On KnowEase.AI, every SQL practice problem is graded against real PostgreSQL — not a mock. Your attempts are tracked across the four primitive clusters above, and the dimension scoring engine surfaces exactly where your fluency is weakest: joins, aggregations, window functions, or CTEs. Start with the SQL coding practice bank — it maps directly to the four topic clusters in this guide.
KnowEase.AI gives you AI-graded HLD practice, structured learning checks with dimension scoring, and a personalised roadmap. Start free — no credit card needed.