Need sql query to find the top poster in a node

[Renegade]

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

 

[Renegade]

Come on, so many stalwarts in here and nobody to help me out with the query.

 

[Jake Bunce]

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;

 

[Jake Bunce]

SELECT u.username, COUNT(*) AS totalThreads
FROM xf_thread AS t
LEFT JOIN xf_user AS u ON (u.user_id = t.user_id)
WHERE t.node_id = 4
GROUP BY t.user_id
ORDER BY totalThreads
DESC;

 

[Renegade]

Awesome! Thanks Jake. Of help always

 

[wmnitin]

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]

I use this website to determine the Unix timestamp:

http://www.epochconverter.com/

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

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

 

[wmnitin]

I use this website to determine the Unix timestamp:

http://www.epochconverter.com/

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

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

 

[AndyB]

Is it xf_user.username or xf_post.username ? I think some mistake

xf_user.username

Did you run the query in phpMyAdmin? Be sure to change the xf_thread.node_id as needed.

 

[wmnitin]

xf_user.username

Did you run the query in phpMyAdmin? Be sure to change the xf_thread.node_id as needed.

Yeah Worked ! Thanks .