Persist official climb attempts with climbs pages

Record one attempt row per (ride, climb, occurrence) when a ride detail
loads, snapshotting distance, elevation gain, slope, cotacol and attempt
timestamps; a climbs-backfill CLI command seeds all existing rides.

New /climbs list page aggregates attempts (medians computed in Go, best
time as min duration) with sortable columns, and /climbs/{id} shows the
median scalar stats, a median normalized elevation profile (50 steps
over each attempt's own length), the full-precision recorded route of
the median-distance attempt on an OSM map, and every attempt with time
at second precision.

6 files changed, 694 insertions, 0 deletions

diff --git a/official_climb/aggregate.go b/official_climb/aggregate.go
new file mode 100644
index 0000000..c2bd75d
--- /dev/null
+++ b/official_climb/aggregate.go
@@ -0,0 +1,147 @@
+package official_climb
+
+import (
+	"sort"
+	"time"
+
+	"github.com/martinlehoux/biking_home/climb"
+)
+
+const profileSteps = 50
+
+// Aggregate is the climb-level summary derived from its recorded attempts.
+type Aggregate struct {
+	Attempts       int
+	DistanceM      float64
+	ElevationGainM float64
+	SlopePercent   float64
+	Cotacol        float64
+	Category       climb.Category
+	BestDuration   time.Duration
+	HasBest        bool
+}
+
+// AggregateAttempts summarizes a climb from its attempts: every scalar is the
+// median across attempts so one noisy ride cannot define the climb.
+func AggregateAttempts(attempts []Attempt) Aggregate {
+	if len(attempts) == 0 {
+		return Aggregate{}
+	}
+	distances := make([]float64, len(attempts))
+	gains := make([]float64, len(attempts))
+	slopes := make([]float64, len(attempts))
+	cotacols := make([]float64, len(attempts))
+	aggregate := Aggregate{Attempts: len(attempts)}
+	for i, attempt := range attempts {
+		distances[i] = attempt.DistanceM
+		gains[i] = attempt.ElevationGainM
+		slopes[i] = attempt.SlopePercent
+		cotacols[i] = attempt.Cotacol
+		duration := attempt.EndedAt.Sub(attempt.StartedAt)
+		if duration > 0 && (!aggregate.HasBest || duration < aggregate.BestDuration) {
+			aggregate.BestDuration = duration
+			aggregate.HasBest = true
+		}
+	}
+	aggregate.DistanceM = Median(distances)
+	aggregate.ElevationGainM = Median(gains)
+	aggregate.SlopePercent = Median(slopes)
+	aggregate.Cotacol = Median(cotacols)
+	aggregate.Category = climb.CategoryOf(aggregate.Cotacol)
+	return aggregate
+}
+
+// Median returns the median of values; for an even count it averages the two
+// middle values.
+func Median(values []float64) float64 {
+	if len(values) == 0 {
+		return 0
+	}
+	sorted := make([]float64, len(values))
+	copy(sorted, values)
+	sort.Float64s(sorted)
+	middle := len(sorted) / 2
+	if len(sorted)%2 == 1 {
+		return sorted[middle]
+	}
+	return (sorted[middle-1] + sorted[middle]) / 2
+}
+
+// normalizedSample locates one normalized step inside a segment: the index of
+// the recorded point preceding the target distance and the interpolation
+// fraction toward the next point.
+type normalizedSample struct {
+	index    int
+	fraction float64
+}
+
+// normalizedSamples locates profileSteps equally spaced fractions of the
+// segment length, interpolating between recorded points. Distances are
+// relative: segments of different lengths stay comparable.
+func normalizedSamples(distances []float64) []normalizedSample {
+	if len(distances) < 2 {
+		return nil
+	}
+	start, end := distances[0], distances[len(distances)-1]
+	span := end - start
+	if span <= 0 {
+		return nil
+	}
+	samples := make([]normalizedSample, profileSteps)
+	i := 0
+	for step := range profileSteps {
+		target := start + span*float64(step)/float64(profileSteps-1)
+		for i < len(distances)-2 && distances[i+1] < target {
+			i++
+		}
+		segmentSpan := distances[i+1] - distances[i]
+		fraction := 0.0
+		if segmentSpan > 0 {
+			fraction = (target - distances[i]) / segmentSpan
+		}
+		samples[step] = normalizedSample{index: i, fraction: fraction}
+	}
+	return samples
+}
+
+// NormalizeProfile samples a climb segment's elevation at profileSteps
+// equally spaced fractions of the segment length, interpolating between
+// recorded points.
+func NormalizeProfile(segment climb.Climb) []float64 {
+	samples := normalizedSamples(segment.Distances)
+	if samples == nil {
+		return nil
+	}
+	elevations := make([]float64, len(samples))
+	for step, sample := range samples {
+		elevations[step] = sampleElevation(segment.Distances, segment.Elevations, sample)
+	}
+	return elevations
+}
+
+func sampleElevation(distances, elevations []float64, sample normalizedSample) float64 {
+	i := sample.index
+	if i+1 >= len(distances) {
+		return elevations[len(elevations)-1]
+	}
+	return elevations[i] + sample.fraction*(elevations[i+1]-elevations[i])
+}
+
+// MedianProfile merges per-attempt normalized elevation samples into one
+// canonical profile: the median elevation at every step.
+func MedianProfile(samples [][]float64) []float64 {
+	median := make([]float64, 0, profileSteps)
+	for step := range profileSteps {
+		values := make([]float64, 0, len(samples))
+		for _, sample := range samples {
+			if step < len(sample) {
+				values = append(values, sample[step])
+			}
+		}
+		if len(values) == 0 {
+			continue
+		}
+		median = append(median, Median(values))
+	}
+	return median
+}
diff --git a/official_climb/aggregate_test.go b/official_climb/aggregate_test.go
new file mode 100644
index 0000000..2e051ce
--- /dev/null
+++ b/official_climb/aggregate_test.go
@@ -0,0 +1,76 @@
+package official_climb
+
+import (
+	"testing"
+	"time"
+
+	"github.com/martinlehoux/biking_home/climb"
+	"github.com/stretchr/testify/assert"
+	"github.com/stretchr/testify/require"
+)
+
+func TestMedianOddAndEven(t *testing.T) {
+	assert.Equal(t, 3.0, Median([]float64{5, 1, 3}))
+	assert.Equal(t, 2.5, Median([]float64{1, 2, 3, 4}))
+	assert.Equal(t, 0.0, Median(nil))
+}
+
+func TestAggregateAttemptsUsesMedians(t *testing.T) {
+	attempts := []Attempt{
+		{DistanceM: 3000, ElevationGainM: 200, SlopePercent: 6.7, Cotacol: 80, StartedAt: time.Date(2026, 1, 1, 8, 0, 0, 0, time.UTC), EndedAt: time.Date(2026, 1, 1, 8, 20, 0, 0, time.UTC)},
+		{DistanceM: 3200, ElevationGainM: 210, SlopePercent: 6.6, Cotacol: 84, StartedAt: time.Date(2026, 2, 1, 8, 0, 0, 0, time.UTC), EndedAt: time.Date(2026, 2, 1, 8, 15, 0, 0, time.UTC)},
+		{DistanceM: 6000, ElevationGainM: 900, SlopePercent: 15, Cotacol: 400, StartedAt: time.Date(2026, 3, 1, 8, 0, 0, 0, time.UTC), EndedAt: time.Date(2026, 3, 1, 8, 40, 0, 0, time.UTC)},
+	}
+	aggregate := AggregateAttempts(attempts)
+	assert.Equal(t, 3, aggregate.Attempts)
+	assert.Equal(t, 3200.0, aggregate.DistanceM)
+	assert.Equal(t, 210.0, aggregate.ElevationGainM)
+	assert.Equal(t, 6.7, aggregate.SlopePercent)
+	assert.Equal(t, 84.0, aggregate.Cotacol)
+	require.True(t, aggregate.HasBest)
+	assert.Equal(t, 15*time.Minute, aggregate.BestDuration)
+}
+
+func TestAggregateAttemptsIgnoresBrokenDurations(t *testing.T) {
+	attempts := []Attempt{
+		{DistanceM: 3000, StartedAt: time.Date(2026, 1, 1, 8, 0, 0, 0, time.UTC), EndedAt: time.Date(2026, 1, 1, 7, 0, 0, 0, time.UTC)},
+		{DistanceM: 3200, StartedAt: time.Date(2026, 2, 1, 8, 0, 0, 0, time.UTC), EndedAt: time.Date(2026, 2, 1, 8, 15, 0, 0, time.UTC)},
+	}
+	aggregate := AggregateAttempts(attempts)
+	require.True(t, aggregate.HasBest)
+	assert.Equal(t, 15*time.Minute, aggregate.BestDuration)
+}
+
+func TestAggregateAttemptsEmpty(t *testing.T) {
+	aggregate := AggregateAttempts(nil)
+	assert.Equal(t, 0, aggregate.Attempts)
+	assert.False(t, aggregate.HasBest)
+	assert.Equal(t, climb.CategoryNo, aggregate.Category)
+}
+
+func TestNormalizeProfileLinearSegment(t *testing.T) {
+	segment := climb.Climb{Route: climb.Route{
+		Distances:  []float64{0, 500, 1000},
+		Elevations: []float64{100, 150, 200},
+	}}
+	samples := NormalizeProfile(segment)
+	require.Len(t, samples, profileSteps)
+	assert.InDelta(t, 100, samples[0], 0.01)
+	assert.InDelta(t, 200, samples[profileSteps-1], 0.01)
+	assert.InDelta(t, 100+100*float64(profileSteps/2)/float64(profileSteps-1), samples[profileSteps/2], 0.01)
+}
+
+func TestMedianProfileRobustToOutlier(t *testing.T) {
+	straight := make([]float64, profileSteps)
+	for i := range straight {
+		straight[i] = float64(i)
+	}
+	spiked := make([]float64, profileSteps)
+	for i := range spiked {
+		spiked[i] = float64(i) + 500
+	}
+	median := MedianProfile([][]float64{straight, spiked, straight})
+	require.Len(t, median, profileSteps)
+	assert.InDelta(t, 10, median[10], 0.01)
+	assert.InDelta(t, 49, median[49], 0.01)
+}
diff --git a/official_climb/attempt.go b/official_climb/attempt.go
new file mode 100644
index 0000000..43778f0
--- /dev/null
+++ b/official_climb/attempt.go
@@ -0,0 +1,158 @@
+package official_climb
+
+import (
+	"database/sql"
+	"fmt"
+	"time"
+
+	"github.com/martinlehoux/biking_home/climb"
+)
+
+// Attempt is one recorded passage of an official climb during one ride,
+// identified by (rideID, climbID, occurrence). Scalar stats are snapshotted at
+// recording time so climb listings never re-parse ride files.
+type Attempt struct {
+	RideID             int64
+	ClimbID            int64
+	Occurrence         int
+	DistanceM          float64
+	ElevationGainM     float64
+	SlopePercent       float64
+	Cotacol            float64
+	CotacolAlgoVersion string
+	StartedAt          time.Time
+	EndedAt            time.Time
+	CreatedAt          time.Time
+}
+
+const attemptColumns = "a.ride_id, a.climb_id, a.occurrence, a.distance_m, a.elevation_gain_m, a.slope_percent, a.cotacol, a.cotacol_algo_version, a.started_at, a.ended_at, a.created_at"
+
+// AttemptsForClimb returns every recorded attempt of one official climb,
+// oldest ride first.
+func AttemptsForClimb(db *sql.DB, climbID int64) ([]Attempt, error) {
+	return listAttempts(db, "climb_id = ?", climbID, "r.start_date, a.ride_id, a.occurrence")
+}
+
+// AttemptsForRide returns every recorded attempt within one ride, in route
+// order.
+func AttemptsForRide(db *sql.DB, rideID int64) ([]Attempt, error) {
+	return listAttempts(db, "ride_id = ?", rideID, "a.occurrence")
+}
+
+func listAttempts(db *sql.DB, where string, arg any, order string) ([]Attempt, error) {
+	rows, err := db.Query(`
+		SELECT `+attemptColumns+`
+		FROM ride_official_climb_attempts a
+		JOIN rides r ON r.id = a.ride_id
+		WHERE `+where+`
+		ORDER BY `+order, arg)
+	if err != nil {
+		return nil, fmt.Errorf("list official climb attempts: %w", err)
+	}
+	defer rows.Close()
+	attempts := make([]Attempt, 0)
+	for rows.Next() {
+		attempt, err := scanAttempt(rows)
+		if err != nil {
+			return nil, err
+		}
+		attempts = append(attempts, attempt)
+	}
+	if err := rows.Err(); err != nil {
+		return nil, fmt.Errorf("iterate official climb attempts: %w", err)
+	}
+	return attempts, nil
+}
+
+// ReplaceRideAttempts atomically rewrites every attempt row of one ride,
+// dropping stale matches and inserting the freshly analyzed ones.
+func ReplaceRideAttempts(db *sql.DB, rideID int64, attempts []Attempt) error {
+	tx, err := db.Begin()
+	if err != nil {
+		return fmt.Errorf("replace ride attempts: %w", err)
+	}
+	defer func() { _ = tx.Rollback() }()
+	if _, err := tx.Exec("DELETE FROM ride_official_climb_attempts WHERE ride_id = ?", rideID); err != nil {
+		return fmt.Errorf("delete ride attempts: %w", err)
+	}
+	for _, attempt := range attempts {
+		if attempt.RideID != rideID {
+			return fmt.Errorf("replace ride attempts: attempt references ride %d, expected %d", attempt.RideID, rideID)
+		}
+		if _, err := tx.Exec(`
+			INSERT INTO ride_official_climb_attempts (ride_id, climb_id, occurrence, distance_m, elevation_gain_m, slope_percent, cotacol, cotacol_algo_version, started_at, ended_at)
+			VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
+		`, attempt.RideID, attempt.ClimbID, attempt.Occurrence, attempt.DistanceM, attempt.ElevationGainM, attempt.SlopePercent, attempt.Cotacol, attempt.CotacolAlgoVersion,
+			attempt.StartedAt.UTC().Format(time.RFC3339), attempt.EndedAt.UTC().Format(time.RFC3339)); err != nil {
+			return fmt.Errorf("insert ride attempt: %w", err)
+		}
+	}
+	if err := tx.Commit(); err != nil {
+		return fmt.Errorf("commit ride attempts: %w", err)
+	}
+	return nil
+}
+
+type attemptScanner interface {
+	Scan(dest ...any) error
+}
+
+func scanAttempt(s attemptScanner) (Attempt, error) {
+	var (
+		attempt            Attempt
+		startedAt, endedAt string
+		createdAt          sql.NullString
+	)
+	if err := s.Scan(&attempt.RideID, &attempt.ClimbID, &attempt.Occurrence, &attempt.DistanceM, &attempt.ElevationGainM, &attempt.SlopePercent, &attempt.Cotacol, &attempt.CotacolAlgoVersion, &startedAt, &endedAt, &createdAt); err != nil {
+		return Attempt{}, err
+	}
+	var err error
+	if attempt.StartedAt, err = time.Parse(time.RFC3339, startedAt); err != nil {
+		return Attempt{}, fmt.Errorf("invalid ride_official_climb_attempts.started_at: %w", err)
+	}
+	if attempt.EndedAt, err = time.Parse(time.RFC3339, endedAt); err != nil {
+		return Attempt{}, fmt.Errorf("invalid ride_official_climb_attempts.ended_at: %w", err)
+	}
+	if createdAt.Valid {
+		if attempt.CreatedAt, err = time.Parse(time.RFC3339, createdAt.String); err != nil {
+			return Attempt{}, fmt.Errorf("invalid ride_official_climb_attempts.created_at: %w", err)
+		}
+	}
+	return attempt, nil
+}
+
+// AttemptStats carries the analyzed values snapshotted into an attempt row.
+type AttemptStats struct {
+	OfficialClimbID int64
+	DistanceM       float64
+	ElevationGainM  float64
+	SlopePercent    float64
+	Cotacol         float64
+	StartIndex      int
+	EndIndex        int
+}
+
+// AttemptFromStats builds the persisted snapshot of one analyzed climb
+// occurrence. Timestamps come from the ride's recorded points bounding the
+// climb interval.
+func AttemptFromStats(rideID int64, occurrence int, stats AttemptStats, timestamps []time.Time) Attempt {
+	startedAt, endedAt := time.Time{}, time.Time{}
+	if stats.StartIndex < len(timestamps) {
+		startedAt = timestamps[stats.StartIndex]
+	}
+	if stats.EndIndex < len(timestamps) {
+		endedAt = timestamps[stats.EndIndex]
+	}
+	return Attempt{
+		RideID:             rideID,
+		ClimbID:            stats.OfficialClimbID,
+		Occurrence:         occurrence,
+		DistanceM:          stats.DistanceM,
+		ElevationGainM:     stats.ElevationGainM,
+		SlopePercent:       stats.SlopePercent,
+		Cotacol:            stats.Cotacol,
+		CotacolAlgoVersion: climb.CotacolAlgorithmVersion,
+		StartedAt:          startedAt,
+		EndedAt:            endedAt,
+	}
+}
diff --git a/official_climb/attempt_test.go b/official_climb/attempt_test.go
new file mode 100644
index 0000000..147c80b
--- /dev/null
+++ b/official_climb/attempt_test.go
@@ -0,0 +1,128 @@
+package official_climb_test
+
+import (
+	"database/sql"
+	"testing"
+	"time"
+
+	"github.com/jftuga/geodist"
+	"github.com/martinlehoux/biking_home/internal/dbtest"
+	"github.com/martinlehoux/biking_home/official_climb"
+	"github.com/stretchr/testify/assert"
+	"github.com/stretchr/testify/require"
+)
+
+func coord(lat, lon float64) geodist.Coord {
+	return geodist.Coord{Lat: lat, Lon: lon}
+}
+
+func insertRide(t *testing.T, db *sql.DB, opts ...func(*rideInsert)) int64 {
+	t.Helper()
+	insert := rideInsert{startDate: "2026-09-01T08:00:00Z"}
+	for _, opt := range opts {
+		opt(&insert)
+	}
+	result, err := db.Exec(`
+		INSERT INTO rides (external_id, gpx_path, name, external_sport_type, start_date, distance_m, external_moving_time_s, elapsed_time_s, total_elevation_gain_m, average_speed_mps)
+		VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
+	`, "strava:test-ride-"+insert.startDate, "missing.gpx", "Test Ride", "Ride", insert.startDate, 20_000, 0, 0, 0, 0)
+	require.NoError(t, err)
+	id, err := result.LastInsertId()
+	require.NoError(t, err)
+	return id
+}
+
+type rideInsert struct {
+	startDate string
+}
+
+func withStartDate(startDate string) func(*rideInsert) {
+	return func(r *rideInsert) { r.startDate = startDate }
+}
+
+func attemptFixture(rideID, climbID int64, occurrence int) official_climb.Attempt {
+	return official_climb.Attempt{
+		RideID:             rideID,
+		ClimbID:            climbID,
+		Occurrence:         occurrence,
+		DistanceM:          3200,
+		ElevationGainM:     210,
+		SlopePercent:       6.5,
+		Cotacol:            84.2,
+		CotacolAlgoVersion: "v1",
+		StartedAt:          time.Date(2026, 9, 1, 10, 0, 0, 0, time.UTC),
+		EndedAt:            time.Date(2026, 9, 1, 10, 18, 30, 0, time.UTC),
+	}
+}
+
+func TestReplaceRideAttemptsRoundTrip(t *testing.T) {
+	db := dbtest.New(t)
+	created, err := official_climb.Create(db, official_climb.OfficialClimb{Name: "Col de Test", StartCoord: coord(43.6, 5.4), EndCoord: coord(43.62, 5.42)})
+	require.NoError(t, err)
+	rideID := insertRide(t, db)
+
+	require.NoError(t, official_climb.ReplaceRideAttempts(db, rideID, []official_climb.Attempt{
+		attemptFixture(rideID, created.ID, 0),
+		attemptFixture(rideID, created.ID, 1),
+	}))
+
+	attempts, err := official_climb.AttemptsForClimb(db, created.ID)
+	require.NoError(t, err)
+	require.Len(t, attempts, 2)
+	assert.Equal(t, 0, attempts[0].Occurrence)
+	assert.Equal(t, 1, attempts[1].Occurrence)
+	assert.Equal(t, 3200.0, attempts[0].DistanceM)
+	assert.Equal(t, "v1", attempts[0].CotacolAlgoVersion)
+	assert.Equal(t, time.Date(2026, 9, 1, 10, 18, 30, 0, time.UTC), attempts[0].EndedAt)
+
+	rideAttempts, err := official_climb.AttemptsForRide(db, rideID)
+	require.NoError(t, err)
+	require.Len(t, rideAttempts, 2)
+}
+
+func TestReplaceRideAttemptsDropsStaleRows(t *testing.T) {
+	db := dbtest.New(t)
+	first, err := official_climb.Create(db, official_climb.OfficialClimb{Name: "Col A", StartCoord: coord(43.6, 5.4), EndCoord: coord(43.62, 5.42)})
+	require.NoError(t, err)
+	second, err := official_climb.Create(db, official_climb.OfficialClimb{Name: "Col B", StartCoord: coord(43.7, 5.5), EndCoord: coord(43.72, 5.52)})
+	require.NoError(t, err)
+	rideID := insertRide(t, db)
+
+	require.NoError(t, official_climb.ReplaceRideAttempts(db, rideID, []official_climb.Attempt{attemptFixture(rideID, first.ID, 0)}))
+	require.NoError(t, official_climb.ReplaceRideAttempts(db, rideID, []official_climb.Attempt{attemptFixture(rideID, second.ID, 0)}))
+
+	attempts, err := official_climb.AttemptsForClimb(db, first.ID)
+	require.NoError(t, err)
+	assert.Empty(t, attempts)
+	attempts, err = official_climb.AttemptsForClimb(db, second.ID)
+	require.NoError(t, err)
+	require.Len(t, attempts, 1)
+}
+
+func TestReplaceRideAttemptsRejectsForeignRide(t *testing.T) {
+	db := dbtest.New(t)
+	created, err := official_climb.Create(db, official_climb.OfficialClimb{Name: "Col de Test", StartCoord: coord(43.6, 5.4), EndCoord: coord(43.62, 5.42)})
+	require.NoError(t, err)
+	rideID := insertRide(t, db)
+
+	attempt := attemptFixture(rideID+1, created.ID, 0)
+	err = official_climb.ReplaceRideAttempts(db, rideID, []official_climb.Attempt{attempt})
+	assert.Error(t, err)
+}
+
+func TestAttemptsForClimbOrdersByRideDate(t *testing.T) {
+	db := dbtest.New(t)
+	created, err := official_climb.Create(db, official_climb.OfficialClimb{Name: "Col de Test", StartCoord: coord(43.6, 5.4), EndCoord: coord(43.62, 5.42)})
+	require.NoError(t, err)
+	newer := insertRide(t, db, withStartDate("2026-09-10T08:00:00Z"))
+	older := insertRide(t, db, withStartDate("2026-09-02T08:00:00Z"))
+
+	require.NoError(t, official_climb.ReplaceRideAttempts(db, newer, []official_climb.Attempt{attemptFixture(newer, created.ID, 0)}))
+	require.NoError(t, official_climb.ReplaceRideAttempts(db, older, []official_climb.Attempt{attemptFixture(older, created.ID, 0)}))
+
+	attempts, err := official_climb.AttemptsForClimb(db, created.ID)
+	require.NoError(t, err)
+	require.Len(t, attempts, 2)
+	assert.Equal(t, older, attempts[0].RideID)
+	assert.Equal(t, newer, attempts[1].RideID)
+}
diff --git a/official_climb/list.go b/official_climb/list.go
new file mode 100644
index 0000000..33b12e8
--- /dev/null
+++ b/official_climb/list.go
@@ -0,0 +1,133 @@
+package official_climb
+
+import (
+	"database/sql"
+	"fmt"
+	"time"
+
+	"github.com/martinlehoux/biking_home/climb"
+)
+
+// SortColumn is a climbs-list ordering key.
+type SortColumn string
+
+const (
+	SortName     SortColumn = "name"
+	SortAttempts SortColumn = "attempts"
+	SortDistance SortColumn = "distance"
+	SortSlope    SortColumn = "slope"
+	SortCotacol  SortColumn = "cotacol"
+	SortBestTime SortColumn = "best_time"
+)
+
+// ListedClimb is one climbs-list row: official climb identity plus aggregates
+// over its recorded attempts. Medians are computed in Go from the attempt
+// rows; best time is the minimum attempt duration.
+type ListedClimb struct {
+	ID             int64
+	Name           string
+	Attempts       int
+	DistanceM      float64
+	ElevationGainM float64
+	SlopePercent   float64
+	Cotacol        float64
+	BestDurationS  float64
+	HasBest        bool
+}
+
+func (c ListedClimb) Category() string {
+	return climb.CategoryOf(c.Cotacol).String()
+}
+
+// ListWithAggregates returns every official climb with aggregates over its
+// attempts. Attempts default to zero for climbs never ridden; climbs without
+// attempts show no distance/slope stats.
+func ListWithAggregates(db *sql.DB) ([]ListedClimb, error) {
+	climbRows, err := db.Query("SELECT id, name FROM official_climbs ORDER BY name ASC")
+	if err != nil {
+		return nil, fmt.Errorf("list climbs: %w", err)
+	}
+	defer climbRows.Close()
+	climbs := make([]ListedClimb, 0)
+	index := make(map[int64]int)
+	for climbRows.Next() {
+		var row ListedClimb
+		if err := climbRows.Scan(&row.ID, &row.Name); err != nil {
+			return nil, err
+		}
+		index[row.ID] = len(climbs)
+		climbs = append(climbs, row)
+	}
+	if err := climbRows.Err(); err != nil {
+		return nil, fmt.Errorf("iterate climbs: %w", err)
+	}
+
+	attemptRows, err := db.Query(`
+		SELECT climb_id, distance_m, elevation_gain_m, slope_percent, cotacol, started_at, ended_at
+		FROM ride_official_climb_attempts`)
+	if err != nil {
+		return nil, fmt.Errorf("list climb attempts: %w", err)
+	}
+	defer attemptRows.Close()
+	samples := make(map[int64]*attemptSamples)
+	for attemptRows.Next() {
+		var (
+			climbID   int64
+			distanceM float64
+			gainM     float64
+			slope     float64
+			cotacol   float64
+			startedAt string
+			endedAt   string
+		)
+		if err := attemptRows.Scan(&climbID, &distanceM, &gainM, &slope, &cotacol, &startedAt, &endedAt); err != nil {
+			return nil, err
+		}
+		sample, known := samples[climbID]
+		if !known {
+			sample = &attemptSamples{}
+			samples[climbID] = sample
+		}
+		sample.distances = append(sample.distances, distanceM)
+		sample.gains = append(sample.gains, gainM)
+		sample.slopes = append(sample.slopes, slope)
+		sample.cotacols = append(sample.cotacols, cotacol)
+		start, err := time.Parse(time.RFC3339, startedAt)
+		if err != nil {
+			return nil, fmt.Errorf("invalid ride_official_climb_attempts.started_at: %w", err)
+		}
+		end, err := time.Parse(time.RFC3339, endedAt)
+		if err != nil {
+			return nil, fmt.Errorf("invalid ride_official_climb_attempts.ended_at: %w", err)
+		}
+		durationS := end.Sub(start).Seconds()
+		if durationS > 0 && (sample.bestDurationS == 0 || durationS < sample.bestDurationS) {
+			sample.bestDurationS = durationS
+		}
+	}
+	if err := attemptRows.Err(); err != nil {
+		return nil, fmt.Errorf("iterate climb attempts: %w", err)
+	}
+
+	for id, sample := range samples {
+		row := &climbs[index[id]]
+		row.Attempts = len(sample.distances)
+		row.DistanceM = Median(sample.distances)
+		row.ElevationGainM = Median(sample.gains)
+		row.SlopePercent = Median(sample.slopes)
+		row.Cotacol = Median(sample.cotacols)
+		if sample.bestDurationS > 0 {
+			row.BestDurationS = sample.bestDurationS
+			row.HasBest = true
+		}
+	}
+	return climbs, nil
+}
+
+type attemptSamples struct {
+	distances     []float64
+	gains         []float64
+	slopes        []float64
+	cotacols      []float64
+	bestDurationS float64
+}
diff --git a/official_climb/list_test.go b/official_climb/list_test.go
new file mode 100644
index 0000000..6a65f80
--- /dev/null
+++ b/official_climb/list_test.go
@@ -0,0 +1,52 @@
+package official_climb_test
+
+import (
+	"testing"
+
+	"github.com/martinlehoux/biking_home/internal/dbtest"
+	"github.com/martinlehoux/biking_home/official_climb"
+	"github.com/stretchr/testify/assert"
+	"github.com/stretchr/testify/require"
+)
+
+func TestListWithAggregatesMediansAndUnridden(t *testing.T) {
+	db := dbtest.New(t)
+	unridden, err := official_climb.Create(db, official_climb.OfficialClimb{Name: "Col Fantome", StartCoord: coord(43.9, 5.9), EndCoord: coord(43.92, 5.92)})
+	require.NoError(t, err)
+	climbed, err := official_climb.Create(db, official_climb.OfficialClimb{Name: "Col Fréquente", StartCoord: coord(43.6, 5.4), EndCoord: coord(43.62, 5.42)})
+	require.NoError(t, err)
+	rideID := insertRide(t, db)
+
+	attempt := attemptFixture(rideID, climbed.ID, 0)
+	attempt.DistanceM = 3000
+	attempt.Cotacol = 80
+	medianAttempt := attempt
+	medianAttempt.Occurrence = 1
+	medianAttempt.DistanceM = 3200
+	medianAttempt.Cotacol = 84
+	outlier := attempt
+	outlier.Occurrence = 2
+	outlier.DistanceM = 9000
+	outlier.Cotacol = 500
+	require.NoError(t, official_climb.ReplaceRideAttempts(db, rideID, []official_climb.Attempt{attempt, medianAttempt, outlier}))
+
+	climbs, err := official_climb.ListWithAggregates(db)
+	require.NoError(t, err)
+	require.Len(t, climbs, 2)
+
+	byID := make(map[int64]official_climb.ListedClimb)
+	for _, climb := range climbs {
+		byID[climb.ID] = climb
+	}
+	frequente := byID[climbed.ID]
+	assert.Equal(t, 3, frequente.Attempts)
+	assert.Equal(t, 3200.0, frequente.DistanceM, "median ignores the 9 km outlier")
+	assert.Equal(t, 84.0, frequente.Cotacol)
+	assert.True(t, frequente.HasBest)
+	assert.Positive(t, frequente.BestDurationS)
+
+	fantome := byID[unridden.ID]
+	assert.Equal(t, 0, fantome.Attempts)
+	assert.Equal(t, 0.0, fantome.DistanceM)
+	assert.False(t, fantome.HasBest)
+}