Average rep gain per post per tag

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for programmers

SELECT  t.tagName 
,       COUNT(*) as NrOfQuestions 
,       AVG(RepGain) as AvgRepGain
FROM    tags t
JOIN    posttags pt
ON      pt.tagid = t.id
JOIN    (
        SELECT  pp.id
        ,       SUM(case when v.votetypeid = 2 then 10
                         when v.votetypeid = 2 then -2
                         else null 
                    end) As RepGain
        FROM    posts pp
        JOIN    posts pa
        ON      pa.parentid = pp.id
        JOIN    votes v
        ON      v.postid = pa.id
        GROUP BY
                pp.id
        ) as pv
ON      pv.id = pt.postid
GROUP BY
        t.tagName
HAVING
        COUNT(*) > 1000
ORDER BY
        AVG(RepGain) DESC

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results
:records returned in :time ms:cached