Lead Climber on Each Route

Problem

A climbing gym assigns climbers to routes. Each climber has a recorded number of career ascents.

Table: route_assignment
+-------------+------+
| Column Name | Type |
+-------------+------+
| route_id | int |
| climber_id | int |
+-------------+------+
Each row says that the climber climber_id is assigned to route route_id. A pair (route_id, climber_id) is unique.

Table: climber
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| climber_id | int |
| handle | varchar |
| ascents | int |
+-------------+---------+
climber_id is the primary key. ascents is the climber's total number of completed ascents.

Write a solution that reports, for each route, the climber(s) with the highest number of ascents. If two or more climbers on the same route tie for the most ascents, report all of them. Return the result in any order with columns route_id and climber_id.

Tables

Example rows — the live problem includes the full dataset.

Expected output

Your answer should return 3 rows with the columns route_id, climber_id.

Starter code (SQL)

SELECT *
FROM route_assignment;

Related SQL questions

• 3-Day Tweet Rolling AverageMedium
• 7-Day vs 28-Day Rolling Avg DivergenceMedium
• A/B Test Sample Ratio Mismatch (SRM) CheckMedium
• Accounts Streaming From Two Devices at OnceMedium
• Ad Campaign Funnel AnalysisMedium
• Agent Resolution RateMedium