JOIN — Learn Practical Joins and Subqueries in Practice

QUESTION 6

JOIN + GROUP BY Aggregation — Aggregate Sales by Customer with COUNT and SUM After an INNER JOIN

INNER JOINGROUP BYCOUNT / SUMSales aggregation

Background ▼

JOIN and GROUP BY can be combined in the flow “join → group → aggregate.” JOIN widens rows horizontally, while GROUP BY consolidates rows vertically. Combining these two operations lets you write aggregate queries across multiple tables.

SELECT
  c.name,
  COUNT(o.order_id) AS order_count,  -- count the orders
  SUM(o.amount)     AS total_amount  -- calculate the total amount
FROM       customers AS c
INNER JOIN orders    AS o ON c.customer_id = o.customer_id
GROUP BY   c.customer_id, c.name
ORDER BY   total_amount DESC;

List every non-aggregate column from SELECT in GROUP BY: A column used without an aggregate function, such as SELECT c.name, must be included in GROUP BY. With only GROUP BY c.customer_id, you cannot select c.name (PostgreSQL and standard SQL).

Problem ▼

INNER JOIN the customers and orders tables, then retrieve each customer's order count (order_count) and total amount (total_amount). Exclude customers with no orders (Suzuki) and sort by total amount in descending order.

Tables used

▸ customers

customer_id	name	city
1	Tanaka	Tokyo
2	Sato	Osaka
3	Yamada	Tokyo
4	Suzuki	Nagoya

▸ orders

order_id	customer_id	amount
1	1	5000
2	1	3000
3	2	7000
4	3	2000

Expected Output ▼

name	order_count	total_amount
Tanaka	2	8000
Sato	1	7000
Yamada	1	2000

Model Answer ▼

SELECT
  c.name,
  COUNT(o.order_id)  AS order_count,  -- order count for each customer
  SUM(o.amount)      AS total_amount  -- total amount for each customer

FROM customers AS c
INNER JOIN orders AS o
  ON c.customer_id = o.customer_id  -- Suzuki is excluded by INNER JOIN because there are no orders

GROUP BY c.customer_id, c.name  -- list every non-aggregate column from SELECT

ORDER BY total_amount DESC;  -- sort in descending order by the aggregate alias

/*
  Execution order:
  1. FROM customers AS c     → read customers
  2. INNER JOIN orders AS o  → join with ON (exclude customers with no orders)
  3. GROUP BY customer_id    → group by customer
  4. SELECT COUNT/SUM        → aggregate each group
  5. ORDER BY total_amount   → sort in descending order
  */

Explanation (table transitions & key points) ▼

SELECT
  c.name,
  COUNT(o.order_id)  AS order_count,
  SUM(o.amount)      AS total_amount
FROM customers AS c
INNER JOIN orders AS o
  ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name
ORDER BY total_amount DESC;

LEGEND ▼

Rows read / loaded

① FROM ▼

FROM customers AS cRead the customers table (4 rows) as the left table. Suzuki (customer_id=4) has no matching row in orders, so the next INNER JOIN excludes Suzuki.

1 / 4

customer_id	name	city
1	Tanaka	Tokyo
2	Sato	Osaka
3	Yamada	Tokyo
4	Suzuki	Nagoya

All 4 rows read

LEARNING POINTS

INNER JOIN + GROUP BY

Widen rows through a JOIN, consolidate them with GROUP BY, and calculate values with aggregate functions.

A: customers (c) / B: orders (o)

Think in the order “JOIN → GROUP BY → aggregate”: Before writing SQL, think in this order: which tables to join (JOIN) → what unit to group by (GROUP BY) → what to calculate (COUNT/SUM). In this question, the flow is customers ⋈ orders → by customer → order count and total amount. This thinking order applies to nearly every aggregate query.

