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

select
    cr.route_name,
    cr.distance_km,
    t.rider_name,
    t.ride_date,
    t.completion_min,
    t.avg_speed_kmh
from
    cycling_routes cr
join lateral (
    select
        t1.ride_id,
        t1.route_id,
        t1.rider_name,
        t1.ride_date,
        t1.completion_min,
        t1.avg_speed_kmh
    from
        (
            select
                rr.*,
                row_number() over(partition by rr.rider_name order by completion_min,ride_date,ride_id) as ran_1
            from
                ride_records rr
            where
                rr.route_id = cr.route_id
        ) t1
    where
        t1.ran_1=1
    order by
        completion_min,ride_date,ride_id
    limit 2
) t on true