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

WITH t1 AS (
    SELECT route_id,
            rider_name,
            completion_min,
            ride_date,
            ride_id,
            avg_speed_kmh
    FROM (
        SELECT route_id,
               rider_name,
               completion_min,
               ride_date,
               ride_id,
               avg_speed_kmh,
               ROW_NUMBER() OVER(PARTITION BY route_id,rider_name ORDER BY completion_min,ride_date,ride_id) AS rnk
        FROM ride_records
    ) b
    WHERE rnk=1
)
select route_name,
       distance_km,
       rider_name,
       ride_date,
       completion_min,
       avg_speed_kmh
from cycling_routes c
inner join lateral(
    select t1.*
    from t1
    where t1.route_id=c.route_id
    order by completion_min,ride_date,ride_id
    limit 2
) b on true