10

I wrote a SEDE query to find the users who should have the Unsung Hero badge, but they have not.

The query simply counts the non-CW, not self-accepted, but accepted answers of the users, and compares the ratio of the zero-scored ones (COUNT(CASE WHEN Score=0 THEN 1 END)) to all (COUNT(*)).

Surprisingly, it has found a lot (603) some (6). I think, something might be problematic with my query, or with the exact details of the Unsung Hero badge giving algorithm. (A possible another problem source might be if the SEDE dump generation is not completely atomic, but it is unlikely, because none of the found users got the badge since the last dump.)

The most obvious case is user alotropico, who has 42 accepted answers, 11 of them are zero-scored, and it is manually easily testable, that none of their accepted answers are CW or self-accepts. Despite that, they don't have the Unsung Hero badge.

Why?

Edit: It was made clear that the minimal count of the zero-scored, non-CW, non-own-accept, not-deleted answers is 11, and not 10. The modified query still shows exceptions, the most obvious is Alotropico. User Jean was the prior example.

Edit #2: I extended the query to use all the relevant informations here we could see (10 day oldness calculation, > instead >=). I also extended it with another query which shows the induvidual details of a single user. Beside that, a bug in the query was found (<> gives false result for NULL-values in SQL). Now the SEDE data and the badge requirements exactly match, problem solved.

Improve this question
22
  • Hopefully needless to say: please do not modify his answer scores until this question is open, not deleted, and has not an accepted answer. :-)
    – peterh
    Commented Jan 12, 2021 at 23:11
  • 1
    "Zero score accepted answers: more than 10 and 25% of total." from the badge description. The user you show has exactly 10 accepted answers with zero score (11 total, one has a score of 1) and 60 answers total, so less than 25 percent of the total. Also, I'm not sure if it's > 10 or >= 10.
    – VLAZ
    Commented Jan 12, 2021 at 23:15
  • 1
    @VLAZ No, the 25% of the accepted answers and not his all answers. I dig the meta reference for that.
    – peterh
    Commented Jan 12, 2021 at 23:16
  • 3
    @VLAZ No, it's a percentage of accepted answers, not overall answers.
    – Sonic the Anonymous Hedgehog
    Commented Jan 12, 2021 at 23:17
  • Ah, I see. So my reading is wrong there. What about the more than vs more than equal?
    – VLAZ
    Commented Jan 12, 2021 at 23:17
  • @VLAZ I changed >= to >, there are still mismatches, although not so obvious. Example.
    – peterh
    Commented Jan 12, 2021 at 23:24
  • 3
    Yeah, it seems that it needs to be more than 10, per the exact criteria for the badge (revision where this was edited in by then-employee balpha). However, even after factoring those out, there are still 13 users who haven't earned the badge when they should have: data.stackexchange.com/stackoverflow/query/1357631/…
    – Sonic the Anonymous Hedgehog
    Commented Jan 12, 2021 at 23:24
  • @VLAZ Sonic linked the exact critera. On them, >10, thus at least 11, (non-cw, non-deleted, non-self-accept) answers are needed, but the criteria for the ratio remains >= 25%.
    – peterh
    Commented Jan 12, 2021 at 23:28
  • @Rob No, see the the link in Sonic's comment. The 25% of the accepted answers must be zero-scored, not of his all answers. Jean isn't eligible because the absolute limit for the zero-scored answers is >10 and not >=10. But many others are eligible, still they have not the badge, for example Atropico.
    – peterh
    Commented Jan 12, 2021 at 23:45
  • 11 out of 33 - stackoverflow.com/… stackoverflow.com/…
    – Rob
    Commented Jan 12, 2021 at 23:54
  • @Rob Yes. It is 25%. Also works if you order the answers by votes and count the zeros by eye.
    – peterh
    Commented Jan 12, 2021 at 23:56
  • See meta.stackexchange.com/questions/67397/…, there are more criteria you need to cover: the posts must be older than 10 days to qualify for example.
    – Martijn Pieters
    Commented Jan 13, 2021 at 0:11
  • @MartijnPieters The query handles them all.
    – peterh
    Commented Jan 13, 2021 at 0:13
  • @peterh-ReinstateMonica: your query looks at stale data; the SEDE doesn’t reflect changes since Sunday. Also: when were the answers marked accepted? The badge script awards the badge to an account if it meets the criteria at the time it runs; and the circumstances can have changed in the interim.
    – Martijn Pieters
    Commented Jan 13, 2021 at 0:18
  • 1
    @MartijnPieters Thanks. In the lack of deleted post access is it hard to say, but hoping that the user had not enough accepted and deleted answer to modify the result, an estimation could be made. | I think it might be rounding problem (it could be prevented by multipling the zero-scored answer count by 4 and compare integers, so it would be so much simpler). Or, somewhere I read that automatic maintenance scripts like to consider only 1-2-3 day old votes, to give time for the serial vote detector to intervene. Maybe this had some side effect.
    – peterh
    Commented Jan 13, 2021 at 1:18

1 Answer 1

Reset to default
9

