• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.
  • This forum has been archived. New threads and replies may not be made. All add-ons/resources that are active should be migrated to the Resource Manager. See this thread for more information.

new users/posts/threads reports (queries)

Jake Bunce

XenForo moderator
Staff member
#1
These all generate results like this:

Screen shot 2010-12-27 at 9.17.08 AM.png

By Day:

For registrations per day:

Code:
SELECT COUNT(*) AS registrations, FROM_UNIXTIME(register_date, '%Y-%m-%d') AS date
FROM xf_user
GROUP BY date
ORDER BY date
For posts per day:

Code:
SELECT COUNT(*) AS posts, FROM_UNIXTIME(post_date, '%Y-%m-%d') AS date
FROM xf_post
GROUP BY date
ORDER BY date
For threads per day:

Code:
SELECT COUNT(*) AS threads, FROM_UNIXTIME(post_date, '%Y-%m-%d') AS date
FROM xf_thread
GROUP BY date
ORDER BY date
 

Jake Bunce

XenForo moderator
Staff member
#2
By Month:

For registrations per month:

Code:
SELECT COUNT(*) AS registrations, FROM_UNIXTIME(register_date, '%Y-%m') AS date
FROM xf_user
GROUP BY date
ORDER BY date
For posts per month:

Code:
SELECT COUNT(*) AS posts, FROM_UNIXTIME(post_date, '%Y-%m') AS date
FROM xf_post
GROUP BY date
ORDER BY date
For threads per month:

Code:
SELECT COUNT(*) AS threads, FROM_UNIXTIME(post_date, '%Y-%m') AS date
FROM xf_thread
GROUP BY date
ORDER BY date
 

Jake Bunce

XenForo moderator
Staff member
#3
These 'per week' queries return slightly different results than the others. You will see the week number of the year, like this:

Screen shot 2011-05-03 at 9.45.48 AM.png

And startOfWeek isn't necessarily the first day of the week. Rather it's the earliest day of the week within the data set. So, for example, if no one posted on Sunday then startOfWeek will show the date for Monday.

By week:

For registrations per week:

Code:
SELECT COUNT(*) AS registrations, FROM_UNIXTIME(register_date, '%Y-%U') AS yearWeek, MIN(FROM_UNIXTIME(register_date, '%Y-%m-%d')) AS startOfWeek
FROM xf_user
GROUP BY yearWeek
ORDER BY yearWeek
For posts per week:

Code:
SELECT COUNT(*) AS posts, FROM_UNIXTIME(post_date, '%Y-%U') AS yearWeek, MIN(FROM_UNIXTIME(post_date, '%Y-%m-%d')) AS startOfWeek
FROM xf_post
GROUP BY yearWeek
ORDER BY yearWeek
For threads per week:

Code:
SELECT COUNT(*) AS threads, FROM_UNIXTIME(post_date, '%Y-%U') AS yearWeek, MIN(FROM_UNIXTIME(post_date, '%Y-%m-%d')) AS startOfWeek
FROM xf_thread
GROUP BY yearWeek
ORDER BY yearWeek