0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

-- How many recently active users come back to the site after their initial account creation?

 -----

 -- User input:  How many days between creation and last activity counts as "retained"?

DECLARE @RetentionDays int = ##RetentionDays##

 -- User input:  How far back do we want to search (in days)?
 
DECLARE @ReportingPeriodDays int = ##ReportingPeriodDays##
 
 -- Get the count of users who were active N or more days after creation, and were active in the reporting period
 -- (N defined in RetentionDays)
 -- (reporting period defined in ReportingPeriodDays)

DECLARE @Returning_Users float

SELECT @Returning_Users = Count(Id)
FROM Users
WHERE DATEDIFF(day,CreationDate,LastAccessDate) > @RetentionDays AND DATEDIFF(day,LastAccessDate,GetDate()) <= @ReportingPeriodDays;

 -- Get the count of users who were active in the reporting period
 -- (reporting period defined in ReportingPeriodDays)

DECLARE @Total_Users float

SELECT @Total_Users = Count(Id)
From Users
WHERE DATEDIFF(day,LastAccessDate,GetDate()) <= @ReportingPeriodDays;

 -- Compute the proportion of total users who were "retained"

SELECT @Returning_Users / @Total_Users

fork query

edited may 11 18

created may 2 18 A_S00