0

Please login or register to vote for this query.

(click on this box to dismiss)

Percentage of users returning in the last 30 days, broken down by "class" of user.

Q&A about the site for enthusiasts and scholars of mythology and folklore

-- Account for data dump restores
DECLARE @Today datetime = (SELECT MAX(LastAccessDate) FROM dbo.Users)

-- Everybody
SELECT 1 AS Id,
       COUNT(*) AS AllUsers,
       SUM(CASE WHEN
         U.LastAccessDate >= (@Today - 30) THEN 1
         ELSE 0
       END) AS ActiveUsers
INTO #AllUsers
FROM dbo.Users U

-- Askers
SELECT 1 AS Id,
       COUNT(*) AS AllUsers,
       SUM(CASE WHEN
         U.LastAccessDate >= (@Today - 30) THEN 1
         ELSE 0
       END) AS ActiveUsers
INTO #Askers
FROM dbo.Users U
WHERE 
  (SELECT COUNT(*) 
   FROM dbo.Posts P 
   WHERE P.OwnerUserId = U.Id AND P.PostTypeId = 1 AND P.Score > 0) > 0

-- Answerers
SELECT 1 AS Id,
       COUNT(*) AS AllUsers,
       SUM(CASE WHEN
         U.LastAccessDate >= (@Today - 30) THEN 1
         ELSE 0
       END) AS ActiveUsers
INTO #Answerers
FROM dbo.Users U
WHERE 
  (SELECT COUNT(*) 
   FROM dbo.Posts P 
   WHERE P.OwnerUserId = U.Id AND P.PostTypeId = 2 AND P.Score > 0) > 0

SELECT 
A.AllUsers AS [All], A.ActiveUsers AS [Active], CAST(A.ActiveUsers AS float) / CAST(A.AllUsers AS float) * 100.0 AS PercentAllActive,
Q.AllUsers AS [Askers], Q.ActiveUsers AS [ActiveAskers], CAST(Q.ActiveUsers AS float) / CAST(Q.AllUsers AS float) * 100.0 AS PercentAskersActive,
An.AllUsers AS [Answerers], An.ActiveUsers AS [ActiveAnswerers], CAST(An.ActiveUsers AS float) / CAST(An.AllUsers AS float) * 100.0 AS PercentAnswerersActive
FROM #AllUsers A
JOIN #Askers Q ON Q.Id = A.Id
JOIN #Answerers An ON An.Id = A.Id

fork query

edited mar 18 12

created mar 18 12 anonymous