Visualize SQL

A query isn’t run top to bottom. The database starts with FROM, then WHERE, then window functions, then SELECT, then ORDER BY. Each step below shows the table at that point. All visualizers.

• Your own queryWrite any SELECT and watch it run clause by clause, with the table after each step.Roadmap: Logical query processing order, GROUP BY and HAVING, Joins: inner, left, right, full, self and cross
• Rows checked against keysWatch new rows go into a table with a primary key, foreign keys and UNIQUE, one rule at a time.Lesson: My report shows reviews of a product we don’t sell · Roadmap: Tables, rows and keys
• The warehouse listWatch WHERE, ORDER BY and LIMIT pick the oldest orders, and see what LIMIT keeps with no ORDER BY.Lesson: Order 109 disappeared from the warehouse’s list · Roadmap: SELECT, WHERE, ORDER BY and LIMIT, Logical query processing order
• The second-highest priceWatch DISTINCT keep one row per price, so OFFSET 1 skips the top price and not just one copy of it.Lesson: Our “second-highest price” is the highest price · Roadmap: DISTINCT, Logical query processing order
• The sales tileWatch count, sum and avg fold one day’s orders into one row, even when no rows are left.Lesson: The sales tile says two customers ordered, but it was only Hugo · Roadmap: Aggregates, Logical query processing order
• The loyalty couponWatch WHERE drop rows, GROUP BY make groups, and HAVING drop whole groups.Lesson: Grace shopped on one day and got the loyalty coupon · Roadmap: GROUP BY and HAVING, Logical query processing order
• The reminder listWatch one NULL in a NOT IN list make every comparison unknown, so no customer gets the reminder.Lesson: A guest checked out, and nobody got a reminder · Roadmap: NULL and three-valued logic, Simple subqueries
• The payments reportWatch an inner JOIN drop an order with no payment, LEFT JOIN keep it, and GROUP BY fold a split payment.Lesson: Dana never paid, and finance never noticed · Roadmap: Joins: inner, left, right, full, self and cross, GROUP BY and HAVING
• The double-order checkWatch a self join pair each order with itself and every pair both ways, then keep each pair once and check both orders.Lesson: Grace cancelled, and we still asked if she’d ordered twice · Roadmap: Joins: inner, left, right, full, self and cross
• The bottle offerWatch two subqueries list who bought a backpack and who bought a water bottle. Then IN keeps each customer once, and NOT IN drops the rest.Lesson: Elif bought two backpacks and got the bottle offer twice · Roadmap: Simple subqueries
• The duplicate cleanupWatch GROUP BY gather the payments that match in every column but the id, keep the first of each, and DELETE remove the rest.Lesson: We deleted Ben’s duplicate payment, and the real one went too · Roadmap: INSERT, UPDATE and DELETE
• The welcome listWatch a query run in the order the database runs it: WHERE drops cancelled orders, then HAVING tests each customer’s first order.Lesson: Dana first ordered in August and got September’s welcome email · Roadmap: Logical query processing order
• The pay listWatch WITH name the staff past probation once, then the company average and each department read the same rows.Lesson: A team of one made the above-average pay list · Roadmap: CTEs
• The growing ordersWatch a correlated subquery run once for each order, and pick the same customer’s order just before it.Lesson: Bigger than the order before, or just typed in late? · Roadmap: Correlated subqueries
• The window shoppersWatch EXISTS stop at the first match, and NOT EXISTS ignore a guest’s NULL that empties NOT IN.Lesson: Grace got the window-shopper email twice · Roadmap: EXISTS vs IN vs JOIN, and the NOT IN + NULL trap
• Window functionsWatch a query run in the order the database runs it, numbering each customer’s orders.Lesson: Each customer's latest order · Roadmap: Window functions: ranking, LAG, LEAD, running totals, frames

Your own query

Write any SELECT on the store’s tables and press Show the steps. Each step shows the table so far: rows kept ✓ or dropped ✗, groups and partitions shaded in turn, and the line of your query that runs. The last step is checked against what Run gives.

Write a query, then watch it run

Ctrl + Enter shows the steps

The tables

• customers (customer_id, name, email, city, signed_up_on)
• products (product_id, name, category, price)
• orders (order_id, customer_id, order_date, status, total)
• order_items (order_id, product_id, quantity, unit_price)
• payments (payment_id, order_id, paid_on, method, amount)
• departments (department_id, name)
• employees (employee_id, name, department_id, manager_id, salary, hired_on)
• events (event_id, customer_id, event_type, occurred_at)

