Grace cancelled, and we still asked if she’d ordered twice

See the problem

The shop emails customers who may have ordered twice by mistake, so they can cancel one. This query finds them:

The double-order check’s query

SELECT a.customer_id, a.order_id AS order_1, b.order_id AS order_2
FROM orders a
JOIN orders b ON b.customer_id = a.customer_id AND b.order_date = a.order_date
WHERE a.order_id < b.order_id
ORDER BY a.customer_id, a.order_id, b.order_id;

It found Ben’s two orders from 14 October, 109 and 110. Then, on 21 October, Grace ordered a water bottle, cancelled it, and ordered a desk lamp instead: orders 115 and 116.

That evening she got the email too, asking if she’d meant to order twice. She had already cancelled one of them herself.

Define the task

List each pair of orders from the same customer on the same day, where neither order is cancelled: customer_id, order_1 (the smaller order_id) and order_2. Each pair once, by customer_id, then order_1, then order_2.

• Three orders on one day make three pairs.
• An order doesn’t pair with itself.

Pause and think

⏸ Pause and think

The query reads orders twice, as a and as b, and each row is two orders side by side. If you add WHERE a.status <> 'cancelled', which of the two orders does it check?

Give it two minutes with pen and paper before you open a hint.

Hints open one at a time:

Hint 1 of 3

Okay never looks at status, so a cancelled order still makes a pair.

Hint 2 of 3

a.status is the status of the pair’s first order only. If the second order is cancelled, the pair stays.

Hint 3 of 3

a and b are two different orders, so check both: a.status <> 'cancelled' AND b.status <> 'cancelled'.

Understand the idea

A self join reads one table twice. FROM orders a JOIN orders b uses orders as two tables, named a and b. Each row of the result is one order from a beside one order from b. The names are how you say which copy you mean.

Name each copy for its job. In FROM employees e JOIN employees m ON m.employee_id = e.manager_id, e is the employee and m is their manager. Then “earns more than their manager” is WHERE e.salary > m.salary. In the store, nobody does. Give Sam a raise to 7,500, and Sam does: Tom, his manager, is on 7,200.

Each row pairs with itself, and each pair shows up twice. Ben’s orders 109 and 110 are both from 14 October. ON b.customer_id = a.customer_id AND b.order_date = a.order_date gives four rows for them: 109 with 109, 109 with 110, 110 with 109, and 110 with 110. WHERE a.order_id < b.order_id keeps 109 with 110, once. a.order_id <> b.order_id would keep it both ways.

Watch the check run on one day’s orders. Then type your own, like three orders from one customer, or a cancelled order on either side of a pair:

The double-order check, step by step

⤢ Full view

1. FROM orders a
2. JOIN orders b ON …
3. WHERE a.order_id < b.order_id
4. AND a.status <> 'cancelled'
5. AND b.status <> 'cancelled'
6. SELECT
7. ORDER BY

orders a

order_id	customer_id	status
115	2	placed
116	2	placed
117	7	cancelled
118	7	placed
119	3	placed
120	3	cancelled

Step 1 of 7. FROM orders reads 6 orders, all from 21 October, so order_date always matches. The query reads orders a second time, as b, to pair them up. Question: Customer 2 has two orders. The join pairs each of them, as a, with each of them, as b. How many rows does customer 2 get?

What happens next? Customer 2 has two orders. The join pairs each of them, as a, with each of them, as b. How many rows does customer 2 get?

124Just show me

◀ BackPlayNext ▶Reset Ask me first Show as text

1SELECT a.customer_id, a.order_id AS order_1, b.order_id AS order_2
2FROM orders a
3JOIN orders b ON b.customer_id = a.customer_id AND b.order_date = a.order_date
4WHERE a.order_id < b.order_id
5  AND a.status <> 'cancelled'
6  AND b.status <> 'cancelled'
7ORDER BY a.customer_id, a.order_id, b.order_id;

Try your own input

Orders on 21 October: customer_id, status2, placed 2, placed 7, cancelled 7, placed 3, placed 3, cancelledShow it

