• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

Need sql query to find the top poster in a node

Renegade

Well-known member
#1
I can understand basic queries but I rather not write them. So can someone please let me know the query that I can run to find out the top 10 posters in a node and another to find the top thread starter in a node.

I need to run it only once so I will be doing it from phpmyadmin.

Thank you :love:
 
#6
Code:
SELECT u.username, COUNT(*) AS totalPosts
FROM xf_post AS p
LEFT JOIN xf_thread AS t ON (t.thread_id = p.thread_id)
LEFT JOIN xf_user AS u ON (u.user_id = p.user_id)
WHERE t.node_id = 4
GROUP BY p.user_id
ORDER BY totalPosts
DESC;
View attachment 49034
Hello,

if i use WHERE FROM_UNIXTIME (`post_date`) BETWEEN '20140101' AND '20140303'

It is showing some error like ambiguous, I think error is because post_date column is present on both table xf_thread & xf_post.

Please provide me correct code for same ! (Need to calculate post count based on time)
 

AndyB

Well-known member
#7
I use this website to determine the Unix timestamp:

http://www.epochconverter.com/

One the Unix timestamp is determined, you can do this:

Code:
SELECT xf_user.username, COUNT(*) AS totalPosts
FROM xf_post
LEFT JOIN xf_thread ON xf_thread.thread_id = xf_post.thread_id
LEFT JOIN xf_user ON xf_user.user_id = xf_post.user_id
WHERE xf_thread.node_id = 5
AND xf_post.post_date >= 1388534400
AND xf_post.post_date < 1393632000
GROUP BY xf_post.user_id
ORDER BY totalPosts DESC
 
#8
I use this website to determine the Unix timestamp:

http://www.epochconverter.com/

One the Unix timestamp is determined, you can do this:

Code:
SELECT xf_user.username, COUNT(*) AS totalPosts
FROM xf_post
LEFT JOIN xf_thread ON xf_thread.thread_id = xf_post.thread_id
LEFT JOIN xf_user ON xf_user.user_id = xf_post.user_id
WHERE xf_thread.node_id = 5
AND xf_post.post_date >= 1388534400
AND xf_post.post_date < 1393632000
GROUP BY xf_post.user_id
ORDER BY totalPosts DESC
Is it xf_user.username or xf_post.username ? I think some mistake