0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for those who base their lives on Jewish law and tradition and anyone interested in learning more

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

fork query

created may 11 18 A_S00