Each copy has its own columns. a.status and b.status are the statuses of two different orders. A condition on a says nothing about b, so a rule like “not cancelled” needs checking on both.

FULL JOIN keeps rows with no partner on both sides. FROM customers c FULL JOIN events e ON e.customer_id = c.customer_id keeps Chen, who has no events, with NULL for the event. It also keeps the visitor’s page view, event 13, with NULL for the customer. LEFT JOIN would keep only Chen, and RIGHT JOIN only the visitor’s event. The key can be NULL on either side, so take it with coalesce(c.customer_id, e.customer_id).

CROSS JOIN pairs every row with every row. FROM customers CROSS JOIN products gives each of the 8 customers with each of the 8 products: 64 rows. It starts a grid, like every category in every month, which a LEFT JOIN then fills in. A JOIN with ON is a CROSS JOIN that keeps only the rows where ON is true.

Climb the ladder

All three pair each customer’s orders from one day, once each. What changes is which cancelled orders they notice.

Okay

a.order_id < b.order_id

The idea: Pair each customer’s orders from one day, each pair once.

It looks right because neither of Ben’s orders, the store’s only pair, is cancelled.

SELECT a.customer_id, a.order_id AS order_1, b.order_id AS order_2
FROM orders a
JOIN orders b ON b.customer_id = a.customer_id AND b.order_date = a.order_date
WHERE a.order_id < b.order_id
ORDER BY a.customer_id, a.order_id, b.order_id;

Gets right

Pairs with no cancelled order, like Ben’s

Gets wrong

A pair with a cancelled order

Why

It never looks at status, so a cancelled order still makes a pair.

Where it breaks: Grace cancels order 115 and orders 116 instead, and she gets the email. Okay passes 1 of the 3 checks below.

Better

Also a.status <> 'cancelled'

The idea: Skip a pair whose first order is cancelled.

The gap in Okay: a cancelled order still made a pair.

Highlighted lines are new in this rung.

SELECT a.customer_id, a.order_id AS order_1, b.order_id AS order_2
FROM orders a
JOIN orders b ON b.customer_id = a.customer_id AND b.order_date = a.order_date
WHERE a.order_id < b.order_id
  AND a.status <> 'cancelled'
ORDER BY a.customer_id, a.order_id, b.order_id;

Gets right

A pair whose first order is cancelledwas Pairs with no cancelled order, like Ben’s

Gets wrong

A pair whose second order is cancelled

Why

a.status is the first order’s status only. The second order’s is never checked.

What changed from the previous rung

SELECT a.customer_id, a.order_id AS order_1, b.order_id AS order_2
FROM orders a
JOIN orders b ON b.customer_id = a.customer_id AND b.order_date = a.order_date
WHERE a.order_id < b.order_id
  AND a.status <> 'cancelled'
ORDER BY a.customer_id, a.order_id, b.order_id;

Where it breaks: Chen orders a desk lamp twice by mistake, 115 and 116, and cancels 116. In the pair, 116 is b, so Better keeps it, and Chen gets the email. Better passes 2 of 3.

Best

And b.status <> 'cancelled'

The idea: Check both orders of the pair.

The gap in Better: only one copy was checked.

Highlighted lines are new in this rung.

SELECT a.customer_id, a.order_id AS order_1, b.order_id AS order_2
FROM orders a
JOIN orders b ON b.customer_id = a.customer_id AND b.order_date = a.order_date
WHERE a.order_id < b.order_id
  AND a.status <> 'cancelled'
  AND b.status <> 'cancelled'
ORDER BY a.customer_id, a.order_id, b.order_id;

Gets right

Every checkwas A pair whose first order is cancelled

Gets wrong

Nothing the checks try

Why

a and b are two different orders, so each gets its own check.

What changed from the previous rung

SELECT a.customer_id, a.order_id AS order_1, b.order_id AS order_2
FROM orders a
JOIN orders b ON b.customer_id = a.customer_id AND b.order_date = a.order_date
WHERE a.order_id < b.order_id
  AND a.status <> 'cancelled'
  AND b.status <> 'cancelled'
