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 for those who study, teach, research and apply economics and econometrics
-- 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, CreationDate, Convert(Char(7), 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)') ,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)