SQL queries to find out your forum daily stats

[Andy.N]

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

 

[Andy.N]

I like queries.

Or so I heard
How do I query the number of post/like/thread per day for a specific forum over a specific period of time?
Thanks

 

[Jake Bunce]

Or so I heard
How do I query the number of post/like/thread per day for a specific forum over a specific period of time?
Thanks

Off hand I can't think of any way to do that in a single query without some SQL programming (or a PHP script). Here are the individual queries though.

Posts per day in a forum over period of time:

SELECT COUNT(*) AS posts, FROM_UNIXTIME(post.post_date, '%Y-%m-%d') AS date
FROM xf_post AS post
LEFT JOIN xf_thread AS thread ON (thread.thread_id = post.thread_id)
WHERE thread.node_id = 10
AND post.post_date BETWEEN UNIX_TIMESTAMP('2001-01-15 00:00:00') AND UNIX_TIMESTAMP('2001-02-15 00:00:00')
GROUP BY date
ORDER BY date

Likes per day in a forum over period of time:

SELECT COUNT(*) AS likes, FROM_UNIXTIME(liked_content.like_date, '%Y-%m-%d') AS date
FROM xf_liked_content AS liked_content
LEFT JOIN xf_post AS post ON (post.post_id = liked_content.content_id)
LEFT JOIN xf_thread AS thread ON (thread.thread_id = post.thread_id)
WHERE liked_content.content_type = 'post'
AND thread.node_id = 10
AND liked_content.like_date BETWEEN UNIX_TIMESTAMP('2001-01-15 00:00:00') AND UNIX_TIMESTAMP('2001-02-15 00:00:00')
GROUP BY date
ORDER BY date

Threads per day in a forum over period of time:

SELECT COUNT(*) AS threads, FROM_UNIXTIME(thread.post_date, '%Y-%m-%d') AS date
FROM xf_thread AS thread
WHERE thread.node_id = 10
AND thread.post_date BETWEEN UNIX_TIMESTAMP('2001-01-15 00:00:00') AND UNIX_TIMESTAMP('2001-02-15 00:00:00')
GROUP BY date
ORDER BY date

 

[Andy.N]

Thank you Jake. I know you will deliver.
These should cover the so-called activities in a forum, right?

 

[Jake Bunce]

These should cover the so-called activities in a forum, right?

Yep. Posts, threads, and likes.

 

[Andy.N]

Yep. Posts, threads, and likes.

Jake,
Can the query be modified to display post/per username over that period of time?

 

[Jake Bunce]

Jake,
Can the query be modified to display post/per username over that period of time?

SELECT user.username, COUNT(*) AS posts
FROM xf_post AS post
LEFT JOIN xf_user AS user ON (user.user_id = post.user_id)
AND post.post_date BETWEEN UNIX_TIMESTAMP('2010-01-15 00:00:00') AND UNIX_TIMESTAMP('2010-02-15 00:00:00')
GROUP BY user.user_id
ORDER BY user.user_id

 

[Andy.N]

SELECT user.username, COUNT(*) AS posts
FROM xf_post AS post
LEFT JOIN xf_user AS user ON (user.user_id = post.user_id)
AND post.post_date BETWEEN UNIX_TIMESTAMP('2010-01-15 00:00:00') AND UNIX_TIMESTAMP('2010-02-15 00:00:00')
GROUP BY user.user_id
ORDER BY user.user_id

Jake,
Thanks. What I mean is posts in that specific forum just like before, but instead of display total posts in that forum, now segment by user.

 

[Jake Bunce]

Jake,
Thanks. What I mean is posts in that specific forum just like before, but instead of display total posts in that forum, now segment by user.

SELECT user.username, COUNT(*) AS posts
FROM xf_post AS post
LEFT JOIN xf_user AS user ON (user.user_id = post.user_id)
LEFT JOIN xf_thread AS thread ON (thread.thread_id = post.thread_id)
WHERE thread.node_id = 10
AND post.post_date BETWEEN UNIX_TIMESTAMP('2010-01-15 00:00:00') AND UNIX_TIMESTAMP('2010-02-15 00:00:00')
GROUP BY user.user_id
ORDER BY user.user_id

 

[Andy.N]

Perfect, Mr. Query

 

[Andy.N]

SELECT user.username, COUNT(*) AS posts
FROM xf_post AS post
LEFT JOIN xf_user AS user ON (user.user_id = post.user_id)
LEFT JOIN xf_thread AS thread ON (thread.thread_id = post.thread_id)
WHERE thread.node_id = 10
AND post.post_date BETWEEN UNIX_TIMESTAMP('2010-01-15 00:00:00') AND UNIX_TIMESTAMP('2010-02-15 00:00:00')
GROUP BY user.user_id
ORDER BY user.user_id

