SQL Interview Questions: The Complete Practice Guide

SQL remains one of the most heavily tested skills in data engineering, analytics, and backend engineering interviews. The good news: SQL interviews follow predictable patterns. The topics are consistent across companies, and the difficulty progression is standard.

Here's what to master and how.

The Four Tiers of SQL Interview Questions

Tier 1: Fundamentals (everyone must know)

Basic SELECT, WHERE, GROUP BY, ORDER BY, and aggregate functions (COUNT, SUM, AVG, MAX, MIN). These questions test whether you know SQL at all, and any experienced candidate should answer them immediately without hesitation.

Example: "Write a query to find all customers who placed more than 5 orders in the last 30 days."

Tier 2: JOINs and Subqueries

Understanding when to use INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN — and being able to explain the difference — is essential. Most SQL interview questions at the junior-to-mid level involve some form of JOIN.

Subqueries and correlated subqueries appear frequently. Know when to use a subquery versus a JOIN versus a CTE.

Example: "Find all users who made a purchase but have never left a review."

Tier 3: Window Functions

This is where most candidates struggle. Window functions — ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), LEAD(), SUM() OVER (PARTITION BY...) — appear in virtually every mid-to-senior data interview.

The key concept: window functions perform calculations across a set of rows related to the current row, without collapsing them into a single result the way GROUP BY does.

Example: "For each customer, find their first and second purchase dates."

This is trivially solved with LAG() or ROW_NUMBER() and nearly impossible to write cleanly without it.

Tier 4: Performance and Query Optimization

Senior-level SQL interviews go beyond writing correct queries into writing efficient queries. Topics include:

• Indexes — When to add them, the cost of over-indexing, composite index column order

• Query execution plans — How to read EXPLAIN output and identify bottlenecks

• Avoiding full table scans — When WHERE clauses prevent index use

• Partitioning — Table partitioning strategies for large datasets

• Materialized views — When to use them versus querying live tables

The Most Common SQL Interview Questions

These appear repeatedly across companies. Practice all of them until you can write them from memory:

1. 1

   Find the Nth highest salary in a table

2. 2

   Find duplicate rows and count them

3. 3

   Calculate a running total

4. 4

   Find customers who haven't purchased in 90 days

5. 5

   Calculate month-over-month growth

6. 6

   Find the most recent event for each user

7. 7

   Identify users who performed action A but not action B

8. 8

   Calculate a 7-day rolling average

9. 9

   Rank products by revenue within each category

10. 10

    Find the second-most-recent login for each user

Window Function Syntax That Often Trips Candidates

Example: SELECT user_id, purchase_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY purchase_date ASC) AS purchase_rank FROM purchases

Practice reading window functions out loud: "For each user_id, number the rows in order of purchase date." Being able to articulate what a window function does in plain English is often tested directly.

What Interviewers Are Actually Testing

Beyond syntax, SQL interviewers want to see: - Do you ask clarifying questions about edge cases? (What if a user has no purchases? What if there are ties?) - Do you think about performance implications at scale? - Can you reason about whether your query is correct before running it? - Do you understand NULL behavior in aggregations and JOINs?

NULL handling is a specific trap. COUNT(*) and COUNT(column) behave differently. NULL in a JOIN condition behaves unexpectedly. Know this cold.

How to Practice

The best SQL practice platforms are LeetCode (Database section), Mode Analytics SQL Tutorial, and StrataScratch. Work through 20–30 problems at increasing difficulty before a data interview.

For each problem, don't just get the right answer — be able to explain your approach out loud as you write it. SQL interviews are rarely silent.