I’m preparing to interview candidates for SQL-focused roles and want to include practical, fair, and insightful SQL interview questions — not just theory-based ones.
I’m looking for questions that test both conceptual understanding and real-world problem-solving, such as:
- Writing queries using
JOIN, GROUP BY, or HAVING
- Using window functions (like
ROW_NUMBER() or RANK())
- Handling performance optimization and indexing
- Dealing with complex reporting, pagination, or data quality issues
- Evaluating SQL at scale (millions of records, keyset pagination, etc.)
Would appreciate suggestions that are fair across different experience levels — from junior analysts to senior data engineers — and ideally, include what each question tests or why it’s valuable to ask.
Having been on both sides of SQL interviews, I’ve found that structuring questions by difficulty really works well, it’s all about gauging a candidate’s approach to problem-solving, not just testing for memorization. For junior candidates, I’d typically start with:
-
Explain the difference between INNER JOIN vs LEFT JOIN, and follow up with an example query.
-
What it tests: This question helps you gauge the candidate’s understanding of joins and how data relationships are built in SQL.
-
Write a query to find duplicate records in a table.
-
What it tests: This is a classic problem that checks basic SQL skills, especially the ability to identify data quality issues, a fundamental skill in most SQL roles.
-
Use GROUP BY and HAVING to filter aggregates.
-
What it tests: This ensures they can group and filter data in meaningful ways, a core skill for reporting and analysis.
For mid-level candidates, I like to introduce window functions:
-
Use ROW_NUMBER() or DENSE_RANK() to find the latest record per user or top 3 sales per region.
-
What it tests: It tests their ability to work with window functions and think about how to manipulate row-level data in a structured way.
For senior-level candidates, I dive into more complex issues like:
-
How would you paginate 10M+ rows efficiently without using OFFSET?
-
What it tests: This question evaluates their understanding of performance optimization at scale. You’ll see if they consider methods like keyset pagination or how to leverage indexes effectively.
The key to these SQL interview questions is to keep them practical and scenario-based, allowing the candidate to think through performance trade-offs and real-world situations.
I totally agree with @kumari_babitaa , and from my experience interviewing data engineers, I’ve learned that good SQL interview questions should go beyond just syntax. Here’s what works for me:
- “How would you identify customers who placed orders in consecutive months?”
- What it tests: This checks their knowledge of LAG(), date logic, and how to deal with time-based data in SQL. A common situation in reporting tasks where you’re comparing rows based on time intervals.
- “Given millions of rows, how would you find the 2nd highest salary efficiently?”
- What it tests: This one tests the candidate’s understanding of both indexing and window functions, particularly ROW_NUMBER() or RANK(), and how they optimize queries with large datasets.
- “Explain when you’d prefer a CTE vs a subquery.”
- What it tests: Here, you’re asking about query design. CTEs (Common Table Expressions) are often easier to read and maintain than subqueries, and this question gauges their understanding of best practices and query organization.
I find that case-based questions like these also help candidates demonstrate how they think about scalability and optimization, not just SQL syntax. Bonus points if they mention analyzing execution plans or partitioning strategies to improve performance.
Great points, @prynka.chatterjee . I approach it a bit differently based on experience level, and I think it helps keep things well-rounded. Here’s how I break it down:
For junior candidates, I start with:
- Basic CRUD operations, simple filtering (using
WHERE), grouping (with GROUP BY), and joins. For example: “Find employees who have a salary above their department’s average.”
- What it tests: This is a foundational SQL skill, ensuring they can write basic queries and understand aggregations.
For mid-level candidates, I start introducing CTEs, window functions, and tasks like data cleaning. A good example might be:
- “Use ROW_NUMBER() to remove duplicates but keep the latest record.”
- What it tests: It evaluates their understanding of data cleaning and handling edge cases where multiple records need to be managed based on specific criteria.
For senior candidates, I focus on query optimization, indexing, partitioning, and database design. One of my favorites is:
- “Why might a query using an index still be slow?”
- What it tests: It’s a great question because it probes their understanding of how indexes work and challenges them to consider other factors like query structure, table size, and database configuration.
This approach lets candidates show both their fluency with SQL syntax and how they approach data challenges, from basics to advanced concepts. It’s not just about writing code; it’s about thinking strategically.