Postgres | select rank from only one entry

I have a following table that represents user activity in API endpoints.

create table crud_logs
(
    id           bigint generated always as identity
        constraint pk_crud_logs
            primary key,
    object_type  varchar(255)                           not null,
    object_id    bigint                                 not null,
    action       crudtypes                              not null,
    operation_ts timestamp with time zone default now() not null,
    user_id      bigint
        constraint fk_crud_logs_user_id_users
            references users
            on delete set null
);

I would like to somehow get , as a part of a user statistics api, information for exact user with exact user_id about what rank (numeric or %-wise) he has by count of records being made in this table during exact period (for simlicity let’s say from forever) comparative to all other users present in this table.

example –

here user with user_id == 34 would have first place cos he has larger amount of entries amoung other users, etc, etc…

What i have managed to write is the following query:

select user_id, rank() over (order by cnt desc )
from (select user_id, count(*) cnt from crud_logs group by user_id) sq

but it kind of works for all users, whereas i need to have a result user_id, rank only for 1 specific user, for example with user_id = 58

expected output would be (user_id = 58, rank = 2)

>Solution :

The query you’ve written is pretty close. To obtain the rank for a specific user, you could simply add a WHERE clause in the outer SQL query to filter for the required user_id.

Here’s how you could modify your query:

SELECT user_id, rank 
FROM (
    SELECT user_id, RANK() OVER (ORDER BY cnt DESC) as rank 
    FROM (
        SELECT user_id, COUNT(*) cnt 
        FROM crud_logs 
        GROUP BY user_id
    ) sq
) result
WHERE user_id = 58

In this query, the innermost subquery groups the crud_logs table by user_id and counts the number of entries for each user. The middle subquery ranks these users by their count in descending order. Finally, the outer query filters these results to return the rank of the user with user_id = 58.