Announcement

Collapse
No announcement yet.

Monthly report queries...

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Monthly report queries...

    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.
    Last edited by funkmiester; Sat 13 Nov '04, 10:12pm.

  • #2
    - 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

    Comment


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

      Comment


      • #4
        Looks like a good candidate for a sticky.
        Lats...

        Comment


        • #5
          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.

          Comment


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

            Comment


            • #7
              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.
              Steve Machol, former vBulletin Customer Support Manager (and NOT retired!)
              Change CKEditor Colors to Match Style (for 4.1.4 and above)

              Steve Machol Photography


              Mankind is the only creature smart enough to know its own history, and dumb enough to ignore it.


              Comment


              • #8
                Try this...
                Code:
                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
                Lats...

                Comment


                • #9
                  Originally posted by Jake Bunce View Post

                  - % 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

                  Comment


                  • #10
                    Originally posted by mobosix View Post
                    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.
                    Last edited by funkmiester; Thu 29 Oct '09, 6:35am.

                    Comment


                    • #11
                      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!

                      Comment


                      • #12
                        A co-worker got it figured out for me...

                        Code:
                        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.

                        Comment


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

                          http://www.vbulletin.com/forum/showt...osts-per-month
                          Lats...

                          Comment

                          widgetinstance 262 (Related Topics) skipped due to lack of content & hide_module_if_empty option.
                          Working...
                          X