SELECT customer_id, count(*) AS orders, sum(total) AS spent FROM orders WHERE status <> 'cancelled' GROUP BY customer_id HAVING count(*) >= 2 ORDER BY spent DESC LIMIT 3;

Show the stepsReset queryRuns in this browser. Loads about 5 MB the first time, best on Wi-Fi.

Under every SQL editor in the lessons, Show the steps does the same.

Rows checked against keys

New rows go into reviews, which has a primary key, two foreign keys and UNIQUE (customer_id, product_id). Postgres checks each rule in turn, and the first that says no rejects the row. Type your own rows: a repeated review_id, a product that doesn’t exist, a second review of the same product. The lesson: My report shows reviews of a product we don’t sell.

Rows checked against the keys

⤢ Full view

1. PRIMARY KEY
2. UNIQUE (customer_id, product_id)
3. REFERENCES products
4. REFERENCES customers

products

product_id	name
1	Notebook
2	Gel pen (5 pack)
3	Desk lamp
4	Water bottle
5	Wireless mouse
6	USB-C cable
7	Headphones
8	Backpack

customers

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

reviews

review_id	product_id	customer_id

Step 1 of 13. reviews starts empty. Each new row must pass 4 rules before it goes in, in the order listed above. The first rule that says no rejects the row.

◀ BackPlayNext ▶Reset Ask me first Show as text

1CREATE TABLE reviews (
2  review_id   integer PRIMARY KEY,
3  product_id  integer NOT NULL REFERENCES products,
4  customer_id integer NOT NULL REFERENCES customers,
5  stars       integer NOT NULL,
6  body        text,
7  UNIQUE (customer_id, product_id)
8);

Try your own input

Reviews: review_id, product_id, customer_id1, 7, 2 1, 3, 4 2, 3, 4 3, 3, 4 4, 9, 5Show it

The warehouse list

The 3 oldest placed orders: WHERE keeps the placed ones, ORDER BY sorts them by date and then by order_id, and LIMIT keeps the first 3. Rows come in the order they’re stored, so see which 3 LIMIT would keep with no ORDER BY. Type your own rows, in any stored order. The lesson: Order 109 disappeared from the warehouse’s list.

The warehouse list, step by step

⤢ Full view

1. FROM orders
2. WHERE status = 'placed'
3. SELECT order_id, order_date
4. ORDER BY order_date, order_id
5. LIMIT 3

orders

order_id	order_date	status
101	2026-10-02	placed
104	2026-10-05	shipped
103	2026-10-04	placed
102	2026-10-04	placed
105	2026-10-01	placed

Step 1 of 7. FROM orders reads all 5 rows, in the order they’re stored. That isn’t order_id order: a changed row is written again at the end.

◀ BackPlayNext ▶Reset Ask me first Show as text

1SELECT order_id, order_date
2FROM orders
3WHERE status = 'placed'
4ORDER BY order_date, order_id
5LIMIT 3;

Try your own input

Orders as stored: order_id, order_date, status101, 2026-10-02, placed 104, 2026-10-05, shipped 103, 2026-10-04, placed 102, 2026-10-04, placed 105, 2026-10-01, placedShow it

The second-highest price

DISTINCT keeps one row for each different price, ORDER BY price DESC puts the highest first, and LIMIT 1 OFFSET 1 skips it and keeps the next. With the top price twice and no DISTINCT, the second row would be the top price again. Type your own products, with prices that repeat, or all the same. The lesson: Our “second-highest price” is the highest price.

The second-highest price, step by step

⤢ Full view

1. FROM products
2. SELECT price
3. DISTINCT
4. ORDER BY price DESC
5. OFFSET 1
6. LIMIT 1
7. SELECT (…) AS second_price

products

name	price
Desk lamp	24.99
Water bottle	12.00
Lamp shade	24.99
Notebook	4.50

Step 1 of 7. FROM products reads all 4 rows.

◀ BackPlayNext ▶Reset Ask me first Show as text

1SELECT (
2  SELECT DISTINCT price
3  FROM products
4  ORDER BY price DESC
5  LIMIT 1 OFFSET 1
6) AS second_price;

Try your own input

Products: name, priceDesk lamp, 24.99 Water bottle, 12.00 Lamp shade, 24.99 Notebook, 4.50Show it

The sales tile

