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.

All difficulty levels