Generalist Badge, Progress toward -- with improved sort

0

Please login or register to vote for this query.

(click on this box to dismiss)

Of the top 40 tags, how many do I have a score of 15 in? Need 15 score, or better, in at least 20 of the top 40 tags, to get the badge.

Physics

Q&A for active researchers, academics and students of physics

DECLARE @scorePerTop40Tags TABLE (
    tagname         CHAR(128),
    score           INT,
    tagpopularity   INT,
    rank            INT IDENTITY(1, 1)
);

WITH tags40 AS (
    SELECT TOP  40
                tagid,
                COUNT(*) AS postCount
    FROM        posttags
    GROUP BY    tagid
    ORDER BY    COUNT(*) DESC
),
answers AS (
    SELECT      score,
                parentid
    FROM        posts
    WHERE       communityowneddate IS NULL  -- Not community wiki
    AND         posttypeid = 2              -- Answers
    AND         owneruserid = ##userid##    -- By the specified user
)
INSERT @scorePerTop40Tags (tagname, score, tagpopularity)
SELECT      t.tagname,
            SUM(a.score) AS score,
            t40.postcount
FROM        answers a
INNER JOIN  posttags    pt  ON pt.postid = a.parentid
RIGHT JOIN  tags40      t40 ON t40.tagid = pt.tagid
INNER JOIN  tags        t   ON t40.tagid = t.id
GROUP BY    t.tagname,
            t40.postcount
ORDER BY    t40.postcount DESC


SELECT      *,
            CASE WHEN score >= 15  THEN  ' good'  ELSE ' improve'
            END AS [Status]
FROM        @scorePerTop40Tags
ORDER BY    score DESC,
            tagpopularity DESC,
            tagname

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results