SQLRepPath
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960
00/60 solved

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…