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

season	jumper_id	total_score	pos
2017/2018	65	1443	1
2017/2018	67	1070	2
2017/2018	68	985	3
2017/2018	66	881	4
2017/2018	76	840	5
2017/2018	72	828	6
2017/2018	81	821	7
2017/2018	77	665	8
2017/2018	73	633	9
2017/2018	78	597	10
2017/2018	64	568	11
2017/2018	88	496	12
2017/2018	80	427	13
2017/2018	71	427	13
2017/2018	83	413	15
2017/2018	70	403	16
2017/2018	122	379	17
2017/2018	74	327	18
2017/2018	97	312	19
2017/2018	87	280	20

2017/2018 World Cup standings

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

select 
	wc.pos as no,
	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

no	jumper	total_score
1	STOCH Kamil	1443
2	FREITAG Richard	1070
3	TANDE Daniel Andre	985
4	KRAFT Stefan	881
5	JOHANSSON Robert	840
6	WELLINGER Andreas	828
7	FORFANG Johann Andre	821
8	STJERNEN Andreas	665
9	KUBACKI Dawid	633
10	EISENBICHLER Markus	597
11	KOBAYASHI Junshiro	568
12	FANNEMEL Anders	496
13	HULA Stefan	427
13	GEIGER Karl	427
15	PREVC Peter	413
16	ZYLA Piotr	403
17	DAMJAN Jernej	379
18	LEYHE Stephan	327
19	AMMANN Simon	312
20	GRANERUD Halvor Egner	280
21	KOT Maciej	261
22	SEMENIC Anze	256
23	HAYBOECK Michael	245
24	KOBAYASHI Ryoyu	187
25	BARTOL Tilen	185
26	KASAI Noriaki	164
27	FETTNER Manuel	137
28	HUBER Daniel	117
29	AIGNER Clemens	104
30	PASCHKE Pius	90
31	TAKEUCHI Taku	89
32	ZAJC Timi	88
33	PREVC Domen	81
33	SCHMID Constantin	81
35	SCHLIERENZAUER Gregor	77
36	WOLNY Jakub	73
37	POPPINGER Manuel	66
38	DESCHWANDEN Gregor	54
39	DEZMAN Nejc	50
39	BICKNER Kevin	50
41	LINDVIK Marius	49
42	KORNILOV Denis	46
43	WANK Andreas	45
44	JELAR Ziga	43
45	SATO Yukiya	41
46	ASCHENWALD Philipp	34
47	BOYD-CLOWES Mackenzie	31
48	KOZISEK Cestmir	30
49	KRANJEC Robert	26
50	TEPES Jurij	22
51	ALTENBURGER Florian	20
52	ZOGRAFSKI Vladimir	15
53	NAGLIC Tomaz	14
54	AALTO Antti	13
54	PEIER Killian	13
54	PAVLOVCIC Bor	13
57	RHOADS William	12
57	KOUDELKA Roman	12
59	INSAM Alex	11
59	SIEGEL David	11
61	RINGEN Sondre	9
61	WOHLGENANNT Ulrich	9
63	LEAROYD Jonathan	8
63	ITO Daiki	8
65	KLIMOV Evgeniy	7
66	NOUSIAINEN Eetu	6
67	DESCOMBES SEVOIE Vincent	4
68	STURSA Vojtech	3
69	VASSILIEV Dimitry	2
69	SCHULER Andreas	2
69	LEITNER Clemens	2
69	NAKAMURA Naoki	2
73	PILCH Tomasz	1
73	COLLOREDO Sebastian	1
75	ROMASHOV Alexey	0
75	HILDE Tom	0
75	LANISEK Anze	0
75	GLASDER Michael	0
75	POLASEK Viktor	0
75	PEDERSEN Robin	0
75	BRESADOLA Davide	0
75	SOKOLENKO Konstantin	0
75	AIGRO Artti	0
75	TROFIMOV Roman Sergeevich	0
75	HLAVA Lukas	0
75	MAKSIMOCHKIN Mikhail	0
75	WASEK Pawel	0
75	ZNISZCZOL Aleksander	0
75	HARADA Yumu	0
75	SAKUYAMA Kento	0
75	NAITO Tomofumi	0
75	HAZETDINOV Ilmir	0
75	VANCURA Tomas	0
75	SCHIFFNER Markus	0
75	EGLOFF Luca	0
75	CECON Federico	0
75	NAZAROV Mikhail	0
75	PREVC Cene	0
75	LACKNER Thomas	0
75	ASIKAINEN Lauri	0
75	AHONEN Janne	0
75	KORHONEN Janne	0
75	ALAMOMMO Andreas	0
75	MAEAETTAE Jarkko	0
75	TOLLINGER Elias	0
75	NOMME Martti	0
75	HAUSWIRTH Sandro	0
75	CZYZ Bartosz	0
75	BJOERENG Joacim Oedegaard	0

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.