Grace got the window-shopper email twice

See the problem

The shop emails “window shoppers”: customers who viewed a page but never added anything to their cart. The list came from this query:

The window-shopper list

SELECT c.customer_id, c.name
FROM customers c
JOIN events v ON v.customer_id = c.customer_id
  AND v.event_type = 'page_view'
WHERE c.customer_id NOT IN (
  SELECT customer_id
  FROM events
  WHERE event_type = 'add_to_cart'
)
ORDER BY c.customer_id;

It listed Elif, Grace and Hugo, once each, and the list was right.

Then Grace came back and viewed another page. The next list had Grace twice, so she got the email twice.

Define the task

List the customers who have a page_view event but no add_to_cart event: customer_id and name, by customer_id.

• Each customer comes once, however many pages they viewed.
• Guests who weren’t signed in have NULL as their customer_id. They can’t get an email, and they mustn’t change who does.

Pause and think

⏸ Pause and think

The query joins customers to their page views. Grace has two page views now. How many rows does the JOIN make for her?

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

Hints open one at a time:

Hint 1 of 3

A JOIN gives one row for each match. Grace has two page views, so she has two rows.

Hint 2 of 3

You only want to know whether a page view exists. EXISTS (SELECT 1 FROM events v WHERE v.customer_id = c.customer_id AND …) keeps each customer once.

Hint 3 of 3

events.customer_id can be NULL. A NULL in a NOT IN list makes it keep no one. NOT EXISTS doesn’t have that problem.

Understand the idea

EXISTS asks “is there at least one?”. WHERE EXISTS (SELECT 1 FROM events v WHERE v.customer_id = c.customer_id …) keeps the customer if the subquery finds any row. It stops at the first match, and what the subquery selects doesn’t matter, so people write SELECT 1. The customer stays one row. Database people call this a semi-join.

A JOIN asks “what are the matches?”. It gives a row for each one. That’s right when you want the matches, like each order’s items. It’s wrong when you only want to know whether there are any.

★Filter with EXISTS, fetch with JOIN

Ask yourself: do I need columns from the other table? If yes, JOIN. If you only need to know a match is there, or isn’t, use EXISTS or NOT EXISTS.

IN works for “is there one?” too. customer_id IN (SELECT customer_id FROM events WHERE …) keeps each customer once, like EXISTS. Postgres often runs the two the same way.

NOT IN has a trap. x NOT IN (1, NULL) means x <> 1 AND x <> NULL. The last part is NULL, so the whole test is never true, and no row passes. events.customer_id can be NULL, for guests. One guest adding to cart empties the list. NOT EXISTS doesn’t compare with every value: it looks for a row where a.customer_id = c.customer_id, and a NULL simply doesn’t match.

Watch EXISTS and NOT EXISTS run for each customer. Then type your own events, like a guest who adds to cart:

The window shoppers, step by step

⤢ Full view

1. FROM customers c
2. EXISTS (a page_view)
3. NOT EXISTS (an add_to_cart)
4. ORDER BY c.customer_id

customers c

customer_id	name
1	Asha Rao
2	Ben Okafor
3	Chen Wei
4	Dana Cohen
5	Elif Demir
6	Farid Haddad
7	Grace Kim
8	Hugo Silva

Step 1 of 8. FROM customers c reads all 8 customers. For each one, the two subqueries look in events.

◀ BackPlayNext ▶Reset Ask me first Show as text

1SELECT c.customer_id, c.name
2FROM customers c
3WHERE EXISTS (
4    SELECT 1
5    FROM events v
6    WHERE v.customer_id = c.customer_id
7      AND v.event_type = 'page_view'
8  )
9  AND NOT EXISTS (
10    SELECT 1
11    FROM events a
12    WHERE a.customer_id = c.customer_id
13      AND a.event_type = 'add_to_cart'
14  )
15ORDER BY c.customer_id;

Try your own input

Events: customer_id, event_type4, page_view 4, add_to_cart 5, page_view 6, add_to_cart 7, page_view 7, page_view 8, page_view NULL, add_to_cartShow it

Climb the ladder

All three give Elif, Grace and Hugo on the store’s data. What changes is how many rows a second page view makes, and what a guest’s NULL does.

Okay

JOIN the page views, NOT IN the carts

