useful xenforo queries

I'm going to start playing around with SQL queries, trying to display particular data in a side block or on a new page and here are a few if anyone is interested in adding this to their forums as well.

Posts per day in a forum over period of time:

Code:

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:

Code:

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:

Code:

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

[Source...]

and then one I put together last night, showing users with the most points (MyPoints)

Code:

SELECT `username` , `mypoints_currency`
FROM `user`
ORDER BY `user`.`mypoints_currency` DESC
LIMIT 0 , 10

Has anyone tried showing this data in a side block or on a new node page, any tips for those looking?

Thanks