Need help with a query

[cmeinck]

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.

 

[trilogy33]

This any good?
http://xenforo.com/community/threads/new-users-posts-threads-reports-queries.15508/

 

[cmeinck]

This any good?
http://xenforo.com/community/threads/new-users-posts-threads-reports-queries.15508/

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.

 

[borbole]

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:

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:

BETWEEN UNIX_TIMESTAMP('2012-08-14 00:00:00') AND UNIX_TIMESTAMP('2012-09-14 00:00:00')

Hope it helps.

 

[cmeinck]

Try this query:

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:

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!

 

[borbole]

This worked out perfectly. Thanks once again for the assistance. You're the query master!

I am far from a query master but you are welcome