- Backend engineer
- Junior
- 30 min
The current ticket charge
At fictional Cedar Dispatch, return ticket, revision and charge from changes. Keep the highest revision for each ticket; ties go to the latest received_at, then highest id. Sort by ticket.
Skills and rubricThe current ticket charge
- I use DISTINCT ON with ticket first in ORDER BY because PostgreSQL keeps the first row of each ticket group. Revision DESC, received_at DESC and id DESC put the required winner first. I check that cedar keeps id 4 and reed keeps id 3.
- I compare both cedar rows at revision 2 because a maximum revision alone leaves ids 2 and 4 tied. Their received_at values make id 4 current, while a global maximum would also drop reed's valid revision 1.
- I retain the DISTINCT ON query and the winning source ids because the charge total alone can't prove which revision was selected. I rerun it on tied timestamps and changed charges, then compare each returned ticket with its ordered source rows before releasing the query.