SQL practice: 42 exercises with instant answers
Real business questions on a made-up company's data. Write a query, press Check, and see straight away whether you got it. Stuck? Every exercise has a hint and an answer, and links to the lesson that explains it.
The practice data (7 tables)
Basics: look, filter, sort
1. A database is just a stack of spreadsheets
Show just the name and country of every customer.
List the two column names after SELECT, with a comma between them.
2. Filter rows with WHERE
Find every customer from India who signed up in or after 2026-04-01. Show their name and signup_date.
Two conditions joined with AND: one on country, one on signup_date with >=.
3. Sort with ORDER BY
List the 5 most expensive products: name and unit_price, most expensive first.
ORDER BY unit_price DESC, then LIMIT 5.
4. Remove Duplicates is DISTINCT
List each different event_name in the events table, A to Z.
SELECT DISTINCT event_name FROM events, then ORDER BY.
5. SQL WHERE: filter the rows you want
Find orders from the Europe or Latin America regions with a quantity above 4. Show id, region and quantity.
region IN ('Europe', 'Latin America') AND quantity > 4.
6. SQL ORDER BY: sort your results
List the 10 most recent signups: name and signup_date, newest first. Break ties by name A to Z.
ORDER BY signup_date DESC, name, then LIMIT 10.
7. SQL DISTINCT: remove duplicate rows
List every different plan and mrr combination in subscriptions, cheapest first.
SELECT DISTINCT plan, mrr ... ORDER BY mrr.
8. Check if text contains a word in SQL
Find customers whose name contains an anywhere. Show their names.
WHERE name LIKE '%an%'.
Totals and pivots
9. SUM, COUNT and AVERAGE
How many different customers have placed an order? Call the answer buyers.
COUNT(DISTINCT customer_id).
10. COUNTIF and SUMIF are WHERE plus a total
Count the support tickets with priority urgent. Call it urgent_tickets.
FROM tickets WHERE priority = 'urgent', then COUNT(*).
11. A pivot table is GROUP BY
How many customers came from each acquisition_channel? Show the channel and the count as customers, biggest first.
GROUP BY acquisition_channel, COUNT(*), then ORDER BY the count DESC.
12. Filter a pivot table with HAVING
Which customers have placed 3 or more orders? Show customer_id and the count as orders.
GROUP BY customer_id, then HAVING COUNT(*) >= 3.
13. IF is CASE WHEN
Label each subscription 'paying a lot' when mrr is 100 or more, otherwise 'standard', as a column called tier. Count subscriptions per tier as subs.
CASE WHEN mrr >= 100 THEN ... ELSE ... END AS tier, then GROUP BY tier.
14. SQL COUNT: count rows, values and blanks
In one row: total subscriptions, how many are cancelled (have a cancelled_at), and how many different customers have one.
COUNT(*), COUNT(cancelled_at), COUNT(DISTINCT customer_id).
15. SQL COUNT DISTINCT: count unique values
How many different products has each region sold? Columns region and products.
COUNT(DISTINCT product_id) with GROUP BY region.
16. SQL GROUP BY: totals per category
How many tickets did each agent handle, per priority? Columns agent, priority, tickets.
GROUP BY agent, priority with COUNT(*).
17. HAVING vs WHERE in SQL
Which countries have more than 20 customers who came from paid_search? Show country and customers.
WHERE acquisition_channel = 'paid_search', GROUP BY country, HAVING COUNT(*) > 20.
18. SQL CASE WHEN: IF logic in a query
Label each order 'big' when quantity is 4 or more, otherwise 'small', as size, and count orders per size as orders.
CASE WHEN quantity >= 4 THEN ... ELSE ... END AS size, then GROUP BY size.
19. IF in SQL: IIF, IF() and CASE
For each subscription show id and a column status that says 'cancelled' when cancelled_at has a value, otherwise 'active'.
IIF(cancelled_at IS NOT NULL, 'cancelled', 'active').
20. Pivot in SQL: turn rows into columns
Tickets per agent with priorities as columns: urgent, normal, low (counts).
GROUP BY agent, and one SUM(CASE WHEN priority = '...' THEN 1 ELSE 0 END) per priority.
Joining tables (VLOOKUP)
21. VLOOKUP is a JOIN
Total revenue per product category: join orders to products and sum quantity * unit_price as revenue, highest first.
JOIN products p ON p.id = o.product_id, GROUP BY p.category, SUM(o.quantity * p.unit_price).
22. SQL JOIN: combine two tables (like VLOOKUP)
Show each order's id, the product name and the quantity, by joining orders to products.
JOIN products p ON p.id = o.product_id.
23. SQL LEFT JOIN: keep rows with no match
How many customers have no subscription? Call it never_paid.
LEFT JOIN subscriptions, keep rows WHERE s.id IS NULL, then COUNT(*).
Text and dates
24. Text functions: LEFT, CONCAT, UPPER, FIND
Show each customer's name and the first name only, as first_name (everything before the space).
instr(name, ' ') finds the space. Take substr from 1 up to one before it.
25. Dates: months, weeks and days between
How many orders were placed in each month? Show month (like 2026-03) and orders, in date order.
strftime('%Y-%m', order_date) AS month, GROUP BY month, ORDER BY month.
26. SQL SUBSTRING: cut out part of text
Show each order's order_date and just its month number as month_number (characters 6 and 7 of the date).
SUBSTRING(order_date, 6, 2).
27. SQL CONCAT: join text together
For each ticket build summary like urgent ticket handled by Asha from priority and agent.
CONCAT(priority, ' ticket handled by ', agent).
28. Days between two dates in SQL (DATEDIFF)
Average days from signup_date to a subscription's started_at, as avg_days_to_pay, rounded to 1 decimal.
JOIN subscriptions to customers, AVG(julianday(s.started_at) - julianday(c.signup_date)).
29. Add days or months to a date in SQL
Orders placed in the 7 days up to and including 2026-06-30: show id and order_date. (Hint: compare with a date 6 days earlier.)
order_date >= date('2026-06-30', '-6 days') AND order_date <= '2026-06-30'.
30. Format dates in SQL (month, year, weekday)
Which weekday gets the most orders? Show weekday_number (strftime '%w') and orders, most first.
GROUP BY strftime('%w', order_date), ORDER BY the count DESC.
Analysis: rankings, running totals, metrics
31. SQL window functions, explained simply
Each product's share of total units sold: product_id, units, and pct_of_total (units ÷ all units × 100, rounded to 1 decimal).
SUM(SUM(quantity)) OVER () is the grand total on every row.
32. RANK, DENSE_RANK and ROW_NUMBER in SQL
Rank customers by number of orders, most first: customer_id, orders and order_rank using DENSE_RANK. Keep only the top 3 ranks.
Count orders per customer in a WITH, add DENSE_RANK() OVER (ORDER BY orders DESC), then keep order_rank <= 3.
33. Running total (cumulative sum) in SQL
Cumulative marketing spend by month: month, monthly spend, and spend_to_date, in month order.
GROUP BY month, then SUM(SUM(spend)) OVER (ORDER BY month).
34. Weekly and monthly active users (WAU, MAU)
Monthly active users: count distinct customers with a login each month. Columns month and mau, in order.
Same as the example, but GROUP BY strftime('%Y-%m', event_date).
35. Monthly recurring revenue (MRR) by month
Simpler check: what is MRR today, meaning the sum of mrr for subscriptions with no cancelled_at? Call it current_mrr.
WHERE cancelled_at IS NULL, then SUM(mrr).
36. Monthly churn rate
How many subscriptions were cancelled in each month? Columns month and cancellations, in order, skipping active ones.
WHERE cancelled_at IS NOT NULL, GROUP BY strftime('%Y-%m', cancelled_at).
37. Rolling 7-day average
Running total of signups: for each signup_date, show daily signups and total_so_far, in date order.
SUM(COUNT(*)) OVER (ORDER BY signup_date) adds up every day so far.
38. Conversion funnel
What percentage of customers who signed up went on to created_report? Return one number called pct, rounded to 1 decimal.
Divide the created_report count by the signed_up count, times 100.0, inside ROUND(..., 1).
39. Retention by signup cohort
Cohort sizes: how many customers signed up each month? Columns cohort and customers, in order.
GROUP BY strftime('%Y-%m', signup_date).
40. Customer acquisition cost (CAC) by channel
Total marketing spend per month, as spend, in month order.
GROUP BY month on the campaigns table.
41. Top 3 in every group
For each country, find the customer who signed up first. Show country, name and signup_date.
ROW_NUMBER() OVER (PARTITION BY country ORDER BY signup_date, id), keep rn = 1.
42. Ticket resolution time and throughput
Throughput: how many tickets did each agent close? Column closed, most first.
WHERE closed_at IS NOT NULL, GROUP BY agent.