3

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for researchers and developers who explore the principles of a system through analysis of its structure, function, and operation

with

-- Total score and number of answers in each tag
RawData as (
  select
    Tags.TagName,
    sum(Answer.Score) as Score,
    count(Answer.Id) as Answers
  from
    Posts Answer
    join Posts Question on Answer.ParentId = Question.Id
    join PostTags on PostTags.PostId = Question.Id
    join Tags on Tags.Id = PostTags.TagId
  where
    Answer.OwnerUserId = ##UserId## and
    Answer.CommunityOwnedDate is null
  group by
    Tags.TagName
),

-- Predefined tag badge goals
TagBadges as (
  select * from
    (values
      (1, 'Bronze', 100, 20),
      (2, 'Silver', 400, 80),
      (3, 'Gold', 1000, 200))
    as Badge(Idx, Class, Score, Answers)
),

-- Progress per badge class
TypeProgress as (
  select
    RawData.TagName,
    format(iif(RawData.Score > TagBadges.Score, 1,
               cast(RawData.Score as float)/TagBadges.Score), '#0.#%') as Score,
    format(iif(RawData.Answers > TagBadges.Answers, 1,
               cast(RawData.Answers as float)/TagBadges.Answers), '#0.#%') as Answers,
    TagBadges.Class
  from RawData cross join TagBadges
),

AllProgress as (
  select TagName, Progress, Class+' '+Type as Category
  from TypeProgress
  unpivot (Progress for Type in (Score, Answers)) p
)

select *
from AllProgress
pivot (
  max(Progress) for Category in
    ([Bronze Score], [Bronze Answers],
     [Silver Score], [Silver Answers],
     [Gold Score], [Gold Answers])
) q
order by cast(left([Gold Score], len([Gold Score])-1) as float) desc

view current version

edited aug 8 14

created aug 8 14 jtbandes