Practice Β· lesson 10/13
SQL lab (live judge)
Each exercise is judged live against a fresh SQLite database, the way Quera does it: your result set is compared with the reference. Column count, values, and (when stated) order must match; column names don't matter. Try every exercise before you reveal hints or solutions. Aim for ~2 minutes on easy ones, ~5 on medium and ~8 on hard.
ποΈ Practice databaseclick a table to preview it
id, name, city, signup_date
id, name, category, price
id, customer_id, order_date, status
order_id, product_id, quantity, unit_price
user_id, event_type, ts
id, name, manager_id, dept, salary
user_id, login_date
customer_id, email, updated_at
The engine is SQLite: use strftime/julianday for dates. In the contest, check whether
it's MySQL (DATE_FORMAT, DATEDIFF) and translate.
Warm-up
easy
Revenue per category
order mattersβ solvedFor completed orders only, return
category, revenue where revenue = Ξ£ quantity Γ unit_price. Order by revenue descending.easy
Customers who never ordered
order mattersβ solvedReturn
name of customers with no orders at all (any status), ordered by name.easy
Customers per city (NULL-safe)
order mattersβ solvedReturn
city, n: number of customers per city. A NULL city must appear as 'unknown'. Order by n desc, then city asc.Core data-engineering SQL
medium
Pivot order statuses
order mattersβ solvedFor every customer that has at least one order return
customer_id, completed, cancelled, pending (counts of orders in each status). Order by customer_id.medium
Above department average
order mattersβ solvedReturn
name, dept, salary of employees earning strictly more than the average salary of their department. Order by name.medium
Monthly revenue + running total
order mattersβ solvedCompleted orders only. Return
month (format YYYY-MM), revenue, running_total ordered by month.medium
Best seller per category (ties kept)
order mattersβ solvedFor completed orders, find the product(s) with the highest total quantity sold in each category. Return
category, product, qty, keep ties, order by category.medium
Deduplicate: latest email per customer
order mattersβ solvedcustomer_updates is an append-only change log. Return the latest customer_id, email per customer, ordered by customer_id.medium
Days since previous order (LAG)
order mattersβ solvedFor completed orders return
customer_id, order_id, order_date, days_since_prev (integer days since the same customer's previous completed order; NULL for the first). Order by customer_id, order_date.medium
Second-highest salary per department
order mattersβ solvedReturn
dept, name, salary of the employee(s) whose salary is the second-highest distinct salary in their department. Departments with fewer than two distinct salaries are left out. Order by dept, name.medium
Bought from both categories
order mattersβ solvedReturn
name of customers who bought at least one electronics item and at least one grocery item in completed orders (not necessarily in the same order). Order by name.Hard (contest-level)
hard
Recursive CTE: everyone under Ben
order mattersβ solvedReturn
name, depth of every direct or indirect report of Ben (id 2). Direct reports have depth 1. Order by depth, name.hard
Gaps & islands: longest login streak
order mattersβ solvedReturn
user_id, longest_streak β the longest run of consecutive login days per user. Order by user_id.hard
Sessionize clickstream (30 min gap)
order mattersβ solvedA new session starts when a user's event is more than 30 minutes after their previous event (or is their first). Return
user_id, session_no, session_start, session_end, n_events with session_no starting at 1 per user. Order by user_id, session_no.hard
Median price per category (no MEDIAN function)
order mattersβ solvedReturn
category, median_price from products. With an even number of products the median is the average of the two middle prices. Order by category.When the judge says no
| Verdict | Usual cause | Quick check |
|---|---|---|
| Wrong number of columns | Extra helper column left in the final SELECT (rn, grp) | Select exactly the columns the statement lists, in that order |
| Too many rows | Join fan-out, missing filter, RANK where ROW_NUMBER was meant | SELECT key, COUNT(*) β¦ GROUP BY key HAVING COUNT(*) > 1 on your intermediate CTE |
| Too few rows | INNER JOIN where LEFT was needed, filter in WHERE instead of ON, NOT IN with NULLs | Count rows after each CTE step |
| Right rows, wrong order | Missing tie-breaker, or sorting a string column that holds numbers | Add every column the statement mentions to ORDER BY |
| Values off slightly | Integer division, ROUND placement, NULLs ignored by AVG | Multiply by 1.0; round only in the final SELECT |
Finished the lab? Go to the Mock contest lesson for six new problems against the clock.