Tag Progress

1

Please login or register to vote for this query.

(click on this box to dismiss)

Physical Fitness Meta

Q&A about the site for physical fitness professionals, athletes, trainers, and those providing health-related needs

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,
    NextBadge = CHOOSE(BadgeCode, 'Gold', 'Silver', 'Bronze'),
    WhatsNeedeed = CONCAT_WS(', ', CAST(AnswersNeeded AS VARCHAR) + ' answers', CAST(ScoreNeeded AS VARCHAR) + ' score'),
    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 AND ScoreNeeded IS NOT NULL THEN 1
        WHEN ScoreNeeded IS NULL AND AnswersNeeded IS NOT NULL THEN 2
        ELSE 3
    END,
    AnswersNeeded,
    ScoreNeeded

Enter Parameters

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