Tag Badge progress — All Tags

3

Please login or register to vote for this query.

(click on this box to dismiss)

Windows Phone Meta

Q&A about the site for enthusiasts and power users of Windows Phone OS

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, Name, Score, Answers)
)/*,

-- Readable summary
Results as (
  select
    TagBadges.Idx,
    RawData.TagName,
    TagBadges.Name,
    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
  from
    RawData cross join TagBadges
  union
  select
    0, '', 'Total', cast(Score as varchar), cast(Answers as varchar)
  from RawData
)*/
--select Name as [ ], TagName, Score, Answers from Results
,
AllThings as (
  select
    RawData.TagName,
    RawData.Score+' '+TagBadges.Score as Score,
    RawData.Answers+' '+TagBadges.Answers as Answers,
    TagBadges.Name
  from RawData cross join TagBadges
),
AT2 as (
select *, Name+' '+Type as Foo from AllThings
unpivot (Progress for Type in (Score,Answers)) p
)

select *
from AT2
--pivot (max(TagName) for Foo in (BronzeScoreGoal,BronzeAnswersGoal,SilverScoreGoal,SilverAnswersGoal,GoldScoreGoal,GoldAnswersGoal)) q
--pivot (max(TagName) 
--pivot (min(Score) for Name in (Bronze,Silver,Gold)) as p
--from RawData cross join TagBadges
--pivot (min(Idx) for Name in (Bronze,Silver,Gold)) as p
--order by Idx

Enter Parameters

Options:
Switch to main site
loading Hold tight while we fetch your results