Q&A about the site for researchers and developers who explore the principles of a system through analysis of its structure, function, and operation
DECLARE @FirstContributeStart varchar(10); DECLARE @FirstContributeEnd varchar(10); DECLARE @RevisitRangeStart varchar(10); DECLARE @RevisitRangeEnd varchar(10); DECLARE @N1 int; DECLARE @N2 int; DECLARE @PostType int = 2; /* Answers */ CREATE TABLE #TT ( uid int ); SET @FirstContributeStart = '2019-08-01'; SET @FirstContributeEnd = '2019-08-31'; SET @RevisitRangeStart = '2019-12-01'; SET @RevisitRangeEnd = '2019-12-31'; INSERT INTO #TT (uid) SELECT p.OwnerUserId FROM Posts AS p WHERE p.PostTypeId = @PostType AND CAST(p.CreationDate AS Date) >= CAST (@FirstContributeStart as DATE) AND CAST(p.CreationDate AS Date) <= CAST (@FirstContributeEnd as DATE) AND NOT EXISTS ( SELECT p2.OwnerUserId AS [uid] FROM Posts AS p2 WHERE p.OwnerUserId=p2.OwnerUserId AND p2.PostTypeId = @PostType AND CAST(p2.CreationDate AS Date) < CAST (@FirstContributeStart as DATE) ) GROUP BY p.OwnerUserId SET @N1 = (SELECT COUNT(t1.uid) FROM #TT AS [t1]); SET @N2 = ( SELECT COUNT(t2.uid) FROM ( SELECT p.OwnerUserId AS [uid] FROM Posts AS p WHERE p.PostTypeId = 1 AND CAST(p.CreationDate AS Date) >= CAST (@RevisitRangeStart as DATE) AND CAST(p.CreationDate AS Date) <= CAST (@RevisitRangeEnd as DATE) AND p.OwnerUserId IN (SELECT uid FROM #TT) GROUP BY p.OwnerUserId ) AS [t2] ); PRINT '---------'; PRINT 'Total # of user contributed first time in between '; PRINT @FirstContributeStart; PRINT 'to'; PRINT @FirstContributeEnd; PRINT 'is :'; PRINT @N1; PRINT '---------'; PRINT @N2; PRINT 'of these users also contributed in between'; PRINT @RevisitRangeStart; PRINT 'to'; PRINT @RevisitRangeEnd; PRINT '---------'; PRINT 'Return Rate is :'; PRINT (@N2 * 100 ) / @N1 ; PRINT 'percent'; PRINT '---------';