Mysql query - find out 1000th row from a table for each user

Question

Lets say I have a table where we log activities of user, I need to find out created_at of the 1000th activity of each user..

Following query works fine for one user..

select `created_at` from `user_activity` where `user_id` = 2 order by `created_at` limit 1 offset 1000

But how do i run it for all users..

I have tried

where user_id in (select DISTINCT(user_id) from user_activity)

OR

GROUP BY user_id

Answer 1

To get all the (1000th) rows, using the primary key of the table:

SELECT du.user_id
     , ua.*                               -- any other column you need
FROM 
    ( SELECT DISTINCT user_id
      FROM user_activity
    ) AS du
  LEFT JOIN
    user_activity AS ua 
      ON ua.pk =                          -- the PRIMARY KEY of the table
         ( SELECT ai.pk 
           FROM user_activity AS ai
           WHERE ai.user_id = du.user_id
           ORDER BY created_at 
               LIMIT 1 OFFSET 999
         ) ;

or using the (user_id, created_at) combination:

SELECT du.user_id
     , ua.* 
FROM 
    ( SELECT DISTINCT user_id
      FROM user_activity
    ) AS du
  LEFT JOIN
    user_activity AS ua 
      ON  ua.user_id = du.user_id
      AND ua.created_at =
         ( SELECT ai.created_at
           FROM user_activity AS ai
           WHERE ai.user_id = du.user_id
           ORDER BY created_at 
               LIMIT 1 OFFSET 999
         ) ;

I guess the number of rows in the table per user is high, so an index on (user_id, created_at, pk) would be good for efficiency.