题解 | 骑行运动社区路线个人最佳排名

select 
    c.route_name,
    c.distance_km,
    t.rider_name,
    t.ride_date,
    t.completion_min,
    t.avg_speed_kmh
from cycling_routes c
    join lateral
    (
        select *
        from (
            select 
                ride_id,
                route_id,
                rider_name,
                ride_date,
                completion_min,
                avg_speed_kmh,
                rank() over(partition by route_id,rider_name order by completion_min,ride_date,ride_id) rk
            from ride_records r   
        )temp
        where temp.route_id=c.route_id and temp.rk=1
        order by completion_min ,ride_date ,temp.ride_id 
        limit 2
    )t
order by c.route_id,completion_min;