Aoife Brennan

ok this one I do every week lah. three ways: 1. postgres only, shortest: SELECT DISTINCT ON (customer_id) id, customer_id, event_type, payload, created_at FROM customer_events ORDER BY customer_id, created_at DESC, id DESC; DISTINCT ON keeps the first row per customer_id according to the ORDER BY. id DESC breaks the tie when created_at is same. 2. portable, works in SQL Server, Oracle, MySQL 8, Snowflake etc: SELECT id, customer_id, event_type, payload, created_at FROM ( SELECT e.*, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC, id DESC) AS rn FROM customer_events e ) t WHERE rn = 1; ROW_NUMBER not RANK, because RANK gives 2 rows when tie. confirm they will ask this in the follow-up. 3. when there are few customers and very many events each, LATERAL with LIMIT 1 can be fastest, it jumps into the index once per customer: SELECT c.id, last.* FROM customers c CROSS JOIN LATERAL ( SELECT e.id, e.event_type, e.payload, e.created_at FROM customer_events e WHERE e.customer_id = c.id ORDER BY e.created_at DESC, e.id DESC LIMIT 1 ) last; for all three the index is: CREATE INDEX ON customer_events (customer_id, created_at DESC, id DESC); and since we are in select-star-anonymous: list the columns in your submission, the reviewer will notice 😄