The idea: Join each customer to their page views, and drop anyone in the list of cart customers.

It looks right because, in the store, each window shopper viewed one page.

SELECT c.customer_id, c.name
FROM customers c
JOIN events v ON v.customer_id = c.customer_id
  AND v.event_type = 'page_view'
WHERE c.customer_id NOT IN (
  SELECT customer_id
  FROM events
  WHERE event_type = 'add_to_cart'
)
ORDER BY c.customer_id;

Gets right

One page view each, as in the store

Gets wrong

Two page views, or a guest’s cart

Why

The JOIN gives a row for each page view. And NOT IN keeps no one when the list holds a NULL.

Where it breaks: Grace views a second page, and she’s listed twice. Okay passes 1 of the 3 checks below.

Better

IN and NOT IN

The idea: Keep customers in the page-view list and not in the cart list.

The gap in Okay: the JOIN copied Grace once for each page view.

Highlighted lines are new in this rung.

SELECT customer_id, name
FROM customers
WHERE customer_id IN (
    SELECT customer_id
    FROM events
    WHERE event_type = 'page_view'
  )
  AND customer_id NOT IN (
    SELECT customer_id
    FROM events
    WHERE event_type = 'add_to_cart'
  )
ORDER BY customer_id;

Gets right

Two page viewswas One page view each, as in the store

Gets wrong

A guest’s cart

Why

IN keeps each customer once. But NOT IN compares with every value in its list, and NULL makes that never true.

What changed from the previous rung

SELECT c.customer_id, c.name
FROM customers c
JOIN events v ON v.customer_id = c.customer_id
  AND v.event_type = 'page_view'
WHERE c.customer_id NOT IN (
  SELECT customer_id
  FROM events
  WHERE event_type = 'add_to_cart'
)
ORDER BY c.customer_id;
SELECT customer_id, name
FROM customers
WHERE customer_id IN (
    SELECT customer_id
    FROM events
    WHERE event_type = 'page_view'
  )
  AND customer_id NOT IN (
    SELECT customer_id
    FROM events
    WHERE event_type = 'add_to_cart'
  )
ORDER BY customer_id;

Where it breaks: a guest, not signed in, adds to cart. The cart list now holds a NULL, and Better lists no one. Better passes 2 of 3.

Best

EXISTS and NOT EXISTS

The idea: Keep a customer when a page view of theirs exists and no add to cart of theirs does.

The gap in Better: NOT IN over a column that can be NULL.

Highlighted lines are new in this rung.

SELECT c.customer_id, c.name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM events v
    WHERE v.customer_id = c.customer_id
      AND v.event_type = 'page_view'
  )
  AND NOT EXISTS (
    SELECT 1
    FROM events a
    WHERE a.customer_id = c.customer_id
      AND a.event_type = 'add_to_cart'
  )
ORDER BY c.customer_id;

Gets right

Every checkwas Two page views

Gets wrong

Nothing the checks try

Why

Each subquery looks only for this customer’s rows. One match is enough, and a NULL never matches.

What changed from the previous rung

SELECT customer_id, name
FROM customers
WHERE customer_id IN (
    SELECT customer_id
    FROM events
    WHERE event_type = 'page_view'
SELECT c.customer_id, c.name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM events v
    WHERE v.customer_id = c.customer_id
      AND v.event_type = 'page_view'
  )
  AND customer_id NOT IN (
    SELECT customer_id
    FROM events
    WHERE event_type = 'add_to_cart'
  AND NOT EXISTS (
    SELECT 1
    FROM events a
    WHERE a.customer_id = c.customer_id
      AND a.event_type = 'add_to_cart'
  )
ORDER BY customer_id;
ORDER BY c.customer_id;

When Best isn’t best: when you need columns from the matching rows, like the time of the last page view, use a JOIN or a subquery in SELECT. For speed, an index on events (customer_id, event_type) lets each EXISTS stop fast.

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 window-shopper list

Ctrl + Enter runs

The tables

• customers (customer_id, name, email, city, signed_up_on)
• events (event_id, customer_id, event_type, occurred_at)

-- Customers who viewed a page but never added to cart. Each customer once. SELECT customer_id, event_type FROM events ORDER BY customer_id NULLS FIRST, event_type;

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 comes back and views another page (row order matters)
   What this check changes

   INSERT INTO events (event_id, customer_id, event_type, occurred_at)
   VALUES (21, 7, 'page_view', '2026-10-21 09:00:00');

3. ○Not run yet: Grace’s second visit, and a guest, not signed in, adds to cart (row order matters)
   What this check changes

   INSERT INTO events (event_id, customer_id, event_type, occurred_at)
   VALUES (21, 7, 'page_view', '2026-10-21 09:00:00');
   INSERT INTO events (event_id, customer_id, event_type, occurred_at)
   VALUES (22, NULL, 'add_to_cart', '2026-10-21 10:00:00');

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: JOIN and NOT IN	One match each	Two matches, or a NULL in the list	You want a row per match, and the list can’t hold NULL
Better: IN and NOT IN	Two matches	A NULL in the NOT IN list	The list’s column is NOT NULL
Best: EXISTS and NOT EXISTS	Every check	Nothing the checks try	The usual way to ask “is there one?” or “is there none?”

Common mistakes and edge cases

!NOT IN over a column that can be NULL

One NULL in the list makes NOT IN keep no row, with no error. Use NOT EXISTS, or add WHERE col IS NOT NULL inside the list. A NOT NULL column, like a primary key, is safe.

• DISTINCT isn’t a fix for a JOIN’s copies. It hides them, but it also merges rows that are really different, like two customers with the same name. Filter with EXISTS instead.
• A LEFT JOIN can do “none” too. LEFT JOIN … ON … WHERE other.id IS NULL keeps rows with no match. Put every condition on the other table in ON, not WHERE, or the LEFT JOIN turns back into a JOIN.
• EXISTS ignores what the subquery selects. SELECT 1, SELECT * and SELECT NULL all work the same.
• EXISTS needs the link. Without v.customer_id = c.customer_id, the subquery finds any page view at all, so every customer passes.
• An empty list. x IN (empty) is false, and x NOT IN (empty) is true for every row. EXISTS and NOT EXISTS agree.

Say it in the interview

Questions to ask first

• Can a customer have many page views? Should they come once?
• Can customer_id in events be NULL, like for guests?
• Do I need any columns from events, or only whether a row is there?

A 60-second walkthrough

“I only need to know whether a page view exists, so I filter customers with EXISTS, linked on customer_id. That keeps each customer once, however many views they have. For “never added to cart” I use NOT EXISTS, not NOT IN. events.customer_id can be NULL for guests, and one NULL would make NOT IN keep no one. An index on events (customer_id, event_type) lets each check stop at the first match.”

Likely follow-ups

• Is EXISTS faster than IN? Usually the same in Postgres: both become a semi-join. NOT EXISTS and NOT IN differ, because NOT IN must handle NULL.
• EXISTS or a LEFT JOIN … IS NULL? Both work for “none”. NOT EXISTS reads more clearly, and Postgres plans both as an anti-join.
• When is a JOIN right? When you need the other table’s columns, or one row per match.

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. Ask headphone buyers for a review (easy)

The shop asks everyone who bought headphones (product 7) for a review, once each. Cancelled orders don’t count. List each buyer’s name, by name, then by customer_id.

Hint 1 of 3

A JOIN to order_items gives a row for each headphone order, so a customer who bought twice comes twice.

Hint 2 of 3

DISTINCT name would merge two different customers who share a name.

Hint 3 of 3

Filter customers with EXISTS: an order of theirs, not cancelled, with an item whose product_id is 7.

Ask headphone buyers for a review

Ctrl + Enter runs

The tables

• customers (customer_id, name, email, city, signed_up_on)
• orders (order_id, customer_id, order_date, status, total)
• order_items (order_id, product_id, quantity, unit_price)

SELECT o.customer_id, o.order_id, o.status FROM orders o JOIN order_items i ON i.order_id = o.order_id WHERE i.product_id = 7;

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: Asha buys a second pair of headphones (row order matters)
   What this check changes

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

3. ○Not run yet: Asha’s second pair, and a new customer, also called Ben Okafor, buys headphones (row order matters)
   What this check changes

   INSERT INTO orders (order_id, customer_id, order_date, status, total)
   VALUES (115, 1, '2026-10-21', 'placed', 59.00);
   INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES (115, 7, 1, 59.00);
   INSERT INTO customers (customer_id, name, email, city, signed_up_on)
   VALUES (9, 'Ben Okafor', 'ben.okafor@example.net', 'Abuja', '2026-10-01');
   INSERT INTO orders (order_id, customer_id, order_date, status, total)
   VALUES (116, 9, '2026-10-22', 'placed', 59.00);
   INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES (116, 7, 1, 59.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 to the orders

The idea: Join customers to their headphone orders.

Okay passes 1 of 3. When Asha buys a second pair, she’s asked twice.

SELECT c.name
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
JOIN order_items i ON i.order_id = o.order_id
WHERE i.product_id = 7
  AND o.status <> 'cancelled'
ORDER BY c.name, c.customer_id;

Gets right

One headphone order each, as in the store

Gets wrong

A second pair

Why

The JOIN gives a row for each headphone order.

Better

SELECT DISTINCT name

The idea: Join, then drop repeated names.

The gap in Okay: the JOIN copied Asha.

Highlighted lines are new in this rung.

SELECT DISTINCT c.name
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
JOIN order_items i ON i.order_id = o.order_id
WHERE i.product_id = 7
  AND o.status <> 'cancelled'
ORDER BY c.name;

Gets right

A second pair

Gets wrong

Two customers with the same name

Why

DISTINCT works on the columns you show. Two different people with the same name look the same.

What changed from the previous rung

SELECT c.name
SELECT DISTINCT c.name
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
JOIN order_items i ON i.order_id = o.order_id
WHERE i.product_id = 7
  AND o.status <> 'cancelled'
ORDER BY c.name, c.customer_id;
ORDER BY c.name;

Where it breaks: a new customer, also called Ben Okafor, buys headphones. Two people should be asked, but DISTINCT makes them one. Better passes 2 of 3.

Best

EXISTS a headphone order

The idea: Keep each customer with at least one headphone order that wasn’t cancelled.

The gap in Better: DISTINCT merged two people.

Highlighted lines are new in this rung.

SELECT c.name
FROM customers c
WHERE EXISTS (
  SELECT 1
  FROM orders o
  JOIN order_items i ON i.order_id = o.order_id
  WHERE o.customer_id = c.customer_id
    AND i.product_id = 7
    AND o.status <> 'cancelled'
)
ORDER BY c.name, c.customer_id;

Gets right

Every checkwas A second pair

Gets wrong

Nothing the checks try

Why

Each customer is one row of customers, and EXISTS only filters it.

What changed from the previous rung

SELECT DISTINCT c.name
SELECT c.name
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
JOIN order_items i ON i.order_id = o.order_id
WHERE i.product_id = 7
  AND o.status <> 'cancelled'
ORDER BY c.name;
WHERE EXISTS (
  SELECT 1
  FROM orders o
  JOIN order_items i ON i.order_id = o.order_id
  WHERE o.customer_id = c.customer_id
    AND i.product_id = 7
    AND o.status <> 'cancelled'
)
ORDER BY c.name, c.customer_id;

When Best isn’t best: if the email needs the order date, you need the order’s columns. Then a JOIN with DISTINCT ON (c.customer_id) picks one order per customer.

2. Customers who are all paid up (medium)

Finance wants the customers whose every order is paid. Count an order as paid once it has a payment. Cancelled orders don’t count, and a customer needs at least one order. List customer_id and name, by customer_id.

Hint 1 of 3

One paid order isn’t enough. The customer must have no order without a payment.

Hint 2 of 3

Counting payments goes wrong when one order is paid in two parts.

Hint 3 of 3

Two NOT EXISTS, one inside the other: no order of theirs exists where no payment exists.

Customers who are all paid up

Ctrl + Enter runs

The tables

• customers (customer_id, name, email, city, signed_up_on)
• orders (order_id, customer_id, order_date, status, total)
• payments (payment_id, order_id, paid_on, method, amount)

SELECT o.customer_id, o.order_id, o.status, p.payment_id FROM orders o LEFT JOIN payments p ON p.order_id = o.order_id ORDER BY o.customer_id, o.order_id;

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

1. ○Not run yet: The store’s data: every order that wasn’t cancelled has a payment (row order matters)
2. ○Not run yet: Asha places an order she hasn’t paid for yet (row order matters)
   What this check changes

   INSERT INTO orders (order_id, customer_id, order_date, status, total)
   VALUES (115, 1, '2026-10-21', 'placed', 20.00);

3. ○Not run yet: Asha’s unpaid order, and Ben pays order 110 in two parts, then places one he hasn’t paid for (row order matters)
   What this check changes

   INSERT INTO orders (order_id, customer_id, order_date, status, total)
   VALUES (115, 1, '2026-10-21', 'placed', 20.00);
   UPDATE payments SET amount = 29.00 WHERE payment_id = 9;
   INSERT INTO payments (payment_id, order_id, paid_on, method, amount) VALUES (14, 110, '2026-10-15', 'card', 30.00);
   INSERT INTO orders (order_id, customer_id, order_date, status, total)
   VALUES (116, 2, '2026-10-22', 'placed', 12.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

IN the customers with a paid order

The idea: Keep anyone with at least one paid order.

Okay passes 1 of 3. When Asha places an order she hasn’t paid for, she’s still listed.

SELECT customer_id, name
FROM customers
WHERE customer_id IN (
  SELECT o.customer_id
  FROM orders o
  JOIN payments p ON p.order_id = o.order_id
  WHERE o.status <> 'cancelled'
)
ORDER BY customer_id;

Gets right

Every order paid, as in the store

Gets wrong

An unpaid order

Why

It asks “is one order paid?”, not “is every order paid?”.

Better

Count the payments

The idea: Keep a customer whose number of payments equals their number of orders.

The gap in Okay: one paid order was enough.

Highlighted lines are new in this rung.

SELECT c.customer_id, c.name
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
LEFT JOIN payments p ON p.order_id = o.order_id
WHERE o.status <> 'cancelled'
GROUP BY c.customer_id, c.name
HAVING count(p.payment_id) = count(DISTINCT o.order_id)
ORDER BY c.customer_id;

Gets right

An unpaid order

Gets wrong

An order paid in two parts

Why

One order with two payments makes up for another order with none.

What changed from the previous rung

SELECT customer_id, name
FROM customers
WHERE customer_id IN (
  SELECT o.customer_id
  FROM orders o
  JOIN payments p ON p.order_id = o.order_id
  WHERE o.status <> 'cancelled'
)
ORDER BY customer_id;
SELECT c.customer_id, c.name
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
LEFT JOIN payments p ON p.order_id = o.order_id
WHERE o.status <> 'cancelled'
GROUP BY c.customer_id, c.name
HAVING count(p.payment_id) = count(DISTINCT o.order_id)
ORDER BY c.customer_id;

Where it breaks: Ben pays order 110 in two parts, then places an order he hasn’t paid for. He has 4 payments and 4 orders, so Better lists him. Better passes 2 of 3.

Best

NOT EXISTS an unpaid order

The idea: Keep a customer with an order, and with no order that has no payment.

The gap in Better: counts can cancel each other out.

Highlighted lines are new in this rung.

SELECT c.customer_id, c.name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
      AND o.status <> 'cancelled'
  )
  AND NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
      AND o.status <> 'cancelled'
      AND NOT EXISTS (
        SELECT 1
        FROM payments p
        WHERE p.order_id = o.order_id
      )
  )
ORDER BY c.customer_id;

Gets right

Every checkwas An unpaid order

Gets wrong

Nothing the checks try

Why

It looks for one unpaid order, so how many payments an order has doesn’t matter.

What changed from the previous rung

SELECT c.customer_id, c.name
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
LEFT JOIN payments p ON p.order_id = o.order_id
WHERE o.status <> 'cancelled'
GROUP BY c.customer_id, c.name
HAVING count(p.payment_id) = count(DISTINCT o.order_id)
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
      AND o.status <> 'cancelled'
  )
  AND NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
      AND o.status <> 'cancelled'
      AND NOT EXISTS (
        SELECT 1
        FROM payments p
        WHERE p.order_id = o.order_id
      )
  )
ORDER BY c.customer_id;

When Best isn’t best: when “paid” means paid in full, compare each order’s total with the sum of its payments. That needs a sum, not only EXISTS.

3. What wallet customers spend (medium)

The wallet team wants to know what its customers spend. A wallet customer has paid with wallet for an order that wasn’t cancelled. List each one’s customer_id and spent, the sum of their orders that weren’t cancelled, by customer_id.

Hint 1 of 3

Joining every order to every wallet payment of the customer copies each order once per wallet payment, and the sum grows.

Hint 2 of 3

Pick the wallet customers in a subquery, and sum orders on the outside.

Hint 3 of 3

The wallet payment must be for an order that wasn’t cancelled. That rule goes inside the subquery.

What wallet customers spend

Ctrl + Enter runs

The tables

• orders (order_id, customer_id, order_date, status, total)
• payments (payment_id, order_id, paid_on, method, amount)

SELECT o.customer_id, o.order_id, o.status, p.method FROM orders o JOIN payments p ON p.order_id = o.order_id ORDER BY o.customer_id, o.order_id;

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

1. ○Not run yet: The store’s data: each wallet customer used it once (row order matters)
2. ○Not run yet: Asha pays a second order with her wallet (row order matters)
   What this check changes

   UPDATE payments SET method = 'wallet' WHERE payment_id = 7;

3. ○Not run yet: Asha’s second wallet payment, and Grace pays for an order with her wallet, then cancels it (row order matters)
   What this check changes

   UPDATE payments SET method = 'wallet' WHERE payment_id = 7;
   INSERT INTO orders (order_id, customer_id, order_date, status, total)
   VALUES (115, 7, '2026-10-21', 'cancelled', 9.00);
   INSERT INTO payments (payment_id, order_id, paid_on, method, amount) VALUES (14, 115, '2026-10-21', 'wallet', 9.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 to the wallet payments

The idea: Join each customer’s orders to their wallet payments, then sum.

Okay passes 1 of 3. When Asha pays a second order with her wallet, her spend doubles to 341.76.

SELECT o.customer_id, sum(o.total) AS spent
FROM orders o
JOIN orders wo ON wo.customer_id = o.customer_id
JOIN payments p ON p.order_id = wo.order_id
WHERE p.method = 'wallet'
  AND o.status <> 'cancelled'
GROUP BY o.customer_id
ORDER BY o.customer_id;

Gets right

One wallet payment each, as in the store

Gets wrong

A second wallet payment

Why

Each order is copied once for each wallet payment, so the sum counts it again.

Better

IN the wallet customers

The idea: Sum the orders of customers in the list of wallet payers.

The gap in Okay: the JOIN copied the orders.

Highlighted lines are new in this rung.

SELECT customer_id, sum(total) AS spent
FROM orders
WHERE status <> 'cancelled'
  AND customer_id IN (
    SELECT wo.customer_id
    FROM orders wo
    JOIN payments p ON p.order_id = wo.order_id
    WHERE p.method = 'wallet'
  )
GROUP BY customer_id
ORDER BY customer_id;

Gets right

A second wallet payment

Gets wrong

A wallet payment on a cancelled order

Why

The list counts every wallet payment. The outer WHERE doesn’t reach into it.

What changed from the previous rung

SELECT o.customer_id, sum(o.total) AS spent
FROM orders o
JOIN orders wo ON wo.customer_id = o.customer_id
JOIN payments p ON p.order_id = wo.order_id
WHERE p.method = 'wallet'
  AND o.status <> 'cancelled'
GROUP BY o.customer_id
ORDER BY o.customer_id;
SELECT customer_id, sum(total) AS spent
FROM orders
WHERE status <> 'cancelled'
  AND customer_id IN (
    SELECT wo.customer_id
    FROM orders wo
    JOIN payments p ON p.order_id = wo.order_id
    WHERE p.method = 'wallet'
  )
GROUP BY customer_id
ORDER BY customer_id;

Where it breaks: Grace pays for an order with her wallet, then cancels it. She isn’t a wallet customer, but Better lists her. Better passes 2 of 3.

Best

EXISTS a wallet payment, not cancelled

The idea: Keep orders whose customer has a wallet payment on an order that wasn’t cancelled.

The gap in Better: the rule was outside the list.

Highlighted lines are new in this rung.

SELECT o.customer_id, sum(o.total) AS spent
FROM orders o
WHERE o.status <> 'cancelled'
  AND EXISTS (
    SELECT 1
    FROM orders wo
    JOIN payments p ON p.order_id = wo.order_id
    WHERE wo.customer_id = o.customer_id
      AND wo.status <> 'cancelled'
      AND p.method = 'wallet'
  )
GROUP BY o.customer_id
ORDER BY o.customer_id;

Gets right

Every checkwas A second wallet payment

Gets wrong

Nothing the checks try

Why

EXISTS only filters, so no order is copied, and the status rule is inside the subquery.

What changed from the previous rung

SELECT customer_id, sum(total) AS spent
FROM orders
WHERE status <> 'cancelled'
  AND customer_id IN (
    SELECT wo.customer_id
SELECT o.customer_id, sum(o.total) AS spent
FROM orders o
WHERE o.status <> 'cancelled'
  AND EXISTS (
    SELECT 1
    FROM orders wo
    JOIN payments p ON p.order_id = wo.order_id
    WHERE p.method = 'wallet'
    WHERE wo.customer_id = o.customer_id
      AND wo.status <> 'cancelled'
      AND p.method = 'wallet'
  )
GROUP BY customer_id
ORDER BY customer_id;
GROUP BY o.customer_id
ORDER BY o.customer_id;

When Best isn’t best: for many reports on wallet customers, a CTE that names them once keeps the rule in one place.

Check yourself

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

1. Predict the outputWhat does this give?

   SELECT count(*)
   FROM (VALUES (1), (2), (3)) AS t(x)
   WHERE x NOT IN (1, NULL);

   203It stops with an error

   Check answer
2. Spot the bugThis should list each customer who viewed a page. What goes wrong?

   SELECT c.name
   FROM customers c
   WHERE EXISTS (
     SELECT 1 FROM events v WHERE v.event_type = 'page_view'
   );

   It stops with an errorCustomers with two page views come twiceIt lists no oneIt lists every customer, since the subquery isn’t linked to c

   Check answer
3. Multiple choiceCustomer 7 has three page views. How many rows does each query give for them?

   -- A
   SELECT c.customer_id FROM customers c
   JOIN events v ON v.customer_id = c.customer_id AND v.event_type = 'page_view';
   -- B
   SELECT c.customer_id FROM customers c
   WHERE EXISTS (SELECT 1 FROM events v
     WHERE v.customer_id = c.customer_id AND v.event_type = 'page_view');

   A: 3, B: 1A: 1, B: 1A: 3, B: 3A: 1, B: 3

   Check answer
4. Multiple choiceWhen is NOT IN (SELECT col FROM t) safe?

   AlwaysNeverWhen col can’t be NULL, or the subquery adds WHERE col IS NOT NULLWhen the list is short

   Check answer
5. Multiple choiceYou need each customer’s name and the date of each of their orders. Which fits?

   EXISTSNOT EXISTSINA JOIN

   Check answer

Flashcards

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

• Card 1What does EXISTS (subquery) do?Show answer
• Card 2Why is NOT EXISTS safer than NOT IN?Show answer
• Card 3Why isn’t DISTINCT a good fix for a JOIN’s copies?Show answer
• Card 4When should you use a JOIN instead of EXISTS?Show answer
• Card 5What goes in EXISTS’s SELECT list?Show answer

Add 5 cards to my review deck

Cheat sheet

• EXISTS (…): is there at least one match? Stops at the first. Keeps the row once.
• NOT EXISTS (…): is there none? A NULL never matches, so it’s safe.
• IN (…) works like EXISTS. NOT IN (…) keeps no row if its list holds a NULL.
• A JOIN gives a row per match. Use it when you need the other table’s columns.
• DISTINCT hides a JOIN’s copies, and also merges rows that only look the same.
• Link the subquery to the outer row, like v.customer_id = c.customer_id.

Go deeper

• In the PostgreSQL docs: EXISTS, IN and NOT IN.
• Try your own events: the window shoppers on the SQL visualizer.
• Next on the SQL roadmap: the whole map, with UNION and UNION ALL, which stack two results.

Can you do these now?

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

• Ask “is there a match?” with EXISTS, and “is there none?” with NOT EXISTS
• Say why a JOIN can copy a row, and why DISTINCT isn’t the fix
• Spot NOT IN over a column that can be NULL, and replace it
• Pick between EXISTS, IN and JOIN for a task

✓Mark as completeI already know this

Status: Not started