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
| race_id | jumper_id | total_points | pos | score |
|---|---|---|---|---|
| 4916 | 122 | 301.40002 | 1 | 100 |
| 4916 | 81 | 298.6 | 2 | 80 |
| 4916 | 72 | 293 | 3 | 60 |
| 4916 | 68 | 292.7 | 4 | 50 |
| 4916 | 78 | 284.3 | 5 | 45 |
| 4916 | 67 | 284 | 6 | 40 |
| 4916 | 76 | 283.09998 | 7 | 36 |
| 4916 | 73 | 282 | 8 | 32 |
| 4916 | 74 | 281.09998 | 9 | 29 |
| 4916 | 64 | 280.7 | 10 | 26 |
| 4916 | 70 | 279.39996 | 11 | 24 |
| 4916 | 79 | 279 | 12 | 22 |
| 4916 | 66 | 276.7 | 13 | 20 |
| 4916 | 75 | 269.5 | 14 | 18 |
| 4916 | 186 | 267.6 | 15 | 16 |
| 4916 | 82 | 265.2 | 16 | 15 |
| 4916 | 111 | 261.3 | 17 | 14 |
| 4916 | 80 | 260.80002 | 18 | 13 |
| 4916 | 97 | 258.3 | 19 | 12 |
| 4916 | 65 | 258.1 | 20 | 11 |
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
| pos | jumper | total_points | score |
|---|---|---|---|
| 1 | TSCHOFENIG Daniel (AUT) | 308.60 | 100 |
| 2 | HOERL Jan (AUT) | 306.50 | 80 |
| 3 | KRAFT Stefan (AUT) | 303.20 | 60 |
| 4 | FORFANG Johann Andre (NOR) | 301.70 | 50 |
| 5 | ORTNER Maximilian (AUT) | 300.00 | 45 |
| 6 | OESTVOLD Benjamin (NOR) | 298.20 | 40 |
| 7 | HAYBOECK Michael (AUT) | 296.90 | 36 |
| 8 | WASEK Pawel (POL) | 294.10 | 32 |
| 9 | WELLINGER Andreas (GER) | 290.90 | 29 |
| 10 | NIKAIDO Ren (JPN) | 289.90 | 26 |
| 11 | DESCHWANDEN Gregor (SUI) | 288.60 | 24 |
| 12 | PASCHKE Pius (GER) | 286.50 | 22 |
| 13 | FRANTZ Tate (USA) | 282.60 | 20 |
| 14 | BICKNER Kevin (USA) | 281.20 | 18 |
| 15 | RAIMUND Philipp (GER) | 280.90 | 16 |
| 16 | SUNDAL Kristoffer Eriksen (NOR) | 278.80 | 15 |
| 17 | MUELLER Markus (AUT) | 276.20 | 14 |
| 18 | LANISEK Anze (SLO) | 275.10 | 13 |
| 19 | PREVC Domen (SLO) | 274.20 | 12 |
| 20 | FETTNER Manuel (AUT) | 272.60 | 11 |
| 21 | GRANERUD Halvor Egner (NOR) | 271.50 | 10 |
| 22 | EMBACHER Stephan (AUT) | 271.10 | 9 |
| 23 | GEIGER Karl (GER) | 270.90 | 8 |
| 24 | KOBAYASHI Ryoyu (JPN) | 270.40 | 7 |
| 25 | PEIER Killian (SUI) | 267.00 | 6 |
| 26 | ZAJC Timi (SLO) | 266.50 | 5 |
| 27 | ZOGRAFSKI Vladimir (BUL) | 262.20 | 4 |
| 28 | KUBACKI Dawid (POL) | 261.60 | 3 |
| 29 | ZNISZCZOL Aleksander (POL) | 258.30 | 2 |
| 30 | LINDVIK Marius (NOR) | 233.20 | 1 |
| 31 | NAKAMURA Naoki (JPN) | 134.20 | 0 |
| 32 | VILLUMSTAD Fredrik (NOR) | 130.70 | 0 |
| 33 | TITTEL Adrian (GER) | 125.90 | 0 |
| 34 | FOUBERT Valentin (FRA) | 125.00 | 0 |
| 35 | AIGRO Artti (EST) | 124.60 | 0 |
| 36 | HOFFMANN Felix (GER) | 124.10 | 0 |
| 36 | KOUDELKA Roman (CZE) | 124.10 | 0 |
| 38 | AIGNER Clemens (AUT) | 124.00 | 0 |
| 39 | JELAR Ziga (SLO) | 123.60 | 0 |
| 40 | SATO Keiichi (JPN) | 121.90 | 0 |
| 41 | NAITO Tomofumi (JPN) | 120.40 | 0 |
| 42 | KOBAYASHI Sakutaro (JPN) | 119.50 | 0 |
| 43 | SCHUSTER Jonas (AUT) | 118.90 | 0 |
| 44 | LANDERER Hannes (AUT) | 117.70 | 0 |
| 45 | KOS Lovro (SLO) | 117.10 | 0 |
| 46 | INSAM Alex (ITA) | 116.00 | 0 |
| 47 | IPCIOGLU Fatih Arda (TUR) | 115.50 | 0 |
| 48 | ZYLA Piotr (POL) | 108.60 | 0 |
| 49 | URLAUB Andrew (USA) | 86.50 | 0 |
| 50 | KYTOSAHO Niko (FIN) | 80.40 | 0 |