You have read about SQL and run queries that someone else wrote. This lab is the other half: you write the query, press Check my work, and a hidden test compares your result with the right answer. There is one small shop database with customers, products and orders. Every exercise starts with the question in plain words and the answer worked out by hand, so you know what the right result looks like before you type a line. Eight exercises cover the tools that most real queries are built from.
Every exercise has the same shape. First comes a short setup in plain words. Then the answer worked out by hand, so you know what the right result looks like. Then a card with the goal, a ladder of hints, and a code editor. The hints appear one at a time, so you can take the smallest nudge that gets you moving. Under the editor sit a Run button, a Check my work button and a Show solution button.
The editor holds a short Python cell. You do not need to know Python. The cell builds the practice database in memory, runs a text called QUERY, and prints the column names and the rows. The only part you edit is the SQL between the triple quotes of QUERY. The database is SQLite, the same kind of engine as the SQL playground in the earlier lessons, so what works here works there. The cell is built fresh on every run, so nothing you do to it lasts, and the hidden tests only ever read data.
Check my work runs your cell, then runs a list of hidden tests one at a time. It tells you how many passed and shows the message of the first one that failed. The starter is built to fail on purpose, so a fresh cell always says some checks failed. When your query is right, all of them pass.
Three things about the tests are worth knowing before you start.
They compare results, not wording. Any query that returns the right columns and rows in the right order passes, however you wrote it.
They check the shape first. The column names must match the ones in the goal, because a name such as total_paid is part of the question.
A few tests also look for the tool the exercise teaches, such as a subquery, OVER or WITH. Those exercises exist to practise the tool, and the message tells you which one.
The result of a query is a table. If you are unsure whether you are right, work out two or three rows by hand from the tables below and compare.
Words you need9 terms
A row is one thing, such as one order. A column is one kind of fact kept for every row, such as the amount.
A new name given to a column or a table with AS, for example SUM(amount) AS total_paid. In this lab the tests check column names, so aliases matter.
A function that turns many rows into one number: COUNT, SUM, AVG, MIN and MAX.
GROUP BY puts rows with the same value into one group. HAVING keeps or drops whole groups after they are summed. WHERE works on single rows before grouping.
A JOIN lines up rows of two tables. ON says which column of one table must equal which column of the other.
The marker for no value. It is not 0 and it is not empty text. A LEFT JOIN fills the columns of a missing match with NULL.
A query written inside parentheses, whose answer the outer query uses, for example an average.
A calculation done across related rows that keeps every row. It is written with OVER, for example a running total.
A common table expression: a step given a name with WITH name AS (...), so the main query can use it like a table.
All eight exercises use the same small shop database. Read it once now, because you will check your hand answers against it.
customers, six rows, one per person.
id
name
city
joined
1
Asha
Hyderabad
2024-01-10
2
Bilal
Pune
2024-02-14
3
Chitra
Hyderabad
2024-03-02
4
Dev
Chennai
2024-03-20
5
Esha
Pune
2024-05-05
6
Farid
Chennai
2024-06-18
products, five rows, one per item for sale.
id
name
category
price
1
Notebook
Stationery
120
2
Pen Set
Stationery
80
3
Desk Lamp
Home
650
4
Backpack
Travel
1400
5
Water Bottle
Home
300
orders, twelve rows, one per order. The amount is the quantity times the price. The status is paid, pending or cancelled. Only a paid order is money received.
id
customer_id
product_id
quantity
amount
order_date
status
1
1
1
3
360
2024-06-01
paid
2
1
4
1
1400
2024-06-15
paid
3
2
3
2
1300
2024-06-03
paid
4
2
2
5
400
2024-07-02
cancelled
Three facts to keep in mind. Ten of the twelve orders are paid, and together they add up to 7920. Order 4 is cancelled and order 7 is pending, so a query that forgets to look at status will quietly include them. Farid, customer 6, has never placed an order, which matters in exercise 4. Dates are written year-month-day, so sorting them as text puts them in date order.
In plain words. WHERE keeps the rows that pass a test, and drops the rest. ORDER BY then puts the kept rows in order. Add DESC after a column to put the biggest first, which for a date means the newest first. When a question needs two things to be true at once, join the tests with AND. Text in a test goes in single quotes, as in status = 'paid'.
The answer by hand. Of the 12 orders, 10 are paid. Of those, six have an amount above 500: orders 2 (1400), 3 (1300), 5 (600), 6 (1200), 8 (800) and 10 (1400). Order 7 is the biggest at 2600, but it is pending, so it does not count. Newest first, the list starts with order 10 on 2024-07-25 and ends with order 3 on 2024-06-03.
Mini project
Filter and sort the orders
~5 min
List the id, amount and order_date of every paid order with an amount above 500, newest first. Return exactly those three columns, in that order. Press Check my work to run the hidden tests.
In plain words. GROUP BY puts all the rows that share a value into one group, and an aggregate such as COUNT or SUM then summarises each group into one row. There are two places to filter. WHERE removes single rows before the grouping happens. HAVING removes whole groups after they have been summed, so it is the only place that can test a total. A good question to ask is: am I filtering a row, or a group?
The answer by hand. Paid orders only, grouped by customer: customer 1 has 3 orders totalling 2080 (360 + 1400 + 320), customer 2 has 2 totalling 1540, customer 3 has 2 totalling 1800, customer 4 has 2 totalling 1100 and customer 5 has 1 totalling 1400. Customer 6 has none. Keeping the totals above 1500 leaves customers 1, 3 and 2. Biggest first, they come in the order 2080, 1800, 1540.
Mini project
Group and filter the groups
~6 min
For each customer, count their paid orders as paid_orders and add them up as total_paid. Keep only customers whose total_paid is above 1500, biggest total first. Return customer_id, paid_orders, total_paid, in that order.
In plain words. An order row holds numbers, not names: customer_id 1 and product_id 4. The names live in other tables. A JOIN lines up each order with its customer and product. The ON part says how: an order matches the customer whose id equals the order's customer_id. Every JOIN needs its own ON. A JOIN with no ON pairs every row with every row, so 12 orders and 6 customers would give 72 rows.
The answer by hand. Order 1 has customer_id 1 and product_id 1. Customer 1 is Asha and product 1 is Notebook, so the first row is (1, Asha, Notebook). Order 2 is Asha and Backpack. There are 10 paid orders, so 10 rows, and sorted by order id the list runs 1, 2, 3, 5, 6, 8, 9, 10, 11, 12.
Mini project
Turn ids into names with joins
~6 min
For every paid order, show order_id, customer (the customer's name) and product (the product's name). Sort by order id. Return exactly those three columns, in that order.
In plain words. An ordinary JOIN keeps only rows that have a match. A LEFT JOIN keeps every row of the left table, and where there is no match it fills the right table's columns with NULL, the marker for no value. That is how you ask about everybody, including people who did nothing. Two details bite. A SUM over nothing is NULL, not 0, so COALESCE(value, 0) swaps a NULL for 0. And a WHERE test on the right table, such as o.status = 'paid', is false for the NULL row, so it throws the unmatched customer away and quietly turns your LEFT JOIN back into an ordinary one. The fix is to put that test in the ON part, where it only decides what counts as a match.
The answer by hand. Six customers, so six rows. Totals of paid orders: Asha 2080, Bilal 1540, Chitra 1800, Dev 1100, Esha 1400 and Farid 0, because Farid has no orders at all. Chitra's total is 1800, not 4400, because her pending order 7 for 2600 is not paid. Sorted by name, Asha comes first and Farid last.
Mini project
Keep customers who have no orders
~7 min
For every customer, show name and total_paid, the sum of their paid orders, with 0 for a customer who has none. Sort by name. Return name and total_paid, in that order. There must be one row for each of the 6 customers.
In plain words. Sometimes the number you need to compare with has to be worked out first, such as an average. A subquery is a complete SELECT written in parentheses inside another query. The inner query runs first and hands its answer to the outer one. This is better than typing the average by hand, because the query stays right when the data changes. Remember that an average depends on which rows it covers, so the inner query needs the same paid-only filter as the outer one.
The answer by hand. The ten paid amounts add up to 7920, so the average paid order is 792. Paid orders above 792 are order 2 (1400), 3 (1300), 6 (1200), 8 (800) and 10 (1400): five rows, sorted by id. Order 8 is close to the line. If you averaged all 12 orders instead, including the pending 2600, the average would be 910 and order 8 would drop out wrongly.
Mini project
Compare each order with the average
~6 min
List the id and amount of every paid order whose amount is above the average amount of paid orders. Work the average out with a subquery, do not type the number. Sort by id. Return id and amount, in that order.
Your codeEdits save in this browser
pythonrunnable cell
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
#Exercise 6: A Running Total With a Window Function
In plain words. GROUP BY squeezes many rows into one. A window function does a calculation across related rows but keeps every row. You write it as a function followed by OVER (...). Inside OVER, PARTITION BY says which rows belong together, here one customer at a time, and ORDER BY says in what order to walk through them. SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date) is a running total: at each row it adds up every amount from the customer's first order up to that row. If you leave ORDER BY out of OVER, every row shows the grand total instead.
The answer by hand. Take Asha, customer 1, in date order: 360 on 2024-06-01, 1400 on 2024-06-15 and 320 on 2024-08-02. The running total is 360, then 360 + 1400 = 1760, then 1760 + 320 = 2080. Her last running total, 2080, equals her total from exercise 2. That is a handy self-check. Dev's two orders give 800 then 1100. The result has 10 rows, one for each paid order.
Mini project
Add up spending as it happens
~7 min
For each paid order, show customer_id, order_date, amount and running_total, the customer's paid spending up to and including that order. Sort by customer_id, then order_date. Return those four columns, in that order.
In plain words. A CTE (common table expression) gives a step of your query a name. You write WITH name AS ( ... ) at the top, and below it the main query can use that name as if it were a table. It makes a query read from top to bottom: first work out each customer's total, then use the totals. It is also handy when you need to use the same intermediate result twice, as this exercise does: once to list the customers and once to work out the average of their totals.
The answer by hand. Step one is the totals of paid orders per customer: 2080, 1540, 1800, 1100 and 1400. Five customers have a total, since Farid has no paid orders and so no row. Step two is their average: 7920 / 5 = 1584. Step three keeps the customers above the average: Asha (2080) and Chitra (1800). Biggest first, Asha comes before Chitra.
Mini project
Name a step with WITH
~7 min
Use a CTE named totals to work out each customer's paid total, then list the name and total_paid of customers whose total is above the average of those totals, biggest first. Return name and total_paid, in that order. Start the query with WITH.
In plain words. Reading and repairing a query is as important as writing one. The starter below is meant to show how much each city has paid, biggest first. It runs without an error and returns no rows at all. There are two separate mistakes. A good way to find them is to test the parts one at a time: remove the WHERE and look at what the join produces, then add the WHERE back and look at what it removes.
The answer by hand. Hyderabad has Asha and Chitra: 2080 + 1800 = 3880. Pune has Bilal and Esha: 1540 + 1400 = 2940. Chennai has Dev and Farid: 1100 + 0 = 1100. Biggest first the rows are Hyderabad, Pune, Chennai, and the three totals add up to 7920, the total of all paid orders. That sum is a useful check for any query of this kind.
Mini project
Find the two mistakes
~6 min
The query in the editor should show city and total_paid for paid orders, biggest total first, but it returns nothing. Fix it so that it returns the three cities with the right totals. There are two mistakes. You may change the query in any way you like.
Every exercise has one common wrong answer that looks right and runs without an error. They fall into four families.
Wrong place for a filter. WHERE works on single rows before grouping, HAVING works on groups after they are summed, and a test on the right-hand table of a LEFT JOIN belongs in ON, not in WHERE. The same test in the wrong place gives a different answer.
A join that does not link. A JOIN with no ON, or an ON that compares the wrong two columns, either multiplies the rows or pairs them wrongly. Neither gives an error. Check the row count against what you expect, and look at one row by hand.
Missing values. A LEFT JOIN fills the gaps with NULL, a sum over nothing is NULL, and a plain test never matches NULL. Decide what a missing value should mean in the answer, often 0, and say so with COALESCE.
The wrong set of rows. An average, a count or a total is only as good as the rows it covers. Forgetting status = 'paid' quietly adds a cancelled and a pending order. Averaging over the wrong group moves the bar, as in exercises 5 and 7.
Symptom
Likely cause
A customer who should appear is missing
A WHERE test on the right-hand table turned a LEFT JOIN into an ordinary join
Totals are far too big, or rows are repeated
A JOIN with no ON, or an ON on the wrong columns
The query returns nothing
A condition that can never be true, such as a wrong spelling or capital letter, or a join on the wrong columns
A total is NULL instead of 0
A sum over no rows, which COALESCE fixes
A total includes cancelled or pending orders
The status test is missing, or sits in the wrong clause
Every row shows the same total in a window function
ORDER BY is missing inside OVER
The numbers are close but not equal to the hand answer
The average or the filter covers a different set of rows
Use this list to check that you are ready to write queries for real questions. Tick each item only if you can do it without looking.
I can filter with WHERE and AND, and sort with ORDER BY and DESC
I can group rows, use COUNT and SUM, and say whether a filter belongs in WHERE or in HAVING
I can join three tables and say what goes wrong if an ON line is missing
I can write a LEFT JOIN that keeps unmatched rows, and say why the test belongs in ON
I can use COALESCE to turn NULL into a number
I can write a subquery that computes an average, and a CTE that names a step
I can write a running total with SUM OVER, PARTITION BY and ORDER BY
I can find a broken join and a wrong piece of text by testing the parts of a query one at a time
Given a query I did not write, I can check its row count and one hand-worked row before I trust it
If you can tick every line, you can answer most everyday questions about a small database. If you cannot, go back to the exercise for that line, open the solution, and write the query again without it.
WHERE filters single rows before grouping, GROUP BY makes one row per group, and HAVING filters the finished groups. A test on a total can only go in HAVING
Every JOIN needs its own ON that compares the matching columns. Without it, rows multiply
A LEFT JOIN keeps every row of the left table, and puts NULL where there is no match. A test on the right table belongs in ON, or the unmatched rows disappear
COALESCE(SUM(x), 0) turns the NULL of an empty sum into 0
A subquery in parentheses computes a number such as an average, so the query stays right when the data changes. The inner query needs the same filter as the outer one
A window function keeps every row. SUM(x) OVER (PARTITION BY a ORDER BY b) is a running total that restarts for each a
A CTE names a step with WITH, so a query reads top to bottom. A query that runs is not proof that it is right: count the rows and check one total by hand
You want customers whose total spend is above 1500. Where does that test go?
Where to next. Revisit SQL Interview Patterns and try its top-N and running-total questions with the habit from this lab: a known total to check against. To use SQL from your own programs, continue to Databases in Python.