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

jumper	firsts	seconds	thirds	total
GRANERUD Halvor Egner	12	5	1	18
KUBACKI Dawid	6	4	5	15
KRAFT Stefan	5	6	6	17
LANISEK Anze	4	9	3	16
KOBAYASHI Ryoyu	3	2	1	6
WELLINGER Andreas	2	1	0	3
ZAJC Timi	1	1	0	2
JELAR Ziga	0	1	0	1
FETTNER Manuel	0	1	1	2
ZYLA Piotr	0	1	3	4
PREVC Domen	0	0	1	1
LINDVIK Marius	0	0	1	1
EISENBICHLER Markus	0	0	1	1
TANDE Daniel Andre	0	0	1	1
NAKAMURA Naoki	0	0	1	1
TSCHOFENIG Daniel	0	0	3	3
GEIGER Karl	0	0	4	4

Podiums in the entire 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

jumper	firsts	seconds	thirds	total
GRANERUD Halvor Egner	10	4	1	15
KRAFT Stefan	9	6	6	21
KOBAYASHI Ryoyu	3	3	2	8
WELLINGER Andreas	3	3	2	8
KUBACKI Dawid	2	2	4	8
GEIGER Karl	2	0	3	5
LANISEK Anze	1	7	2	10
ZAJC Timi	1	1	0	2
PASCHKE Pius	1	1	1	3
HOERL Jan	0	2	1	3
JELAR Ziga	0	1	0	1
LINDVIK Marius	0	1	0	1
DESCHWANDEN Gregor	0	1	0	1
FETTNER Manuel	0	0	1	1
ZYLA Piotr	0	0	1	1
LEYHE Stephan	0	0	1	1
PREVC Domen	0	0	1	1
EISENBICHLER Markus	0	0	1	1
TANDE Daniel Andre	0	0	1	1
TSCHOFENIG Daniel	0	0	4	4

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

jumper	firsts	seconds	thirds	total
KOBAYASHI Ryoyu	37	24	18	79
KRAFT Stefan	34	26	33	93
GRANERUD Halvor Egner	25	13	3	41
PREVC Domen	19	9	6	34
STOCH Kamil	17	10	9	36
GEIGER Karl	15	12	14	41
LANISEK Anze	11	21	11	43
KUBACKI Dawid	11	9	18	38
TSCHOFENIG Daniel	11	5	11	27
LINDVIK Marius	9	6	10	25
WELLINGER Andreas	7	9	9	25
PASCHKE Pius	6	2	2	10
HOERL Jan	5	15	8	28
FORFANG Johann Andre	5	7	6	18
TANDE Daniel Andre	5	6	6	17
ZAJC Timi	5	4	6	15
EISENBICHLER Markus	3	12	10	25
FREITAG Richard	3	5	0	8
HUBER Daniel	3	3	2	8
JOHANSSON Robert	3	3	9	15
PREVC Peter	2	6	3	11
SATO Yukiya	2	1	1	4
KOS Lovro	2	1	1	4
ZYLA Piotr	1	7	12	20
NIKAIDO Ren	1	5	2	8
DESCHWANDEN Gregor	1	4	2	7
JELAR Ziga	1	3	0	4
EMBACHER Stephan	1	3	2	6
LEYHE Stephan	1	3	2	6
STJERNEN Andreas	1	2	1	4
RAIMUND Philipp	1	2	4	7
KLIMOV Evgeniy	1	1	0	2
NAITO Tomofumi	1	1	0	2
FANNEMEL Anders	1	1	1	3
SEMENIC Anze	1	0	0	1
KOBAYASHI Junshiro	1	0	0	1
DAMJAN Jernej	1	0	0	1
PEIER Killian	0	1	0	1
STEKALA Andrzej	0	1	0	1
ASCHENWALD Philipp	0	1	1	2
FETTNER Manuel	0	1	2	3
NAKAMURA Naoki	0	1	2	3
ORTNER Maximilian	0	1	2	3
PAVLOVCIC Bor	0	1	2	3
HOFFMANN Felix	0	1	3	4
SUNDAL Kristoffer Eriksen	0	1	3	4
HAYBOECK Michael	0	1	5	6
AMMANN Simon	0	0	1	1
SCHMID Constantin	0	0	1	1
ZOGRAFSKI Vladimir	0	0	1	1
SCHUSTER Jonas	0	0	1	1
PREVC Cene	0	0	1	1
AALTO Antti	0	0	1	1
WASEK Pawel	0	0	1	1
ZNISZCZOL Aleksander	0	0	2	2

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

country	firsts	seconds	thirds	total
AUT	54	56	67	177
NOR	49	39	39	127
SLO	42	45	30	117
JPN	42	32	23	97
GER	36	46	45	127
POL	29	27	42	98
SUI	1	5	3	9
RUS	1	1	0	2
FIN	0	0	1	1
BUL	0	0	1	1