Percentage of users returning in the last 30 days, broken down by "class" of user.
Q&A for scientific skepticism
-- 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