Drill 9 of 12 in Window Functions
0

Window Functions · DISTINCT ON · first row per group

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