I am using postgres window functions to get a list of users taking part in a competition and their corresponding ranks based on a number of columns, all good so far....now I need to get rank based on 'age group' which are a number of pre-defined categories (eg. 0-15 years, 15+ years etc...), where age is calculated based on a timestamp column 'dob'.
I am pretty sure I can do this using CASE statements but I am unable to get the query structured correctly, I have written the query below which defines the 'age groups' in a separate FROM subquery so I can reference it in the WINDOW definition, but this doesn't work as the two FROM's are independent
SELECT cu.* as compUser, cus.time_in_seconds as timeInSeconds, rank() OVER allTimes as overallRank, u.gender as gender, rank() OVER genderTimes as genderRank, ageGroup as ageGroup, rank() OVER ageGroupTimes as ageGroupRank, FROM competition_users cu, (SELECT CASE WHEN usr.dob>'2000-01-01' AND usr.dob<now() THEN '0-15' ELSE '15+' END FROM users usr WHERE usr.user_id = cu.user_id) ageGroup LEFT JOIN users u ON cu.user_id = u.user_id FULL JOIN current_competition_sessions ccs ON cu.competition_user_id = ccs.competition_user_id WHERE cu.left_competition = false AND cu.competition_id = :compId WINDOW allTimes AS (PARTITION BY cu.competition_id ORDER BY cus.time_in_seconds ASC), genderTimes AS (PARTITION BY u.gender ORDER BY cus.time_in_seconds ASC), ageGroupTimes AS (PARTITION BY ageGroup ORDER BY cus.time_in_seconds ASC)
(above produces: ERROR: invalid reference to FROM-clause entry for table "cu"...HINT: There is an entry for table "cu", but it cannot be referenced from this part of the query.)
Can someone point me in the right direction? (sample query above is simplified, there are many age categories)