Biggest Second-Round Climbs

The largest gains in position between the end of the first round and the final competition standings.

Rules

• Each athlete’s position after the first round is compared with their final position in the competition.
• The improvement is calculated by subtracting the final position from the first-round position. For example, an athlete who moves from 30th after the first round to 5th overall records an advancement of 25 positions.
• Results are ordered by the size of the improvement, from the largest climb downward.

Reusing competition rankings

This analysis combines the round_results view with the competition_results materialized view created earlier.

Code

select
	to_char(c.competition_started, 'Mon MM YYYY') as date,
	c.hill_city || ' HS' || c.hill_size as hill,
	j.jumper_name as jumper,
	rr.pos as first_round_position,
	cr.pos as final_position,
	rr.pos - cr.pos as advancement
from competition_results cr
join round_results rr on
	cr.race_id = rr.race_id
	and cr.jumper_id = rr.jumper_id
	and rr.round_no = 1
join competitions c on cr.race_id = c.race_id
join jumpers j on cr.jumper_id = j.jumper_id
order by advancement desc

Results