Q&A for professional and enthusiast programmers
WITH cte AS ( SELECT TagName, AnswerCount = COUNT(*), ApproxScore = SUM(a.Score), BadgeCode = CASE WHEN COUNT(*) < 20 OR SUM(a.Score) < 100 THEN 3 WHEN COUNT(*) < 80 OR SUM(a.Score) < 400 THEN 2 WHEN COUNT(*) < 200 OR SUM(a.Score) < 1000 THEN 1 END FROM Posts AS a INNER JOIN PostTags AS x ON a.ParentId = x.PostId INNER JOIN Tags AS t ON x.TagId = t.Id WHERE a.PostTypeId = 2 AND a.OwnerUserId = ##UserId## GROUP BY TagName ) SELECT TagName, AnswerCount, ApproxScore, CHOOSE(BadgeCode, 'Gold', 'Silver', 'Bronze') AS NextBadge, CONCAT_WS(', ', CAST(AnswersNeeded AS VARCHAR) + ' answers', CAST(ScoreNeeded AS VARCHAR) + ' score') AS WhatsNeedeed, AnswersNeeded, ScoreNeeded FROM cte CROSS APPLY ( SELECT AnswersNeeded = CASE WHEN BadgeCode = 3 AND AnswerCount < 20 THEN -AnswerCount + 20 WHEN BadgeCode = 2 AND AnswerCount < 80 THEN -AnswerCount + 80 WHEN BadgeCode = 1 AND AnswerCount < 200 THEN -AnswerCount + 200 END, ScoreNeeded = CASE WHEN BadgeCode = 3 AND ApproxScore < 100 THEN -ApproxScore + 100 WHEN BadgeCode = 2 AND ApproxScore < 400 THEN -ApproxScore + 400 WHEN BadgeCode = 1 AND ApproxScore < 1000 THEN -ApproxScore + 1000 END ) AS ca ORDER BY BadgeCode, CASE WHEN AnswersNeeded IS NULL THEN 1 WHEN ScoreNeeded IS NULL THEN 2 ELSE 3 END, AnswersNeeded, ScoreNeeded