COUNT(*) vs COUNT(column): COUNT(*) counts every row, including rows with NULL values, while specifying a column, as in COUNT(o.order_id), counts only rows where that column is not NULL. With INNER JOIN the unmatched rows have already been excluded, so there is no difference here, but COUNT(column) is safer when combined with LEFT JOIN. To display users with zero orders as 0, use the pattern LEFT JOIN + COUNT(o.order_id) + COALESCE(SUM(o.amount), 0).

ANTI-PATTERNS

Omitting a non-aggregate SELECT column from GROUP BY causes an error: Writing SELECT c.name, c.city, COUNT(...) with GROUP BY c.customer_id, c.name causes an error because c.city is not included in GROUP BY (PostgreSQL). Include every column used without an aggregate function in SELECT.

INNER JOIN removes customers with zero orders: If the requirement is “show the order count for every customer,” using INNER JOIN removes customers with no orders from the result. To display zero-order customers as 0, switch to LEFT JOIN and use COUNT(o.order_id) with COALESCE(SUM(o.amount), 0). Confirming the requirement is essential.

SELECT
  c.name,
  COUNT(o.order_id)       AS order_count,
  COALESCE(SUM(o.amount), 0) AS total_amount
FROM customers AS c
LEFT JOIN orders AS o
  ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name;

LEGEND ▼

Rows read / join targets

① LEFT JOIN ▼

LEFT JOIN orders AS oUse LEFT JOIN to keep Suzuki (customer_id=4), who has no orders. All columns from orders become NULL for Suzuki.

1 / 2

▸ customers (c)

customer_id	name
1	Tanaka
2	Sato
3	Yamada
4	Suzuki

⋈

▸ orders (o)

order_id	amount
1	5000
2	3000
3	7000
4	2000
NULL	NULL

Matches: 4 rows + 1 NULL row for Suzuki = 5 rows

FIELD NOTE

This JOIN + GROUP BY aggregation pattern is a standard for dashboard APIs. Examples include: (1) monthly sales reports (GROUP BY DATE_TRUNC('month', ordered_at)), (2) product counts by category (GROUP BY category_id), and (3) per-user activity summaries by joining a log table and counting each action type. Making this basic query shape second nature can greatly improve implementation speed.

Model answers and explanations are for members

Read every model answer, explanation and table-transition visualization across all 48 advanced sets (240 questions)
One-time purchase — no subscription. Future advanced sets are included
Background, problem and expected output stay free

Unlock for $20 USD

Payments are securely processed by Stripe — your card details never touch this site.

You are charged in US dollars. If your card is issued in another currency, your bank’s exchange rate and any foreign transaction fee apply.

Already purchased? Sign in on your account page.

QUESTION 7

FULL OUTER JOIN — Include Rows Found on Either Side and Classify Employees as Retired, Continuing, or New

FULL OUTER JOINCOALESCECASE WHENDifference detection

Background ▼

FULL OUTER JOIN includes all rows from both tables, including rows that exist in only one of the two tables. Columns from the side with no match are filled with NULL.

SELECT ...
FROM   table_a AS a
FULL OUTER JOIN table_b AS b ON a.id = b.id;
-- a.id IS NULL → row exists only in B
-- b.id IS NULL → row exists only in A
-- neither is NULL → row exists in both

Combine the id with COALESCE: In a FULL JOIN, either side's id may be NULL, so COALESCE(a.id, b.id) is the standard idiom for retrieving whichever id is not NULL.

MySQL does not support FULL OUTER JOIN: PostgreSQL, SQL Server, and Oracle support it as standard. In MySQL, use LEFT JOIN UNION RIGHT JOIN as an alternative.

Problem ▼

FULL OUTER JOIN the employee tables for 2023 and 2024, then classify every employee's status as retired, continuing, or new. Use CASE WHEN to classify rows according to whether one side's emp_id is NULL.

Tables used

▸ employees_2023 (previous year)

emp_id	name
1	Tanaka
2	Sato
3	Yamada

▸ employees_2024 (current year)

emp_id	name
2	Sato
3	Yamada
4	Suzuki

Expected Output ▼

emp_id	name_2023	name_2024	status
1	Tanaka	NULL	Retired
2	Sato	Sato	Continuing
3	Yamada	Yamada	Continuing
4	NULL	Suzuki	New hire

