Metrics - User - Accumulated PU Ratio by Quarter

2

Please login or register to vote for this query.

(click on this box to dismiss)

Emacs

Q&A for those using, extending or developing Emacs

-- Accumulated PU ratio by quarter.
-- Power Users are those who have written at least @minPost answer 
--   that have at least @minVote scores.

-- declare @minVote numeric = 5;
-- declare @minPost numeric = 5;

declare @minVote numeric = ##minvote:int##;
declare @minPost numeric = ##minpost:int##;

select r as Registered,
       count(1) as Users,
       sum(s) as Writers,
       round(sum(s) * 100.0 / count(1), 2) as [Writers %]
from (
  select
    u.Id,
    (case
       when sum(case
               when p.PostTypeId = 2 and (p.Score >= @minVote) then 1
               else 0
             end) >= @minPost then 1
       else 0
     end) s,
     (datepart(year, u.CreationDate) * 100
       + datepart(q, u.CreationDate)) as r
 from Users u
 left join Posts p on p.OwnerUserId = u.Id
 group by u.Id, u.CreationDate
) as X
group by r
order by r

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results