Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE crud_logs (
  id int
, object_type text
, object_id int
, action text
, operation_ts timestamptz
, user_id int
);
    
INSERT INTO crud_logs VALUES
  (57, 'Enterprise', 56, 'update', '2023-07-21 12:51:43.904511 +00:00', 34)
, (58, 'Enterprise', 56, 'update', '2023-07-21 12:52:46.472828 +00:00', 34)
, (59, 'Enterprise', 56, 'delete', '2023-07-21 13:00:26.499430 +00:00', 34)
, (60, 'Enterprise', 56, 'update', '2023-07-21 12:51:43.904511 +00:00', 34)
, (61, 'Enterprise', 56, 'update', '2023-07-21 12:52:46.472828 +00:00', 34)
, (62, 'Enterprise', 56, 'delete', '2023-07-21 13:00:26.499430 +00:00', 34)
, (67, 'Enterprise', 56, 'update', '2023-07-21 12:51:43.904511 +00:00', 34)
, (68, 'Enterprise', 56, 'update', '2023-07-21 12:52:46.472828 +00:00', 34)
, (69, 'Enterprise', 56, 'delete', '2023-07-21 13:00:26.499430 +00:00', 59)
, (70, 'Enterprise', 56, 'update', '2023-07-21 12:51:43.904511 +00:00', 58)
, (71, 'Enterprise', 56, 'update', '2023-07-21 12:52:46.472828 +00:00', 59)
, (72, 'Enterprise', 56, 'delete', '2023-07-21 13:00:26.499430 +00:00', 58)
, (73, 'Enterprise', 56, 'delete', '2023-07-21 13:00:26.499430 +00:00', 66)
;

CREATE TABLE



INSERT 0 13


SELECT *
FROM  (
   SELECT user_id, rank() OVER (ORDER BY count(*) DESC) AS rank
   FROM   crud_logs 
   GROUP  BY 1
   ) sub
WHERE  user_id = 58;


user_id
rank


58
2



SELECT 1


-- Fix corner case issues
SELECT *
FROM  (
   SELECT user_id, rank() OVER (ORDER BY count(*) DESC) AS rank
   FROM   crud_logs 
   WHERE  user_id IS NOT NULL  -- !
   AND    operation_ts >= timestamp '2023-07-01' AT TIME ZONE 'Australia/Sydney'  -- !
   AND    operation_ts <  timestamp '2023-08-01' AT TIME ZONE 'Australia/Sydney'  -- !
   GROUP  BY 1
   ) sub
WHERE  user_id = 58;


user_id
rank


58
2



SELECT 1