Grace shopped on one day and got the loyalty coupon

See the problem

The store sends a loyalty coupon to customers who ordered on 3 or more different days. This query picks them:

The coupon’s query

SELECT customer_id
FROM orders
GROUP BY customer_id
HAVING count(*) >= 3
ORDER BY customer_id;

For weeks it picked customers 1 and 2, Asha and Ben, and both had earned it. Then two new names showed up:

• Farid (customer 6) ordered on 22 October. He has 3 orders now, but one was cancelled.
• Grace (customer 7) placed two more orders on 16 October. She has 3 orders, all on that one day.

Neither ordered on 3 different days. Both got the coupon.

Define the task

List the customers who ordered on 3 or more different days, as customer_id, smallest first.

• Cancelled orders don’t count.
• Two orders on the same day count as one day.

Pause and think

⏸ Pause and think

count(*) counted Farid’s cancelled order, and Grace’s three orders on one day. What should the query count instead, and when should the cancelled order go?

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

Hints open one at a time:

Hint 1 of 3

count(*) counts the rows in a group. Each order is a row, so it counts orders, not days.

Hint 2 of 3

WHERE runs before GROUP BY. A cancelled order dropped in WHERE never reaches its group.

Hint 3 of 3

count(DISTINCT order_date) counts different days. Put it in HAVING.

Understand the idea

GROUP BY makes one group for each value, and each group becomes one row. GROUP BY customer_id puts all of a customer’s rows together. Aggregates like count(*) then work on each group, not on the whole table.

Runs	Clause	What it does
1	FROM orders	Reads the table’s rows
2	WHERE status <> 'cancelled'	Drops rows, before any groups exist
3	GROUP BY customer_id	Puts the rows that are left into groups
4	HAVING count(DISTINCT order_date) >= 3	Drops whole groups
5	SELECT customer_id	Gives one row for each group that’s left
6	ORDER BY customer_id	Sorts the rows

WHERE filters rows; HAVING filters groups. WHERE can’t use an aggregate, because the groups don’t exist yet: WHERE count(*) >= 3 stops with “aggregate functions are not allowed in WHERE”. HAVING can, because it runs after the groups are made. Put a filter on single rows, like status, in WHERE, so those rows never reach a group.

A group’s row can only show what’s the same for the whole group. That’s the grouped columns and aggregates. SELECT customer_id, order_date, count(*) ... GROUP BY customer_id stops with “column “orders.order_date” must appear in the GROUP BY clause or be used in an aggregate function”: a customer has many dates, and the row has room for one. Use min(order_date), or group by both.

HAVING can’t use a name from SELECT. HAVING n >= 3, where n is count(*) AS n, stops with “column “n” does not exist”, since SELECT runs after HAVING. Write the aggregate again: HAVING count(*) >= 3.

count(DISTINCT x) counts different values in each group. Ben (customer 2) has 4 orders, two on 14 October: count(*) is 4, and count(DISTINCT order_date) is 3.

Watch the coupon’s query group the orders, then drop whole groups. Then type your own orders, with a cancelled one, or several on one day:

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
1	2026-09-02	delivered
1	2026-09-20	delivered
1	2026-10-11	shipped
6	2026-10-04	cancelled
6	2026-10-18	placed
6	2026-10-22	placed
7	2026-10-16	delivered
7	2026-10-16	placed
7	2026-10-16	placed

Step 1 of 6. FROM orders reads all 9 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, status1, 2026-09-02, delivered 1, 2026-09-20, delivered 1, 2026-10-11, shipped 6, 2026-10-04, cancelled 6, 2026-10-18, placed 6, 2026-10-22, placed 7, 2026-10-16, delivered 7, 2026-10-16, placed 7, 2026-10-16, placedShow it

NULL is a group of its own. Grouping events by customer_id gives 8 groups: 7 customers, and one NULL group for every visitor who isn’t signed in. Practice problem 1 meets it.

GROUP BY doesn’t sort. The groups come out in whatever order is easiest for Postgres. Add ORDER BY when the order matters.

Climb the ladder

All three group the orders by customer and keep groups with 3 or more of something. What changes is which rows reach the groups, and what is counted.

