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