Return rates

1

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.

Arqade

Q&A for passionate videogamers on all platforms

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

-- Recent Askers (asked a question two months ago, still visited within the last month)
SELECT 1 AS Id,
       COUNT(*) AS AllUsers,
       SUM(CASE WHEN
         U.LastAccessDate >= (@Today - 30) THEN 1
         ELSE 0
       END) AS ActiveUsers
INTO #RecentAskers
FROM dbo.Users U
WHERE 
  (SELECT COUNT(*) 
   FROM dbo.Posts P 
   WHERE P.OwnerUserId = U.Id AND P.PostTypeId = 1 AND P.Score > 0 AND P.CreationDate BETWEEN (@Today - 60) AND (@Today - 30)) > 0

-- Recent Answerers (answered a question two months ago, still visited within the last month)
SELECT 1 AS Id,
       COUNT(*) AS AllUsers,
       SUM(CASE WHEN
         U.LastAccessDate >= (@Today - 30) THEN 1
         ELSE 0
       END) AS ActiveUsers
INTO #RecentAnswerers
FROM dbo.Users U
WHERE 
  (SELECT COUNT(*) 
   FROM dbo.Posts P 
   WHERE P.OwnerUserId = U.Id AND P.PostTypeId = 2 AND P.Score > 0 AND P.CreationDate BETWEEN (@Today - 60) AND (@Today - 30)) > 0

SELECT 
--A.AllUsers AS [All], A.ActiveUsers AS [Active], 
--Q.AllUsers AS [Askers], Q.ActiveUsers AS [ActiveAskers], 
--RQ.AllUsers AS [RecentAskers], RQ.ActiveUsers AS [RecentActiveAskers], 
--An.AllUsers AS [Answerers], An.ActiveUsers AS [ActiveAnswerers], 
--RAn.AllUsers AS [RecentAnswerers], RAn.ActiveUsers AS [RecentActiveAnswerers], 
CAST(A.ActiveUsers AS float) / CAST(A.AllUsers AS float) * 100.0 AS PercentAllActive,
CAST(Q.ActiveUsers AS float) / CAST(Q.AllUsers AS float) * 100.0 AS PercentAskersActive,
CAST(RQ.ActiveUsers AS float) / CAST(RQ.AllUsers AS float) * 100.0 AS PercentRecentAskersActive,
CAST(An.ActiveUsers AS float) / CAST(An.AllUsers AS float) * 100.0 AS PercentAnswerersActive,
CAST(RAn.ActiveUsers AS float) / CAST(RAn.AllUsers AS float) * 100.0 AS PercentRecentAnswerersActive
FROM #AllUsers A
JOIN #Askers Q ON Q.Id = A.Id
JOIN #Answerers An ON An.Id = A.Id
JOIN #RecentAskers RQ ON RQ.Id = A.Id
JOIN #RecentAnswerers RAn ON RAn.Id = A.Id

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results
:records returned in :time ms:cached