Result Streaks

The longest sequences of consecutive competition results within a selected range of finishing positions.

Rules

• A streak continues while an athlete finishes within the selected inclusive position range.
• A result outside that range ends the athlete’s current streak.
• Streaks continue across season boundaries rather than resetting for each season.
• The same calculation supports wins, podiums, top 10 finishes, top 30 finishes, and other position ranges.

The find_in_row() function processes final positions from competition_results in chronological order. Its lower and upper position arguments define which results continue a streak—for example, 1–1 for wins or 1–3 for podiums.

Finding consecutive results

create or replace function find_in_row(lb_pos int, ub_pos int)
returns table(
	jumper_id int,
	date_from date,
	date_to date,
	n int
)
language plpgsql
as $$
declare
	curr_competition record;
begin
	create temporary table tmp_results(
		jumper_id int,
		date_from date,
		date_to date,
		n int
	) on commit drop;

	create temporary table tmp_counters(
		jumper_id int,
		date_from date,
		date_to date,
		n int,
		constraint uq_tmp_counters_jumper unique (jumper_id)
	) on commit drop;

	for curr_competition in (
		select c.race_id, c.competition_started
		from competitions c
		order by c.competition_started
	)
	loop
		insert into tmp_counters as tc
			select 
				cr.jumper_id, 
				curr_competition.competition_started::date, 
				curr_competition.competition_started::date,
				1
			from competition_results cr
			where cr.race_id = curr_competition.race_id 
				and cr.pos >= lb_pos 
				and cr.pos <= ub_pos
		on conflict on constraint uq_tmp_counters_jumper
		do update set 
			date_to = curr_competition.competition_started::date, 
			n = tc.n + 1;

		with outdated as (
			delete from tmp_counters tc
			where tc.date_to != curr_competition.competition_started::date
			returning tc.jumper_id, tc.date_from, tc.date_to, tc.n
		)
		insert into tmp_results
		select * from outdated;
	end loop;

	return query
		select * from tmp_results;
end $$;

Consecutive wins

select
	j.jumper_name,
	date_from,
	date_to,
	n as wins_in_a_row
from find_in_row(1, 1)
join jumpers j using (jumper_id)
order by n desc;

Results

Consecutive podiums

select
	j.jumper_name,
	date_from,
	date_to,
	n as wins_in_a_row
from find_in_row(1, 3)
join jumpers j using (jumper_id)
order by n desc;

Results

Consecutive top 30 finishes

select
	j.jumper_name,
	date_from,
	date_to,
	n as wins_in_a_row
from find_in_row(1, 30)
join jumpers j using (jumper_id)
order by n desc;

Results