User Answer Retention


Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

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

select @thismonth = DateAdd(Day, 
                            1 - DatePart(day, CURRENT_TIMESTAMP),
                            Convert(Date, CURRENT_TIMESTAMP))
select @thisweek = DateAdd(Day, 
                            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,
                case when CreationDate < @epoch
                then @epoch
                else DateAdd(Day, 
                             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)') 
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 = 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
  and p.tags like '%<font-identification>%'
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'


Enter Parameters

Switch sites:
loading Hold tight while we fetch your results