SQL Exercises

1 Easy Employees earning above a threshold Filter rows with WHERE and sort the result with ORDER BY. Filtering & Sorting 2 Easy Count employees per department Group rows and count them with GROUP BY and COUNT. Aggregation 3 Easy List customers with their orders Combine two tables with an INNER JOIN. Joins 4 Easy List the distinct product categories Remove duplicate values with DISTINCT. Filtering & Sorting 5 Easy Orders above a given amount Filter numeric values and sort the result. Filtering & Sorting 6 Easy Employees hired after a date Filter on a date column with a comparison. Strings & Dates 7 Easy Total sales amount Add up a column with SUM. Aggregation 8 Easy Average salary by department Compute an average per group with AVG and GROUP BY. Aggregation 9 Easy Top 5 most expensive products Sort descending and take the top rows with TOP. Filtering & Sorting 10 Easy Customers from a specific city Filter text with an equality or LIKE. Filtering & Sorting 11 Easy Products within a price range Filter a range with BETWEEN. Filtering & Sorting 12 Easy Number of orders per customer Join two tables and count per group. Aggregation 13 Easy Employees with no manager Filter NULLs with IS NULL. Filtering & Sorting 14 Easy Search products by name Match text patterns with LIKE and wildcards. Strings & Dates 15 Easy Distinct departments sorted by name Combine DISTINCT with ORDER BY. Filtering & Sorting 16 Medium Find the second highest salary Use a subquery or DENSE_RANK to skip the maximum. Subqueries 17 Medium Employees earning above their department average Compare each row to a correlated aggregate. Subqueries 18 Medium Departments with more than N employees Filter groups with HAVING. Aggregation 19 Medium Customers who never ordered Find non-matching rows with a LEFT JOIN or NOT EXISTS. Joins 20 Medium Total revenue per product category Join, group and sum across tables. Aggregation 21 Medium Rank employees by salary Assign ranks with a window function. Window Functions 22 Medium Join orders, customers and products Combine three tables in one query. Joins 23 Medium Count orders per month Group by a date part. Aggregation 24 Medium Find repeat customers Group and filter with HAVING COUNT. Aggregation 25 Medium Average order value per customer Aggregate joined rows per customer. Aggregation 26 Medium Employees who earn more than their manager Compare rows with a self join. Joins 27 Medium Find duplicate email addresses Detect duplicates with GROUP BY and HAVING. Aggregation 28 Medium Products priced above the average Compare each row to a scalar subquery. Subqueries 29 Medium Each customer first order Pick one row per group with ROW_NUMBER. Window Functions 30 Medium Category with the highest sales Aggregate then pick the top group. Subqueries 31 Hard Find the Nth highest salary Generalise second highest to any N. Analytics Patterns 32 Hard Running total of sales Accumulate with SUM OVER and a window frame. Window Functions 33 Hard Top 3 products per category Rank within groups and filter to the top N. Window Functions 34 Hard Delete duplicate rows keeping one De-duplicate with ROW_NUMBER in a CTE. Analytics Patterns 35 Hard Find consecutive ranges (gaps and islands) Collapse consecutive rows into ranges. Analytics Patterns 36 Hard Month over month growth Compare each period to the previous with LAG. Window Functions 37 Hard Calculate the median salary Find the median with PERCENTILE_CONT. Analytics Patterns 38 Hard Walk an employee hierarchy Traverse a tree with a recursive CTE. CTEs & Recursion 39 Hard Each product as a percent of its category Divide a row by a partitioned SUM OVER. Window Functions 40 Hard Longest consecutive login streak A gaps and islands variant over dates. Analytics Patterns 41 Hard Pivot sales into month columns Turn rows into columns with PIVOT or CASE. Analytics Patterns 42 Hard Three day moving average Average over a sliding window frame. Window Functions 43 Hard Days between each customer order Difference each row from the previous with LAG. Window Functions 44 Hard Highest paid employee per department One row per group with a partitioned ranking. Window Functions 45 Hard Cumulative count of new customers Count first-seen customers over time. CTEs & Recursion