Top 30 SQL Query Interview Questions Experienced Developers

Top 30 SQL Query Interview Questions

For experienced developers - practical query patterns, concise explanations, and interview-ready SQL
examples.

How to use this PDF Practice writing each query without looking at the answer. Then explain the
trade-off: JOIN vs EXISTS, ROW_NUMBER vs DENSE_RANK, index usage,
and edge cases like duplicates and NULLs.

Ranking, Window Functions, and Duplicates

1. Find the second highest salary from an employees table.
Answer approach: Experienced answer should handle duplicates. Use DENSE_RANK instead of LIMIT offset.
SELECT salary
FROM (
SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees
) x
WHERE rnk = 2;

2. Find the Nth highest salary.

Answer approach: Use a parameterized rank. This is safer than correlated COUNT for large data.
-- :n = required rank
SELECT salary
FROM (
SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees
) x
WHERE rnk = :n;

3. Get the top 3 salaries in each department.

Answer approach: Partition by department and rank within each group.
SELECT department_id, employee_id, salary
FROM (
SELECT e.*,
DENSE_RANK() OVER (
PARTITION BY department_id ORDER BY salary DESC
) AS rnk
FROM employees e
) x
WHERE rnk <= 3
ORDER BY department_id, salary DESC;

4. Find employees earning more than their department average.

Answer approach: Use a window average to avoid an extra join.
SELECT employee_id, department_id, salary
FROM (
SELECT e.*,
AVG(salary) OVER (PARTITION BY department_id) AS dept_avg_salary
FROM employees e
) x
WHERE salary > dept_avg_salary;

5. Find duplicate email IDs in a users table.

Answer approach: Group and filter with HAVING. For actual duplicate rows, include COUNT(*).
SELECT email, COUNT(*) AS duplicate_count
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

Page 1
Top 30 SQL Query Interview Questions Experienced Developers

6. Delete duplicate rows and keep the latest record.

Answer approach: Use ROW_NUMBER with a stable ordering column such as created_at or id.
DELETE FROM users
WHERE id IN (
SELECT id
FROM (
SELECT id,
ROW_NUMBER() OVER (
PARTITION BY email ORDER BY created_at DESC, id DESC
) AS rn
FROM users
) x
WHERE rn > 1
);

Joins, Anti-Joins, Dates, and Aggregation

7. Find customers who never placed an order.
Answer approach: LEFT JOIN + IS NULL or NOT EXISTS. NOT EXISTS is usually safer with nullable columns.
SELECT c.customer_id, [Link]
FROM customers c
WHERE NOT EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.customer_id
);

8. Find orders placed in the last 30 days.

Answer approach: Avoid wrapping the indexed date column in a function; compare with a boundary.
SELECT order_id, customer_id, order_date, amount
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '30 days';

9. Calculate monthly revenue.

Answer approach: Use date truncation for grouping. Adjust function for your SQL dialect.
-- PostgreSQL style
SELECT DATE_TRUNC('month', order_date) AS month_start,
SUM(amount) AS monthly_revenue
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month_start;

10. Find month-over-month revenue growth percentage.

Answer approach: Aggregate first, then use LAG to compare with the previous month.
WITH monthly AS (
SELECT DATE_TRUNC('month', order_date) AS month_start,
SUM(amount) AS revenue
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
)
SELECT month_start,
revenue,
LAG(revenue) OVER (ORDER BY month_start) AS previous_revenue,
ROUND(
100.0 * (revenue - LAG(revenue) OVER (ORDER BY month_start))
/ NULLIF(LAG(revenue) OVER (ORDER BY month_start), 0), 2
) AS growth_pct
FROM monthly
ORDER BY month_start;

Page 2
Top 30 SQL Query Interview Questions Experienced Developers

11. Find the first order of each customer.

Answer approach: Use ROW_NUMBER so you can return the complete order row.
SELECT *
FROM (
SELECT o.*,
ROW_NUMBER() OVER (
PARTITION BY customer_id ORDER BY order_date, order_id
) AS rn
FROM orders o
) x
WHERE rn = 1;

12. Find the latest order of each customer.

Answer approach: Same pattern, descending order. Add order_id as a tie-breaker.
SELECT *
FROM (
SELECT o.*,
ROW_NUMBER() OVER (
PARTITION BY customer_id ORDER BY order_date DESC, order_id DESC
) AS rn
FROM orders o
) x
WHERE rn = 1;

13. Find customers who ordered in every month of 2025.

Answer approach: Count distinct months after filtering the year.
SELECT customer_id
FROM orders
WHERE order_date >= DATE '2025-01-01'
AND order_date < DATE '2026-01-01'
GROUP BY customer_id
HAVING COUNT(DISTINCT DATE_TRUNC('month', order_date)) = 12;

Analytics Patterns: Streaks, Gaps, Running Totals, Pivot

14. Find users with consecutive login days.
Answer approach: Normalize dates by subtracting row number; equal normalized value forms a streak.
WITH daily AS (
SELECT DISTINCT user_id, login_date::date AS login_day
FROM logins
), marked AS (
SELECT user_id,
login_day,
login_day - (ROW_NUMBER() OVER (
PARTITION BY user_id ORDER BY login_day
) * INTERVAL '1 day') AS grp
FROM daily
)
SELECT user_id, MIN(login_day) AS streak_start,
MAX(login_day) AS streak_end, COUNT(*) AS streak_days
FROM marked
GROUP BY user_id, grp
HAVING COUNT(*) >= 3;

15. Find gaps in a sequence of order IDs.

Answer approach: Use LAG and compare current id with previous id + 1.
SELECT previous_order_id + 1 AS missing_from,
order_id - 1 AS missing_to
FROM (
SELECT order_id,
LAG(order_id) OVER (ORDER BY order_id) AS previous_order_id
FROM orders
) x
WHERE previous_order_id IS NOT NULL
AND order_id > previous_order_id + 1;

Page 3
Top 30 SQL Query Interview Questions Experienced Developers

16. Find overlapping date ranges, such as conflicting bookings.

Answer approach: Two ranges overlap when each starts before the other ends.
SELECT b1.booking_id AS booking_1,
b2.booking_id AS booking_2,
b1.room_id
FROM bookings b1
JOIN bookings b2
ON b1.room_id = b2.room_id
AND b1.booking_id < b2.booking_id
AND b1.start_time < b2.end_time
AND b2.start_time < b1.end_time;

17. Find running total of sales by date.

Answer approach: Window SUM gives a cumulative result without self-joins.
SELECT order_date,
amount,
SUM(amount) OVER (
ORDER BY order_date, order_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total
FROM orders
ORDER BY order_date, order_id;

18. Find each product's contribution percentage to total sales.

Answer approach: Use SUM over all rows after product-level aggregation.
WITH product_sales AS (
SELECT product_id, SUM(amount) AS revenue
FROM order_items
GROUP BY product_id
)
SELECT product_id,
revenue,
ROUND(100.0 * revenue / SUM(revenue) OVER (), 2) AS revenue_pct
FROM product_sales
ORDER BY revenue DESC;

19. Find the median salary.

Answer approach: PostgreSQL supports percentile_cont. Other databases may require window-based logic.
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median_salary
FROM employees;

20. Pivot rows into columns: count orders by status.

Answer approach: Conditional aggregation is portable and interview-friendly.
SELECT customer_id,
SUM(CASE WHEN status = 'PLACED' THEN 1 ELSE 0 END) AS placed_orders,
SUM(CASE WHEN status = 'SHIPPED' THEN 1 ELSE 0 END) AS shipped_orders,
SUM(CASE WHEN status = 'CANCELLED' THEN 1 ELSE 0 END) AS cancelled_orders
FROM orders
GROUP BY customer_id;

21. Find products with no sales.

Answer approach: Use NOT EXISTS to avoid accidental NULL issues.
SELECT p.product_id, p.product_name
FROM products p
WHERE NOT EXISTS (
SELECT 1
FROM order_items oi
WHERE oi.product_id = p.product_id
);

Product, Funnel, Hierarchy, and Performance Queries

Page 4
Top 30 SQL Query Interview Questions Experienced Developers

22. Find the most purchased product per category.

Answer approach: Aggregate quantity, then rank inside category.
WITH sales AS (
SELECT p.category_id, oi.product_id, SUM([Link]) AS total_qty
FROM order_items oi
JOIN products p ON p.product_id = oi.product_id
GROUP BY p.category_id, oi.product_id
)
SELECT category_id, product_id, total_qty
FROM (
SELECT sales.*,
ROW_NUMBER() OVER (
PARTITION BY category_id ORDER BY total_qty DESC, product_id
) AS rn
FROM sales
) x
WHERE rn = 1;

23. Find users who performed action A but not action B.

Answer approach: This pattern is common in funnel and product analytics.
SELECT DISTINCT e1.user_id
FROM events e1
WHERE e1.event_name = 'signup'
AND NOT EXISTS (
SELECT 1
FROM events e2
WHERE e2.user_id = e1.user_id
AND e2.event_name = 'purchase'
);

24. Calculate conversion rate from signup to purchase.

Answer approach: Count distinct users in each step and divide safely.
SELECT ROUND(
100.0 * COUNT(DISTINCT CASE WHEN event_name = 'purchase' THEN user_id END)
/ NULLIF(COUNT(DISTINCT CASE WHEN event_name = 'signup' THEN user_id END), 0),
2
) AS conversion_rate_pct
FROM events
WHERE event_name IN ('signup', 'purchase');

25. Find employees who are managers.

Answer approach: Self-join employee.manager_id to employee.employee_id.
SELECT DISTINCT m.employee_id, [Link]
FROM employees e
JOIN employees m
ON e.manager_id = m.employee_id;

26. Find the full employee hierarchy under a manager.

Answer approach: Recursive CTE is the standard solution for hierarchy traversal.
WITH RECURSIVE emp_tree AS (
SELECT employee_id, name, manager_id, 1 AS level
FROM employees
WHERE employee_id = :manager_id

UNION ALL

SELECT e.employee_id, [Link], e.manager_id, [Link] + 1

FROM employees e
JOIN emp_tree t ON e.manager_id = t.employee_id
)
SELECT *
FROM emp_tree
ORDER BY level, employee_id;

Page 5
Top 30 SQL Query Interview Questions Experienced Developers

27. Find the department with the highest total salary.

Answer approach: Aggregate by department, then rank. This handles ties if you use DENSE_RANK.
SELECT department_id, total_salary
FROM (
SELECT department_id,
SUM(salary) AS total_salary,
DENSE_RANK() OVER (ORDER BY SUM(salary) DESC) AS rnk
FROM employees
GROUP BY department_id
) x
WHERE rnk = 1;

28. Return rows from table A that do not exactly match table B.
Answer approach: Use EXCEPT where available. MySQL can use LEFT JOIN / NOT EXISTS.
SELECT id, name, amount FROM table_a
EXCEPT
SELECT id, name, amount FROM table_b;

29. Update one table based on another table.

Answer approach: Syntax varies by database; this is PostgreSQL style.
UPDATE employees e
SET salary = salary * 1.10
FROM departments d
WHERE d.department_id = e.department_id
AND d.department_name = 'Engineering';

30. Find slow query causes and suggest query/index improvement.

Answer approach: In interviews, explain EXPLAIN plan, filtering, joins, sort, and index selectivity.
-- Example: query pattern
SELECT order_id, customer_id, order_date
FROM orders
WHERE customer_id = :customer_id
AND order_date >= DATE '2026-01-01'
ORDER BY order_date DESC;

-- Helpful composite index for this access pattern

CREATE INDEX idx_orders_customer_date
ON orders (customer_id, order_date DESC);

Final Interview Tips

- Always ask about SQL dialect: PostgreSQL, MySQL, Oracle, SQL Server, etc.

- Mention edge cases: duplicate salaries, NULL values, date boundaries, ties, and empty result sets.

- For performance questions, talk about EXPLAIN plan, composite indexes, selectivity, join order, and avoiding functions
on indexed columns.

- Prefer window functions for ranking and analytics; prefer NOT EXISTS for anti-join logic when NULLs may exist.

Page 6