## User Retention

3

(click on this box to dismiss)

This query tries to profile the activity/behaviour of new users on the site - Group the users in to monthly batches... all the users who joined in month X are grouped together. - Count the number of users in that month's intake. - Count the number of questions/answers that are asked by that month's intake for every week after they joined. - convert it to a percentage Reading the graph is as follows: Any data point represents the ratio of the number of posts in that week as a proportion of the number of users in the intake month for that curve. If a value at a given point is 10, and there were 1000 users that joined in that month, then it means that those users posted 100 questions/answers (100 / 1000 expressed as a percent).

Q&A about the site for gardeners and landscapers

``````--  This query tries to profile the activity/behaviour of new users on
--  the site
--
--  * Group the users in to monthly batches... all the users who joined
--    in month X are grouped together.
--  * Count the number of users in that month's intake.
--  * Count the number of questions/answers that are asked by that month's
--    intake for every week after they joined.
--  * convert it to a percentage
--
--  Reading the graph is as follows:
--
--  Any data point represents the ratio of the number of posts in that
--  week as a proportion of the number of users in the intake month for
--  that curve. If a value at a given point is 10, and there were 1000
--  users that joined in that month, then it means that those users posted
--  100 questions/answers (100 / 1000 expressed as a percent).

declare @months as int = ##Months:int?18##;

declare @thismonth as Date;
declare @pivot as NVarchar(Max);
declare @query as NVarchar(Max);
declare @thisweek as Date;

1 - DatePart(day, CURRENT_TIMESTAMP),
Convert(Date, CURRENT_TIMESTAMP))
;
1 - DatePart(dw, CURRENT_TIMESTAMP),
Convert(Date, CURRENT_TIMESTAMP))
;
declare @epoch as Date = DateAdd(Month, - @months, @thismonth);

print Convert(nvarchar, @epoch) + ' to ' + Convert(nvarchar, @thismonth);

create table #tusers (
Id int,
CreateDate DateTime,
CreateMonth Char(7))

;

insert into #tusers
select Id,
CreationDate,
Convert(Char(7),
case when CreationDate < @epoch
then @epoch
1 - DatePart(day, CreationDate),
Convert(Date, CreationDate))
end, 102)

from Users
;

create table #tmonths (
CreateMonth Char(7),
UserCount float)
;

insert into #tmonths
select CreateMonth, Count (*)
from #tusers
where CreateMonth <> Convert(Char(7), @thismonth, 102)
group by CreateMonth
;
SET @pivot = STUFF((SELECT ',' + QUOTENAME(CreateMonth)
FROM #tmonths
order by CreateMonth
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
;
print @pivot
;

create table #tactivity (
CreateMonth Char(7),
Week Date,
PostCount float)
;

insert into #tactivity
select tu.CreateMonth,
DateAdd(day, 7 - DatePart(dw, CreationDate), Convert(Date, CreationDate)),
100.0 * count (p.Id) / tm.UserCount
from Posts p, #tusers tu, #tmonths tm
where p.OwnerUserId = tu.Id
and p.PostTypeId in (1, 2)
and tu.CreateMonth = tm.CreateMonth
and p.CreationDate >= @epoch
and p.CreationDate >= tu.CreateDate
and p.CommunityOwnedDate is null
and p.ClosedDate is null
and p.CreationDate <= @thisweek
group by tu.CreateMonth, tm.UserCount, DateAdd(day, 7 - DatePart(dw, CreationDate), Convert(Date, CreationDate))

set @query = 'select * from #tactivity pivot (sum(PostCount) for CreateMonth in (' + @pivot + ')) as Pivoted order by Week ASC'

execute(@query)``````

### Enter Parameters

Options:
-
Switch sites:
Hold tight while we fetch your results
:records returned in :time ms:cached