1

Please login or register to vote for this query.

(click on this box to dismiss)

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

view current version

edited apr 25 19 Salman.Arshad

created dec 3 18 anonymous