ORDER BY a.customer_id, a.order_id, b.order_id;

When Best isn’t best: with three orders on one day, the email lists three pairs. For one email per customer and day, skip the self join: GROUP BY customer_id, order_date HAVING count(*) > 1 on the orders that aren’t cancelled, with string_agg(order_id::text, ', ') for the list.

Write it yourself

Now write it in the page. Press Run query to see your result and check it. Postgres runs in your browser, so nothing is sent anywhere. Each check changes a few rows first, then they’re put back.

Try Okay from the ladder first, and open a failed check to see what it changed.

The double-order check

Ctrl + Enter runs

The tables

• orders (order_id, customer_id, order_date, status, total)

-- Pairs of orders from one customer on one day, neither of them cancelled. SELECT a.order_id, b.order_id FROM orders a JOIN orders b ON b.customer_id = a.customer_id;

Run queryShow the stepsReset queryLoads about 5 MB the first time. Best on Wi-Fi.

1. ○Not run yet: The store’s data (row order matters)
2. ○Not run yet: Grace orders a water bottle, cancels it, and orders a desk lamp instead, the same day (row order matters)
   What this check changes

   INSERT INTO orders (order_id, customer_id, order_date, status, total)
   VALUES (115, 7, '2026-10-21', 'cancelled', 12.00),
          (116, 7, '2026-10-21', 'placed', 24.99);
   INSERT INTO order_items (order_id, product_id, quantity, unit_price)
   VALUES (115, 4, 1, 12.00),
          (116, 3, 1, 24.99);

3. ○Not run yet: Chen orders a desk lamp twice by mistake, and cancels the second order (row order matters)
   What this check changes

   INSERT INTO orders (order_id, customer_id, order_date, status, total)
   VALUES (115, 3, '2026-10-21', 'placed', 24.99),
          (116, 3, '2026-10-21', 'cancelled', 24.99);
   INSERT INTO order_items (order_id, product_id, quantity, unit_price)
   VALUES (115, 3, 1, 24.99),
          (116, 3, 1, 24.99);

Each check runs on its own copy of the data, so your query can’t change the real tables.

Compare

Way	Gets right	Gets wrong	Use it when
Okay: a.order_id < b.order_id	Pairs with no cancelled order	A pair with a cancelled order	Orders are never cancelled
Better: and a.status <> 'cancelled'	A pair whose first order is cancelled	A pair whose second order is cancelled	Never on its own: b needs the same check
Best: and b.status <> 'cancelled'	Every check	Nothing the checks try	Both rows of a pair must pass a rule

Common mistakes and edge cases

!A condition on one copy says nothing about the other

In a self join, a.status and b.status belong to two different rows. Checking only a lets a pair through when b is cancelled. Write each condition for both copies.

• <> lists each pair twice. a.employee_id <> b.employee_id keeps Kai with Lea, and Lea with Kai. < keeps the pair once. Practice problem 2.
• Every row pairs with itself. Without a.order_id < b.order_id, or at least <>, each order is its own double.
• Mixing up the copies flips the answer. ON m.employee_id = e.manager_id makes m the manager. Write ON m.manager_id = e.employee_id instead, and m is someone e manages: WHERE e.salary > m.salary then lists each manager once for each person they manage. Practice problem 1.
• After a FULL JOIN, the key can be NULL on either side. Take it with coalesce(o.customer_id, c.customer_id), or join with USING (customer_id), which merges the two. Practice problem 3.
• A WHERE on one side undoes a FULL JOIN. The rows with no partner have NULL there, so WHERE c.checkouts > 0 drops the customers with no checkout. Filter each side before the join, in its own query.
• A missing join condition is a CROSS JOIN. FROM orders, payments with no WHERE pairs each of the 14 orders with each of the 13 payments: 182 rows. When a result has far more rows than you expect, check that each table has its condition.

Say it in the interview

Questions to ask first

• Can a row pair with itself? Should a pair show up once, or both ways?
• Does a rule like “not cancelled” apply to both rows of a pair?
• When comparing two lists, can a key be missing from either one?