Model Answer ▼

SELECT
  COALESCE(a.emp_id, b.emp_id) AS emp_id,    -- retrieve the ID even when one side is NULL
  a.name                         AS name_2023, -- name from the left table (previous year)
  b.name                         AS name_2024, -- name from the right table (current year)
  CASE
    WHEN a.emp_id IS NULL THEN 'New hire'      -- absent in the previous year → new employee
    WHEN b.emp_id IS NULL THEN 'Retired'      -- absent in the current year → retired
    ELSE                       'Continuing'   -- present in both → continuing
  END AS status

FROM employees_2023 AS a
FULL OUTER JOIN employees_2024 AS b
  ON a.emp_id = b.emp_id

ORDER BY COALESCE(a.emp_id, b.emp_id); -- combine the IDs with COALESCE before sorting

/*
  Execution order:
  1. FROM employees_2023 AS a               → read the 3 previous-year employees
  2. FULL OUTER JOIN employees_2024 AS b
  3. SELECT + COALESCE + CASE WHEN          → combine emp_id and classify status
  4. ORDER BY COALESCE(a.emp_id, b.emp_id)  → sort by emp_id in ascending order
  */

Explanation (table transitions & key points) ▼

SELECT
  COALESCE(a.emp_id, b.emp_id) AS emp_id,
  a.name AS name_2023,
  b.name AS name_2024,
  CASE
    WHEN a.emp_id IS NULL THEN 'New hire'
    WHEN b.emp_id IS NULL THEN 'Retired'
    ELSE 'Continuing'
  END AS status
FROM employees_2023 AS a
FULL OUTER JOIN employees_2024 AS b
  ON a.emp_id = b.emp_id
ORDER BY COALESCE(a.emp_id, b.emp_id);

LEGEND ▼

Rows read / loaded

① FROM ▼

FROM employees_2023 AS aRead the previous-year employee table (3 rows) as the left table. FULL OUTER JOIN keeps rows that exist on only one side in the final result.

1 / 4

emp_id	name
1	Tanaka
2	Sato
3	Yamada

All 3 rows read (left table)

LEARNING POINTS

FULL OUTER JOIN

Join every row from both tables, including rows found only on one side.

A: employees_2023 (a) / B: employees_2024 (b)

FULL OUTER JOIN = LEFT JOIN ∪ RIGHT JOIN: The row count is the total of A ∩ B (common rows) + A only + B only. Here, 2 common rows + 1 Tanaka row (A only) + 1 Suzuki row (B only) = 4 rows. Full JOIN is useful when you need to detect differences between two tables in both directions, such as data-migration consistency checks or comparisons between versions.

Classify three join outcomes with IS NULL: After a FULL OUTER JOIN, combining a.emp_id IS NULL and b.emp_id IS NULL in CASE WHEN classifies rows into “A only,” “B only,” and “both.” This pattern classification with IS NULL is the most important FULL JOIN idiom.

ANTI-PATTERNS

Writing FULL OUTER JOIN in MySQL causes an error: MySQL does not support FULL OUTER JOIN. Use SELECT ... FROM a LEFT JOIN b ON a.id=b.id UNION ALL SELECT ... FROM a RIGHT JOIN b ON a.id=b.id WHERE a.id IS NULL as an alternative. PostgreSQL supports it as standard.

Sorting without COALESCE makes the position of NULL implementation-dependent: With ORDER BY a.emp_id, the position of the row where a.emp_id is NULL (Suzuki) depends on the database implementation (PostgreSQL defaults to NULLS LAST). After a FULL JOIN, combine the IDs with COALESCE(a.id, b.id) before sorting as a best practice.

FIELD NOTE

Practical examples of FULL OUTER JOIN include: (1) consistency checks after data migration (FULL JOIN old and new tables to find rows whose IDs exist on only one side), (2) detecting missed master-data synchronization (FULL JOIN production and development master tables and visualize differences with CASE WHEN), and (3) checking A/B-test variant coverage (FULL JOIN the configuration tables on the A and B sides to find unset items). Use it whenever you need to see differences between two datasets in both directions.