WHERE keeps one day’s orders, then count, sum and avg fold them into one row. count(DISTINCT customer_id) counts a customer who ordered twice once. With no orders left, the query still gives one row: count gives 0, sum and avg give NULL, and coalesce turns the sum into 0. Type your own orders, with one customer twice, or none on 20 October. The lesson: The sales tile says two customers ordered, but it was only Hugo.

The sales tile, step by step

⤢ Full view

1. FROM orders
2. WHERE order_date = '2026-10-20'
3. count(*)
4. count(DISTINCT customer_id)
5. coalesce(sum(total), 0)
6. round(avg(total), 2)

orders

customer_id	order_date	total
3	2026-10-20	12.50
3	2026-10-20	30.00
5	2026-10-20	7.25
4	2026-10-19	18.00

Step 1 of 7. FROM orders reads all 4 rows.

◀ BackPlayNext ▶Reset Ask me first Show as text

1SELECT
2  count(*) AS orders,
3  count(DISTINCT customer_id) AS customers,
4  coalesce(sum(total), 0) AS sales,
5  round(avg(total), 2) AS avg_order
6FROM orders
7WHERE order_date = '2026-10-20';

Try your own input

Orders: customer_id, order_date, total3, 2026-10-20, 12.50 3, 2026-10-20, 30.00 5, 2026-10-20, 7.25 4, 2026-10-19, 18.00Show it

The loyalty coupon

WHERE drops the cancelled orders, GROUP BY customer_id makes one group for each customer, and HAVING keeps the groups with orders on 3 or more different days. Three orders on one day count as one day. Type your own orders, with a cancelled one, or several on one day. The lesson: Grace shopped on one day and got the loyalty coupon.

The loyalty coupon, step by step

⤢ Full view

1. FROM orders
2. WHERE status <> 'cancelled'
3. GROUP BY customer_id
4. HAVING count(DISTINCT order_date) >= 3
5. SELECT customer_id
6. ORDER BY customer_id

orders

customer_id	order_date	status
3	2026-10-01	delivered
3	2026-10-08	shipped
3	2026-10-08	placed
3	2026-10-15	placed
4	2026-10-02	cancelled
4	2026-10-09	placed
4	2026-10-12	placed
5	2026-10-20	placed
5	2026-10-20	placed
5	2026-10-20	placed

Step 1 of 6. FROM orders reads all 10 rows.

◀ BackPlayNext ▶Reset Ask me first Show as text

1SELECT customer_id
2FROM orders
3WHERE status <> 'cancelled'
4GROUP BY customer_id
5HAVING count(DISTINCT order_date) >= 3
6ORDER BY customer_id;

Try your own input

Orders: customer_id, order_date, status3, 2026-10-01, delivered 3, 2026-10-08, shipped 3, 2026-10-08, placed 3, 2026-10-15, placed 4, 2026-10-02, cancelled 4, 2026-10-09, placed 4, 2026-10-12, placed 5, 2026-10-20, placed 5, 2026-10-20, placed 5, 2026-10-20, placedShow it

The reminder list

The subquery lists the customers who checked out. A guest checks out without signing in, so their customer_id is NULL. NOT IN compares each customer with every value in the list, and a comparison with NULL is unknown. So with a NULL in the list, no customer is kept. AND customer_id IS NOT NULL takes it out. Type your own checkouts, with a NULL or without one. The lesson: A guest checked out, and nobody got a reminder.

The reminder list, step by step

⤢ Full view

1. FROM events
2. WHERE event_type = 'checkout'
3. AND customer_id IS NOT NULL
4. FROM customers
5. WHERE customer_id NOT IN (…)
6. SELECT customer_id, name
7. ORDER BY customer_id

checkouts

customer_id
1
3
3
6
NULL
8

Step 1 of 6. The subquery runs first. It reads the 6 checkouts: one is a guest’s, whose customer_id is NULL.

◀ BackPlayNext ▶Reset Ask me first Show as text

1SELECT customer_id, name
2FROM customers
3WHERE customer_id NOT IN (
4  SELECT customer_id
5  FROM events
6  WHERE event_type = 'checkout'
7    AND customer_id IS NOT NULL
8)
9ORDER BY customer_id;

Try your own input

Checkouts: customer_id, or NULL for a guest1 3 3 6 NULL 8Show it

The payments report