A 60-second walkthrough

“I join orders to itself, as a and b, on the same customer and day. That pairs each order with itself and lists each real pair twice, so I keep a.order_id < b.order_id: each pair once, the smaller id first. Neither order may be cancelled, and a and b are different rows, so I check a.status and b.status both. Then I sort by customer and the two ids.”

Likely follow-ups

• Who earns more than their manager? FROM employees e JOIN employees m ON m.employee_id = e.manager_id WHERE e.salary > m.salary.
• What’s the difference between LEFT, RIGHT and FULL JOIN? LEFT keeps every row of the left table, RIGHT every row of the right one, and FULL both. The side with no partner gets NULLs.
• How many rows does a CROSS JOIN give? The two row counts multiplied: 8 customers and 8 products give 64.

Practice

Three problems, from easy to medium. Each has hints, checks you can run here, and its own Okay, Better and Best answers. Try before you open them.

1. Paid as much as their manager (easy)

HR’s rule: everyone earns less than their manager. List everyone who breaks it, equal pay included: employee, salary, manager and manager_salary, the biggest gap first, then by the employee’s name. It’s the classic “employees who out-earn their manager”, with equal pay counted too.

Hint 1 of 3

Join employees to itself: e for the employee and m for their manager, ON m.employee_id = e.manager_id.

Hint 2 of 3

Breaking the rule means earning as much or more: e.salary >= m.salary, not >.

Hint 3 of 3

Two people can be the same amount over. Add e.name to the ORDER BY.

Paid as much as their manager

Ctrl + Enter runs

The tables

• employees (employee_id, name, department_id, manager_id, salary, hired_on)

SELECT e.name AS employee, e.salary, m.name AS manager FROM employees e JOIN employees m ON m.employee_id = e.manager_id;

Run queryShow the stepsReset queryLoads about 5 MB the first time. Best on Wi-Fi.

1. ○Not run yet: The store’s data: everyone earns less than their manager (row order matters)
2. ○Not run yet: Jon gets a raise to 6,800, the same as Priya, his manager (row order matters)
   What this check changes

   UPDATE employees SET salary = 6800.00 WHERE employee_id = 8;

3. ○Not run yet: Tom and Ana both get a raise to 9,500, the same as Meera, their manager (row order matters)
   What this check changes

   UPDATE employees SET salary = 9500.00 WHERE employee_id IN (2, 3);

Each check runs on its own copy of the data, so your query can’t change the real tables.

Show the Okay, Better and Best answers

Okay

e.salary > m.salary

The idea: Keep the people paid more than their manager.

Okay passes 1 of 3. When Jon gets a raise to 6,800, the same as Priya, Okay doesn’t list him.

SELECT e.name AS employee, e.salary, m.name AS manager, m.salary AS manager_salary
FROM employees e
JOIN employees m ON m.employee_id = e.manager_id
WHERE e.salary > m.salary
ORDER BY e.salary - m.salary DESC;

Gets right

Nobody on their manager’s pay or more, as in the store

Gets wrong

Someone paid the same as their manager

Why

> leaves out equal pay, which breaks the rule too.

Better

e.salary >= m.salary

The idea: Count equal pay too.

The gap in Okay: > missed equal pay.

Highlighted lines are new in this rung.

SELECT e.name AS employee, e.salary, m.name AS manager, m.salary AS manager_salary
FROM employees e
JOIN employees m ON m.employee_id = e.manager_id
WHERE e.salary >= m.salary
ORDER BY e.salary - m.salary DESC;

Gets right

Equal pay

Gets wrong

Two people the same amount over

Why

ORDER BY the gap leaves a tie in whatever order the rows came out.

What changed from the previous rung

SELECT e.name AS employee, e.salary, m.name AS manager, m.salary AS manager_salary
FROM employees e
JOIN employees m ON m.employee_id = e.manager_id
WHERE e.salary > m.salary
WHERE e.salary >= m.salary
ORDER BY e.salary - m.salary DESC;

