SQL Practice
Medium SQL Exercises
Step it up. These problems combine several tables, group and filter aggregates, use subqueries and CASE, and introduce window functions. Each one has schema, sample data, an expected result to aim for, and a full solution with explanation.
Medium
15 problems
Topic:
1
Find the second highest salary
Use a subquery or DENSE_RANK to skip the maximum.
Subqueries
2
Employees earning above their department average
Compare each row to a correlated aggregate.
Subqueries
3
Departments with more than N employees
Filter groups with HAVING.
Aggregation
4
Customers who never ordered
Find non-matching rows with a LEFT JOIN or NOT EXISTS.
Joins
5
Total revenue per product category
Join, group and sum across tables.
Aggregation
6
Rank employees by salary
Assign ranks with a window function.
Window Functions
7
Join orders, customers and products
Combine three tables in one query.
Joins
8
Count orders per month
Group by a date part.
Aggregation
9
Find repeat customers
Group and filter with HAVING COUNT.
Aggregation
10
Average order value per customer
Aggregate joined rows per customer.
Aggregation
11
Employees who earn more than their manager
Compare rows with a self join.
Joins
12
Find duplicate email addresses
Detect duplicates with GROUP BY and HAVING.
Aggregation
13
Products priced above the average
Compare each row to a scalar subquery.
Subqueries
14
Each customer first order
Pick one row per group with ROW_NUMBER.
Window Functions
15
Category with the highest sales
Aggregate then pick the top group.
Subqueries
No problems match this topic.