0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for programmers

-- Candidates for Mayor

with top10 as (
  select top 10 pt.Tagid,  count(pt.postid) K
  from posttags pt
  group by pt.tagid
  order by count(pt.postid) desc),

activedays 
     AS (SELECT owneruserid, 
                tagid, 
                COUNT(answerdate) activedays 
         FROM   (SELECT DISTINCT a.owneruserid, 
                                 CAST(a.creationdate AS DATE) AS answerdate, 
                                 pt.tagid 
                 FROM   posts a 
                        INNER JOIN posts q 
                          ON a.parentid = q.id 
                             AND a.posttypeid = 2 --Just answers no comments 
                        INNER JOIN posttags pt 
                          ON q.id = pt.postid 
                         INNER JOIN top10 
                          on pt.TagId = top10.TagId
                 WHERE  a.creationdate > '11/22/2011' and a.score > 0) answeractivity 
         GROUP  BY owneruserid, 
                   tagid), 
     postmetrics 
     AS (SELECT a.owneruserid, 
                pt.TagID, 
                SUM(a.score) totalscore, 
                COUNT(a.id)  answercount 
         FROM   posts a 
                INNER JOIN posts q 
                  ON a.parentid = q.id 
                     AND a.posttypeid = 2 
                INNER JOIN posttags pt 
                  ON q.id = pt.postid 
                         INNER JOIN top10 
                          on pt.TagId = top10.TagId
         WHERE  a.creationdate > '11/22/2011' 
         GROUP  BY a.owneruserid, 
                   pt.TagID) ,
ranks as (
SELECT 
       rank() over (partition by T.Id Order by ad.activedays DESC , Cast(pm.totalscore as decimal(10,3)) / cast(pm.answercount as decimal(10,3))  DESC) rank,
       t.TagName, 
       u.displayname, 
       u.reputation, 
       ad.activedays, 
       pm.totalscore, 
       pm.answercount, 
       ( Cast(pm.totalscore as decimal(10,3)) / cast(pm.answercount as decimal(10,3)) ) ratio 
FROM   users u 
       INNER JOIN activedays ad 
         ON u.id = ad.owneruserid 
       INNER JOIN postmetrics pm 
         ON u.id = pm.owneruserid 
            AND ad.tagid = pm.tagid 
       INNER JOIN tags t 
         ON ad.tagid = t.id 
 ) 
SELECT * FROM ranks
WHERE rank <11
ORDER  BY TagName, 
          rank

fork query

created jan 6 12 conradfrix