new users/posts/threads reports (queries)

[Jake Bunce]

These all generate results like this:



By Day:

For registrations per day:

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:

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:

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

 

[Jake Bunce]

By Month:

For registrations per month:

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:

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:

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

 

[Jake Bunce]

These 'per week' queries return slightly different results than the others. You will see the week number of the year, like this:



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:

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:

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:

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