25 SQL Interview Questions: Queries, Joins & Window Functions

Here's the open secret of SQL interviews: the hard questions are almost never about syntax you haven't seen. They're about grouping and windows. If you can think in sets rather than loops — and you can wield window functions without flinching — you will clear the bar at most companies, from startups to the big ones.

Part of our interview questions by role hub — practice live with the AI coding copilot.

The key idea: SQL is declarative. You describe what result you want, not how to compute it. Interview questions probe whether you actually think that way, or whether you're secretly writing a for-loop in your head.

The question taxonomy

• Joins and filtering. Inner vs left joins, the three-valued logic of NULL, anti-joins ("find customers with no orders").
• Aggregation. GROUP BY with HAVING, conditional aggregation with SUM(CASE WHEN ...).
• Window functions. ROW_NUMBER vs RANK vs DENSE_RANK, running totals, "top N per group" — the highest-signal topic in the whole interview.
• Schema design and indexes. Normalisation trade-offs, what an index actually is, when one doesn't help.
• Query reasoning. Given a slow query and an execution plan, what would you look at first?

Worked example: top N per group

"Get the two most recent orders per customer." The wrong instinct is a correlated subquery or a self-join mess. The right instinct is a window function:

SELECT customer_id, order_id, order_date, total
FROM (
  SELECT o.*,
         ROW_NUMBER() OVER (
           PARTITION BY customer_id
           ORDER BY order_date DESC
         ) AS rn
  FROM orders o
) ranked
WHERE rn <= 2;

Be ready for the follow-ups, because they always come: What if two orders share the same date (use RANK, or add a tiebreaker)? Why not just GROUP BY (because grouping collapses rows — you need the full row back)? How would an index on (customer_id, order_date) change the plan? Each follow-up is an invitation to show depth.

How answers get scored

Rubrics reward set-based thinking, correct NULL handling (this is where most candidates quietly lose points — NULL = NULL is not true), and the ability to reason about performance without a GUI. Saying "I'd check the execution plan and whether the join column is indexed" is a complete, senior-sounding answer. You don't need to recite B-tree internals unless it's a database-engineer role.

Common mistakes

• Filtering on an aggregated column in WHERE instead of HAVING.
• Using NOT IN with a subquery that can return NULL — it silently returns zero rows. Prefer NOT EXISTS.
• Confusing RANK and ROW_NUMBER when duplicates exist.
• Writing SELECT * in joins and getting ambiguous or duplicated columns.

How to practise SQL so it transfers

Reading solutions doesn't work for SQL — you have to write queries against data you haven't seen. Set up a small local database (a Dockerised Postgres with a sample schema takes minutes), then practise three moves daily: a join with an aggregate, a window function, and a "find the gap" anti-join. After solving each, rewrite the query a second way and compare. Can the window function become a self-join? Should it? This habit of producing alternatives is exactly what interview follow-ups demand. And always state your assumptions before writing: "I'll assume order_date has no ties; if it can, I'd add a tiebreaker." In a live round, the interviewer can't see your schema knowledge — only your narration. Two sentences of assumption-stating at the start do more for your score than ten minutes of silent, correct typing.

FAQ

Which SQL dialect should I practise? Standard SQL (PostgreSQL-style) transfers everywhere. Know that MySQL, SQL Server, and BigQuery differ in functions and limits, but the thinking is identical.

Do data roles and backend roles ask different SQL? Data roles lean harder on windows and analytics; backend roles add schema design and transactions. Prepare both if you're interviewing broadly — the data engineer guide goes deeper on pipelines.

Drill real queries with live feedback in Aissence practice, and cross-check the backend angle in the backend interview guide.