Model answers and explanations are for members

Read every model answer, explanation and table-transition visualization across all 48 advanced sets (240 questions)
One-time purchase — no subscription. Future advanced sets are included
Background, problem and expected output stay free

Unlock for $20 USD

Payments are securely processed by Stripe — your card details never touch this site.

You are charged in US dollars. If your card is issued in another currency, your bank’s exchange rate and any foreign transaction fee apply.

Already purchased? Sign in on your account page.

QUESTION 8

Many-to-Many JOIN (M:N) — Connect Users and Tags by INNER JOINing Through a Junction Table Twice

INNER JOIN × 2Junction tableM:N relationshipTag system

Background ▼

Web applications frequently have many-to-many (M:N) relationships: one user can have multiple tags, and one tag can belong to multiple users. In an RDB, this relationship is represented with a junction table.

SELECT u.name, t.tag_name
FROM       users     AS u
INNER JOIN user_tags AS ut ON u.user_id = ut.user_id  -- ① users → junction table
INNER JOIN tags      AS t  ON ut.tag_id = t.tag_id;   -- ② junction table → tags

The junction table is the bridge: The user_tags table contains only user_id and tag_id and serves as the bridge between users and tags. The combination of the two foreign keys is typically the primary key.

Problem ▼

INNER JOIN the three tables users, user_tags (the junction table), and tags twice to retrieve the list of tag names held by each user. Sort by user_id, then tag_id.

Tables used

▸ users

user_id	name
1	Tanaka
2	Sato
3	Yamada

▸ user_tags (junction table)

user_id	tag_id
1	1
1	2
2	2
2	3
3	1

▸ tags

tag_id	tag_name
1	Frontend
2	Backend
3	DevOps

Expected Output ▼

name	tag_name
Tanaka	Frontend
Tanaka	Backend
Sato	Backend
Sato	DevOps
Yamada	Frontend

Model Answer ▼

SELECT
  u.name,     -- user name from the users table
  t.tag_name  -- tag name from the tags table

FROM users AS u
INNER JOIN user_tags AS ut   -- JOIN 1: users → user_tags (first stage through the junction table)
  ON u.user_id = ut.user_id
INNER JOIN tags AS t         -- JOIN 2: user_tags → tags (from the junction table to the actual table)
  ON ut.tag_id = t.tag_id

ORDER BY u.user_id, t.tag_id; -- sort by user, then tag

/*
  Execution order:
  1. FROM users AS u               → read users
  2. INNER JOIN user_tags AS ut    → match by user_id
  3. INNER JOIN tags AS t          → match by tag_id
  4. SELECT u.name, t.tag_name     → project the 2 columns
  5. ORDER BY u.user_id, t.tag_id  → sort by user and tag
  */

Explanation (table transitions & key points) ▼

SELECT
  u.name,
  t.tag_name
FROM users AS u
INNER JOIN user_tags AS ut
  ON u.user_id = ut.user_id
INNER JOIN tags AS t
  ON ut.tag_id = t.tag_id
ORDER BY u.user_id, t.tag_id;

LEGEND ▼

Rows read / loaded

① FROM users ▼

FROM users AS uRead the users table (3 rows) as the starting point. In a three-table JOIN, choosing the entity that is most central to the query as the starting point improves readability.

1 / 4

user_id	name
1	Tanaka
2	Sato
3	Yamada

All 3 rows read

LEARNING POINTS

INNER JOIN × 2 (M:N)

Resolve a many-to-many relationship with two JOINs through a junction table.

A: users → user_tags (junction) → tags :B

Resolve an M:N relationship with “two JOINs”: users and tags cannot be joined directly because they have no foreign key between them. Use the junction table user_tags as a bridge and JOIN in two stages: users → user_tags → tags. This three-table pattern is common in every kind of M:N design, including tags, permissions, and categories.

