I have an existing query that uses the RANK() function to sum (or count) a column and rank results over a specified date range. Up until now, I have only needed the total aggregate sum or count of the given column over the date range. Now, however, I would like to create a window within this larger date range that allows me to rank results based on their count/sum over this smaller period within the overall date range. In plain english, this would be something like “find the most tracks within a consecutive 3 day period over a 30 day period”.

My existing query looks something like this:

SELECT
        u.*,
        user_resort_registrations.*,
        count(tracks) as count,
        RANK () OVER (ORDER BY count(tracks) DESC) as position
      FROM users as u
      INNER JOIN user_resort_registrations ON user_resort_registrations.user_id = u.id
      INNER JOIN resort_days ON user_resort_registrations.id = resort_days.user_resort_registration_id
      INNER JOIN resorts ON user_resort_registrations.resort_id = resorts.id
      INNER JOIN tracks ON resort_days.id = tracks.resort_day_id 
WHERE resort_days.date >= '2023-03-01T07:00:00' 
AND resort_days.date <= '2023-04-01T05:59:59' 
AND resorts.identifier = 'SOME_IDENTIFIER' 
GROUP BY u.id, user_resort_registrations.id 
LIMIT(25) 
OFFSET(0)

This ranks the results correctly by total count of tracks for each user_resort_registration.

Based on other answers on SO, I have tried using the PARTITION BY and WINDOW functionality, unfortunately to no avail. As much as possible, I need to keep the existing result structure of this query (unless what I am proposing is not possible within this structure), particularly the ORDER BY count(tracks) DESC part.

I am using postgres vesrion 14.12. Happy to provide more details if needed.