Joins and windows decide the SQL round

If SQL rounds keep sneaking up on you, you are not imagining it.

LeetCode's SQL 50 study plan packages 50 essential questions covering basic to intermediate SQL topics, and open-source interview prep repos like tech-interview-handbook (138k★, MIT) increasingly treat databases as a first-class interview track next to system design and low-level design. SQL shows up in backend, data, and growth-facing software engineering loops now — it is not an edge case.

The good news is that SQL interviews are more repetitive than they feel. The surface area is small. If you can recognize the query family early, most of the stress disappears.

What interviewers are actually testing

A solid SQL answer usually proves five things:

1. You can identify the right table grain before you write the query.
2. You know when to filter rows before aggregation and when to filter groups after aggregation.
3. You can join tables without accidentally multiplying rows.
4. You know when a window function is cleaner than a subquery.
5. You can explain edge cases like NULL, ties, and duplicate rows.

That is why SQL questions feel deceptively hard. The syntax is often short. The thinking is where people lose points.

DiagramSQL Interview Question Decision Tree

Copy source

Rendering diagram...

The five query families that keep repeating

1. Join plus filter questions

This is the family behind prompts like:

• employees earning more than their managers
• customers who never ordered
• users who signed up but never activated
• products that were viewed but not purchased

The main skill is joining at the right grain, then filtering on the joined result.

SQL

Copy

SELECT e.name AS employee FROM Employee e JOIN Employee m ON e.managerId = m.id WHERE e.salary > m.salary;

The interview talk-track is simple:

• "I need one employee row plus the matching manager row."
• "This is a self-join because both values live in the same table."
• "I filter after the join because the comparison needs columns from both aliases."

If you can say that clearly, the query feels much less magical.

2. Group by plus having questions

This family is about counts, sums, averages, and thresholds.

Typical versions:

• customers with at least 3 orders
• teams with more than 5 active engineers
• products with average rating above 4.5
• users whose total spend passed a limit

SQL

Copy

SELECT customer_id, COUNT(*) AS order_count FROM Orders WHERE order_status = 'completed' GROUP BY customer_id HAVING COUNT(*) >= 3;

The key distinction:

• WHERE filters rows before the grouping work happens
• HAVING filters groups after aggregation exists

This is one of the most common SQL interview mistakes. If you remember only one rule from this guide, remember that one.

3. Top-N-per-group and ranking questions

These are the questions that make window functions worth learning.

Common versions:

• top 3 salaries in each department
• each user's most recent event
• highest-grossing product in each category
• first order per customer

The CTE pattern (WITH ... AS) makes this readable:

SQL

Copy

WITH ranked_salaries AS ( SELECT department_id, employee_id, salary, DENSE_RANK() OVER ( PARTITION BY department_id ORDER BY salary DESC ) AS salary_rank FROM Employees ) SELECT department_id, employee_id, salary FROM ranked_salaries WHERE salary_rank <= 3;

What interviewers want to hear:

• "Partition by the group."
• "Order within that group."
• "Use ROW_NUMBER, RANK, or DENSE_RANK depending on tie behavior."

If the prompt says "top in each department" or "most recent per user," a window function should be one of your first thoughts.

4. Latest-row and dedupe questions

This looks similar to ranking, but the mental model is different: you are not really ranking for display. You are selecting the one row that should represent each entity.

SQL

Copy

WITH ranked_events AS ( SELECT user_id, status, updated_at, ROW_NUMBER() OVER ( PARTITION BY user_id ORDER BY updated_at DESC ) AS rn FROM AccountStatusHistory ) SELECT user_id, status, updated_at FROM ranked_events WHERE rn = 1;

This pattern repeats constantly in real product systems because raw event tables are append-only, while business questions usually want the latest state.

If a question says "current subscription," "latest device," or "most recent status," think dedupe by partition plus ordering.

5. Date and retention questions

These are the business-analytics flavored SQL questions that still show up in engineering loops:

• daily active users
• 7-day retention
• rolling revenue
• month-over-month growth
• users who returned the next day

SQL

Copy

SELECT DATE(order_created_at) AS order_day, COUNT(DISTINCT user_id) AS active_buyers FROM Orders WHERE order_created_at >= CURRENT_DATE - INTERVAL '30 day' GROUP BY DATE(order_created_at) ORDER BY order_day;

The exact date syntax changes by SQL dialect. That is fine. In interviews, say the intent first:

• "I need to bucket timestamps into days."
• "I need distinct users per day."
• "Then I compare one bucket with another."

That usually matters more than perfect vendor-specific syntax on the first pass.

The small set of real question shapes worth drilling

If you are short on time, drill shapes, not fifty unrelated prompts.

These question shapes show up over and over in SQL practice sets:

• self-join comparisons
• anti-joins ("who did not do X")
• grouped counts with thresholds
• top N per group
• latest row per entity
• retention or rolling-window reporting

That is why the same named problems keep getting reused in public prep sets: Employees Earning More Than Their Managers, Second Highest Salary, Department Top Three Salaries, Restaurant Growth, and similar reporting questions are really just different wrappers around those same patterns.

Common mistakes that cost easy points

Joining before you check the row grain

If one table is one row per order and the other is one row per order item, the join can multiply rows before you ever group. Say the grain out loud first.

Using COUNT(column) when NULL changes the meaning

COUNT(*) counts rows. COUNT(column) ignores NULL. That difference bites people on missing-value questions all the time.

Reaching for a subquery when a window function is clearer

Subqueries can work, but once the question becomes "per group, ordered, then pick the top row," window functions are usually easier to reason about. The PostgreSQL documentation on window functions is worth reading even if you use a different dialect -- the mental model transfers directly.

Confusing tie behavior

• ROW_NUMBER() breaks ties arbitrarily
• RANK() skips numbers after ties
• DENSE_RANK() keeps ranking compact

If the prompt cares about "top three salaries" rather than "three employees," that difference matters.

Treating SQL as pure syntax instead of logic

Strong candidates narrate the data flow:

1. start at the right table grain
2. join only what is needed
3. filter
4. aggregate or rank
5. select the final projection

That explanation earns points even before the query is perfect.

A better interview talk-track

Try this format:

1. "The unit of analysis is one row per ___."
2. "I need columns from ___ and ___, so I will join on ___."
3. "I filter early with WHERE because ___."
4. "Then I group or rank by ___ because the result needs ___."
5. "Edge cases are ties, nulls, and duplicate rows from joins."

That sounds much stronger than typing silently and hoping the query works.

The 2-hour prep plan

If you only have one serious SQL session before an interview, do this:

1. 20 minutes: one join question and one anti-join question
2. 25 minutes: one GROUP BY plus HAVING question
3. 25 minutes: one top-N-per-group window-function question
4. 20 minutes: one latest-row-per-user question
5. 20 minutes: one date or retention question
6. 10 minutes: review your own mistakes and rewrite the ugliest query cleanly

That is enough to cover most of the real failure modes:

• wrong join
• wrong filter stage
• wrong grouping grain
• wrong ranking function
• wrong assumptions about duplicates

SQL interviews stop feeling random once you realize there are only a few moves. Practice those moves until you can explain them out loud, and the round becomes much more predictable. If you want additional depth, Use The Index, Luke is the best free reference for understanding how SQL queries actually execute under the hood -- useful context when an interviewer asks about performance.