Lesson 7 of 10

The top row in each group

15 min
5 checks
3 hints
Go to task

Goal

Numbering with ROW_NUMBER and splitting with PARTITION BY

Explanation

GROUP BY collapses each group into a single row, so its details are lost. A window function, on the other hand, computes over a set of rows and keeps every row where it is.

The expression ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY total DESC) numbers each customer's orders separately: PARTITION BY splits the rows into groups, and ORDER BY sorts the rows inside each group, so number 1 goes to each customer's largest order. RANK() works the same way, except that it gives two tied rows the same number.

You can't use a window function inside WHERE, because windows are computed after the filtering. So number the rows inside WITH, then write WHERE rn = 1 in the outer query.

Your task

Show each customer's largest order with the columns name, placed_on and total, sorted from largest to smallest.

Checks

5 checks

Columns are name, placed_on and total
One row for each customer with orders
Each row is that customer's largest order
The date is the date of that order
Sorted from largest to smallest

Tables in this lesson

CREATE TABLE customers (
  id   INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT NOT NULL
);

CREATE TABLE orders (
  id          INTEGER PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(id),
  total       INTEGER NOT NULL,
  placed_on   TEXT    NOT NULL
);

INSERT INTO customers (id, name, city) VALUES
  (1, 'Layan',  'Riyadh'),
  (2, 'Khalid', 'Jeddah'),
  (3, 'Reem',   'Dammam'),
  (4, 'Fahd',   'Riyadh');

INSERT INTO orders (customer_id, total, placed_on) VALUES
  (1, 120, '2026-09-01'),
  (1,  80, '2026-09-12'),
  (2, 300, '2026-09-03'),
  (3,  45, '2026-09-20'),
  (2,  60, '2026-09-25');

INSERT INTO customers (id, name, city) VALUES (5, 'Sara', 'Jeddah');

CREATE TABLE products (
  id    INTEGER PRIMARY KEY,
  name  TEXT    NOT NULL,
  price INTEGER NOT NULL
);

CREATE TABLE order_items (
  id         INTEGER PRIMARY KEY,
  order_id   INTEGER REFERENCES orders(id),
  product_id INTEGER REFERENCES products(id),
  qty        INTEGER NOT NULL
);

INSERT INTO products (id, name, price) VALUES
  (1, 'Notebook',    15),
  (2, 'Pen',          5),
  (3, 'Bag',         90),
  (4, 'Calculator',  60),
  (5, 'Lamp',       100);

INSERT INTO order_items (order_id, product_id, qty) VALUES
  (1, 3, 1), (1, 1, 2),
  (2, 4, 1), (2, 2, 4),
  (3, 3, 2), (3, 4, 2),
  (4, 1, 3),
  (5, 1, 2), (5, 2, 6);

The top row in each group

Explanation

Your task

Checks

Hints