## Active experts (100-burnout rate) after a year (by default)

1

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

### Enter Parameters

Options:
-
Switch to meta site
Hold tight while we fetch your results
:records returned in :time ms:cached