Comment by gigatexal

select id from tracks qualify row_number() over partition by (album_id order by milliseconds desc) =1

That should work

Look I’m a diehard SQL just use it guy but open to improvements. But I’m loathe to use abstractions for things when the underlying thing is so expressive.

Autocomplete of fields in a good editor, schema help, etc go a long way to making SQL being written raw very nice.