Competition Results

The final classification of a competition, including each athlete’s combined points, finishing position, and awarded World Cup points.

Rules

• Points scored across all rounds are summed to produce each athlete’s final result.
• Athletes are ranked by their combined points.
• World Cup points are awarded to the top 30 finishers and later contribute to the overall season standings.
• Athletes outside the top 30 receive zero World Cup points.

World Cup scoring system

• 1st → 100
• 2nd → 80
• 3rd → 60
• 4th → 50
• 5th → 45
• 6th → 40
• 7th → 36
• 8th → 32
• 9th → 29
• 10th → 26
• 11th–30th → decreasing from 24 to 1

Reusable competition results

Competition results form the foundation of many later statistics. To make them faster and easier to reuse, the aggregated data is stored in a materialized view.

create materialized view competition_results
as (
	with competition_total_points as (
		select r.race_id, r.jumper_id, SUM(r.total_points) as total_points
		from results r
		group by r.race_id, r.jumper_id
	),
	results_ranked as (
		select *,
			rank() over (partition by race_id order by total_points desc) as pos
		from competition_total_points
	),
	competition_scoring as (
	    SELECT * FROM (VALUES
	        (1, 100), (2, 80), (3, 60), (4, 50), (5, 45),
	        (6, 40),  (7, 36), (8, 32), (9, 29), (10, 26),
	        (11, 24), (12, 22), (13, 20), (14, 18), (15, 16),
	        (16, 15), (17, 14), (18, 13), (19, 12), (20, 11),
	        (21, 10), (22, 9),  (23, 8),  (24, 7),  (25, 6),
	        (26, 5),  (27, 4),  (28, 3),  (29, 2),  (30, 1)
	    ) AS t(pos, score)
	)
	select r.*, coalesce(cs.score, 0) as score
	from results_ranked r
	left join competition_scoring cs on r.pos = cs.pos
	order by race_id, total_points desc
)
with data;

View presentation

Refreshing the view

PostgreSQL does not refresh materialized views automatically when their source data changes. A statement-level trigger therefore refreshes competition_results after new rows are inserted into the results table.

create function refresh_competition_results()
	returns trigger
	language plpgsql
as $$
begin
	refresh materialized view competition_results;
	return null;
end $$;

create trigger results_insert
	after insert
	on results
	for each statement
	execute function refresh_competition_results();

Refreshing a materialized view through a trigger would often be too expensive for a frequently updated table. Here, however, results change only when the web scraping pipeline loads new competitions, making the trade-off acceptable for this project.

Presenting a competition

The materialized view can now be combined with athlete data and find_race_id to present the competition held in Bischofshofen, Austria, in 2025:

select
	cr.pos,
	j.jumper_name || ' (' || j.jumper_country || ')' as jumper,
	cr.total_points::decimal(10, 2),
	cr.score
from competition_results cr
join jumpers j using (jumper_id)
where race_id = find_race_id(2025, 'Bischofshofen', 1);

Results