Where it breaks: Tom and Ana both get a raise to 9,500, Meera’s pay. Both are 0 over, and Better lists Tom first. Better passes 2 of 3.

Best

ORDER BY the gap DESC, e.name

The idea: Break a tie on the gap by name.

The gap in Better: a tie on the gap.

Highlighted lines are new in this rung.

SELECT e.name AS employee, e.salary, m.name AS manager, m.salary AS manager_salary
FROM employees e
JOIN employees m ON m.employee_id = e.manager_id
WHERE e.salary >= m.salary
ORDER BY e.salary - m.salary DESC, e.name;

Gets right

Every check

Gets wrong

Nothing the checks try

Why

Ana and Tom tie on the gap, and e.name puts Ana first.

What changed from the previous rung

SELECT e.name AS employee, e.salary, m.name AS manager, m.salary AS manager_salary
FROM employees e
JOIN employees m ON m.employee_id = e.manager_id
WHERE e.salary >= m.salary
ORDER BY e.salary - m.salary DESC;
ORDER BY e.salary - m.salary DESC, e.name;

When Best isn’t best: if HR means only people paid more than their manager, the classic question, > is right. Ask which one they mean. And two people with the same name would still tie: add e.employee_id last.

2. Onboarding buddies (medium)

People who join the same department on the same day do their onboarding together. List each pair once: department_id, buddy_1 and buddy_2, with the smaller employee_id as buddy_1. By department_id, then the two ids.

Hint 1 of 3

Join employees to itself on the same hired_on. Each person pairs with themselves, too.

Hint 2 of 3

a.employee_id <> b.employee_id lists each pair twice, once each way. a.employee_id < b.employee_id lists it once.

Hint 3 of 3

Buddies are in the same department: add b.department_id = a.department_id to ON.

Onboarding buddies

Ctrl + Enter runs

The tables

• employees (employee_id, name, department_id, manager_id, salary, hired_on)

SELECT a.name, b.name, a.hired_on FROM employees a JOIN employees b ON b.hired_on = a.hired_on;

Run queryShow the stepsReset queryLoads about 5 MB the first time. Best on Wi-Fi.

1. ○Not run yet: The store’s data: nobody joined on the same day (row order matters)
2. ○Not run yet: Kai and Lea both join Support on 2 November (row order matters)
   What this check changes

   INSERT INTO employees (employee_id, name, department_id, manager_id, salary, hired_on)
   VALUES (11, 'Kai Tanaka', 2, 5, 4700.00, '2026-11-02'),
          (12, 'Lea Fischer', 2, 5, 4700.00, '2026-11-02');

3. ○Not run yet: Kai and Lea join Support, and Nina joins Sales, all on 2 November (row order matters)
   What this check changes

   INSERT INTO employees (employee_id, name, department_id, manager_id, salary, hired_on)
   VALUES (11, 'Kai Tanaka', 2, 5, 4700.00, '2026-11-02'),
          (12, 'Lea Fischer', 2, 5, 4700.00, '2026-11-02');
   INSERT INTO employees (employee_id, name, department_id, manager_id, salary, hired_on)
   VALUES (13, 'Nina Rossi', 3, 7, 4800.00, '2026-11-02');

Each check runs on its own copy of the data, so your query can’t change the real tables.

Show the Okay, Better and Best answers

Okay

a.employee_id <> b.employee_id

The idea: Pair people who joined on one day, but not with themselves.

Okay passes 1 of 3. When Kai and Lea both join Support on 2 November, it lists them twice.

SELECT a.department_id, a.name AS buddy_1, b.name AS buddy_2
FROM employees a
JOIN employees b ON b.hired_on = a.hired_on AND b.employee_id <> a.employee_id
ORDER BY a.department_id, a.employee_id, b.employee_id;

Gets right

Nobody joining on the same day, as in the store

Gets wrong

Two people joining on one day

Why

<> keeps both ways round: Kai with Lea, and Lea with Kai.

Better

a.employee_id < b.employee_id

The idea: Keep each pair once, the smaller id first.

