An example next_suitable_contestant query, pulled from the log

next_suitable_contestant.sql

SELECT 
 contestants.*,
 count(runs_with_chosen.id) AS heat_count_with_chosen,
 count(DISTINCT heats.id) AS heat_count,
 avg(runs.time) AS average_run_time
FROM "contestants"
LEFT JOIN runs ON runs.contestant_id = contestants.id 
LEFT JOIN runs AS runs_in_lane ON runs.contestant_id = contestants.id AND runs.lane = 1 
LEFT JOIN heats ON heats.id = runs.heat_id 
LEFT JOIN runs AS runs_with_chosen ON runs_with_chosen.heat_id = heats.id AND runs_with_chosen.contestant_id IN (0) 
WHERE
 (contestants.id NOT IN (0)) AND
 (contestants.retired IS NOT TRUE) 
GROUP BY contestants.id 
HAVING
 count(DISTINCT heats.id) < 3 AND
 count(runs_in_lane.id) = 0 
ORDER BY
 heat_count_with_chosen,
 heat_count,
 average_run_time DESC,
 contestants.created_at 
LIMIT 1