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.

  • Looks like that query includes CW votes as well, which isn't counted for the badge. – kennytm Jun 16 '10 at 14:31
  • That was my guess too. However, in my quick attempts, I couldn't get it to exclude them. (@Kenny) – jjnguy Jun 16 '10 at 14:35

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:

-- 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.)

  • This seems like it may be correct. – jjnguy Jun 16 '10 at 14:56
  • This one is probably right, because it's slightly low on java (-14), swing (-3), and generics (-1), and I think that's about how many upvotes I've gotten this month. – mmyers Jun 16 '10 at 14:59
  • this calculates score for each tag - ie upvotes - downvotes, not just upvotes. Is this what's counted for tag badges? (I'm not sure...) – Jon Jun 16 '10 at 15:00
  • This seems to be what's calculated on the stats pages. I guess the question is whether that's the same as what's calculated for the badges. – mmyers Jun 16 '10 at 15:13

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

  • That's what I just tried (checking the wiki date), but it's still wrong according to the stats pages. It's off by +25 on java, -1 on swing, and +1 on generics. – mmyers Jun 16 '10 at 14:41
  • @mmyers: That's because the data are from May 31st, not today. – kennytm Jun 16 '10 at 14:47
  • 1
    @Kenny, why would mmyers have more upvotes in the past than he does now? – jjnguy Jun 16 '10 at 14:48
  • This one says I have 995. But, I have received more than 5 (or 6) java votes since I think. – jjnguy Jun 16 '10 at 14:56

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)

  • It shows the same (1073) for total votes, but it still shows 1050 for non CW votes. :( – jjnguy Jun 16 '10 at 14:48
  • The new query isn't showing me all of those results. Did you change the link? – jjnguy Jun 16 '10 at 17:17

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .