Top users for a given tag

0

Please login or register to vote for this query.

(click on this box to dismiss)

This query returns the list of most relevant users for a given tag. The relevance of a user is calculated based on the number of answers he/she provided for a given tag.

Latin Language

Q&A for linguists, teachers, and students wanting to discuss the finer points of the Latin language

WITH tagAndUserStats AS (
    SELECT
                t.tagname,
                p.owneruserid       AS userid,
                SUM (p.score)       AS tagScore,
                COUNT (p.id)        AS postCount
    FROM        posts       p
    INNER JOIN  posttags    pt      ON pt.postid = COALESCE (p.parentid, p.id)
    INNER JOIN  tags        t       ON pt.tagid = t.id
    WHERE       p.owneruserid >= 1
    AND         p.posttypeid = 2
    AND         p.CreationDate >= '##startdate##'
    AND         p.CreationDate <= '##enddate##'    
    GROUP BY    t.tagname,
                p.owneruserid
)

SELECT * FROM tagAndUserStats
WHERE tagname LIKE '##tagname##'
ORDER BY postCount DESC, tagScore DESC

Enter Parameters

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