Round Results

A ranking of every jumper in a single competition round, based only on the points scored for that round’s jump.

Rules

• Each round is ranked independently using the points awarded for one jump.
• A World Cup competition usually consists of two rounds.
• Every jump is assigned a position within its competition and round.

Ranking each round

To support further analysis, a round_results view is created. It contains the same number of rows as the results table, but keeps only the essential scoring data (total_points) and adds a pos column with the ranking for each round.

create view round_results
as
select
	r.race_id,
	r.jumper_id,
	r.total_points::decimal(10, 2),
	r.round_no,
	rank() over (
		partition by r.race_id, r.round_no 
		order by total_points desc
	) as pos
from results r;

View presentation

As shown above, the view is still intentionally close to the raw data. Finding a specific round is not very intuitive because it requires knowing the exact race_id or competition date.

Finding a competition

From a fan’s perspective, competitions are usually identified by their year, location, and order—for example, the second event held in Engelberg in 2017. The find_race_id function accepts those three values and returns the corresponding race_id.

create function find_race_id(year int, city varchar(100), race_no int)
returns competitions.race_id%type
language plpgsql
as $$
declare
	found_id competitions.race_id%type = -1;
	i int = 1;
begin
	for found_id in (
		select race_id
		from competitions
		where 
			competition_started >= make_date(year, 1, 1)
			and competition_started <= make_date(year, 12, 31)
			and hill_city = city
	)
	loop
		if i = race_no then
			return found_id;
		else
			i = i + 1;
		end if;
	end loop;

	if not found then
		raise warning 'Such competition was not found';
	else
		raise warning 'Similar competitions exists but the race_no = % is too high', race_no;
	end if;

	return -1;
end $$;

Function presentation

-- First competition held in Kulm in 2026
select find_race_id(2026, 'Kulm', 1)
union all
-- Second competition held in Oberstdorf in 2025
select find_race_id(2025, 'Oberstdorf', 2)
union all
-- Second competition held in Zakopane in 2020
select find_race_id(2020, 'Zakopane', 2);

The lookup for the second Zakopane competition in 2020 returns -1 because only one competition was held there that year.

Presenting a round

Finally, the view and function can be combined to present a complete round—for example, the second round of the 2026 competition in Kulm:

select 
	j.jumper_name as jumper,
	r.distance,
	rr.total_points,
	rr.pos
from round_results rr
join results r on 
	rr.race_id = r.race_id
	and rr.round_no = r.round_no
	and rr.jumper_id = r.jumper_id
join competitions c on rr.race_id = c.race_id
join jumpers j on rr.jumper_id = j.jumper_id
where 
	rr.race_id = find_race_id(2026, 'Kulm', 1)
	and rr.round_no = 2;

Results