SQL interviews are not as unpredictable as they feel. Most companies pull from the same pool of questions. Once you have seen them, they stop being scary.
Today let’s look at the questions that come up during the interviews — grouped by topic, with the answers and the code.
SQL tip: SQL queries are written and executed in a different order. The easy way to remember the order of execution: First We Got Hungry So Ordered Lunch:
From → Where → Group By → Having → Select → Order by → Limit
Before we proceed, a small advertisement, The Code newsletter by Superhuman - for data engineers and people in tech. As always, your clicks on ads help me to cover hosting fees. Thank you for your support.
What 200K+ Engineers Read to Stay Ahead
Your GitHub stars won't save you if you're behind on tech trends.
That's why over 200K engineers read The Code to spot what's coming next.
Get curated tech news, tools, and insights twice a week
Learn about emerging trends you can leverage at work in just 5 mins a day
Become the engineer who always knows what's next
BASIC SELECT, FILTERING AND AGGREGATIONS
These always appear. Even senior roles get asked these as a warm-up.
Q: What is the difference between WHERE and HAVING?
WHERE filters rows before aggregation. HAVING filters after.
Classic trick question.
-- WHERE filters individual rows first
SELECT department, COUNT(*) AS headcount
FROM employees
WHERE status = 'active'
GROUP BY department
-- HAVING filters the aggregated result
SELECT department, COUNT(*) AS headcount
FROM employees
GROUP BY department
HAVING COUNT(*) > 10The rule: use WHERE to filter raw data, use HAVING to filter grouped results.
Q: What is the difference between COUNT(*) and COUNT(column)?
COUNT(*) counts all rows including NULLs. COUNT(column) counts only non-NULL values in that column.
SELECT
COUNT(*) AS total_rows,
COUNT(customer_id) AS rows_with_customer -- ignores NULLs
FROM ordersThis matters more than people think. If your column has NULLs, these two will give different numbers and you need to know why.
JOINS
JOINs are the most tested topic in SQL interviews. You will get a conceptual question and a written query question.
Q: What is the difference between INNER JOIN, LEFT JOIN and FULL OUTER JOIN?
INNER JOIN — only rows that match in both tables
LEFT JOIN — all rows from the left table, NULLs where there is no match on the right
FULL OUTER JOIN — all rows from both tables, NULLs where there is no match on either side
As you can see, nobody ever asks about RIGHT JOIN, haha.
-- INNER JOIN: only customers who have placed an order
SELECT c.customer_name, o.order_id
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
-- LEFT JOIN: all customers, including those with no orders
SELECT c.customer_name, o.order_id
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
-- FULL OUTER JOIN: everything from both tables
SELECT c.customer_name, o.order_id
FROM customers c
FULL OUTER JOIN orders o ON c.customer_id = o.customer_idInterviewers love asking: "How would you find customers who have never placed an order?" The answer is LEFT JOIN + WHERE o.order_id IS NULL.
Another example of the same thing I did today at work - I needed to find users that stopped using a service in March, but used it in February. You do LEFT JOIN Feb and Mar and then select only the rows WHERE March user_id is NULL.
Q: What is a self join and when would you use it?
A self join is when you join a table to itself. It sounds strange but it is useful when rows in a table relate to other rows in the same table.
Classic example: an employees table where each row has a manager_id that points to another row in the same table.
SELECT
e.employee_name,
m.employee_name AS manager_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_idCTEs AND SUBQUERIES
Q: What is a CTE and why would you use one instead of a subquery?
A CTE (Common Table Expression) is a named temporary result set you define at the top of a query using WITH. A subquery is nested inside the main query.
Both do similar things. CTEs are easier to read, easier to debug, and can be referenced multiple times. Subqueries get messy fast.
-- Subquery (harder to read)
SELECT customer_id, total_spend
FROM (
SELECT customer_id, SUM(amount) AS total_spend
FROM orders
GROUP BY customer_id
) AS customer_totals
WHERE total_spend > 500
-- CTE (same result, much cleaner)
WITH customer_totals AS (
SELECT customer_id, SUM(amount) AS total_spend
FROM orders
GROUP BY customer_id
)
SELECT customer_id, total_spend
FROM customer_totals
WHERE total_spend > 500Q: What is a recursive CTE?
A recursive CTE references itself. It is used for hierarchical data — like org charts, folder structures, category trees.
This is a more advanced question. You will not always need to write one, but it is good to know what it is and explain how and when it is being used.
-- Find all levels of an org chart starting from a top-level manager
WITH RECURSIVE org_chart AS (
-- Anchor: start with the CEO
SELECT employee_id, employee_name, manager_id, 1 AS level
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- Recursive: find their direct reports, then reports of reports
SELECT e.employee_id, e.employee_name, e.manager_id, oc.level + 1
FROM employees e
INNER JOIN org_chart oc ON e.manager_id = oc.employee_id
)
SELECT * FROM org_chart
ORDER BY levelWINDOW FUNCTIONS
Window functions are a must. They are confusing at first, but once you understand how to use them - it becomes much easier to get the data you need.
Q: What is a window function and how is it different from GROUP BY?
GROUP BY collapses rows into one row per group. Window functions calculate across a set of rows but keep every individual row.
-- GROUP BY: one row per department, individual rows are gone
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
-- Window function: every employee stays, avg added as a new column
SELECT
employee_name,
department,
salary,
AVG(salary) OVER (PARTITION BY department) AS dept_avg_salary
FROM employeesThis is a genuinely useful pattern. You can see each employee AND their department average in the same row, without losing any data.
Q: What is the difference between ROW_NUMBER(), RANK() and DENSE_RANK()?
All three number rows. The difference is how they handle ties.
SELECT
employee_name,
salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num, -- no ties, always unique
RANK() OVER (ORDER BY salary DESC) AS rnk, -- ties get same number, next rank skips
DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rnk -- ties get same number, next rank does not skip
FROM employeesExample with tied salaries of 90,000:
Name | Salary | ROW_NUMBER | RANK | DENSE_RANK |
|---|---|---|---|---|
Ana | 95,000 | 1 | 1 | 1 |
Bob | 90,000 | 2 | 2 | 2 |
Sara | 90,000 | 3 | 2 | 2 |
Tom | 85,000 | 4 | 4 | 3 |
RANK skips 3. DENSE_RANK does not. ROW_NUMBER does not care about ties at all.
Q: How would you find the top 3 earners in each department?
This is one of the most common window function interview questions. The trick is using ROW_NUMBER() inside a CTE, then filtering.
WITH ranked AS (
SELECT
employee_name,
department,
salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
FROM employees
)
SELECT employee_name, department, salary
FROM ranked
WHERE rn <= 3One last thing
Reading this is a good start. But interviews reward people who have actually written the queries, not just read about them. Therefore, here are some good resources:
Practice:
SQLZoo — sqlzoo.net — free, interactive, beginner friendly
LeetCode SQL — leetcode.com/problemset/database — the most realistic interview prep, companies actually use these questions
Learning:
Mode SQL Tutorial — mode.com/sql-tutorial — free, well structured, goes from basics to window functions
W3Schools SQL — w3schools.com/sql — good quick reference for syntax
Keep pushing 💪,
Karina
Need more help?
Just starting with Python? Wondering if programming is for you?
Master key data analysis tasks like cleaning, filtering, pivot and grouping data using Pandas, and learn how to present your insights visually with Matplotlib with ‘Data Analysis with Python’ masterclass.
Building your portfolio?
Grab the Complete EDA Portfolio Project — a full e-commerce analysis (ShopTrend 2024) with Python notebook, realistic dataset, portfolio template, and step-by-step workflow. See exactly how to structure professional portfolio projects.
Grab your Pandas CheatSheet here. Everything you need to know about Pandas - from file operations to visualisations in one place.
Data Analyst & Data Scientist
