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 active researchers, academics and students of physics

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

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

select @thismonth = DateAdd(Day, 
                            1 - DatePart(day, 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 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)),
       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
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)

view current version

edited apr 16 14

created apr 16 14 rolfl