Q&A for professional and enthusiast programmers
-- http://yangjiera.github.io/home/works/umap2014_experts.pdf declare @tag_debate float = ( select avg(1.0*AnswerCount) from Posts q join PostTags on q.Id = PostId join Tags t on TagId = t.Id where TagName = '##tag##' ); with answers as ( select a.OwnerUserId UserId, 1.0/rank() over (partition by q.Id order by a.Score desc) utility, count(a.Id) over (partition by q.Id) debatableness, 1.0/rank() over (partition by q.Id order by a.Score desc) * count(a.Id) over (partition by q.Id)/@tag_debate EC from Posts q join Posts a on q.Id = a.ParentId join PostTags on q.Id = PostId join Tags t on TagId = t.Id where TagName = '##tag##' --and q.Id = 9929585 ) select UserId as [User Link], sum(EC)/count(*) MEC, avg(utility) AU, avg(debatableness) D, @tag_debate D_avg_t, count(*) Q_u_t from answers group by UserId having sum(EC)/count(*) > 1 order by sum(EC)/count(*) desc