A junction table can also carry additional information: This question's user_tags contains only user_id and tag_id, but attributes attached to the relationship, such as created_at (when the tag was added) or weight (the tag's weight), can also be added. This is called an “attributed junction table,” and order_items with quantity and unit price for an order-product relationship is a typical example.

ANTI-PATTERNS

Packing an M:N relationship into one array column: Storing a many-to-many relationship in one column as an array or comma-separated string, such as users.tag_ids = '{1,2,3}', is an anti-pattern. You cannot retrieve tag names with JOIN, and searching, aggregation, and indexing all become difficult. Always normalize with a junction table.

Allowing duplicate rows in the junction table: Without a UNIQUE constraint on the (user_id, tag_id) pair, the same combination can be inserted more than once. The JOIN result then doubles and aggregates become incorrect. Always define a composite PRIMARY KEY or UNIQUE constraint on (user_id, tag_id) in the junction table.

SELECT u.name, ut.tag_id
FROM users AS u
INNER JOIN user_tags_dup AS ut
  ON u.user_id = ut.user_id;

LEGEND ▼

Rows read / join targets

① JOIN with dangerous duplicates ▼

INNER JOIN user_tags_dupIf two identical (1, 1) pairs are accidentally registered in the junction table, Tanaka's (user_id=1) rows multiply unexpectedly.

1 / 1

▸ users (u)

user_id	name
1	Tanaka

⋈

▸ user_tags_dup (with duplicates)

user_id	tag_id
1	1
1	1

The same pair matches multiple times

FIELD NOTE

The M:N junction-table pattern appears throughout web applications. Examples include: (1) articles and tags (articles ↔ article_tags ↔ tags), (2) users and permissions/roles (users ↔ user_roles ↔ roles), (3) orders and products (orders ↔ order_items ↔ products, with attributes such as quantity), and (4) courses and learners (courses ↔ enrollments ↔ students). When you see this pattern, recognizing it as “three tables, two JOINs” can greatly speed up implementation.

Model answers and explanations are for members

Read every model answer, explanation and table-transition visualization across all 48 advanced sets (240 questions)
One-time purchase — no subscription. Future advanced sets are included
Background, problem and expected output stay free

Unlock for $20 USD

Payments are securely processed by Stripe — your card details never touch this site.

You are charged in US dollars. If your card is issued in another currency, your bank’s exchange rate and any foreign transaction fee apply.

Already purchased? Sign in on your account page.

QUESTION 9

CTE (WITH) + JOIN — Turn Per-Product Sales Aggregates into a Virtual Table, Then Add Details with a JOIN

WITH / CTEINNER JOINGROUP BY + SUMQuery readability

Background ▼

A CTE (Common Table Expression, or WITH clause) gives a complex subquery a name so it can be reused as a “virtual table.” Because a query can be written as multiple steps, readability and maintainability improve significantly.

WITH cte_name AS (
  -- subquery (aggregation, filtering, etc.)
  SELECT col1, SUM(col2) AS total
  FROM some_table
  GROUP BY col1
)
SELECT m.name, c.total
FROM       master_table AS m
INNER JOIN cte_name      AS c ON m.id = c.col1;

A CTE is a “temporary named subquery”: Rewriting an inline subquery in the FROM clause as a CTE improves readability. The same CTE can be referenced multiple times, making it useful for organizing complex aggregate queries (supported by PostgreSQL, MySQL 8+, SQL Server, and others).

Problem ▼

Use a CTE to aggregate the order_items table by product (total quantity and order count), then INNER JOIN it with the products table to retrieve the product name, total quantity, order count, and total revenue (price × total_qty). Sort by total revenue in descending order.

Tables used

▸ products

product_id	name	price
1	Notebook PC	80000
2	Mouse	3000
3	Keyboard	8000

▸ order_items

item_id	product_id	quantity
1	1	2
2	2	5
3	2	3
4	3	1
5	1	1

Expected Output ▼

name	total_qty	order_count	total_revenue
Notebook PC	3	2	240000
Mouse	8	2	24000
Keyboard	1	1	8000