Jake,
Can I hav a query like this showing the number of likes each members have from posts in forum ID = 10 over a period of time. Thank you.

The result should be something like this
Username Likes
Jakes 2340
Ben 34
Tommy 452

 

[Andy.N]

This is what I got, please let me know if I got it right

SELECT user.username, COUNT(*) AS likes
FROM xf_liked_content AS liked_content
LEFT JOIN xf_post AS post ON (post.post_id = liked_content.content_id)
LEFT JOIN xf_thread AS thread ON (thread.thread_id = post.thread_id)
LEFT JOIN xf_user AS user ON (user.user_id = post.user_id)
WHERE liked_content.content_type = 'post'
AND thread.node_id = 9
AND liked_content.like_date BETWEEN UNIX_TIMESTAMP('2012-08-15 00:00:00') AND UNIX_TIMESTAMP('2012-12-31 00:00:00')
GROUP BY user.username
ORDER BY likes desc

 

[Jake Bunce]

Looks correct to me.

 

[Andy.N]

Looks correct to me.

Jake,
How would I arrange the query in post #31 so that it's sorted by date, grouped by username and post count.

In sort, I like to show the usernames and their post counts for each day from DDMMYY to DDMMYY

Thanks

 

[Jake Bunce]

Jake,
How would I arrange the query in post #31 so that it's sorted by date, grouped by username and post count.

In sort, I like to show the usernames and their post counts for each day from DDMMYY to DDMMYY

Thanks

Try this:

SELECT FROM_UNIXTIME(post_date, '%Y-%m-%d') AS date, user.username, COUNT(*) AS postCount
FROM xf_post AS post
INNER JOIN xf_user AS user ON (user.user_id = post.user_id)
WHERE 1
AND post.post_date BETWEEN UNIX_TIMESTAMP('2007-01-15 00:00:00') AND UNIX_TIMESTAMP('2013-02-15 00:00:00')
GROUP BY date, post.user_id
ORDER BY date, postCount
DESC;

 

[Andy.N]

Try this:

SELECT FROM_UNIXTIME(post_date, '%Y-%m-%d') AS date, user.username, COUNT(*) AS postCount
FROM xf_post AS post
INNER JOIN xf_user AS user ON (user.user_id = post.user_id)
WHERE 1
AND post.post_date BETWEEN UNIX_TIMESTAMP('2007-01-15 00:00:00') AND UNIX_TIMESTAMP('2013-02-15 00:00:00')
GROUP BY date, post.user_id
ORDER BY date, postCount
DESC;

Thanks Jake. I'm looking to filter posts in a specific node id only.

 

[Jake Bunce]

Thanks Jake. I'm looking to filter posts in a specific node id only.

SELECT FROM_UNIXTIME(post_date, '%Y-%m-%d') AS date, user.username, COUNT(*) AS postCount
FROM xf_post AS post
INNER JOIN xf_user AS user ON (user.user_id = post.user_id)
LEFT JOIN xf_thread AS thread ON (thread.thread_id = post.thread_id)
WHERE thread.node_id = 9
AND post.post_date BETWEEN UNIX_TIMESTAMP('2007-01-15 00:00:00') AND UNIX_TIMESTAMP('2013-02-15 00:00:00')
GROUP BY date, post.user_id
ORDER BY date, postCount
DESC;

 

[Andy.N]

SELECT FROM_UNIXTIME(post_date, '%Y-%m-%d') AS date, user.username, COUNT(*) AS postCount
FROM xf_post AS post
INNER JOIN xf_user AS user ON (user.user_id = post.user_id)
LEFT JOIN xf_thread AS thread ON (thread.thread_id = post.thread_id)
WHERE thread.node_id = 9
AND post.post_date BETWEEN UNIX_TIMESTAMP('2007-01-15 00:00:00') AND UNIX_TIMESTAMP('2013-02-15 00:00:00')
GROUP BY date, post.user_id
ORDER BY date, postCount
DESC;

Column 'post_date' in field list is ambiguous

 

[Jake Bunce]

Column 'post_date' in field list is ambiguous

Make not ambiguous:

SELECT FROM_UNIXTIME(post.post_date, '%Y-%m-%d') AS date, user.username, COUNT(*) AS postCount
FROM xf_post AS post
INNER JOIN xf_user AS user ON (user.user_id = post.user_id)
LEFT JOIN xf_thread AS thread ON (thread.thread_id = post.thread_id)
WHERE thread.node_id = 9
AND post.post_date BETWEEN UNIX_TIMESTAMP('2007-01-15 00:00:00') AND UNIX_TIMESTAMP('2013-02-15 00:00:00')
GROUP BY date, post.user_id
ORDER BY date, postCount
DESC;