Interview Drills · DISTINCT ON · first row per group
38. Each customer's first order
Concept: DISTINCT ON · first row per groupshow
Postgres's DISTINCT ON (key) keeps the first row of each key, and ORDER BY decides which row is first. The ORDER BY must start with the same key, then the column that picks the winner: DISTINCT ON (team) ... ORDER BY team, score DESC keeps each team's top scorer, with all its columns.
Example (different data):
SELECT DISTINCT ON (device_id) device_id, reading, recorded_at FROM sensor_readings ORDER BY device_id, recorded_at DESC;
Your task
Customer success wants to see how every relationship started: each customer's very first order and whether it went through.
Return customer_id, order_id, order_date, amount and status of each customer's earliest order (any status). Sort by customer_id.
Starting Postgres in your browser…
Checking your access…