It's a pretty common pattern (take the top N by group with some order), so I'd expect that it's a familiar pattern to a lot of people who regularly do analytical queries in SQL. It's clearer with formatting/naming:

    WITH ranked_tracks AS (
        SELECT *,
            ROW_NUMBER() OVER ( 
                PARTITION BY album_id
                ORDER BY milliseconds DESC
            ) AS row_num
        FROM tracks
    )
    SELECT *
    FROM ranked_tracks
    WHERE
        row_num = 1

But it is still super ugly for such a common need. If I were to add syntax to make this kind of thing easier, I'd just go for a syntax that made something like this valid:

    SELECT *
    FROM tracks
    WHERE
        ROW_NUMBER() OVER ( 
            PARTITION BY album_id
            ORDER BY milliseconds DESC
        ) = 1

Which apparently the QUALIFY statement does in a few dialects.