How close am I to earning the <TagName> badge

Question

I have a request for someone to write a query in the data explorer that will accurately predict how close someone is to earning a tag-based badge.

The current query (How Many Upvotes Do I have for each tag) says that I have over 1000.

Or, if it cannot be done any better, please tell me why.

_{I would do this myself, but I am not experienced at SQL at all.}

Answer 1

Edit: This computes the score while tag badges are counted by upvotes only. @BalusC's answer should be correct.

---

Try this query instead, which I forked from that calculates Generalist badge:

• http://odata.stackexchange.com/stackoverflow/q/3950/undefined

-- What is my score for each badge?
-- What is the net non-CW upvote for all tags I have participated?

declare @temp table (id int identity(1,1), TagName char(128), score int);

with answers as (
  select score, id, parentId from posts  -- find responses of questions
  where communityowneddate is null   -- which are non-comm-wiki
  and posttypeid = 2                 -- answers
  and owneruserid = ##UserId##       -- by you
)

insert @temp(TagName, score)
select t.TagName, sum(a.score) as score from answers a
inner join posttags pt on pt.postid = a.parentId -- find tags of your answers
inner join tags t on pt.tagid = t.id
group by t.tagname
order by score desc

select *, case when score >= 1000 then 'S,G' when score >= 400 then 'S' else '' end from @temp

This shows you still need 47 votes to get a gold badge in [java].

(Note that these rare badges may not be handed out immediately when you reach 1,000.)

Answer 2

The first query of this overview already takes CW votes into account.

Answer 3

Try this. My SQL is a bit rusty, but I think it's OK. I believe it only counts non-wiki posts, or votes before a post becomes wiki....

I've updated my query a bit - it breaks out

1. Upvotes on posts that have never been CW.
2. Upvotes on CW posts before they became CW (so I believe these should be counted)
3. Upvotes on CW posts after they became CW (definitely out)
4. Total upvotes (just for reference)

(1 + 2 + 3 == 4 unless I've done something crazy)