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

Need help with a query

cmeinck

Well-known member
#1
I need to run a query that will generate a list of members who had [x] or greater posts within a specific timeframe? I remember seeing something months back, but cannot seem to locate it.

Any help would be greatly appreciated.

Thanks.
 

borbole

Well-known member
#4
Thanks. I had seen that, but don't believe it'll provide what I need. I'd like to generate a list of users who have made a minimum post number within a set calendar date.
Try this query:

Code:
SELECT u.username,
COUNT(*) AS monthlypostsers
FROM xf_post p
LEFT JOIN xf_user u ON (p.user_id=u.user_id)
WHERE p.post_date BETWEEN UNIX_TIMESTAMP('2012-08-14 00:00:00') AND UNIX_TIMESTAMP('2012-09-14 00:00:00')
GROUP BY p.user_id
ORDER BY monthlypostsers DESC;

This will return a list of all the users who have posted for a month starting from 14 of last month until today. To change the date at the query above you will have to edit the time in this part:

Code:
BETWEEN UNIX_TIMESTAMP('2012-08-14 00:00:00') AND UNIX_TIMESTAMP('2012-09-14 00:00:00')
Hope it helps.
 

cmeinck

Well-known member
#5
Try this query:

Code:
SELECT u.username,
COUNT(*) AS monthlypostsers
FROM xf_post p
LEFT JOIN xf_user u ON (p.user_id=u.user_id)
WHERE p.post_date BETWEEN UNIX_TIMESTAMP('2012-08-14 00:00:00') AND UNIX_TIMESTAMP('2012-09-14 00:00:00')
GROUP BY p.user_id
ORDER BY monthlypostsers DESC;

This will return a list of all the users who have posted for a month starting from 14 of last month until today. To change the date at the query above you will have to edit the time in this part:

Code:
BETWEEN UNIX_TIMESTAMP('2012-08-14 00:00:00') AND UNIX_TIMESTAMP('2012-09-14 00:00:00')
Hope it helps.
This worked out perfectly. Thanks once again for the assistance. You're the query master!