Okay

HAVING count(*) >= 3

The idea: Count each customer’s orders.

It looks right because on the store’s data, the customers with 3 orders also ordered on 3 days.

SELECT customer_id
FROM orders
GROUP BY customer_id
HAVING count(*) >= 3
ORDER BY customer_id;

Gets right

Customers whose orders all count, one day each, like Asha’s

Gets wrong

A cancelled order; several orders on one day

Why

count(*) counts every row in the group: cancelled orders too, and each order on the same day.

Where it breaks: Farid orders again, and gets the coupon with a cancelled order among his 3. Okay passes 1 of the 3 checks below.

Better

WHERE status <> 'cancelled'

The idea: Drop cancelled orders before the groups are made.

The gap in Okay: cancelled orders reached the groups.

Highlighted lines are new in this rung.

SELECT customer_id
FROM orders
WHERE status <> 'cancelled'
GROUP BY customer_id
HAVING count(*) >= 3
ORDER BY customer_id;

Gets right

Cancelled orderswas Customers whose orders all count, one day each, like Asha’s

Gets wrong

Several orders on one day

Why

count(*) still counts orders, not days.

What changed from the previous rung

SELECT customer_id
FROM orders
WHERE status <> 'cancelled'
GROUP BY customer_id
HAVING count(*) >= 3
ORDER BY customer_id;

Where it breaks: Grace places two more orders on 16 October, and gets the coupon for one day of shopping. Better passes 2 of 3.

Best

HAVING count(DISTINCT order_date) >= 3

The idea: Count different days, not orders.

The gap in Better: it counted orders, not days.

Highlighted lines are new in this rung.

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

Gets right

Every checkwas Cancelled orders

Gets wrong

Nothing the checks try

Why

Two orders on one day are one date, so they count once.

What changed from the previous rung

SELECT customer_id
FROM orders
WHERE status <> 'cancelled'
GROUP BY customer_id
HAVING count(*) >= 3
HAVING count(DISTINCT order_date) >= 3
ORDER BY customer_id;

When Best isn’t best: “3 different days” ever is easy to earn over a few years. Most coupons mean a window, like the last 90 days: add it to WHERE. To print the coupon with the customer’s name, join customers, in the joins lesson.

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 loyalty coupon

Ctrl + Enter runs

The tables

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

-- Customers who ordered on 3 or more different days, not counting cancelled orders. SELECT customer_id, count(*) AS orders FROM orders GROUP BY customer_id ORDER BY 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: Farid orders again on 22 October (row order matters)
   What this check changes

   INSERT INTO orders (order_id, customer_id, order_date, status, total)
   VALUES (115, 6, '2026-10-22', 'placed', 18.00);

3. ○Not run yet: Grace places two more orders on 16 October (row order matters)
   What this check changes

   INSERT INTO orders (order_id, customer_id, order_date, status, total)
   VALUES (115, 7, '2026-10-16', 'placed', 12.00),
          (116, 7, '2026-10-16', 'placed', 4.50);

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: HAVING count(*) >= 3	Customers whose orders all count	Cancelled orders; several on one day	Every row should count, as it is
Better: WHERE drops cancelled orders	Cancelled orders	Several orders on one day	You want orders, not days
Best: count(DISTINCT order_date)	Every check	Nothing the checks try	You want different days

Common mistakes and edge cases

!WHERE before the groups, HAVING after

A filter on single rows goes in WHERE, so the rows never reach a group. A filter on a group’s aggregate goes in HAVING. WHERE count(*) >= 3 is an error; HAVING status <> 'cancelled' is too, since a group has many statuses.

• SELECT shows grouped columns and aggregates only. Anything else is an error, because a group has many values for it.
• HAVING can’t use SELECT names. Repeat the aggregate.
• NULL makes its own group. Drop it with WHERE x IS NOT NULL if it isn’t wanted.
• A group whose rows WHERE removed is gone. If a month whose orders were all cancelled should still show 0, filter inside the aggregate with FILTER instead. Practice problem 2 does this.
• GROUP BY doesn’t sort, and a list built with string_agg comes in no set order either: give it its own ORDER BY. Practice problem 3 shows why.
• extract(month ...) puts every October together, whatever the year. Keep the year in the group, or limit WHERE to one year.

