3

Please login or register to vote for this query.

(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 for peer programmer code reviews

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

fork query

edited apr 16 14

created apr 16 14 rolfl