how to select the max of COUNT(*) (SQL postgresql)

I switched from NOSQL to SQL and i can’t find how to select the max of count(*)

i created users table, posts and comments. i want to select TOP 10 users with the max of posts and max of comments

SELECT 
  fullname, 
   (SELECT COUNT(*)
   FROM posts WHERE posts.author_id = users.id) 
    AS total_posts,
  (SELECT COUNT(*)
    FROM comments WHERE comments.author_id = users.id)
    AS total_comments 
  FROM users

Answer

First, using SQL database require you to define the JOIN statement between the associated tables to get the referenced relationship data.

Since you require to get TOP 10 user with most posts, using traditional approach with LIMIT 10 will have problem when users have the same post count and should be considered the same rank. E.g: 4 people have 5 posts, 3 people have 6 posts..

To solve the same post count problem above, we will choose RANK query, this will treat people with the same post count as the same rank.

SELECT users.fullname, posting_rank_stats.total_posts, posting_rank_stats.posting_rank
FROM users
INNER JOIN
(
    SELECT author_id, total_posts, RANK() OVER (ORDER BY post_author_count.total_posts DESC) AS posting_rank
    FROM
    (
        SELECT COUNT(*) AS total_posts, author_id
        FROM posts
        GROUP BY author_id
    ) post_author_count
) posting_rank_stats
ON users.id = posting_rank_stats.author_id
-- we only want to search for top 10
WHERE posting_rank_stats.posting_rank <= 10;

Breaking to small pieces:

1.Get post count for each user

First, we get the post count for each user, this require the COUNT function, and GROUP BY author_id

SELECT COUNT(*) AS total_posts, author_id
FROM posts
GROUP BY author_id

2. Calculating the rank of post count

Use the RANK() function to calculate the rank of the post. Ranking positition is determined by the highest number of total_posts

SELECT author_id, total_posts, RANK() OVER (ORDER BY post_author_count.total_posts DESC) AS posting_rank
FROM
(
    SELECT COUNT(*) AS total_posts, author_id
    FROM posts
    GROUP BY author_id
) post_author_count

3. Wrap up

The last step is joining the users table with the posting_rank_stats table to get the result. Since we only want to get in the TOP 10 user with most post, so you will need to add the condition posting_rank_stats.posting_rank <= 10 in the WHERE clause

Apply the same concept to get comment rank also.

Here is the db fiddle I created, combine both posting rank and commenting rank

SELECT users.id , users.fullname, posting_rank_stats.posting_rank , posting_rank_stats.total_posts, commenting_rank_stats.comment_rank, commenting_rank_stats.total_comments
FROM users

LEFT JOIN
(
    SELECT author_id, total_posts, RANK() OVER (ORDER BY post_author_count.total_posts DESC) AS posting_rank
    FROM
    (
        SELECT COUNT(*) AS total_posts, author_id
        FROM posts
        GROUP BY author_id
    ) post_author_count 
) posting_rank_stats ON users.id = posting_rank_stats.author_id

LEFT JOIN
(
    SELECT author_id, total_comments, RANK() OVER (ORDER BY comment_author_count.total_comments DESC) AS comment_rank
    FROM 
    (
        SELECT COUNT(*) AS total_comments, author_id
        FROM comments
        GROUP BY author_id
    ) comment_author_count
) commenting_rank_stats ON users.id = commenting_rank_stats.author_id;

id	fullname	posting_rank	total_posts	comment_rank	total_comments
1	Jackson	1	3	5	1
2	Marry	4	1	1	5
3	Josh	4	1	2	3
4	Harley	1	3	4	2
5	Gordon	4	1	5	1
6	Barney	4	1	2	3
7	Gman	4	1
8	Stephan	3	2
9	Lucy
10	Jordan
11	Bill
12	Rosh
13	Lee

---