Say it in the interview

Questions to ask first

• Do cancelled or returned orders count?
• Two orders on the same day: one visit or two?
• Over all time, or a window like the last 90 days?
• Should the result be sorted?

A 60-second walkthrough

“I drop cancelled orders in WHERE, so they never reach a group. Then GROUP BY customer_id makes one group per customer. HAVING keeps the groups with count(DISTINCT order_date) of 3 or more, because two orders on one day are one visit. HAVING is the right place, since WHERE runs before the groups exist. SELECT can show customer_id, as it’s what I grouped by, and I sort with ORDER BY, because GROUP BY doesn’t promise an order.”

Likely follow-ups

• What’s the difference between WHERE and HAVING? WHERE filters rows before grouping and can’t use aggregates; HAVING filters groups after, and can.
• How do you find duplicate emails? GROUP BY lower(email) HAVING count(*) > 1. Practice problem 3.
• Is DISTINCT the same as GROUP BY? For a list of different values, yes: SELECT DISTINCT city and SELECT city ... GROUP BY city give the same rows. GROUP BY also lets you count each group.

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. The most active customers (easy)

The team wants the 3 signed-in customers with the most events: customer_id and events, most first, and on a tie the smaller customer_id first. The checks look at who makes the 3, not the order you list them in.

Hint 1 of 3

GROUP BY customer_id, then count(*) gives each customer’s events.

Hint 2 of 3

Visitors who aren’t signed in have customer_id NULL, and they all fall into one NULL group.

Hint 3 of 3

When two customers tie at the cut, ORDER BY events DESC alone lets Postgres pick. Add customer_id.

The most active customers

Ctrl + Enter runs

The tables

• events (event_id, customer_id, event_type, occurred_at)

SELECT customer_id, event_type FROM events;

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

1. ○Not run yet: The store’s data
2. ○Not run yet: Five visitors browse without signing in
   What this check changes

   INSERT INTO events (event_id, customer_id, event_type, occurred_at)
   VALUES (21, NULL, 'page_view', '2026-10-21 09:00:00'),
          (22, NULL, 'page_view', '2026-10-21 09:05:00'),
          (23, NULL, 'page_view', '2026-10-21 10:00:00'),
          (24, NULL, 'page_view', '2026-10-21 11:00:00'),
          (25, NULL, 'page_view', '2026-10-21 12:00:00');

