Plastic Ball

An unofficial and intentionally humorous classification for athletes who repeatedly finish just outside qualification for the second round.

About the classification

Sports journalist Marcin Hetnał created the Plastic Ball to recognize jumpers who consistently place close to the top 30 without quite qualifying. Its rules were designed for manual tracking after each competition, making their reproduction in SQL considerably more involved than a conventional ranking.

Rules

1. A jumper enters the classification after either:
   • finishing 31st in a competition, or
   • finishing 32nd three times in a row.
2. Once included, the jumper receives:
   • 5 points for a 31st place, including the result that qualified them,
   • 1 point for a 32nd place, including the results that qualified them,
   • 1 additional point when the gap to 30th place is exactly 0.1 points,
   • 1 additional point when 31 jumpers qualify for the second round and the jumper still finishes 31st.
3. At the end of the season:
   • the jumper with the most points wins,
   • a tie is resolved in favor of the jumper with the lower position in the official World Cup standings.

Implementation

The calculation uses final competition positions from competition_results and season scores from world_cups. A PL/pgSQL function is used instead of a view because the classification needs to track state while processing each athlete’s competitions in chronological order.

create function plastic_cup(season_choice varchar(100))
returns table(
	jumper_id jumpers.jumper_id%type,
	plastic_points int,
	pos int,
	wc_points int
)
language plpgsql
as $$
declare
	curr_jumper_id jumpers.jumper_id%type;
begin 
	create temporary table tmp_plastic_cup(
		jumper_id int,
		plastic_points int
	) on commit drop;

	-- for every jumper that was at least once at 31 or 32 place in the chosen season
	for curr_jumper_id in (
		select distinct cr.jumper_id
		from competition_results cr
		join competitions c using (race_id)
		where c.season = season_choice and (cr.pos = 31 or cr.pos = 32) 
	)
	loop
		declare
			curr record;
			is_cond_met boolean = false;
			pos_32_in_row int = 0;
			plastic_points int = 0;
			total_rounds int;
			thirtieth_result_points real;
		begin
			-- for every competition where the jumper was at 31 or 32 place
			for curr in (
				select cr.race_id, cr.total_points, cr.pos
				from competition_results cr
				join competitions c using (race_id)
				where 
					c.season = season_choice and
					cr.jumper_id = curr_jumper_id and
					(cr.pos = 31 or cr.pos = 32)
				order by c.competition_started
			)
			loop
				-- he will be included in the ranking once he is at 32 place three times in a row
				if curr.pos = 32 then
					pos_32_in_row = pos_32_in_row + 1;
					if pos_32_in_row >= 3 then
						is_cond_met = true;
					end if;
				else
					pos_32_in_row = 0;
				end if;

			    -- or once he is at 31 place
				if curr.pos = 31 then
					is_cond_met = true;
				end if;

				-- if both above conditions are not met, skip to the next competition
				if not is_cond_met then
					continue;
				end if;

				if curr.pos = 32 then
					-- one point for 32 place
					plastic_points = plastic_points + 1;
				elsif curr.pos = 31 then
				    -- 5 points for 31 place
					plastic_points = plastic_points + 5;

					-- loading how many rounds the jumper participated in
					select count(*)
					into total_rounds
					from results r
					where r.race_id = curr.race_id and r.jumper_id = curr_jumper_id;

					-- additional point is granted if the jumper missed 30th place by only 0.1 points

					-- for 1 round, we need to compare his score to the 30th place in the first competition round
					if total_rounds = 1 then
						select r.total_points
						into thirtieth_result_points
						from results r
						where r.race_id = curr.race_id and r.round_no = 1
						order by r.total_points desc
						offset 29
						limit 1;
					-- otherwise we need to compare his score to the 30th place in the competition overall
					else
						select cr.total_points
						into thirtieth_result_points
						from competition_results cr
						where cr.race_id = curr.race_id
						order by cr.total_points desc
						offset 29
						limit 1;

						-- additionally, 1 more point for being in the second round and still achieving only 31th place
						plastic_points = plastic_points + 1;
					end if;

					-- here we actually check if the difference is 0.1
					-- I am using < 0.199 to adjust for floating point numbers imprecision
					-- I am using > 0 because in T4S Tournament being in top 30 results is not always enough to pass to the 2nd round
					-- > in this case the difference can be negative or 0
					if (thirtieth_result_points - curr.total_points) between 0 and 0.199 then
						plastic_points = plastic_points + 1;
					end if;
				end if;
			end loop;

			-- adding to the temporary table only jumpers with plastic points
			if plastic_points > 0 then
				insert into tmp_plastic_cup (jumper_id, plastic_points)
				values (curr_jumper_id, plastic_points);
			end if;
		end;
	end loop;

	-- performing final transformations
	return query
		select 
			pc.jumper_id, 
			pc.plastic_points, 
			(rank() over (order by pc.plastic_points desc, wc.total_score asc))::int as pos, 
			wc.total_score::int as wc_points
		from tmp_plastic_cup pc
		join world_cups wc on season_choice = wc.season and pc.jumper_id = wc.jumper_id;
end $$;

2024/2025 Plastic Ball standings

select 
	j.jumper_name, 
	plastic_points, 
	pos, 
	wc_points
from plastic_cup('2024/2025')
join jumpers j using (jumper_id)

Results

Additional notes

The Four Hills Tournament creates a potential edge case because its qualification rules differ from regular World Cup competitions. The available data cannot identify that scenario reliably, so it is not handled; it is rare and does not affect the results presented here.