There are thousands of "SQL interview questions" lists online. Memorising them does not work, because interviewers change the table names and the wording. What works is recognising the pattern behind a question. Here are ten patterns that cover a large share of fresher SQL rounds, with a standard answer for each.
The examples use an employees table (emp_id, name, dept, salary, manager_id, join_date) and an orders table (order_id, customer_id, order_date, amount) for a fictional company.
1. Second highest value
SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);For the Nth highest, use DENSE_RANK so that ties are handled:
SELECT DISTINCT salary
FROM (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS r FROM employees) t
WHERE r = 3;2. Top N per group
"Highest-paid employee in each department."
WITH ranked AS (
SELECT *, RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS r
FROM employees
)
SELECT dept, name, salary FROM ranked WHERE r = 1;Mention whether ties should be included; that shows you think about edge cases.
3. Find duplicates
SELECT customer_id, order_date, amount, COUNT(*) AS copies
FROM orders
GROUP BY customer_id, order_date, amount
HAVING COUNT(*) > 1;Follow-up: "Delete the duplicates, keeping one." Use ROW_NUMBER() OVER (PARTITION BY ... ORDER BY order_id) and delete rows where the number is greater than 1.
4. Rows with no match (anti-join)
"Customers who never placed an order."
SELECT c.*
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;NOT EXISTS is an equally good answer. Be careful with NOT IN if the subquery can return NULLs: then it returns no rows at all, a classic trap.
5. Above the group average
"Employees who earn more than their department's average."
SELECT name, dept, salary
FROM (SELECT *, AVG(salary) OVER (PARTITION BY dept) AS dept_avg FROM employees) t
WHERE salary > dept_avg;A correlated subquery also works; the window version is usually cleaner.
6. Self join: employee and manager
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.emp_id = e.manager_id;Variation: "Employees who earn more than their manager" โ add WHERE e.salary > m.salary and use an inner join.
7. Running total and month-on-month change
WITH monthly AS (
SELECT DATE_TRUNC('month', order_date) AS month, SUM(amount) AS revenue
FROM orders GROUP BY 1
)
SELECT month, revenue,
SUM(revenue) OVER (ORDER BY month) AS running_total,
revenue - LAG(revenue) OVER (ORDER BY month) AS change
FROM monthly;8. Conditional counts (pivot)
"For each department, count employees who joined before and after 2025."
SELECT dept,
SUM(CASE WHEN join_date < '2025-01-01' THEN 1 ELSE 0 END) AS before_2025,
SUM(CASE WHEN join_date >= '2025-01-01' THEN 1 ELSE 0 END) AS from_2025
FROM employees
GROUP BY dept;9. Consecutive events
"Customers who ordered on three consecutive days." The trick: for consecutive dates, order_date โ ROW_NUMBER() is constant.
WITH d AS (
SELECT DISTINCT customer_id, order_date FROM orders
),
g AS (
SELECT customer_id, order_date,
order_date - CAST(ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS INT) AS grp
FROM d
)
SELECT customer_id
FROM g
GROUP BY customer_id, grp
HAVING COUNT(*) >= 3;Date arithmetic syntax varies by database, so say which one you are assuming.
10. Percentage of total
SELECT dept, COUNT(*) AS staff,
ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS pct
FROM employees
GROUP BY dept;Note the 100.0: with integers only, many databases do integer division and return 0.
Concept questions you should answer in one breath
- Difference between
WHEREandHAVING(see execution order). RANKvsDENSE_RANKvsROW_NUMBER.UNIONvsUNION ALL(the second keeps duplicates and is faster).DELETEvsTRUNCATEvsDROP.COUNT(*)vsCOUNT(column).- What a primary key and a foreign key are.
INNERvsLEFTjoin (see joins explained).
How to answer in the interview
- Repeat the question and ask about edge cases: ties, NULLs, duplicates.
- Say your approach in one sentence before writing.
- Write the query step by step; CTEs make your thinking visible.
- Check it against a tiny example in your head.
Practise the patterns
Our SQL interview set collects these patterns with instant checking, and the mock interview lets you rehearse explaining your answers. For the bigger picture of what happens in each round, read the data analyst interview process.
Practise what you just read
Reading helps; writing the code yourself is what sticks. Start here: