SQL course menu
SQL Query Challenges: Practice Problems With Solutions
Realistic SQL query challenges in the style of technical interviews and take-home tests: from salary analysis to running totals, retention and normalization, each with a hint and a verified solution.
On this page
- The data
- Challenge 1: Salary spread per department
- Challenge 2: Where does each salary sit in the range?
- Challenge 3: Cumulative revenue
- Challenge 4: Months that beat the previous month
- Challenge 5: Employees who are leaders
- Challenge 6: Best month per region, side by side
- Challenge 7: A tidy monthly report
- How to approach a new challenge
These challenges mimic live-coding rounds and take-home tests. Each one combines several ideas from the course. Try to solve each challenge yourself first; there is often more than one correct answer, and the solutions explain why each choice was made.
The data#
All challenges use the course's sample tables:
employees
monthly_sales
Plus customers, orders and products, described in SQL Database Basics.
Challenge 1: Salary spread per department#
Challenge 1
For each department, show the difference between its highest and lowest salary as spread, largest spread first.
Show hint
MAX(salary) - MIN(salary) with GROUP BY department.
Show solution
SELECT department,
MAX(salary) - MIN(salary) AS spread
FROM employees
GROUP BY department
ORDER BY spread DESC;department | spread
------------+-------
Sales | 9000
Engineering | 3000
Marketing | 0Marketing has one employee, so its spread is 0, not NULL.
Challenge 2: Where does each salary sit in the range?#
Challenge 2
For each employee, show their salary as a percentage of the company's salary range, where the lowest salary is 0% and the highest is 100%. Round to one decimal place.
Show hint
(salary - min) / (max - min) * 100. Get min and max with scalar subqueries or window functions, and multiply by 100.0 to avoid integer division.
Show solution
SELECT name,
salary,
ROUND(100.0 * (salary - MIN(salary) OVER ())
/ (MAX(salary) OVER () - MIN(salary) OVER ()), 1) AS pct_of_range
FROM employees
ORDER BY salary;name | salary | pct_of_range
------+--------+-------------
John | 46000 | 0
Asha | 52000 | 17.1
Priya | 61000 | 42.9
Ravi | 78000 | 91.4
Meera | 81000 | 100The range is 81,000 − 46,000 = 35,000; Asha sits 6,000 above the minimum, which is 17.1% of it. In production you would guard against a zero range with NULLIF(..., 0).
Challenge 3: Cumulative revenue#
Challenge 3
Show the South region's revenue for each month alongside its cumulative (running) revenue.
Show hint
SUM(revenue) OVER (ORDER BY month).
Show solution
SELECT month,
revenue,
SUM(revenue) OVER (ORDER BY month) AS cumulative
FROM monthly_sales
WHERE region = 'South'
ORDER BY month;month | revenue | cumulative
--------+---------+-----------
2026-01 | 90 | 90
2026-02 | 90 | 180
2026-03 | 130 | 310
2026-04 | 125 | 435Challenge 4: Months that beat the previous month#
Challenge 4
List each region and month in which revenue was higher than the same region's previous month.
Show hint
Use LAG(revenue) OVER (PARTITION BY region ORDER BY month) in a subquery and filter outside it.
Show solution
SELECT region, month, revenue
FROM (
SELECT region, month, revenue,
LAG(revenue) OVER (PARTITION BY region ORDER BY month) AS prev
FROM monthly_sales
) t
WHERE revenue > prev
ORDER BY region, month;region | month | revenue
-------+---------+--------
North | 2026-02 | 150
North | 2026-04 | 170
South | 2026-03 | 130PARTITION BY region stops South's January from being compared with North's April.
Challenge 5: Employees who are leaders#
Challenge 5
Return the employees who have no manager but do manage at least one person, together with their number of direct reports.
Show hint
Self join from manager to report, filter managers whose own manager_id is NULL, and count.
Show solution
SELECT m.name,
COUNT(e.id) AS direct_reports
FROM employees m
JOIN employees e ON e.manager_id = m.id
WHERE m.manager_id IS NULL
GROUP BY m.id, m.name
ORDER BY direct_reports DESC;name | direct_reports
------+---------------
Priya | 2
Meera | 1The inner join already guarantees that each returned person manages someone.
Challenge 6: Best month per region, side by side#
Challenge 6
For each region, return the month with the highest revenue. If two months tie, return both.
Show hint
RANK() (not ROW_NUMBER) partitioned by region, ordered by revenue descending.
Show solution
SELECT region, month, revenue
FROM (
SELECT region, month, revenue,
RANK() OVER (PARTITION BY region ORDER BY revenue DESC) AS r
FROM monthly_sales
) t
WHERE r = 1
ORDER BY region;region | month | revenue
-------+---------+--------
North | 2026-04 | 170
South | 2026-03 | 130Challenge 7: A tidy monthly report#
Challenge 7
Produce one row per month with North and South revenue as separate columns and a total column.
Show hint
Conditional aggregation with CASE, then add the two sums (or use SUM(revenue) for the total).
Show solution
SELECT month,
SUM(CASE WHEN region = 'North' THEN revenue ELSE 0 END) AS north,
SUM(CASE WHEN region = 'South' THEN revenue ELSE 0 END) AS south,
SUM(revenue) AS total
FROM monthly_sales
GROUP BY month
ORDER BY month;month | north | south | total
--------+-------+-------+------
2026-01 | 120 | 90 | 210
2026-02 | 150 | 90 | 240
2026-03 | 110 | 130 | 240
2026-04 | 170 | 125 | 295How to approach a new challenge#
- Restate the question and decide what one row of the answer represents.
- Find the tables and joins needed, and check whether any join can multiply rows.
- Build in steps with CTEs: filter, then aggregate, then rank or compare.
- Check edge cases: ties,
NULLs, empty groups, the first and last rows of a window. - Verify with a quick manual calculation on a couple of rows.
Want more? Try the practice sets, or revisit any topic in the SQL course.
Finished this lesson?