Year-over-year user retention query

0

Please login or register to vote for this query.

(click on this box to dismiss)

Role-playing Games

Q&A for gamemasters and players of tabletop, paper-and-pencil role-playing games

-- How many users who post in one year are active in the following year?

 -----

 -- User input:  Which two years are we comparing?
 
 DECLARE @FirstYear int = ##FirstYear##
 DECLARE @SecondYear int = ##SecondYear##
 
 -- Grab the user IDs of users who made a post in @FirstYear
 
 DECLARE @FirstYearPosters table(Poster int);
 
 INSERT INTO @FirstYearPosters
   SELECT DISTINCT OwnerUserId
   FROM Posts
   WHERE year(CreationDate) = @FirstYear;
 
 -- Grab the user IDs of users who made a post in @SecondYear
  
 DECLARE @SecondYearPosters table(Poster int);
 
 INSERT INTO @SecondYearPosters
   SELECT DISTINCT OwnerUserId
   FROM Posts
   WHERE year(CreationDate) = @SecondYear;
   
 -- Grab the user IDs of users who posted in both years ("returning users")
 
 DECLARE @ReturningPosters table(Poster int);
 
 INSERT INTO @ReturningPosters
   SELECT Poster FROM @FirstYearPosters
   intersect
   SELECT Poster FROM @SecondYearPosters;
   
 -- Count the number of first year posters
 
 DECLARE @FirstYearPostersCount float
 
 SELECT @FirstYearPostersCount = count(Poster)
 FROM @FirstYearPosters;
 
 -- Count the number of returning posters
 
 DECLARE @ReturningPostersCount float
 
 SELECT @ReturningPostersCount = count(Poster)
 FROM @ReturningPosters;
 
 -- Compute the proportion of first year posters who are returning posters
 
 Select @ReturningPostersCount / @FirstYearPostersCount

Enter Parameters

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