World Cup Standings

A season-long classification based on the World Cup points accumulated by each athlete across individual competitions.

Rules

• World Cup points from every individual competition are summed for each athlete within a season.
• Final positions are assigned by ranking athletes by their accumulated points.

The calculation reuses the competition_results materialized view, where World Cup points have already been assigned for every competition.

Assigning competitions to seasons

Before results can be aggregated, each competition needs to belong to a season. A stored generated column derives that value from competition_started.

This project uses a deliberately simple boundary: January through June belongs to the season that began in the previous year, while July through December begins the next season. This is a modelling convention rather than an official World Cup rule.

alter table competitions
add column season varchar(50) generated always as (
	case
		when extract(month from competition_started at time zone 'UTC') <= 6 then
			(extract(year from competition_started at time zone 'UTC') - 1) ||
			'/' ||
			extract(year from competition_started at time zone 'UTC')
		else
			extract(year from competition_started at time zone 'UTC') ||
			'/' ||
			(extract(year from competition_started at time zone 'UTC') + 1)
	end
) stored;

Calculating the standings

The world_cups view sums competition points for each athlete within a season, then assigns positions based on the resulting total.

create view world_cups
as
with score_by_jumper as (
	select 
		c.season, 
		cr.jumper_id, 
		sum(cr.score) as total_score
	from competition_results cr
	join competitions c USING (race_id)
	group by c.season, cr.jumper_id
)
select
	sbj.season,
	sbj.jumper_id,
	sbj.total_score,
	rank() over (partition by season order by total_score desc) as pos
from score_by_jumper sbj;

Raw view presentation

2017/2018 World Cup standings

The raw view can be joined with athlete data to present the final classification for a selected season.

select 
	j.jumper_name as jumper,
	wc.total_score
from world_cups wc
join jumpers j using (jumper_id)
where season = '2017/2018'
order by total_score desc;

Results

Additional notes

In the event of a tie for the overall World Cup victory, the winner is determined by the number of wins, followed by second places, third places, and so on. This has happened only once and is omitted here because such logic would be too complex for a single SQL query.