3. ○Not run yet: Dana checks out, so three customers tie on 3 events
   What this check changes

   INSERT INTO events (event_id, customer_id, event_type, occurred_at)
   VALUES (21, 4, 'checkout', '2026-10-15 11:09: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

ORDER BY events DESC LIMIT 3

The idea: Count each customer’s events, keep the top 3.

Okay passes 1 of 3. When five visitors browse without signing in, the NULL group has 6 events and comes first.

SELECT customer_id, count(*) AS events
FROM events
GROUP BY customer_id
ORDER BY events DESC
LIMIT 3;

Gets right

The store’s events, with few visitors

Gets wrong

Many visitors who aren’t signed in

Why

All visitors share the NULL group, and it can have the most events.

Better

WHERE customer_id IS NOT NULL

The idea: Drop the visitors’ events before grouping.

The gap in Okay: the NULL group counted as a customer.

Highlighted lines are new in this rung.

SELECT customer_id, count(*) AS events
FROM events
WHERE customer_id IS NOT NULL
GROUP BY customer_id
ORDER BY events DESC
LIMIT 3;

Gets right

Visitors

Gets wrong

A tie at the cut

Why

With ORDER BY events DESC alone, Postgres picks which tied customers make the 3.

What changed from the previous rung

SELECT customer_id, count(*) AS events
FROM events
WHERE customer_id IS NOT NULL
GROUP BY customer_id
ORDER BY events DESC
LIMIT 3;

Where it breaks: Dana checks out, so Asha, Dana and Farid each have 3 events, for 2 places. Better keeps Farid and Dana and leaves out Asha, who should be in. Better passes 2 of 3.

Best

ORDER BY events DESC, customer_id

The idea: Break ties by customer_id.

The gap in Better: a tie at the cut.

Highlighted lines are new in this rung.

SELECT customer_id, count(*) AS events
FROM events
WHERE customer_id IS NOT NULL
GROUP BY customer_id
ORDER BY events DESC, customer_id
LIMIT 3;

Gets right

Every check

Gets wrong

Nothing the checks try

Why

customer_id is different for every group, so every row has one place.

What changed from the previous rung

SELECT customer_id, count(*) AS events
FROM events
WHERE customer_id IS NOT NULL
GROUP BY customer_id
ORDER BY events DESC
ORDER BY events DESC, customer_id
LIMIT 3;

When Best isn’t best: if everyone tied at the cut should make the list, write FETCH FIRST 3 ROWS WITH TIES instead of LIMIT 3. It keeps the rows that tie with the last one.

2. Revenue for each month of 2026 (medium)

A chart shows each month of 2026 that had an order: month (its number, 9 for September) and revenue (the totals of orders that weren’t cancelled), by month. A month whose orders were all cancelled still gets a bar, at 0.

Hint 1 of 3

GROUP BY extract(month FROM order_date) makes one group per month number.

Hint 2 of 3

Month numbers repeat every year. Keep 2026 only, with WHERE on order_date.

Hint 3 of 3

If WHERE drops the cancelled orders, a month with only cancelled orders has no rows left, so no group. Filter inside the sum instead: sum(total) FILTER (WHERE status <> 'cancelled').

Revenue for each month of 2026

Ctrl + Enter runs

The tables

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

SELECT order_date, status, total FROM orders ORDER BY order_date;

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: An order from October 2025 is imported (row order matters)
   What this check changes

   INSERT INTO orders (order_id, customer_id, order_date, status, total)
   VALUES (90, 3, '2025-10-12', 'delivered', 40.00);

3. ○Not run yet: November’s only order is cancelled (row order matters)
   What this check changes

   INSERT INTO orders (order_id, customer_id, order_date, status, total)
   VALUES (115, 2, '2026-11-02', 'cancelled', 30.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

GROUP BY extract(month ...)

The idea: Group by month number, skip cancelled orders.

Okay passes 1 of 3. When an order from October 2025 is imported, it lands in October’s bar: 475.14 instead of 435.14.

SELECT extract(month FROM order_date) AS month, sum(total) AS revenue
FROM orders
WHERE status <> 'cancelled'
GROUP BY extract(month FROM order_date)
ORDER BY month;

Gets right

One year of orders, like the store’s

Gets wrong

Orders from another year

Why

extract(month ...) is 10 for every October, whatever the year.

Better

Keep 2026 in WHERE

The idea: Only orders from 2026 reach the groups.

The gap in Okay: every October became one group.

Highlighted lines are new in this rung.

SELECT extract(month FROM order_date) AS month, sum(total) AS revenue
FROM orders
WHERE status <> 'cancelled'
  AND order_date >= '2026-01-01'
  AND order_date < '2027-01-01'
GROUP BY extract(month FROM order_date)
ORDER BY month;

Gets right

Other years

Gets wrong

A month whose orders were all cancelled

Why

WHERE drops the cancelled orders before grouping, so that month has no rows and no group.

What changed from the previous rung

SELECT extract(month FROM order_date) AS month, sum(total) AS revenue
FROM orders
WHERE status <> 'cancelled'
  AND order_date >= '2026-01-01'
  AND order_date < '2027-01-01'
GROUP BY extract(month FROM order_date)
ORDER BY month;

Where it breaks: November’s only order is cancelled. November has no row at all, not a 0. Better passes 2 of 3.

Best

coalesce(sum(total) FILTER (...), 0)

The idea: Keep every order of 2026 in the groups; skip cancelled ones inside the sum.

The gap in Better: WHERE removed a whole month.

Highlighted lines are new in this rung.

SELECT
  extract(month FROM order_date) AS month,
  coalesce(sum(total) FILTER (WHERE status <> 'cancelled'), 0) AS revenue
FROM orders
WHERE order_date >= '2026-01-01'
  AND order_date < '2027-01-01'
GROUP BY extract(month FROM order_date)
ORDER BY month;

Gets right

Every check

Gets wrong

Nothing the checks try

Why

FILTER skips rows for one aggregate but keeps them in the group; coalesce turns the empty sum into 0.

What changed from the previous rung

SELECT extract(month FROM order_date) AS month, sum(total) AS revenue
SELECT
  extract(month FROM order_date) AS month,
  coalesce(sum(total) FILTER (WHERE status <> 'cancelled'), 0) AS revenue
FROM orders
WHERE status <> 'cancelled'
  AND order_date >= '2026-01-01'
WHERE order_date >= '2026-01-01'
  AND order_date < '2027-01-01'
GROUP BY extract(month FROM order_date)
ORDER BY month;

When Best isn’t best: a month with no orders at all still has no row. To show all 12 months, start from a list of months and join the orders to it, in the joins lessons. To keep the year in the group, use date_trunc('month', order_date) instead: it gives each month’s first day, so October 2025 and October 2026 stay apart.

3. Accounts that share an email (medium)

Some customers sign up twice, with the same email in different capitals. List each email used by more than one account, ignoring capitals: email (in lower case) and ids (the accounts’ customer_ids, smallest first, like 1, 9), by email. The store has none yet, so a right answer gives no rows on its data. The checks add some.

Hint 1 of 3

GROUP BY email, then HAVING count(*) > 1 keeps emails with more than one account.

Hint 2 of 3

Asha@Example.com and asha@example.com are different text. Group by lower(email).

Hint 3 of 3

string_agg(customer_id::text, ', ') joins the ids in whatever order it reads the rows. Give it its own ORDER BY: string_agg(..., ', ' ORDER BY customer_id).

Accounts that share an email

Ctrl + Enter runs

The tables

• customers (customer_id, name, email, city, signed_up_on)

SELECT customer_id, email FROM customers;

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

1. ○Not run yet: The store’s data: no email is used twice (row order matters)
2. ○Not run yet: Asha signs up again as Asha@Example.com (row order matters)
   What this check changes

   INSERT INTO customers (customer_id, name, email, city, signed_up_on)
   VALUES (9, 'Asha Rao', 'Asha@Example.com', 'Pune', '2026-10-21');

3. ○Not run yet: Asha signs up again, then the first account moves to Mumbai (row order matters)
   What this check changes

   INSERT INTO customers (customer_id, name, email, city, signed_up_on)
   VALUES (9, 'Asha Rao', 'Asha@Example.com', 'Pune', '2026-10-21');
   UPDATE customers SET city = 'Mumbai' WHERE customer_id = 1;

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

GROUP BY email

The idea: Group by email, keep groups with more than one account.

Okay passes 1 of 3. When Asha signs up again as Asha@Example.com, it finds nothing.

SELECT email, string_agg(customer_id::text, ', ') AS ids
FROM customers
GROUP BY email
HAVING count(*) > 1
ORDER BY email;

Gets right

Emails typed the same way

Gets wrong

The same email in different capitals

Why

Text compares exactly, so Asha@Example.com and asha@example.com are two groups of one.

Better

GROUP BY lower(email)

The idea: Group by the email in lower case.

The gap in Okay: capitals made two groups.

Highlighted lines are new in this rung.

SELECT lower(email) AS email, string_agg(customer_id::text, ', ') AS ids
FROM customers
GROUP BY lower(email)
HAVING count(*) > 1
ORDER BY email;

Gets right

Capitals

Gets wrong

The order of the ids

Why

string_agg joins the ids in the order it reads the rows, and that order can change.

What changed from the previous rung

SELECT email, string_agg(customer_id::text, ', ') AS ids
SELECT lower(email) AS email, string_agg(customer_id::text, ', ') AS ids
FROM customers
GROUP BY email
GROUP BY lower(email)
HAVING count(*) > 1
ORDER BY email;

Where it breaks: after the first account moves to Mumbai, Postgres stores that row again at the end of the table. Now it reads 9 first, and ids says 9, 1. Better passes 2 of 3.

Best

string_agg(... ORDER BY customer_id)

The idea: Give the list its own order.

The gap in Better: the list came in no set order.

Highlighted lines are new in this rung.

SELECT lower(email) AS email, string_agg(customer_id::text, ', ' ORDER BY customer_id) AS ids
FROM customers
GROUP BY lower(email)
HAVING count(*) > 1
ORDER BY email;

Gets right

Every check

Gets wrong

Nothing the checks try

Why

The ORDER BY inside string_agg sorts each group’s ids before joining them.

What changed from the previous rung

SELECT lower(email) AS email, string_agg(customer_id::text, ', ') AS ids
SELECT lower(email) AS email, string_agg(customer_id::text, ', ' ORDER BY customer_id) AS ids
FROM customers
GROUP BY lower(email)
HAVING count(*) > 1
ORDER BY email;

When Best isn’t best: lower() doesn’t catch a space typed before the email; trim() does, as in the DISTINCT lesson. Better still, stop the second sign-up at the door: CREATE UNIQUE INDEX ON customers (lower(email)) makes Postgres refuse it.

Check yourself

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

1. Multiple choiceIn which order does Postgres run these four clauses?

   WHERE, HAVING, GROUP BY, SELECTWHERE, GROUP BY, HAVING, SELECTGROUP BY, WHERE, HAVING, SELECTSELECT, WHERE, GROUP BY, HAVING

   Check answer
2. Spot the bugThis should show each customer’s orders. What happens?

   SELECT customer_id, order_date, count(*)
   FROM orders
   GROUP BY customer_id;

   One row per customer, with their first order_dateOne row per orderPostgres stops with an errorOne row per customer, with any one of their order_dates

   Check answer
3. Predict the outputThe events table has events from 7 different signed-in customers, and one from a visitor who isn’t signed in, with no customer_id. How many rows does this give?

   SELECT customer_id, count(*)
   FROM events
   GROUP BY customer_id;

   78201

   Check answer
4. Multiple choiceThe coupon should only count delivered orders. Where does status = 'delivered' go?

   In WHERE, so the other orders never reach a groupIn HAVING, after the groups are madeIn GROUP BY, next to customer_idIn SELECT, next to customer_id

   Check answer
5. Predict the outputAn order from October 2025 is imported, so the store has ten October orders: one from 2025 and nine from 2026. How many rows does this give?

   SELECT extract(month FROM order_date) AS month, count(*)
   FROM orders
   WHERE extract(month FROM order_date) = 10
   GROUP BY extract(month FROM order_date);

   201An error

   Check answer

Flashcards

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

• Card 1What’s the difference between WHERE and HAVING?Show answer
• Card 2After GROUP BY, what can SELECT show?Show answer
• Card 3Does GROUP BY sort the result?Show answer
• Card 4How does GROUP BY treat NULL?Show answer
• Card 5How do you find values used more than once, like duplicate emails?Show answer
• Card 6WHERE drops every cancelled order, and one month had only cancelled orders. How do you still show it, at 0?Show answer

Add 6 cards to my review deck

Cheat sheet

• Order: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT.
• WHERE drops rows before grouping; HAVING drops groups after, and can use aggregates.
• SELECT shows grouped columns and aggregates only.
• count(DISTINCT x) counts different values in a group.
• NULL is a group of its own. GROUP BY doesn’t sort.
• Duplicates: GROUP BY x HAVING count(*) > 1.
• Keep a group that WHERE would empty: FILTER (WHERE ...) inside the aggregate.

Go deeper

• In the PostgreSQL docs: GROUP BY and HAVING and aggregate functions, string_agg among them.
• Try your own orders: the loyalty coupon on the SQL visualizer, or any query of your own, step by step.
• 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.

• Group rows with GROUP BY and say what one group’s row can show
• Choose between WHERE and HAVING, and say which runs first
• Count different values in a group with count(DISTINCT ...)
• Spot the NULL group, a group WHERE removed, and a list built in no order

✓Mark as completeI already know this

Status: Not started