Meta Stack Exchange is where users like you discuss bugs, features, and support issues that affect the software powering all 158 Stack Exchange communities.

What is meta?
Here's how it works:
  1. Any Stack Exchange user can ask a question
  2. The community provides support, votes on ideas, and reports bugs
  3. Your voice helps shape the way Stack Exchange operates

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.

share|improve this question
    
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
up vote 6 down vote accepted

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

share|improve this answer
    
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.

share|improve this answer
    
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)

share|improve this answer
    
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 .