Find lagging rows of a query omitted by a WHERE clause in SQL/SQLite-CodePudding

Setup

I have a table of race times, listing a runner, their team, and their race time:

CREATE TABLE race (person TEXT, team TEXT, timer FLOAT);

INSERT INTO race
  (person, team, timer)
VALUES
  ("ahmed", "red", 4.3),
  ("baadur", "green", 4.4),
  ("carel", "red", 4.5),
  ("dada", "green", 4.9),
  ("eder", "green", 5.0),
  ("farai", "red", 5.1);

I can make a list of all people on the red team and their ranking:

SELECT person, ROW_NUMBER() OVER(ORDER BY timer) AS ranking FROM race WHERE team="red";

which emits

person	ranking
ahmed	1
carel	2
farai	3

Question

I want to also get the name of runner who followed each of these red runners, i.e., who had the next slowest time—so I want:

person	ranking	next runner
ahmed	1	baadur
carel	2	dada
farai	3	null

where note how since nobody has a slower time than Farai, Farai's third column is null.

Can I do this efficiently with a single query?

Considerations

I'd like to avoid first getting the list of red runners and their times with one query and then making another three (or more generally N) queries to get the runner with the next time, e.g., this is what I do not want to do:

SELECT person FROM race WHERE timer>=4.3 AND person != "ahmed" LIMIT 1;
SELECT person FROM race WHERE timer>=4.5 AND person != "carel" LIMIT 1;
SELECT person FROM race WHERE timer>=5.1 AND person != "farai" LIMIT 1;
--