LEFT JOIN pairs each order with each of its payments. An order with no payment stays once, with NULL for the amount, where an inner JOIN would drop it. An order paid in parts is on one row per payment, until GROUP BY folds them and sum adds them up. Type your own payments, with an order paid in three parts, or one for the cancelled order. The lesson: Dana never paid, and finance never noticed.

The payments report, step by step

⤢ Full view

1. FROM orders
2. LEFT JOIN payments
3. WHERE status <> 'cancelled'
4. GROUP BY order_id, total
5. SELECT …, coalesce(sum(amount), 0)
6. ORDER BY order_id

payments

order_id	amount
112	9.99
113	20.00
113	29.98
114	142.99

orders

order_id	status	total
107	cancelled	136.00
112	delivered	9.99
113	placed	49.98
114	placed	142.99
115	placed	24.99

Step 1 of 7. FROM orders reads 5 orders. Beside them is the payments table, with 4 payments. Question: Order 113 has two payments. After the join, how many rows is order 113 on?

What happens next? Order 113 has two payments. After the join, how many rows is order 113 on?

123Just show me

◀ BackPlayNext ▶Reset Ask me first Show as text

1SELECT o.order_id, o.total, coalesce(sum(p.amount), 0) AS paid
2FROM orders o
3LEFT JOIN payments p ON p.order_id = o.order_id
4WHERE o.status <> 'cancelled'
5GROUP BY o.order_id, o.total
6ORDER BY o.order_id;

Try your own input

Payments: order_id, amount112, 9.99 113, 20.00 113, 29.98 114, 142.99Show it

The double-order check

A self join reads orders twice, as a and as b, and pairs orders of the same customer. Each order pairs with itself, and each pair shows up twice, until a.order_id < b.order_id keeps it once. Then a and b are checked on their own: a cancelled order on either side drops the pair. Type your own day of orders, with three from one customer, or a cancelled one. The lesson: Grace cancelled, and we still asked if she’d ordered twice.

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	5	placed
116	5	placed
117	5	placed
118	8	cancelled
119	8	placed

Step 1 of 7. FROM orders reads 5 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 5 has three orders. The join pairs each of them, as a, with each of them, as b. How many rows does customer 5 get?

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

139Just 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, status5, placed 5, placed 5, placed 8, cancelled 8, placedShow it

The bottle offer

Two subqueries run first: one lists the customers with a backpack order that wasn’t cancelled, the other the ones with a water bottle. Then IN keeps each customer on the first list, once however often they’re on it, and NOT IN drops the ones on the second. Type your own orders, with three backpacks for one customer, or a cancelled one. The lesson: Elif bought two backpacks and got the bottle offer twice.

The bottle offer, step by step

⤢ Full view

1. First subquery: backpacks
2. Second subquery: water bottles
3. FROM customers
4. WHERE customer_id IN (…)
5. AND customer_id NOT IN (…)
6. SELECT customer_id, name
7. ORDER BY customer_id

orders o JOIN order_items i

Kept?	customer_id	product_id	status
✓kept	2	8	placed
✓kept	2	8	shipped
✗dropped	4	4	cancelled
✓kept	4	8	placed
✗dropped	6	8	cancelled
✗dropped	6	4	delivered

Step 1 of 7. The first subquery runs first. Of the 6 orders, it keeps the backpacks (product 8) that weren’t cancelled. The backpack order from customer 6 is cancelled. Its list: (2, 2, 4).

◀ BackPlayNext ▶Reset Ask me first Show as text

1SELECT customer_id, name
2FROM customers
3WHERE customer_id IN (
4    SELECT o.customer_id
5    FROM orders o
6    JOIN order_items i ON i.order_id = o.order_id
7    WHERE i.product_id = 8
8      AND o.status <> 'cancelled'
9  )
10  AND customer_id NOT IN (
11    SELECT o.customer_id
12    FROM orders o
13    JOIN order_items i ON i.order_id = o.order_id
14    WHERE i.product_id = 4
15      AND o.status <> 'cancelled'
16  )
17ORDER BY customer_id;

Try your own input

Orders: customer_id, item, status2, backpack, placed 2, backpack, shipped 4, bottle, cancelled 4, backpack, placed 6, backpack, cancelled 6, bottle, deliveredShow it

The duplicate cleanup

The subquery groups the payments that match in every column but payment_id, and keeps the smallest id of each group: the keep list. Then DELETE removes every payment that isn’t on it. Type your own payments, with three copies of one, or an order paid in two different parts. The lesson: We deleted Ben’s duplicate payment, and the real one went too.

