SQL Interview Questions for Data Engineering — Joins, GROUP BY, Window Functions, CTEs

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.

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

Drill SQL join problems on KnowEaseAI →

2. SQL Aggregations and GROUP BY

"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.

| aggregate | result | |-----------|--------| | COUNT(*) | 3 | | COUNT(amount) | 2 | | SUM(amount) | 40 | | AVG(amount) | 20 |

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
Putting HAVING before GROUP BY — syntax error

Drill GROUP BY and HAVING problems on KnowEaseAI →

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

Drill window function problems on KnowEaseAI →

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

Drill CTE and subquery problems on KnowEaseAI →

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.

KnowEaseAI

Practice SQL on real PostgreSQL with AI-graded feedback

Every SQL problem on KnowEaseAI runs against real PostgreSQL — not a mock. Attempts are tracked across joins, aggregations, window functions, and CTEs, and the gap analysis surfaces exactly where your fluency breaks down.

Practice SQL on KnowEaseAI →