Model Answer ▼

-- CTE: aggregate order_items by product to create a virtual table
WITH sales_summary AS (
  SELECT
    product_id,
    SUM(quantity)  AS total_qty,   -- total quantity sold
    COUNT(*)       AS order_count  -- number of orders (rows)
  FROM order_items
  GROUP BY product_id              -- aggregate by product
)

-- Main query: JOIN the CTE with products and add details
SELECT
  p.name,
  ss.total_qty,
  ss.order_count,
  p.price * ss.total_qty AS total_revenue  -- unit price × total quantity = total revenue

FROM products AS p
INNER JOIN sales_summary AS ss     -- JOIN the CTE like a regular table
  ON p.product_id = ss.product_id

ORDER BY total_revenue DESC;  -- output in descending order of total revenue

/*
  Execution order:
  1. WITH sales_summary AS (...)        → GROUP BY the 5 order_items rows by product_id
  2. FROM products AS p                 → read the 3 products
  3. INNER JOIN sales_summary AS ss     → match on product_id (all 3 rows match)
  4. SELECT ... p.price * ss.total_qty  → calculate total revenue for each row
  5. ORDER BY total_revenue DESC        → sort in descending order
  */

Explanation (table transitions & key points) ▼

WITH sales_summary AS (
  SELECT
    product_id,
    SUM(quantity) AS total_qty,
    COUNT(*) AS order_count
  FROM order_items
  GROUP BY product_id
)
SELECT
  p.name, ss.total_qty,
  ss.order_count,
  p.price * ss.total_qty AS total_revenue
FROM products AS p
INNER JOIN sales_summary AS ss
  ON p.product_id = ss.product_id
ORDER BY total_revenue DESC;

LEGEND ▼

Grouping keys / aggregation targets

Group classification

① WITH CTE: GROUP BY ▼

WITH sales_summary AS (SELECT ... FROM order_items GROUP BY product_id)Group order_items (5 rows) by product_id. Rows with the same color belong to the same product group. SUM and COUNT will be applied to each group.

1 / 4

item_id	product_id	quantity
1	1	2
5	1	1
2	2	5
3	2	3
4	3	1

5 rows → GROUP BY → 3 groups

LEARNING POINTS

WITH (CTE) + INNER JOIN

Turn an aggregate into a virtual table with a CTE, then JOIN it in the main query to add details.

A: products / CTE: sales_summary

CTE vs subquery — choose based on the query: CTEs and inline subqueries return the same result, but a CTE is more readable when a query has multiple steps. In the “aggregate, then JOIN” pattern, putting a subquery inside FROM makes the query more deeply nested. A CTE lets you write “aggregate first → JOIN next” as explicit stages, making the intent clear.

Chain multiple CTEs: Define multiple CTEs separated by commas, as in WITH cte1 AS (...), cte2 AS (...) SELECT ..., and reference an earlier CTE from a later one. The greatest strength of CTEs is that complex report queries can be written step by step: step 1 → step 2 → step 3.

ANTI-PATTERNS

A CTE is verbose for a simple operation: An obvious CTE such as WITH a AS (SELECT * FROM table) is unnecessary. Use a CTE when preprocessing such as aggregation or filtering is needed, or when the same subquery is referenced multiple times. A CTE is not needed for a one-stage simple JOIN.

CTEs before PostgreSQL v12 were optimization fences: Before PostgreSQL 12, a CTE was treated as an “optimization fence,” so the main query's WHERE conditions were not pushed into the CTE (which could make the execution plan inefficient). When performance matters, check the execution plan with EXPLAIN ANALYZE.

FIELD NOTE

CTEs are especially useful in backend reporting APIs. Examples include: (1) a monthly KPI dashboard (aggregate by month in a CTE → JOIN the target table → calculate the achievement rate), (2) a user ranking (aggregate points in a CTE → JOIN user details → rank with RANK()), and (3) inventory alerts (aggregate remaining stock by product in a CTE → JOIN a threshold table for comparison). Use CTEs actively when you want multi-step processing to remain readable.

