By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE posts (id INTEGER PRIMARY KEY AUTO_INCREMENT, user_id INT, title VARCHAR(100))
INSERT INTO posts(user_id) VALUES
(1), (1), (1), (1),
(2), (2), (2), (2), (2),
(3),
(4), (4), (4), (4), (4),
(5), (5), (5), (5);
SELECT total_posts,
COUNT(*) number_of_users
FROM (
SELECT user_id,
COUNT(*) total_posts
FROM posts
GROUP BY user_id
) t
GROUP BY total_posts
ORDER BY total_posts DESC;
| total_posts | number_of_users |
|---|---|
| 5 | 2 |
| 4 | 2 |
| 1 | 1 |
SELECT DISTINCT
COUNT(*) total_posts,
COUNT(*) OVER (PARTITION BY COUNT(*)) number_of_users
FROM posts
GROUP BY user_id
ORDER BY total_posts DESC;
| total_posts | number_of_users |
|---|---|
| 5 | 2 |
| 4 | 2 |
| 1 | 1 |