First and last order amount of each customer

Medium

Question

For each customer, find the amount of their FIRST order and the amount of their LAST order. First and last are decided by the date in ordered_at, not by the id of the order (the ids are not in date order).

A customer with only one order has the same amount in both columns. No customer has two orders on the same day.

Return exactly one row per customer with customer_id, first_amount and last_amount. The order of the rows does not matter.

window function
first value
last value
window frame
first last per group

Read first: Lesson 47, SQL window functions: ranks, running totals, LAG and frames

Tables

1 table

These are the example rows. Run uses only these rows. Submit also tests your query on a second set of rows that you cannot see.

orders4 columns · 9 rowsnot run yet

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL, amount INTEGER NOT NULL, ordered_at TEXT NOT NULL);

Example rows of the orders table
id	customer_id	amount	ordered_at
1	3	500	2026-03-01
2	1	100	2026-02-15
3	4	80	2026-01-20
4	2	200	2026-02-02
5	1	300	2026-01-10
6	4	120	2026-04-01
7	3	400	2026-03-05
8	3	450	2026-03-03
9	1	700	2026-01-02

About this SQL

FIRST_VALUE and LAST_VALUE work in SQLite 3.25 and later. When a window has an ORDER BY, its default frame ends at the CURRENT row, so LAST_VALUE just returns the current row. Add ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, or avoid LAST_VALUE and sort in the other direction with FIRST_VALUE. Window functions do not remove rows, so use DISTINCT or GROUP BY to get one row per customer.

SQLite

Opening the code editor…

Result

Run shows what your query gives on the example rows.Submit checks it on the example rows and on a hidden set of rows.

SQL · ready