LEARN · SQL · MEDIUM PRACTICE
50 Medium SQL Questions
Correlated subqueries, CASE expressions, CTEs, window functions, and real-world data cleaning. Beacon Robotics' employees from the basic set, plus its clients, orders, and products, used throughout.
Beacon Robotics also sells robot products to outside clients. These tables extend the same schema the basic set used (employees/departments are unchanged). Orders and order_items aren't shown in full here, each question shows the exact rows it needs.
| id | name | |
|---|---|---|
| 1 | Orion Logistics | ops@orionlogistics.com |
| 2 | Meridian Farms | procurement@meridianfarms.com |
| 3 | Harbor Freight Co | NULL |
| 4 | Vertex Robotics Labs | contact@vertexlabs |
| 5 | Summit Manufacturing | buy@summitmfg.com |
| 6 | Bluewave Devices | hello@bluewave.io |
| 7 | Bluewave Devices East | HELLO@bluewave.io |
| 8 | Falcon Transit | hi@falcontransit.com |
| id | name | category | price |
|---|---|---|---|
| 1 | SentinelDrone | Drones | 1200 |
| 2 | TitanArm | Arms | 4500 |
| 3 | PulseSensor Kit | Sensors | 300 |
| 4 | CoreController | Controllers | 150 |
| 5 | TrailRover | Rovers | 2200 |
| 6 | ScoutBeacon GPS Tag | Sensors | 80 |
| 7 | NovaBeacon Charging Dock | Controllers | 45 |
orders(id, client_id, order_date, status, total_amount) · order_items(order_id, product_id, quantity, unit_price) · payments(order_id, amount, status)
0 / 50 marked solved
Self-reported, not auto-graded, there's no live SQL engine behind this page. Check your own query against the shown solution and expected output, then mark it yourself. Saved only in this browser.
Level 1 · Advanced Joins and Subqueries
Q1–10 · IntermediateCorrelated subqueries, NOT EXISTS, self-joins, and multi-row comparisons.
Above Your Own Department's Average
Find every employee earning more than the average salary of their own department.
Clients Who Never Ordered
Find every client who has never placed an order.
Each Client's Latest Order
Find the most recent order placed by each client.
Products Never Ordered
Find every product that has never appeared in an order.
A Genuinely High-Volume Client
Find every client who has placed more than five orders.
Out-Earning Your Own Manager
Find every employee whose salary is higher than their manager's.
Bought Every Product in a Category
Find every client who has purchased every product in the Rovers category (there's only one, TrailRover, so this demonstrates the pattern on the simplest possible case).
Orders With a Mismatched Total
Find every order where the recorded total_amount doesn't match the actual sum of its order_items.
Ordered in Both January and February
Find every client who placed at least one order in January 2026 and at least one in February 2026.
Each Client's Second Order
Find the second order ever placed by each client, by date.
Level 2 · CASE Expressions and Conditional Aggregation
Q11–20 · IntermediateCASE for categorizing, and COUNT/SUM with CASE for splitting one query into several measures.
Salary Categories
Categorize every employee's salary as Low (below 30000), Medium (30000 to 60000), or High (above 60000).
Readable Order Status
Display each order's status code as a readable word.
Three Counts in One Query
Count completed, pending, and cancelled orders, all three in a single query.
Sales by Category
Calculate total sales for each product category, excluding cancelled orders.
Completion Rate
Find the percentage of completed orders out of every order ever placed.
Salary Grades by Threshold
Grade every employee's salary: Grade A (70000+), Grade B (50000+), Grade C (30000+), Grade D otherwise.
Client Tiers by Total Purchases
Categorize each client as Gold (10000+ total purchases), Silver (1000+), or Bronze (below 1000), excluding cancelled orders.
Monthly Sales, Completed vs Pending
Show total completed sales and total pending sales for each calendar month.
Experience Category by Hire Date
For every employee hired before 2023, classify them as Senior (hired before 2020), Experienced (before 2022), or Recent.
Successful vs Failed Payments, Per Order
For every order, total its successful payment amount and its failed payment amount separately.
Level 3 · Common Table Expressions
Q21–28 · IntermediateSingle and chained CTEs for readable multi-step queries.
Above the Company Average, via a CTE
Find every employee earning more than the overall company average salary, naming the average with a CTE.
Revenue Per Client, via a CTE
Find the total revenue generated by each client, excluding cancelled orders, highest first.
Top Three Clients
Find the top three clients by total purchases, using two chained CTEs.
Departments Above the Company Average
Find every department whose average salary is greater than the company's overall average salary, using two CTEs.
Multiple Orders, Same Day
Find every client who placed more than one order on the exact same date.
Category Sales, via Chained CTEs
Find total sales for each product category, using a product-level CTE feeding into a category-level one.
Above the Average for Your Hire Year
Find every employee earning more than the average salary of employees hired in the same calendar year as them.
Every Client, Orders or Not
List every client alongside their order count and total spent, including clients with zero orders.
Level 4 · Window Functions and Ranking
Q29–38 · IntermediateROW_NUMBER, DENSE_RANK, running totals, LAG/LEAD, and NTILE.
Ranking Salaries Within Each Department
Assign a row number to every employee based on salary, restarting for each department.
The Top Earner in Every Department
Find the single highest-paid employee in each department.
Top Two Per Department, Ties Included
Find the top two highest-paid employees in each department, including any tie at second place.
Running Total of Orders
Calculate Orion Logistics' running total of order amounts, in date order.
Previous Order Amount
For Orion Logistics, show each order's amount alongside the previous order's amount.
Next Order Date
For Orion Logistics, show each order's date alongside the date of the next order.
Distance From the Department Average
For every employee, show how far their salary is from their own department's average.
Four Salary Groups
Divide all ten employees into four roughly equal salary groups using NTILE.
Three-Order Moving Average
For Orion Logistics, calculate the moving average of order amount across the current order and the two before it.
Each Client's Share of Total Sales
Calculate what percentage of total company sales each client contributes, excluding cancelled orders.
Level 5 · Date, String, and Data Cleaning
Q39–44 · IntermediateDate ranges, string pattern checks, and finding messy data.
Recently Acquired Clients
Assuming today is 2026-03-20, find every client whose first order was within the last 90 days.
Days Between Consecutive Orders
For Orion Logistics, find the number of days between each order and the one before it.
Invalid Email Addresses
Find every client whose email is missing entirely, or doesn't look like a real email address (no @ or no dot).
Extracting the Email Domain
Extract just the domain portion from every client's email address.
Names With Extra Spaces
Find every client whose name has leading or trailing spaces.
Priced Above the Category Average
Find every product priced higher than the average price within its own category.
Level 6 · Real-World Problem Solving
Q45–50 · IntermediateConsecutive-month detection, duplicate data, and tying several techniques together.
Three Consecutive Months of Orders
Find every client who placed at least one order in each of three consecutive calendar months.
Duplicate Client Emails
Find email addresses that belong to more than one client, even if the capitalization or spacing differs.
Each Product's Share of Its Category
For every product, calculate what percentage of its own category's revenue it's responsible for, excluding cancelled orders.
Orders With Outstanding Balances
Find every order where the successfully paid amount is less than the order's total.
Best-Selling Product Per Category
Find the best-selling product, by quantity, within each category.
Clients Whose Spending Increased Month Over Month
Find every client-month where that client's total spending (excluding cancelled orders) increased compared with the previous calendar month they had any orders in.
- Write your own query first against the tables shown, before revealing the solution.
- A few questions deliberately use database-neutral pseudo-syntax (EXTRACT, interval subtraction); translate it to your actual database's real syntax before running it.
- If your query differs from the shown solution but gets the same result, that's fine, especially for CTEs and window functions, more than one correct shape usually exists.
- Use the lesson link on a question that trips you up before retrying it.
Run These for Real
Create the sample tables above in a free SQL playground, then try each question yourself before checking the solution. Pick one based on which database you want to practice on.
Both open a third-party SQL playground in a new tab. External site terms and privacy policy apply.