Query to find top posters in a specific Node ID

[CTXMedia]

I wonder if one of you could help me with an SQL query.

I'd like to list all members who have posted in a specific node ID in order of most posts (excluding any deleted posts), e.g.;

Member A - 15659
Member X - 14957
Member J - 8452
Member C - 6695
Member F - 2341

Thanks in advance,
Shaun

 

[Chris D]

Should be something like this (untested) just change the node_id to whatever you want:

SELECT post.user_id, COUNT(*) AS count
FROM xf_post AS post
INNER JOIN xf_thread AS thread ON
    (post.thread_id = thread.thread_id)
INNER JOIN xf_user AS user ON
    (post.user_id = user.user_id)
WHERE post.message_state = 'visible'
    AND thread.discussion_state = 'visible'
    AND thread.node_id = 123
    AND user.user_state = 'valid'
    AND user.is_banned = 0
GROUP BY post.user_id
ORDER BY count DESC

EDIT: Just updated it to include only valid, not banned users, and a check to ensure the thread is visible.

 

[CTXMedia]

Nice one - thanks Chris - much appreciated.

 

[Xon]

Should be something like this (untested) just change the node_id to whatever you want:

SELECT post.user_id, COUNT(*) AS count
FROM xf_post AS post
INNER JOIN xf_thread AS thread ON
    (post.thread_id = thread.thread_id)
INNER JOIN xf_user AS user ON
    (post.user_id = user.user_id)
WHERE post.message_state = 'visible'
    AND thread.discussion_state = 'visible'
    AND thread.node_id = 123
    AND user.user_state = 'valid'
    AND user.is_banned = 0
GROUP BY post.user_id
ORDER BY count DESC

EDIT: Just updated it to include only valid, not banned users, and a check to ensure the thread is visible.

Much faster way:

SELECT post.user_id, SUM(post.post_count) AS count
FROM xf_thread_user_post AS post
INNER JOIN xf_thread AS thread ON
    (post.thread_id = thread.thread_id)
INNER JOIN xf_user AS user ON
    (post.user_id = user.user_id)
WHERE  thread.discussion_state = 'visible'
    AND thread.node_id = 123
    AND user.user_state = 'valid'
    AND user.is_banned = 0
GROUP BY post.user_id
ORDER BY count DESC

This scales on the number of threads/users, rather than posts/threads/users.

 

[CTXMedia]

Thanks @Xon

 

[Breixo]

Should be something like this (untested) just change the node_id to whatever you want:

SELECT post.user_id, COUNT(*) AS count
FROM xf_post AS post
INNER JOIN xf_thread AS thread ON
    (post.thread_id = thread.thread_id)
INNER JOIN xf_user AS user ON
    (post.user_id = user.user_id)
WHERE post.message_state = 'visible'
    AND thread.discussion_state = 'visible'
    AND thread.node_id = 123
    AND user.user_state = 'valid'
    AND user.is_banned = 0
GROUP BY post.user_id
ORDER BY count DESC

EDIT: Just updated it to include only valid, not banned users, and a check to ensure the thread is visible.

Much faster way:

SELECT post.user_id, SUM(post.post_count) AS count
FROM xf_thread_user_post AS post
INNER JOIN xf_thread AS thread ON
    (post.thread_id = thread.thread_id)
INNER JOIN xf_user AS user ON
    (post.user_id = user.user_id)
WHERE  thread.discussion_state = 'visible'
    AND thread.node_id = 123
    AND user.user_state = 'valid'
    AND user.is_banned = 0
GROUP BY post.user_id
ORDER BY count DESC

This scales on the number of threads/users, rather than posts/threads/users.

Thanks @Xon

Hi, thanks for sharing your help.
I find this quite interesting.
Is it possible to do the same but getting the username instead of user_id?

My attempt:

SELECT post.username, SUM(post.post_count) AS count
FROM xf_thread_user_post AS post
INNER JOIN xf_thread AS thread ON
    (post.thread_id = thread.thread_id)
INNER JOIN xf_user AS user ON
    (post.username = user.username)
WHERE  thread.discussion_state = 'visible'
    AND thread.thread_id = 1
    AND user.user_state = 'valid'
    AND user.is_banned = 0
GROUP BY post.username
ORDER BY count DESC

But obviously it didn't work for me

I already checked this addon from Xon/Fuhrmann
https://xenforo.com/community/resources/who-replied.1537/
But what I need is to be able to export a list of usernames.
Thanks in advance!

PS: well, just made a last search before posting and found this thread.
It helped me to mix both and get the solution that works for me (getting usernames from members who posted in a thread, useful for contests or giveaways):

SELECT post.user_id, user.username, SUM(post.post_count) AS count
FROM xf_thread_user_post AS post
INNER JOIN xf_thread AS thread ON
    (post.thread_id = thread.thread_id)
INNER JOIN xf_user AS user ON
    (post.user_id = user.user_id)
WHERE  thread.discussion_state = 'visible'
    AND thread.thread_id = 1
    AND user.user_state = 'valid'
    AND user.is_banned = 0
GROUP BY post.user_id
ORDER BY count DESC