How can I return top 4 results by user using Postgres sql

Question

Here is my problem: I have a table that stores the results of surveys taken by users. A single user can take multiple surveys. The key columns are: user_id, the user identifier and survey_id, the unique identifier of the survey. survey_id is incremented each time a survey is taken so if I query a specific user_id, order by survey_id descending and limit to top 4 rows I can get the last 4 surveys for a given user. My problem is I would like to query the last 4 surveys for all users in the table. I'm stumped on how to do this but this is what I have so far:

SELECT *
FROM
 (
SELECT user_id
FROM
(
    SELECT
        user_id, count(all_survey_res_id) as numsurveys
    FROM
        all_survey_res
    GROUP BY user_id
    ORDER BY count(all_survey_res_id) DESC
) AS T1
WHERE numsurveys >= 4
)
ORDER BY user_id, all_survey_res_id

This gives me all of the records for each user that has more than 4 surveys but I then want to limit the result to just those top 4 surveys. I could solve this with code back in the application but I would rather see if I can just get the query to do this.

Answer 1

I think you can do this with window functions:

select
    *
from (
    select
        user_id,
        survey_id,
        row_number() over (partition by user_id order by survey_id desc) rn
    from
        all_survey_res
    ) x
where
    x.rn <= 4