(old, badges) Active users rate

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

/* Active users / total users */
/*SELECT total_users.Month,
       'Active users rate' = active_users.[Amount of active users] / CAST(total_users.[Amount of users] AS FLOAT)
FROM (
      SELECT Month, 'Amount of users' = sum([Amount of new users]) over (order by Month rows unbounded preceding)
      FROM (
            SELECT Month = DATEADD(MONTH, DATEDIFF(MONTH, 0, CreationDate), 0), 'Amount of new users' = count(Id)
            FROM Users
            GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, CreationDate), 0)
           ) as new_users
     ) as total_users
INNER JOIN
     (
      SELECT Month = DATEADD(MONTH, DATEDIFF(MONTH, 0, Date), 0), 
             'Amount of active users' = count(distinct UserId)
      FROM Badges
      INNER JOIN Users
      ON Badges.UserId = Users.Id
      GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, Date), 0)
     ) as active_users
ON total_users.Month = active_users.Month
ORDER BY total_users.Month
*/

/* Active users / new users */
SELECT new_users.Month,
       'Active users rate' = active_users.[Amount of active users] / CAST(new_users.[Amount of new users] AS FLOAT)
FROM (
      SELECT Month = DATEADD(MONTH, DATEDIFF(MONTH, 0, CreationDate), 0), 'Amount of new users' = count(Id)
      FROM Users
      GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, CreationDate), 0)
     ) as new_users
INNER JOIN
     (
      SELECT Month = DATEADD(MONTH, DATEDIFF(MONTH, 0, Date), 0), 
             'Amount of active users' = count(distinct UserId)
      FROM Badges
      INNER JOIN Users
      ON Badges.UserId = Users.Id
      GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, Date), 0)
     ) as active_users
ON new_users.Month = active_users.Month
ORDER BY new_users.Month

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results