Generalist Badge, Progress toward -- with improved sort

0

Please login or register to vote for this query.

(click on this box to dismiss)

So I know what needs to be done, with tag popularity included (all 40 tags)

Physics

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

-- 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.

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

WITH tags40 AS (
    SELECT TOP  40
                tagid,
                COUNT(*) AS postCount
    FROM        posttags
    GROUP BY    tagid
    ORDER BY    COUNT(*) DESC
),
answers AS (
    SELECT      score,
                id,
                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,
            f.postcount
FROM        answers a
INNER JOIN  posttags pt ON pt.postid = a.parentid
RIGHT JOIN  tags40 f ON f.tagid = pt.tagid
INNER JOIN  tags t ON f.tagid = t.id
GROUP BY    t.tagname,
            f.postcount


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

Enter Parameters

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