0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

-- Top users by Tag

SELECT scores.owneruserid, 
       scores.score, 
       scores.tagid, 
       t.tagname, 
       u.displayname 
FROM   (SELECT MAX(score) max_score, 
               tagid 
        FROM   (SELECT SUM(p.score) score, 
                       p.owneruserid, 
                       pt.tagid 
                FROM   posts p 
                       INNER JOIN posttags pt 
                         ON p.id = pt.postid 
                GROUP  BY p.owneruserid, 
                          pt.tagid) scores 
        GROUP  BY tagid) maxscores 
       INNER JOIN (SELECT SUM(p.score) score, 
                          p.owneruserid, 
                          pt.tagid 
                   FROM   posts p 
                          INNER JOIN posttags pt 
                            ON p.id = pt.postid 
                   GROUP  BY p.owneruserid, 
                             pt.tagid) scores 
         ON maxscores.max_score = scores.score 
            AND maxscores.tagid = scores.tagid 
       INNER JOIN tags t 
         ON scores.tagid = t.id 
       INNER JOIN users u 
ON scores.owneruserid = u.id 
WHERE t.tagname LIKE 'c#%'
ORDER BY 
   score DESC ,
   t.tagname

fork query

edited jun 12 17 maniero

created jun 13 11 conradfrix