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 ebook publishers and readers
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]