I have some tables that track athletes time around a track:
competitions, competition_users and competition_user_sessions
I am constructing a query that extracts the current rank and total no of competitors for each supplied 'competition_user', I can get rank ok, but my count (totalUsers) doesn't count all competitors in the competition, it only seems to count up to the supplied user (eg. gives the same as the rank)
SELECT compUserId, rank, totalUsers FROM ( SELECT cu.competition_user_id as compUserId, cu.user_id as userId, count(*) OVER w as totalUsers, rank() OVER w as rank FROM competition_users cu LEFT JOIN current_competition_sessions ccs ON cu.competition_user_id = ccs.competition_user_id LEFT JOIN competition_user_sessions cus ON cus.competition_user_session_id = ccs.competition_user_session_id WHERE cu.left_competition = false AND cu.competition_id in (:compIds) WINDOW w AS (PARTITION BY cu.competition_id ORDER BY cus.time_in_seconds ASC) ) as sub WHERE compUserId in (:compUserIds)
My understanding was that the default frame for was the entire window, whereas this seems to be counting from frame start to current row?