SQLRepPath
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960
00/60 solved

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…