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

ProWeb

ProWeb

Enthusiast
Joined
Feb 20, 2008
Messages
242
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

ProWeb

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

Xenforo v2.1.10 Patch 2
 
D

Doug Heffernan

Enthusiast
Joined
Feb 28, 2018
Messages
156
ProWeb said:
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!!!
Click to expand...

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

Karll

Adherent
Joined
Dec 9, 2011
Messages
452
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

ProWeb

Enthusiast
Joined
Feb 20, 2008
Messages
242
Karll said:
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;
Click to expand...
BRILLIANT! This seems to be working PERFECTLY!

THANK YOU SO MUCH!!
 
ProWeb

ProWeb

Enthusiast
Joined
Feb 20, 2008
Messages
242
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

ProWeb

Enthusiast
Joined
Feb 20, 2008
Messages
242
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

Karll

Adherent
Joined
Dec 9, 2011
Messages
452
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

ProWeb

Enthusiast
Joined
Feb 20, 2008
Messages
242
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

ProWeb

Enthusiast
Joined
Feb 20, 2008
Messages
242
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

Karll

Adherent
Joined
Dec 9, 2011
Messages
452
ProWeb said:
@Karll you're a master, and I REALLY appreciate your help!!!!!!!!!!!
Click to expand...
Very happy to hear it was useful!
ProWeb said:
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!!!!
Click to expand...
Indeed - and SQL is an extremely powerful language to extract data insights! It should be taught and used more than it is.
 
ProWeb

ProWeb

Enthusiast
Joined
Feb 20, 2008
Messages
242
A quick follow-up: My members are LOVING seeing which ones are answering the most unanswered threads. It's become almost a bit of a competition for them!

Thanks again guys for the help!
 
SaN-DeeP

SaN-DeeP

TechArena.IN
Joined
Jun 30, 2004
Messages
4,293
Instead check your web stats for queries made..

Make similar topics around same and include in master topics/threads..
 
You must log in or register to reply here.
Top