According to the current code, the correct logic is not "at least" 25%. The "more than" in the description applies to both criteria.

So it is more than 10 answers (meaning at least 11) and more than 25%. Exactly 25% (as in the case of 11/44) does not qualify for the badge.

The same criteria apply to Tenacious, requiring more than 5 (meaning at least 6) and more than 20%.

Improve this answer
15
  • Um, the modified query linked at the bottom of the question does include a couple users whose percentages are indeed above 25% (and meet all other criteria, including at least 11 qualifying answers). What about those? Is it that the badge script hasn't awarded them the badge yet?
    – Sonic the Anonymous Hedgehog
    Commented Jan 13, 2021 at 4:00
  • 1
    @SonictheCuriouserHedgehog The data is simply outdated in SEDE. Those users have more accepted answers (e.g. the example directly in the question which shows 42 accepted answers in the query where they actually have 44).
    – animuson StaffMod
    Commented Jan 13, 2021 at 4:02
  • So you mean, that user met the criteria at the time SEDE was updated, but ceased to meet them by the time the script to award the badge ran? Also, what about the other users in the modified query who have percentages above 25%? Do they fall into the same bucket?
    – Sonic the Anonymous Hedgehog
    Commented Jan 13, 2021 at 4:04
  • 1
    @Sonic Presumably so. I am not going to check every one individually because I simply do not care enough. If they qualified for the badge when it ran, they would have received it. I see nothing to suggest it is malfunctioning.
    – animuson StaffMod
    Commented Jan 13, 2021 at 4:06
  • Sorry but in the moment of the last SEDE dump, Alotropico had 42 total (non-deleted, non-cw, non-self, accepted) answers, 11 of them were zero-scored. This is more than 25%. Last sunday morning he was qualified for the badge.
    – peterh
    Commented Jan 13, 2021 at 13:39
  • @animuson My current best belief is that the "at least 10 days old" criteria might have some unexpected details. It might mean the count of midnights between the answer creationdates and the current timestamp (this is what a DATEDIFF(d, Posts.CreationDate, CURRENT_TIMESTAMP) in t-sql does), but it could be also at least 86400*10 seconds (1 day = 86400s). They have a little bit different meanings.
    – peterh
    Commented Jan 13, 2021 at 13:56
  • 1
    so this confirms the accepted answer in Unsung hero badge: from 11 answers or 10?
    – fedorqui 'SO stop harming'
    Commented Jan 13, 2021 at 14:56
  • @fedorqui'SOstopharming' It confirms, but contrary Animuson's statements, fact is that many "unsung hero" badges are still not given, why they should be. No, that is not some timing problem or so. Simply the site does not work as it is stated. Possibly the exact details of the badge script are not enough well known, but it might be also a bug. (Off: you have the third most non-tag badges on the SO, and you are one of the 2 users having all the retired badges.)
    – peterh
    Commented Jan 13, 2021 at 15:14
  • 1
    @peterh The SQL uses a simply CreationDate < Getutcdate() - 10.
    – animuson StaffMod
    Commented Jan 13, 2021 at 15:33
  • @animuson Ok, that is the same what the query is using. The important thing is that all the data is available on the SEDE, to check the Unsung Hero eligibility, and the query lists the users who still have not. Are the requirements (non-cw, not self-accept, not deleted, CreationDate < Getutcdate() - 10) exactly the same for both the total count and for the score=0 counting?
    – peterh
    Commented Jan 13, 2021 at 15:43
  • @animuson I extended the query to use all the relevant informations here we could see (10 day oldness calculation, > instead >=). I also extended it with another query which shows the induvidual details of a single user. I am sorry but imho it is clearly visible that Alotropico was eligible for the badge at the moment of the last SEDE dump.
    – peterh
    Commented Jan 13, 2021 at 20:26
  • @peterh Well let's look at this logically. That user has not had any events on their account whatsoever since October, so there is no reason that the SEDE data should be out of sync with the live data. So why does the live data say 44 while SEDE says 42?
    – animuson StaffMod
    Commented Jan 13, 2021 at 20:59
  • A simple comparison of the lists in SEDE versus those in live show that they have two accepted answers, both of which happen to be on questions where the owner has been deleted: stackoverflow.com/q/62868182 stackoverflow.com/q/63760408 - It's not immediately obvious to me why those are excluded, but your answer lies with those, not with the script being broken.
    – animuson StaffMod
    Commented Jan 13, 2021 at 20:59
  • 1
    @animuson You are right! Totally right! NULL is tricky in SQL, all comparison with is false, except comparison with another NULL. Thus, Q.OwnerUserId <> A.OwnerUserId results false if the question owner was deleted :-( I fixed the query, and now everything is okay!
    – peterh
    Commented Jan 13, 2021 at 21:06
  • @peterh: it’s a pity T-SQL doesn’t implement IS DISTINCT FROM; I’m sure you have the edge cases covered now (only the question owner could be NULL), but do check this post on the T-SQL equivalent of IS DISTINCT FROM.
    – Martijn Pieters
    Commented Jan 16, 2021 at 1:20

You must log in to answer this question.

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