Drill 2 of 15 in Analytics Patterns
0

Analytics Patterns · DISTINCT ON · latest row per key

2. 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…