Return rates

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.

Audio-Video Production Meta

Q&A about the site for engineers, producers, editors, and enthusiasts spanning the fields of video, and media creation

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

Enter Parameters

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