24

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.

Q&A for professionals and students of engineering

DECLARE @scorePerTop40Tags TABLE (
    [Tag Name]          CHAR(128),
    [Your Score]        INT,
    [Tag Rank]          INT IDENTITY(1, 1),
    [Tag Popularity]    INT
);

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 ([Tag Name], [Your Score], [Tag Popularity])
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      ROW_NUMBER() OVER (
                ORDER BY [Your Score]   DESC,
                [Tag Popularity]        DESC,
                [Tag Name]
            ),
            *,
            CASE WHEN [Your Score] >= 15  THEN  ' good'  ELSE ' improve'
            END AS [Status]
FROM        @scorePerTop40Tags
ORDER BY    [Your Score]        DESC,
            [Tag Popularity]    DESC,
            [Tag Name]

fork query

created nov 12 12 Awesome Poodles