The gap in Okay: each pair twice.

Highlighted lines are new in this rung.

SELECT a.department_id, a.name AS buddy_1, b.name AS buddy_2
FROM employees a
JOIN employees b ON b.hired_on = a.hired_on AND b.employee_id > a.employee_id
ORDER BY a.department_id, a.employee_id, b.employee_id;

Gets right

Two people joining on one day

Gets wrong

People in different departments joining on one day

Why

Nothing in ON says the same department.

What changed from the previous rung

SELECT a.department_id, a.name AS buddy_1, b.name AS buddy_2
FROM employees a
JOIN employees b ON b.hired_on = a.hired_on AND b.employee_id <> a.employee_id
JOIN employees b ON b.hired_on = a.hired_on AND b.employee_id > a.employee_id
ORDER BY a.department_id, a.employee_id, b.employee_id;

Where it breaks: Nina joins Sales on the day Kai and Lea join Support. Better pairs Nina with both of them. Better passes 2 of 3.

Best

And b.department_id = a.department_id

The idea: Pair only people in the same department.

The gap in Better: a missing condition in ON.

Highlighted lines are new in this rung.

SELECT a.department_id, a.name AS buddy_1, b.name AS buddy_2
FROM employees a
JOIN employees b
  ON b.department_id = a.department_id
 AND b.hired_on = a.hired_on
 AND b.employee_id > a.employee_id
ORDER BY a.department_id, a.employee_id, b.employee_id;

Gets right

Every check

Gets wrong

Nothing the checks try

Why

A pair is now two different people in one department who joined on one day.

What changed from the previous rung

SELECT a.department_id, a.name AS buddy_1, b.name AS buddy_2
FROM employees a
JOIN employees b ON b.hired_on = a.hired_on AND b.employee_id > a.employee_id
JOIN employees b
  ON b.department_id = a.department_id
 AND b.hired_on = a.hired_on
 AND b.employee_id > a.employee_id
ORDER BY a.department_id, a.employee_id, b.employee_id;

When Best isn’t best: when three people join one department on one day, it lists three pairs. To make them one group, use GROUP BY department_id, hired_on HAVING count(*) > 1 with string_agg(name, ', ' ORDER BY employee_id).

3. Checkouts against orders (medium)

Tracking started on 11 October. The data team checks that each customer’s number of orders since then matches their number of checkout events. List every customer in either list: customer_id, orders and checkouts, 0 where there are none, by customer_id.

The starter already counts both, each in a WITH. WITH names a query, so you can join it like a table.

Hint 1 of 3

An inner JOIN keeps only customers in both lists. A customer missing from one of them is the mismatch you’re looking for.

Hint 2 of 3

LEFT JOIN keeps every customer with orders, but not one who only checked out.

Hint 3 of 3

FULL JOIN keeps both. Then customer_id can be NULL on either side: coalesce(o.customer_id, c.customer_id), and coalesce each count to 0.

Checkouts against orders

Ctrl + Enter runs

The tables

• orders (order_id, customer_id, order_date, status, total)
• events (event_id, customer_id, event_type, occurred_at)

WITH o AS ( SELECT customer_id, count(*) AS orders FROM orders WHERE order_date >= '2026-10-11' GROUP BY customer_id ), c AS ( SELECT customer_id, count(*) AS checkouts FROM events WHERE event_type = 'checkout' GROUP BY customer_id ) SELECT * FROM o;

Run queryShow the stepsReset queryLoads about 5 MB the first time. Best on Wi-Fi.

1. ○Not run yet: The store’s data: every customer’s orders and checkouts match (row order matters)
2. ○Not run yet: Dana orders a desk lamp by phone, so there’s no checkout event (row order matters)
   What this check changes

   INSERT INTO orders (order_id, customer_id, order_date, status, total)
   VALUES (115, 4, '2026-10-21', 'placed', 24.99);
   INSERT INTO order_items (order_id, product_id, quantity, unit_price)
   VALUES (115, 3, 1, 24.99);

