1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.
  2. This forum has been archived. New threads and replies may not be made. All add-ons/resources that are active should be migrated to the Resource Manager. See this thread for more information.

new users/posts/threads reports (queries)

Discussion in 'Tips and Guides [Archive]' started by Jake Bunce, May 3, 2011.

  1. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    These all generate results like this:

    Screen shot 2010-12-27 at 9.17.08 AM.png

    By Day:

    For registrations per day:

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

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

    Code:
    SELECT COUNT(*) AS threads, FROM_UNIXTIME(post_date, '%Y-%m-%d') AS date
    FROM xf_thread
    GROUP BY date
    ORDER BY date
    
     
    EQnoble, Eagle and Brogan like this.
  2. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    By Month:

    For registrations per month:

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

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

    Code:
    SELECT COUNT(*) AS threads, FROM_UNIXTIME(post_date, '%Y-%m') AS date
    FROM xf_thread
    GROUP BY date
    ORDER BY date
    
     
    EQnoble, Eagle and Brogan like this.
  3. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

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

    Screen shot 2011-05-03 at 9.45.48 AM.png

    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:

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

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

    Code:
    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
    
     
    EQnoble, Eagle, Brogan and 1 other person like this.

Share This Page