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

race_id	jumper_id	total_points	round_no	pos
4916	122	148.20	1	1
4916	76	147.00	1	2
4916	68	146.30	1	3
4916	73	146.10	1	4
4916	72	146.00	1	5
4916	81	145.50	1	6
4916	64	142.30	1	7
4916	70	142.30	1	8
4916	74	140.40	1	9
4916	80	138.70	1	10
4916	79	138.20	1	11
4916	75	138.00	1	12
4916	67	137.80	1	13
4916	90	135.40	1	14
4916	84	134.20	1	15
4916	78	133.30	1	16
4916	97	130.00	1	17
4916	82	129.70	1	18
4916	91	129.60	1	19
4916	186	129.20	1	20

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

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

wanted	race_id
First competition held in Kulm in 2026	7553
Second competition held in Oberstdorf in 2025	7214
Second competition held in Zakopane in 2020	-1

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

jumper	distance	total_points	pos
PREVC Domen	228.5	221.90	1
EMBACHER Stephan	219.5	214.30	2
TSCHOFENIG Daniel	212.5	204.60	3
KYTOSAHO Niko	211	197.30	4
ORTNER Maximilian	205.5	196.40	5
RAIMUND Philipp	209.5	195.80	6
SCHUSTER Jonas	204.5	193.40	7
AALTO Antti	201	191.60	8
BRESADOLA Giovanni	202	190.90	9
BICKNER Kevin	204.5	189.50	10
DESCHWANDEN Gregor	202	188.00	11
KOBAYASHI Ryoyu	202	186.10	12
WELLINGER Andreas	197	186.00	13
SUNDAL Kristoffer Eriksen	200	185.00	14
FORFANG Johann Andre	197	183.70	15
STOCH Kamil	197	183.60	16
GEIGER Karl	200.5	183.30	17
AIGRO Artti	197.5	181.90	18
FOUBERT Valentin	194	181.30	19
ZAJC Timi	197	181.00	20
NAKAMURA Naoki	196	179.80	21
LINDVIK Marius	199	179.10	22
HAUSWIRTH Sandro	197	179.10	23
KRAFT Stefan	193	176.30	24
AMMANN Simon	193	168.10	25
MIZERNYKH Ilya	183	167.40	26
ZOGRAFSKI Vladimir	185	164.50	27
SATO Yukiya	182.5	163.40	28
MOGEL Zak	179.5	162.20	29
INSAM Alex	185	161.80	30