Chapter 10 · 13 min · Basic
One row per entity
Everything upstream of you stores events. A wallet top-up, a ride, a login, a support ticket — one row per thing that happened, appended forever.
Nearly everything downstream wants the opposite: one row per customer, per wallet, per driver, with the events already reduced to columns. A model needs it. A dashboard needs it. A list of accounts for someone to phone needs it.
The collapse between those two shapes is where three specific things go wrong: the entities with no events vanish entirely, columns get taken from different rows and described as one customer, and nobody says as of when. Six wallets and twelve events here — small enough to check every row by hand, which is the only way to see all three.
The dataset
Six wallets and twelve events. Two wallets have no events at all — one dormant, one opened after the reporting date — and one wallet has spending with no top-up behind it. All three are deliberate, and each one breaks a different query in this chapter.
Schema
| wallet_id | int |
| owner | text |
| opened_on | date |
| event_id | int |
| wallet_id | int |
| event_type | text |
| amount_myr | numeric(8,2) |
| event_at | timestamp |
Example data
An event log is not an analysis table
Twelve rows, one per thing that happened. This is the shape almost every operational system stores, because it is the shape events arrive in: something occurred, a row got appended.
It is also the shape almost nothing consumes. A churn model wants one row per wallet. A dashboard of balances wants one row per wallet. A list for the support team to call wants one row per wallet. The work of this chapter is the collapse — twelve event rows into one row per wallet — and every decision in it is about what gets lost on the way down.
Before writing a single aggregate, finish the sentence from pick the grain before you query: one row per wallet, as of a stated date. Both halves matter, and the second half is the one people skip.
There are six wallets on file. How many of them appear in this log?
Basic
The rest of this chapter is Basic
Learning SQL is free here, forever. This track is the paid half: what to do when the data is dirty, duplicated and undocumented, and somebody still wants a number.
RM 25/mo · cancel anytime
Still to come in this chapter
- 02Grouping the log loses the entities that did nothing
- 03Start from the entity list, then attach the events
- 04Build the columns the question actually needs
- 05The attribute you cannot aggregate
- 06One row per wallet, as of when
- 07Now collapse a log you cannot read