SQL Query: Count How Many 2nd Post Replies in past 30 Days

ProWeb

Enthusiast
Joined
Feb 20, 2008
Messages
209
Our members are working towards having zero unanswered threads.

I was thinking it would be cool to run a query on the backend (via phpmyadmin) and see which members are most responsive with being the 2nd person to reply to threads.

Any SQL Masters out there able to put together a query to display the top 20 members that posted the 2nd reply to a thread in the past 30 days?

Thanks!!!
 

ProWeb

Enthusiast
Joined
Feb 20, 2008
Messages
209
Ahh.... that was dumb of me to leave that out.

Xenforo v2.1.10 Patch 2
 

Doug Heffernan

Enthusiast
Joined
Feb 28, 2018
Messages
158
Our members are working towards having zero unanswered threads.

I was thinking it would be cool to run a query on the backend (via phpmyadmin) and see which members are most responsive with being the 2nd person to reply to threads.

Any SQL Masters out there able to put together a query to display the top 20 members that posted the 2nd reply to a thread in the past 30 days?

Thanks!!!
You can not have more than one user who posted the second reply to a thread. Or did you mean to all threads?

If that is the case try this query:

SQL:
SELECT t.title, p.username as second_posters
FROM xf_post AS p
LEFT JOIN xf_thread AS t
ON(p.thread_id = t.thread_id)
WHERE p.position = 1
AND p.message_state = 'visible'
ORDER BY second_posters DESC
LIMIT 20;
 

Karll

Adherent
Joined
Dec 9, 2011
Messages
441
If you want to see how many such posts they made and include only posts from the last 30 days, use something like this - adjust the date as needed:

SQL:
SELECT username, count(*)
FROM xf_post
WHERE position = 1 AND message_state = 'visible' AND post_date > unix_timestamp('2020-05-02 11:24:00')
GROUP BY username
ORDER BY 2 DESC
LIMIT 20;
 
Last edited:

ProWeb

Enthusiast
Joined
Feb 20, 2008
Messages
209
If you want to see how many such posts they made and include only posts from the last 30 days, use something like this - adjust the date as needed:

SQL:
SELECT username, count(*)
FROM xf_post
WHERE position = 1 AND message_state = 'visible' AND post_date > unix_timestamp('2020-05-02 11:24:00')
GROUP BY username
ORDER BY 2 DESC
LIMIT 20;
BRILLIANT! This seems to be working PERFECTLY!

THANK YOU SO MUCH!!
 

ProWeb

Enthusiast
Joined
Feb 20, 2008
Messages
209
I'm a SQL dummy, but I made a tiny change so the date would be relative vs. absolute:

SELECT username, count(*)
FROM xf_post
WHERE position = 1 AND message_state = 'visible' AND post_date > UNIX_TIMESTAMP(DATE_ADD(NOW(), INTERVAL -30 DAY))
GROUP BY username
ORDER BY 2 DESC
LIMIT 20;
 

ProWeb

Enthusiast
Joined
Feb 20, 2008
Messages
209
Hey guys, this has proven to be a really fun project, thanks!

One of my members just asked "What about threads where the original poster replied to their own thread, but there aren't any other replies?"

Really interesting question. It's technically not "unanswered"... but it kinda is ;)

So, anybody up for the challenge to do more sql query magic for something like below (see bold where I clearly don't know how to code ;) ):

SELECT thread_id
FROM xf_post
WHERE position = 1 & 2 both have the same user_id, AND there are no other replies (i.e., there is no position #3 for the thread_id)?
AND message_state = 'visible'
AND post_date > UNIX_TIMESTAMP(DATE_ADD(NOW(), INTERVAL -60 DAY))
 

Karll

Adherent
Joined
Dec 9, 2011
Messages
441
Slightly different - this query lists all the threads where the only poster is the thread starter:

SQL:
SELECT q.thread_id, q.title, u.username, q.reply_count, q.user_posts
FROM (
    SELECT t.thread_id, t.title, t.user_id, t.reply_count, count(p.user_id) user_posts
    FROM xf_thread t
      JOIN xf_post p ON t.thread_id=p.thread_id
    WHERE t.user_id = p.user_id
      AND p.message_state = 'visible'
      AND t.discussion_state = 'visible'
      AND p.post_date > UNIX_TIMESTAMP(DATE_ADD(NOW(), INTERVAL -60 DAY))
    GROUP BY t.thread_id, t.title, t.user_id, t.reply_count
) q
  JOIN xf_user u ON u.user_id=q.user_id
WHERE q.reply_count = q.user_posts - 1 AND u.is_banned=0;
Not the prettiest SQL I've written, but I only spent a few minutes on it. Can definitely be improved.
 

ProWeb

Enthusiast
Joined
Feb 20, 2008
Messages
209
Oh man, you're an f'ing genius! Ugh, where's the Matrix tech when you need it! Would be great to plug-in and download the stuff you know!!!

So, I just ran the query and WOW... some of the results are awesome... and kinda sad.

Like one thread that has 4 posts... and all are the same person trying desperately to get help by supplying more info :(

I'm going to plug this into a custom page for our members to access to find more "unanswered" threads to help with!

Karll you're a master, and I REALLY appreciate your help!!!!!!!!!!!
 

ProWeb

Enthusiast
Joined
Feb 20, 2008
Messages
209
OMG, I just found a thread with 33 replies all from the same person!!! I would have NEVER expected that was possible that zero people would have posted a reply over the last month.

Data is so interesting!!! I LOVE it!!!!
 

Karll

Adherent
Joined
Dec 9, 2011
Messages
441
@Karll you're a master, and I REALLY appreciate your help!!!!!!!!!!!
Very happy to hear it was useful!
OMG, I just found a thread with 33 replies all from the same person!!! I would have NEVER expected that was possible that zero people would have posted a reply over the last month.

Data is so interesting!!! I LOVE it!!!!
Indeed - and SQL is an extremely powerful language to extract data insights! It should be taught and used more than it is.
 
Top