1

Please login or register to vote for this query.

(click on this box to dismiss)

100 % - burnout rate for active experts on a site

Q&A for professional and enthusiast programmers

-- 100 % - burnout rate for active experts on a site
-- Where "active expert" is someone who has posted more than one upvoted answer.
-- Per Deer Hunter's definition in http://meta.stackexchange.com/a/261608/
--
-- X-axis is in years, so the point at 2015.0 is the first week of 2015.

Declare @REFDATE Float

Set @REFDATE=cast(##year:int## as float)+cast(##week:int## as float)/52.0

Declare @COMPGAP Float

Set @COMPGAP=##comparisonGap:float?1.0##

Declare @OLDDATE Float

Set @OLDDATE=@REFDATE-@COMPGAP

-- half a year before

set nocount on 

declare @OldExperts table (uid int) 

insert @OldExperts
select UID
from
(
  select
      (datepart(yy, p.creationdate) + (datepart(wk, p.creationdate)-1) / 52.0) as YR_WK
    , u.id as UID
    , count (*) as NumAnswers
  from users u
  inner join posts p on
        p.owneruserid = u.id
    and p.posttypeid = 2
    and p.score > 0
  group by 
     (datepart(yy, p.creationdate) + (datepart(wk, p.creationdate)-1) / 52.0)
    , u.id
) AnsPerUPerWk
where NumAnswers > 1 and abs(YR_WK-@OLDDATE)<0.001;

declare @NewExperts table (uid int) 

insert @NewExperts
select UID
from
(
  select
      (datepart(yy, p.creationdate) + (datepart(wk, p.creationdate)-1) / 52.0) as YR_WK
    , u.id as UID
    , count (*) as NumAnswers
  from users u
  inner join posts p on
        p.owneruserid = u.id
    and p.posttypeid = 2
    and p.score > 0
  group by 
     (datepart(yy, p.creationdate) + (datepart(wk, p.creationdate)-1) / 52.0)
    , u.id
) AnsPerUPerWk
where NumAnswers > 1 and abs(YR_WK-@REFDATE)<0.001;

set nocount off

Declare @numOldExperts Int

Set @numOldExperts = (Select COUNT(*) FROM @OldExperts)

Declare @numRemaining Int

Set @numRemaining = (select COUNT(OldX.UID)
from @OldExperts OldX 
      Left Outer Join
     @NewExperts NewX
     ON OldX.UID=NewX.UID
     where NewX.UID IS NULL)
     
Declare @numNewInflux Int

Set @numNewInflux = (Select COUNT(*)-@numRemaining FROM @NewExperts)


SELECT (CAST(@numRemaining AS FLOAT)/CAST(@numOldExperts AS FLOAT))*100.0 AS PerseveranceRatePercent,
      @numOldExperts AS NumOldExperts,
      @numNewInflux as NewInflux;

fork query

edited jul 30 15 Deer Hunter

created jul 29 15 Isaac Moses