The duplicate cleanup, step by step

⤢ Full view

1. Subquery: FROM payments
2. GROUP BY order_id, paid_on, method, amount
3. SELECT min(payment_id)
4. DELETE … WHERE payment_id NOT IN (…)
5. The table after

payments

payment_id	order_id	method	amount
14	118	card	24.99
15	118	card	24.99
16	118	card	24.99
17	119	upi	9.99
18	119	wallet	5.00

Step 1 of 5. The subquery runs first. FROM payments reads today’s 5 payments, all paid on 21 October. Question: Payments 14, 15 and 16 match in every column but payment_id. How many groups does GROUP BY order_id, paid_on, method, amount make of them?

What happens next? Payments 14, 15 and 16 match in every column but payment_id. How many groups does GROUP BY order_id, paid_on, method, amount make of them?

13Just show me

◀ BackPlayNext ▶Reset Ask me first Show as text

1DELETE FROM payments
2WHERE payment_id NOT IN (
3  SELECT min(payment_id)
4  FROM payments
5  GROUP BY order_id, paid_on, method, amount
6);

Try your own input

Payments: order_id, method, amount118, card, 24.99 118, card, 24.99 118, card, 24.99 119, upi, 9.99 119, wallet, 5.00Show it

The welcome list

The query runs in the order the database runs it. WHERE drops cancelled orders before any groups exist. Then GROUP BY makes one group for each customer, and HAVING keeps those whose first order is in September. Type your own orders, like a customer whose first order was cancelled. The lesson: Dana first ordered in August and got September’s welcome email.

The welcome list, step by step

⤢ Full view

1. FROM orders
2. WHERE status <> 'cancelled'
3. GROUP BY customer_id
4. HAVING min(order_date) in September
5. SELECT customer_id, min(order_date)
6. ORDER BY customer_id

orders

customer_id	order_date	status
1	2026-09-02	delivered
1	2026-10-11	shipped
4	2026-08-20	delivered
4	2026-09-09	placed
9	2026-08-25	cancelled
9	2026-09-10	placed
6	2026-10-04	cancelled
6	2026-10-18	placed

Step 1 of 6. FROM orders reads all 8 rows. Nothing else has run yet.

◀ BackPlayNext ▶Reset Ask me first Show as text

1SELECT customer_id, min(order_date) AS first_order
2FROM orders
3WHERE status <> 'cancelled'
4GROUP BY customer_id
5HAVING min(order_date) >= '2026-09-01' AND min(order_date) < '2026-10-01'
6ORDER BY customer_id;

Try your own input

Orders: customer_id, order_date, status1, 2026-09-02, delivered 1, 2026-10-11, shipped 4, 2026-08-20, delivered 4, 2026-09-09, placed 9, 2026-08-25, cancelled 9, 2026-09-10, placed 6, 2026-10-04, cancelled 6, 2026-10-18, placedShow it

The pay list

WITH staff runs first and leaves out the people on probation, once. Then the company average and each department read staff, so they always count the same people. Type your own staff, like a department of one, or a new hire with a big salary. The lesson: A team of one made the above-average pay list.

The pay list, step by step

⤢ Full view

1. WITH staff AS (…)
2. (SELECT avg(salary) FROM staff)
3. FROM staff GROUP BY department
4. HAVING avg(salary) > the average
5. SELECT name, round(avg(salary), 2)
6. ORDER BY name

employees

Kept?	department	salary	hired_on
✓kept	Engineering	9500.00	2023-02-01
✓kept	Engineering	5400.00	2025-09-01
✓kept	Support	4300.00	2025-03-17
✗dropped	Support	30000.00	2026-10-01
✓kept	Sales	6800.00	2024-07-29
✓kept	Sales	4900.00	2026-01-05
✓kept	Data	6000.00	2024-03-01

Step 1 of 6. WITH staff runs first. It keeps the people hired before 2026-05-01, and leaves out the one still on probation. From now on, the query reads staff. Question: Next, the company average reads staff. Will it count the Support hire paid 30000.00, still on probation?

What happens next? Next, the company average reads staff. Will it count the Support hire paid 30000.00, still on probation?

Yes: they work at the companyNo: staff left them outJust show me

◀ BackPlayNext ▶Reset Ask me first Show as text

