Monthly report queries...

[funkmiester]

Having recently converted to vB, one thing I now need to do myself is to create a monthly report with various stats.

Can someone help me with SQL queries which would produce any or all of the following information:

- New Members in the past 30 days
- Total Hours usage (members time on the forums) for the past 30 days
- Top 5 Posters for the past 30 days
- Top 5 Thread starters over the past 30 days
- % of Members who posted in the past 30 days
- Total Page Views for the past 30 days
- Average Page Views for the past 30 days
- Total new threads created over the past 30 days
- 5 Most Popular threads in the last 30 days

I'm a complete novice with SQL, so exact syntax would be appreciated.

Many thanks.

[Jake Bunce]

- New Members in the past 30 days
SELECT COUNT(*) AS count
FROM user
WHERE joindate > UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 MONTH))

- Total Hours usage (members time on the forums) for the past 30 days
Usage is not logged, only lastvisit and lastactivity times.

- Top 5 Posters for the past 30 days
SELECT COUNT(postid) AS count, user.username
FROM post
LEFT JOIN user ON (user.userid = post.userid)
WHERE dateline > UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 MONTH))
GROUP BY post.username
ORDER BY count
DESC

- Top 5 Thread starters over the past 30 days
SELECT COUNT(threadid) AS count, user.username
FROM thread
LEFT JOIN user ON (user.userid = thread.postuserid)
WHERE dateline > UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 MONTH))
GROUP BY thread.postusername
ORDER BY count
DESC

- % of Members who posted in the past 30 days
This will get the number who have posted:
SELECT COUNT(*) AS count
FROM user
WHERE lastpost > UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 MONTH))

And this will get the total number:
SELECT COUNT(*) AS count
FROM user
WHERE lastpost > UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 MONTH))

You can divide them into eachother to get a percent.

- Total Page Views for the past 30 days
Views do not have an associated dateline.

- Average Page Views for the past 30 days
Views do not have an associated dateline.

- Total new threads created over the past 30 days
SELECT COUNT(*) AS count
FROM thread
WHERE dateline > UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 MONTH))

- 5 Most Popular threads in the last 30 days
By postcount:
SELECT COUNT(postid) AS count, threadid
FROM post
WHERE dateline > UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 MONTH))
GROUP BY threadid
ORDER BY count
DESC

[funkmiester]

Thank you so much Jake, I appreciate the quick response.

[Lats]

Looks like a good candidate for a sticky.

[Jake Bunce]

This is the first time I have seen anyone ask for these specific queries. Usually the stickies and tips are for common questions.

Incidentally, on a few of the queries you wanted a limit of 5, but the queries didn't want to work when I added a row limit. I don't know why.

[doli]

Do you know how to select top posting users in certain forum ?

[Steve Machol]

Sorry, there is no function to do this. This requires modifying the code. We cannot officially support code modifications or forums running modified code, however you can try searching or asking for help with this over at www.vbulletin.org.

[Lats]

Try this...

SELECT 
    post.username, 
    count( post.postid ) AS postcount
FROM 
    post
LEFT JOIN 
    thread ON thread.threadid = post.threadid
WHERE 
    thread.forumid IN (2,3,4)
GROUP BY 
    post.username
ORDER BY 
    postcount DESC 
LIMIT 
    5

Change the (2,3,4) to your forumid's

[mobosix]

- % of Members who posted in the past 30 days
This will get the number who have posted:
SELECT COUNT(*) AS count
FROM user
WHERE lastpost > UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 MONTH))

And this will get the total number:
SELECT COUNT(*) AS count
FROM user
WHERE lastpost > UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 MONTH))

You can divide them into eachother to get a percent.

This appears to be a typo as they are the same exact queries. Is it possible to get the correct ones to find out the percentage of all members who have posted this month?

Thanks,

Keith

[funkmiester]

Is it possible to get the correct ones to find out the percentage of all members who have posted this month?

This will get the number of Members who have posted in the past month:

SELECT COUNT(*) AS count
FROM user
WHERE lastpost > UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 MONTH))

...and this will get the total number of Members:

SELECT COUNT(*) AS count
FROM user

You can then divide them into eachother to get the percentage.

[mobosix]

Thanks Funkmiester!

If I wanted to find the top poster and top thread starter for a specific date range, how would I go about doing that? I would like to go back and give out awards to my top members for each month my forums has been active. Thanks again for any help!

[mobosix]

A co-worker got it figured out for me...

SELECT COUNT(postid) AS count, user.username
FROM post
LEFT JOIN user ON (user.userid = post.userid)
WHERE dateline > UNIX_TIMESTAMP('2009-10-01') AND dateline < UNIX_TIMESTAMP('2009-10-31')
GROUP BY post.username
ORDER BY count DESC

Just change out the date ranges and remember to use either 30 or 31 days for the correct months.

[Lats]

You may want to check this thread to fine tune that date selection...

http://www.vbulletin.com/forum/showthread.php?111997-posts-per-month