3. ○Not run yet: Chen checks out, but the payment fails, so there’s no order (row order matters)
   What this check changes

   INSERT INTO events (event_id, customer_id, event_type, occurred_at)
   VALUES (21, 3, 'checkout', '2026-10-21 10:15:00');

Each check runs on its own copy of the data, so your query can’t change the real tables.

Show the Okay, Better and Best answers

Okay

JOIN

The idea: Put each customer’s two counts side by side.

Okay passes 1 of 3. When Dana orders by phone, with no checkout event, she’s missing.

WITH o AS (
  SELECT customer_id, count(*) AS orders
  FROM orders
  WHERE order_date >= '2026-10-11'
  GROUP BY customer_id
), c AS (
  SELECT customer_id, count(*) AS checkouts
  FROM events
  WHERE event_type = 'checkout'
  GROUP BY customer_id
)
SELECT o.customer_id, o.orders, c.checkouts
FROM o
JOIN c ON c.customer_id = o.customer_id
ORDER BY o.customer_id;

Gets right

Customers in both lists, as in the store

Gets wrong

Orders with no checkout

Why

An inner JOIN drops a customer missing from either list, which is just the mismatch the team wants to see.

Better

LEFT JOIN

The idea: Keep every customer with orders, with 0 checkouts when there are none.

The gap in Okay: a customer with orders but no checkout was dropped.

Highlighted lines are new in this rung.

WITH o AS (
  SELECT customer_id, count(*) AS orders
  FROM orders
  WHERE order_date >= '2026-10-11'
  GROUP BY customer_id
), c AS (
  SELECT customer_id, count(*) AS checkouts
  FROM events
  WHERE event_type = 'checkout'
  GROUP BY customer_id
)
SELECT o.customer_id, o.orders, coalesce(c.checkouts, 0) AS checkouts
FROM o
LEFT JOIN c ON c.customer_id = o.customer_id
ORDER BY o.customer_id;

Gets right

Orders with no checkout

Gets wrong

A checkout with no order

Why

LEFT JOIN keeps every customer in o, the orders, but none who are only in c.

What changed from the previous rung

WITH o AS (
  SELECT customer_id, count(*) AS orders
  FROM orders
  WHERE order_date >= '2026-10-11'
  GROUP BY customer_id
), c AS (
  SELECT customer_id, count(*) AS checkouts
  FROM events
  WHERE event_type = 'checkout'
  GROUP BY customer_id
)
SELECT o.customer_id, o.orders, c.checkouts
SELECT o.customer_id, o.orders, coalesce(c.checkouts, 0) AS checkouts
FROM o
JOIN c ON c.customer_id = o.customer_id
LEFT JOIN c ON c.customer_id = o.customer_id
ORDER BY o.customer_id;

Where it breaks: Chen checks out, but the payment fails, so there’s no order. Chen isn’t on the list. Better passes 2 of 3.

Best

FULL JOIN with coalesce

The idea: Keep customers from both lists, and take the customer_id from whichever has it.

The gap in Better: a customer only in the checkouts was dropped.

Highlighted lines are new in this rung.

WITH o AS (
  SELECT customer_id, count(*) AS orders
  FROM orders
  WHERE order_date >= '2026-10-11'
  GROUP BY customer_id
), c AS (
  SELECT customer_id, count(*) AS checkouts
  FROM events
  WHERE event_type = 'checkout'
  GROUP BY customer_id
)
SELECT coalesce(o.customer_id, c.customer_id) AS customer_id,
       coalesce(o.orders, 0) AS orders,
       coalesce(c.checkouts, 0) AS checkouts
FROM o
FULL JOIN c ON c.customer_id = o.customer_id
ORDER BY customer_id;

Gets right

Every check

Gets wrong

Nothing the checks try

Why

FULL JOIN keeps rows with no partner from both sides, and coalesce fills in the key and the 0s.

What changed from the previous rung