Model answers and explanations are for members

Read every model answer, explanation and table-transition visualization across all 48 advanced sets (240 questions)
One-time purchase — no subscription. Future advanced sets are included
Background, problem and expected output stay free

Unlock for $20 USD

Payments are securely processed by Stripe — your card details never touch this site.

You are charged in US dollars. If your card is issued in another currency, your bank’s exchange rate and any foreign transaction fee apply.

Already purchased? Sign in on your account page.

QUESTION 10

ROW_NUMBER() + Subquery JOIN — Number Rows Within Each Group and Retrieve Only the Latest Row

ROW_NUMBER()PARTITION BYSubquery JOINLatest-row retrieval

Background ▼

The window function ROW_NUMBER() numbers rows within each group (partition). Using this number to filter for only a specific row within each group (such as the latest or maximum-valued row) is one of the most common advanced patterns in practical work.

ROW_NUMBER() OVER (
  PARTITION BY column_name    -- define groups (unlike GROUP BY, keep the rows)
  ORDER BY     column_name DESC -- define the order within each group
) AS rn              -- the latest or maximum row becomes rn=1

Unlike GROUP BY, a window function does not remove rows: GROUP BY aggregates multiple rows into one, while ROW_NUMBER() only assigns a number to each row and does not change the row count. Even when PARTITION BY defines groups, every row is retained and receives its within-group rank as rn.

A window function cannot be used directly in WHERE: Writing WHERE ROW_NUMBER() OVER (...) = 1 causes an error. Calculate the window function inside a subquery (or CTE), then filter rn in the outer WHERE or JOIN ON clause.

Problem ▼

Retrieve exactly one latest order (the order with the newest ordered_at) for each user from the orders table, then INNER JOIN it with the users table to add user names. Use ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY ordered_at DESC) and select only rows where rn = 1.

Tables used

▸ users

user_id	name
1	Tanaka
2	Sato
3	Yamada

▸ orders

order_id	user_id	amount	ordered_at
1	1	5000	2024-01-10
2	1	8000	2024-02-15
3	2	3000	2024-01-20
4	2	6000	2024-03-01
5	3	4000	2024-02-05

Expected Output ▼

name	order_id	amount	ordered_at
Sato	4	6000	2024-03-01
Tanaka	2	8000	2024-02-15
Yamada	5	4000	2024-02-05

Model Answer ▼

SELECT
  u.name,
  o.order_id,
  o.amount,
  o.ordered_at

FROM users AS u
INNER JOIN (

  -- Subquery: assign a row number within each user_id in descending ordered_at order
  SELECT
    *,
    ROW_NUMBER() OVER (
      PARTITION BY user_id        -- create a group for each user_id
      ORDER BY     ordered_at DESC -- newer orders receive smaller rn values
    ) AS rn                        -- latest row = rn = 1
  FROM orders

) AS o
  ON  u.user_id = o.user_id
  AND o.rn      = 1          -- join only rn=1 (the latest row) in the ON clause

ORDER BY o.ordered_at DESC;  -- sort by order date in descending order

/*
  Execution order:
  1. Subquery ROW_NUMBER()                   → number each user's orders from newest to oldest
  2. FROM users AS u                      → read users
  3. INNER JOIN (subquery) AS o           → match on user_id AND rn=1
  4. SELECT u.name, o.order_id, o.amount  → project the columns
  5. ORDER BY ordered_at DESC             → sort by newest order date first
  */

Explanation (table transitions & key points) ▼

SELECT
  u.name, o.order_id,
  o.amount, o.ordered_at
FROM users AS u
INNER JOIN (
  SELECT *, ROW_NUMBER() OVER (
    PARTITION BY user_id
    ORDER BY ordered_at DESC
  ) AS rn
  FROM orders
) AS o
  ON  u.user_id = o.user_id
  AND o.rn = 1
ORDER BY o.ordered_at DESC;

LEGEND ▼

