How many orders are in each status now

Medium

Question

The order_events table is a log. Every time the status of an order changes, a new row is added with the order_id, the new status and the time changed_at. The current status of an order is the status of its latest event. If two events of one order have exactly the same changed_at, the one with the bigger id is the later one.

Return status and orders (the number of orders whose current status is that status). Sort by orders from high to low, then by status from A to Z.

The order of the rows matters for this question.

window function
row number
greatest n per group
ties
event log
cte
group by

Read first: Lesson 48, SQL patterns that interviews repeat

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.

order_events4 columns · 18 rowsnot run yet

CREATE TABLE order_events (id INTEGER PRIMARY KEY, order_id INTEGER NOT NULL, status TEXT NOT NULL, changed_at TEXT NOT NULL);

Example rows of the order_events table
id	order_id	status	changed_at
1	101	placed	2026-05-01 10:00:00
2	101	packed	2026-05-01 12:00:00
3	101	shipped	2026-05-02 09:00:00
4	101	delivered	2026-05-03 15:00:00
5	102	placed	2026-05-01 11:00:00
6	102	packed	2026-05-01 13:00:00
7	102	shipped	2026-05-01 13:00:00
8	103	placed	2026-05-02 08:00:00
9	104	placed	2026-05-02 09:00:00
10	104	cancelled	2026-05-02 09:30:00
11	105	placed	2026-05-02 10:00:00
12	105	packed	2026-05-02 14:00:00
13	105	shipped	2026-05-03 08:00:00
14	105	packed	2026-05-02 16:00:00
15	106	placed	2026-05-03 07:00:00
16	106	packed	2026-05-03 09:00:00
17	107	placed	2026-05-03 10:00:00
18	107	cancelled	2026-05-03 10:05:00

About this SQL

ROW_NUMBER() OVER (PARTITION BY ... ORDER BY changed_at DESC, id DESC) gives 1 to the latest row of each group, and the second sort key breaks a tie, so every group has exactly one row with number 1. SQLite has no DISTINCT ON and no QUALIFY. MAX(changed_at) joined back to the table returns two rows for an order when two events share the latest time.

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