WITH o AS (
  SELECT customer_id, count(*) AS orders
  FROM orders
  WHERE order_date >= '2026-10-11'
  GROUP BY customer_id
), c AS (
  SELECT customer_id, count(*) AS checkouts
  FROM events
  WHERE event_type = 'checkout'
  GROUP BY customer_id
)
SELECT o.customer_id, o.orders, coalesce(c.checkouts, 0) AS checkouts
SELECT coalesce(o.customer_id, c.customer_id) AS customer_id,
       coalesce(o.orders, 0) AS orders,
       coalesce(c.checkouts, 0) AS checkouts
FROM o
LEFT JOIN c ON c.customer_id = o.customer_id
ORDER BY o.customer_id;
FULL JOIN c ON c.customer_id = o.customer_id
ORDER BY customer_id;

When Best isn’t best: FULL JOIN c USING (customer_id) gives one customer_id column, already merged, so SELECT customer_id works with no coalesce. And a guest’s checkout has no customer: it would come back as a row with a NULL customer_id, which you may want to leave out of c first.

Check yourself

A few quick questions. Ones you miss are remembered, so you can find your weak spots later.

1. Predict the outputBen (customer 2) has four orders. Two of them, 109 and 110, are from 14 October. What does this give?

   SELECT count(*)
   FROM orders a
   JOIN orders b ON b.customer_id = a.customer_id AND b.order_date = a.order_date
   WHERE a.customer_id = 2;

   2461

   Check answer
2. Spot the bugThis should list, once, each pair of people in one department who earn the same. What happens to Jon and Zoe, both in Sales on 4,900?

   SELECT a.name, b.name
   FROM employees a
   JOIN employees b ON b.department_id = a.department_id AND b.salary = a.salary
   WHERE a.employee_id <> b.employee_id;

   They’re listed onceThey’re not listedEach is paired only with themselvesThey’re listed twice, once each way

   Check answer
3. Predict the outputThe store has 8 customers and 20 events. Chen (customer 3) has no events, and event 13 is a visitor’s, with no customer. What does this give?

   SELECT count(*)
   FROM customers c
   FULL JOIN events e ON e.customer_id = c.customer_id;

   202128160

   Check answer
4. Predict the outputThe store has 14 orders and 13 payments. Someone forgot the WHERE. What does this give?

   SELECT count(*)
   FROM orders, payments;

   182271413

   Check answer
5. Multiple choiceWith FROM employees e JOIN employees m ON m.employee_id = e.manager_id, which finds the people who earn more than their manager?

   WHERE m.salary > e.salaryWHERE e.salary > e.manager_idWHERE e.salary > m.salaryWHERE e.salary > max(m.salary)

   Check answer

Flashcards

Add these to your review deck. Each card comes back a little later every time you get it right.

• Card 1What is a self join?Show answer
• Card 2In a self join, why add a.id < b.id?Show answer
• Card 3In a self join, does WHERE a.status <> 'cancelled' check both rows?Show answer
• Card 4What does FULL JOIN keep?Show answer
• Card 5After a FULL JOIN, how do you get the key column?Show answer
• Card 6How many rows does a CROSS JOIN give?Show answer

Add 6 cards to my review deck

Cheat sheet

• A self join reads one table twice. Name the copies for their job, like e and m.
• a.id < b.id keeps each pair once, and never pairs a row with itself.
• A condition on a says nothing about b: check both copies.
• FULL JOIN keeps rows with no partner on both sides. coalesce the key, or join with USING.
• CROSS JOIN pairs every row with every row: the row counts multiplied.

Go deeper

• In the PostgreSQL docs: joined tables, with FULL and CROSS JOIN, and the tutorial on joins between tables, which ends with a self join.
• Try your own orders: the double-order check on the SQL visualizer, or any query of your own, step by step. “Show the steps” under each editor draws a self join too.
• Next on the SQL roadmap: the whole map, from your first SELECT to window functions.

Can you do these now?

If you’re not sure of any of these, scroll back to that part before moving on.

• Join a table to itself, with names that say which copy is which
• Keep each pair once with a.id < b.id
• Check a condition on both copies of a pair
• Say which rows FULL JOIN and CROSS JOIN give

✓Mark as completeI already know this

Status: Not started