Analytics Patterns · DISTINCT ON · latest row per key
47. Current plan per user
Concept: DISTINCT ON · latest row per keyshow
Change logs keep every version of a row, so 'current state' means the newest row per key. Postgres's DISTINCT ON (key) keeps the first row of each key after sorting, so ORDER BY key, changed_at DESC keeps the latest. In other databases, ROW_NUMBER() OVER (PARTITION BY key ORDER BY changed_at DESC) = 1 does the same job.
Example (different data):
SELECT DISTINCT ON (device_id) device_id, firmware, installed_at FROM firmware_updates ORDER BY device_id, installed_at DESC;
Your task
Billing is rebuilding its customer table from the plan change log and needs each user's current plan.
Return user_id, plan and changed_at of each user's most recent plan change, sorted by user_id.
Starting Postgres in your browser…
Checking your access…