You need to select the 100th entry for each user

Question

MySQL

Here is an interesting task for the local bright minds.

There is a MySQL table:

CREATE TABLE `post` (
  `post_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) unsigned NOT NULL,
  PRIMARY KEY (`post_id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The post_id field is auto-incrementing and unique. The user_id field is not unique. Each user with his user_id can appear in this table from one to infinity times. The number of records in the table tends to 2 million.

Question. If the user occurs in the table 100 or more times, then you need to find out the post_id of the hundredth record to which the user belongs.
Those. the resulting selection must contain the post_id of the hundredth record by user and his user_id

Answer 1

SELECT user_id,(SELECT ps2.post_id  from post ps2 WHERE ps2.user_id=p.user_id ORDER BY ps2.post_id LIMIT 100,1  ) as post_id FROM (
    SELECT DISTINCT user_id FROM post 
) p

Answer 2

It?
stackoverflow.com/questions/858746/how-do-you-select-every-n-th-row-from-mysql

Answer 3

SELECT * FROM (
    SELECT p.post_id, p.user_id, COUNT(p.post_id) as n FROM posts p GROUP BY p.user_id HEAVING n >= 100
) p
INNER JOIN users u
ON u.id = p.user_id

something along those lines...