SQL queries to find out your forum daily stats

Andy.N

Well-known member
In the absence of a builtin XF stats system, I'd like to monitor the activities on my site.
Number of new registration, posts, thread per day.

There should be some queries that can be used to get these info out. Then I can do a pre/post Xenforo to see if the new platform improves activity level.
Thanks
 
Number of new registration, posts, thread per day.

I like queries.

For registrations per day (from this post):

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

These all generate results like this:

Screen shot 2010-12-27 at 9.17.08 AM.webp
 
I started working on a mod that basically would show similar stats as a few of Paul's vB mods do (Today's visiting members, today's registrations, etc) a few weeks ago, but have to admit I've gotten sidetracked with other projects. I have the AdminCP part ready, and mostly need to tie together the templates and queries with the settings.

I'll see if I can somehow quickly whip something together for this. It might not be complete, or perfect, but it might serve as a start, so I can finetune it later on.
 
Now, how we aggregate them by week/month?

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
 
Now, how we aggregate them by week/month?

By week:

For registrations per week:

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

For posts per week:

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

For threads per week:

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

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-03-15 at 10.02.19 AM.webp

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.
 
Note: posted this for reference :)

DATE_FORMAT and TIME_FORMAT Output Formats for FROM_UNIXTIME function

format Parameter Output Format
%r 12-hour time (hh:mm:ss (AM|PM))
%T 24-hour time (hh:mm:ss)
%Y Numeric year, 4 digits
%y Numeric year, 2 digits
%m Month with leading 0 (01, 02-12)
%c Month without leading 0 (1, 2-12)
%M Month name (January, February, and so on)
%b Month name, abbreviated (Jan, Feb, and so on)
%D Day of the month with an English suffix (1st, 2nd, and so on)
%d Day of the month with leading 0 (00, 01, 02-31)
%e Day of the month without leading 0 (0, 1, 2-31)
%W Weekday name (Sunday, Monday, and so on)
%a Weekday name, abbreviated (Sun, Mon, and so on)
%H Hour (00, 01-23)
%k Hour (0, 1-23)
%h Hour (01, 02-12)
%I Hour (01, 02-12)
%l Hour (1, 2-12)
%i Minutes (00, 01-59)
%S Seconds (00, 01-59)
%s Seconds (00, 01-59)
%P AM or PM
%U Week number in the year,in which Sunday is the first day of the week
$u Week number in the year,in which Monday is the first day of the week
%X & %V Year and week number, respectively,in which Sunday is the first day of the week
%x & %v Year and week number, respectively,in which Monday is the first day of the week
%j Day of year with leading 0's (001, 002-366)
%w Weekday number (0=Sunday, 1=Monday, and so on)
%% Literal %
 
Oh, my mistake.

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
 
You can add an offset where the date fields appear in the query. For example, for registrations per day:

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

That would subtract 8 hours.
 
Top Bottom