Grouping keys / aggregation targets

Group classification

① ROW_NUMBER() OVER PARTITION BY ▼

ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY ordered_at DESC)Group all orders (5 rows) by user_id (same color means same user) and assign row numbers (rn) in descending ordered_at order within each group. The latest order becomes rn=1. Unlike GROUP BY, the row count does not change.

1 / 4

order_id	user_id	amount	ordered_at	rn
2	1	8000	2024-02-15	1
1	1	5000	2024-01-10	2
4	2	6000	2024-03-01	1
3	2	3000	2024-01-20	2
5	3	4000	2024-02-05	1

Retain all 5 rows (unlike GROUP BY, rows are not removed) / rn=1 is the latest row in each group

LEARNING POINTS

ROW_NUMBER() + INNER JOIN

Number rows within each group with a window function, filter to rn=1, and then JOIN.

A: users / sub: orders (with ROW_NUMBER)

ROW_NUMBER vs RANK vs DENSE_RANK: ROW_NUMBER() always assigns unique sequential numbers, giving different numbers even to tied values. RANK() gives ties the same number and skips the next number (1,1,3), while DENSE_RANK() gives ties the same number without skipping (1,1,2). ROW_NUMBER is best when you need exactly one row with no duplicates. Use RANK when you want multiple tied first-place rows.

SELECT
  order_id, ordered_at,
  ROW_NUMBER() OVER (ORDER BY ordered_at DESC) AS rn,
  RANK()       OVER (ORDER BY ordered_at DESC) AS rnk
FROM orders_dup;

LEGEND ▼

Rows read / loaded

① FROM orders_dup ▼

FROM orders_dup ORDER BY ordered_at DESCRead orders_dup, which contains two orders with the same timestamp (2024-02-15), in descending ordered_at order. These two tied rows are where ROW_NUMBER() and RANK() behave differently.

1 / 2

order_id	ordered_at
6	2024-02-15
2	2024-02-15
5	2024-02-05
1	2024-01-10

All 4 rows read (2024-02-15 is duplicated)

Rewrite it with a CTE for greater readability: This question's inline subquery can be rewritten as a CTE:

WITH ranked_orders AS (SELECT *, ROW_NUMBER()... FROM orders) SELECT ... FROM users INNER JOIN ranked_orders AS o ON ... AND o.rn = 1

. Both return the same result, but a CTE makes the subquery's intent explicit by giving it a name and is easier to read.

ANTI-PATTERNS

Using ROW_NUMBER() directly in WHERE causes an error: WHERE ROW_NUMBER() OVER (...) = 1 is invalid because of SQL's evaluation order (window functions are evaluated in SELECT, so they have not been evaluated in WHERE). Always calculate rn first in a subquery or CTE, then filter it in the outer query.

ROW_NUMBER is more robust than MAX(created_at) + JOIN: WHERE ordered_at = (SELECT MAX(ordered_at) FROM orders WHERE user_id = u.user_id) can produce the same result, but it returns multiple rows when several orders have the same timestamp. ROW_NUMBER() + rn=1 guarantees exactly one row, making it more robust.

FIELD NOTE

Retrieving the latest or maximum one row per group is one of the most frequent Web API scenarios. Examples include: (1) each user's last-login information (PARTITION BY user_id ORDER BY logged_at DESC), (2) each product's latest price history (PARTITION BY product_id ORDER BY changed_at DESC), and (3) the most recent sensor value for each device (PARTITION BY device_id ORDER BY measured_at DESC). Mastering this pattern lets you implement many “latest row” queries quickly and safely.

Model answers and explanations are for members

Read every model answer, explanation and table-transition visualization across all 48 advanced sets (240 questions)
One-time purchase — no subscription. Future advanced sets are included
Background, problem and expected output stay free

Unlock for $20 USD

Payments are securely processed by Stripe — your card details never touch this site.

You are charged in US dollars. If your card is issued in another currency, your bank’s exchange rate and any foreign transaction fee apply.

Already purchased? Sign in on your account page.