Podiums

The number of first-, second-, and third-place finishes achieved by each athlete in individual World Cup competitions.

Rules

• First, second, and third places are counted separately for each athlete.
• The total value is the sum of all three podium positions.
• Results can cover a selected season, a custom date range, or the complete dataset.

Podium finishes are taken from the competition_results materialized view, where athletes already have their final positions for each competition.

Function overloads

Aggregating several finishing positions for different periods would be inconvenient as a single view. Instead, the PL/pgSQL podiums() function uses overloading to support three variants through one consistent name:

• two dates select a custom range,
• a season string selects one season,
• no arguments select the complete dataset.

create or replace function podiums(start_date date, end_date date)
returns table(
	jumper_id jumpers.jumper_id%type,
	firsts	bigint,
	seconds bigint,
	thirds bigint,
	total bigint
)
language plpgsql
as $$
begin
	return query
		with podiums_per_jumper as (
			select
				cr.jumper_id,
				sum(case when pos = 1 then 1 else 0 end) as firsts,
				sum(case when pos = 2 then 1 else 0 end) as seconds,
				sum(case when pos = 3 then 1 else 0 end) as thirds
			from competition_results cr
			join competitions c using (race_id)
			where start_date <= c.competition_started
				and end_date >= c.competition_started
			group by cr.jumper_id
		)
		select
			ppj.jumper_id,
			ppj.firsts,
			ppj.seconds,
			ppj.thirds,
			ppj.firsts + ppj.seconds + ppj.thirds as total
		from podiums_per_jumper ppj
		where (ppj.firsts + ppj.seconds + ppj.thirds) > 0;
end $$;

create or replace function podiums()
returns table(
	jumper_id jumpers.jumper_id%type,
	firsts	bigint,
	seconds bigint,
	thirds bigint,
	total bigint
)
language plpgsql
as $$
begin
	return query
		select *
		from podiums(make_date(1900, 1, 1), now()::date);
end $$;

create or replace function podiums(season char(9))
returns table(
	jumper_id jumpers.jumper_id%type,
	firsts	bigint,
	seconds bigint,
	thirds bigint,
	total bigint
)
language plpgsql
as $$
declare
	start_year int;
	end_year int;
begin
	start_year = split_part(season, '/', 1)::int;
	end_year = split_part(season, '/', 2)::int;

	if (end_year - start_year) != 1 then
		raise 'Argument % is not a valid season', season;
	end if;

	return query
		select *
		from podiums(
			make_date(start_year, 9, 1), 
			make_date(end_year, 5, 1)
		);
end $$;

Podiums in the 2022/2023 season

select
	j.jumper_name as jumper,
	p.firsts,
	p.seconds,
	p.thirds,
	p.total
from podiums('2022/2023') p
join jumpers j using (jumper_id)
order by p.firsts desc, p.seconds desc, p.thirds;

Results

Podiums in 2023

select
	j.jumper_name as jumper,
	p.firsts,
	p.seconds,
	p.thirds,
	p.total
from podiums('01-01-2023', '12-31-2023') p
join jumpers j using (jumper_id)
order by p.firsts desc, p.seconds desc, p.thirds;

Results

Total podiums

The no-argument overload returns all podiums recorded since the 2017/2018 season.

select
	j.jumper_name as jumper,
	p.firsts,
	p.seconds,
	p.thirds,
	p.total
from podiums() p
join jumpers j using (jumper_id)
order by p.firsts desc, p.seconds desc, p.thirds;

Results

Podiums by country

The same complete result can be aggregated by country instead of athlete.

select
	j.jumper_country as country,
	SUM(p.firsts) as firsts,
	SUM(p.seconds) as seconds,
	SUM(p.thirds) as thirds,
	SUM(p.total) as total
from podiums() p
join jumpers j using (jumper_id)
group by country
order by firsts desc, seconds desc, thirds;

Results