1WITH staff AS (
2  SELECT d.name AS department, e.salary
3  FROM employees e
4  JOIN departments d ON d.department_id = e.department_id
5  WHERE e.hired_on < '2026-05-01'
6)
7SELECT department AS name, round(avg(salary), 2) AS avg_salary
8FROM staff
9GROUP BY department
10HAVING avg(salary) > (SELECT avg(salary) FROM staff)
11ORDER BY name;

Try your own input

Employees: department, salary, hired_onEngineering, 9500, 2023-02-01 Engineering, 5400, 2025-09-01 Support, 4300, 2025-03-17 Support, 30000, 2026-10-01 Sales, 6800, 2024-07-29 Sales, 4900, 2026-01-05 Data, 6000, 2024-03-01Show it

The growing orders

The subquery runs once for each order, with that order’s customer and date. It picks the same customer’s order just before it, by date, and skips cancelled ones. Type your own orders, like one typed in late with an earlier date. The lesson: Bigger than the order before, or just typed in late?.

The growing orders, step by step

⤢ Full view

1. FROM orders o
2. WHERE o.status <> 'cancelled'
3. For each row: o.total > (the subquery)
4. ORDER BY o.order_id

orders o

order_id	customer_id	order_date	total	status
101	1	2026-09-02	29.89	delivered
103	1	2026-09-20	104.00	delivered
108	1	2026-10-11	36.99	shipped
115	1	2026-10-01	50.00	delivered
112	7	2026-10-16	9.99	delivered
116	7	2026-10-17	5.00	cancelled
117	7	2026-10-18	9.00	placed

Step 1 of 9. FROM orders o reads all 7 rows. The subquery will read orders too, under the name p.

◀ BackPlayNext ▶Reset Ask me first Show as text

1SELECT o.order_id, o.customer_id, o.total
2FROM orders o
3WHERE o.status <> 'cancelled'
4  AND o.total > (
5    SELECT p.total
6    FROM orders p
7    WHERE p.customer_id = o.customer_id
8      AND p.status <> 'cancelled'
9      AND (p.order_date, p.order_id) < (o.order_date, o.order_id)
10    ORDER BY p.order_date DESC, p.order_id DESC
11    LIMIT 1
12  )
13ORDER BY o.order_id;

Try your own input

Orders: order_id, customer_id, order_date, total, status101, 1, 2026-09-02, 29.89, delivered 103, 1, 2026-09-20, 104.00, delivered 108, 1, 2026-10-11, 36.99, shipped 115, 1, 2026-10-01, 50.00, delivered 112, 7, 2026-10-16, 9.99, delivered 116, 7, 2026-10-17, 5.00, cancelled 117, 7, 2026-10-18, 9.00, placedShow it

The window shoppers

For each customer, EXISTS looks for one page view and stops at the first, so the customer stays one row. NOT EXISTS looks for that customer’s add to cart, so a guest’s NULL never gets in the way. Type your own events, like a guest who adds to cart. The lesson: Grace got the window-shopper email twice.

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

Window functions

Number each customer’s orders from newest to oldest with ROW_NUMBER(), then keep number 1: each customer’s latest order. Rows in the same group share a shade. The lesson: Each customer’s latest order.

Number each customer's orders, keep the first

⤢ Full view

1. FROM orders
2. PARTITION BY
3. ORDER BY in each group
4. ROW_NUMBER
5. WHERE rn = 1
6. ORDER BY

orders

customer_id	order_id	order_date	rn
1	101	2026-09-02
2	102	2026-09-05
1	103	2026-09-20
3	104	2026-09-21
2	106	2026-10-03
1	108	2026-10-11
2	109	2026-10-14
2	110	2026-10-14

Step 1 of 8. The inner query runs first. FROM orders reads every row: 8 orders.

◀ BackPlayNext ▶Reset Ask me first Show as text

1SELECT customer_id, order_id, order_date, total
2FROM (
3  SELECT o.*,
4         ROW_NUMBER() OVER (
5           PARTITION BY customer_id
6           ORDER BY order_date DESC, order_id DESC
7         ) AS rn
8  FROM orders AS o
9) AS ranked
10WHERE rn = 1
11ORDER BY customer_id;

Try your own input

Orders: customer_id, order_id, order_date1, 101, 2026-09-02 2, 102, 2026-09-05 1, 103, 2026-09-20 3, 104, 2026-09-21 2, 106, 2026-10-03 1, 108, 2026-10-11 2, 109, 2026-10-14 2, 110, 2026-10-14Show it