One-time badges awarded multiple times

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

select
  badge_counts.name as [Badge Name],
  badge_count as [Multiple Award User Count],
  min_date as [Min Multiple Award Date],
  max_date as [Max Multiple Award Date],
  max_award as [User Max Award Count],
  top_users as [User Max Award Id(s)]
from (
  select name, min(min_date) min_date, max(max_date) max_date, count(userid) badge_count, max(badge_count) max_award
  from (
    select name, userid, min(date) min_date, max(date) max_date, count(*) badge_count
    from badges
    where tagbased = 0
    and name not in ('Yearling','Caucus','Constituent','Reviewer','Custodian','Reversal','Steward','Not a Robot','Census','Favorite Question','Stellar Question','Nice Question','Good Question','Great Question','Announcer','Populist','Revival','Necromancer','Popular Question','Notable Question','Famous Question','Guru','Booster','Publicist','Enlightened','Nice Answer','Good Answer','Great Answer')
    group by name, userid
  ) b
  where b.badge_count > 1
  group by name) badge_counts
inner join (
  select name, string_agg(userid, ', ') top_users
  from (
    select name, userid, badge_count, max(badge_count) over(partition by name) max_badges
    from (
      select name, userid, count(*) badge_count
      from badges
      where tagbased = 0
      and name not in ('Yearling','Caucus','Constituent','Reviewer','Custodian','Reversal','Steward','Not a Robot','Census','Favorite Question','Stellar Question','Nice Question','Good Question','Great Question','Announcer','Populist','Revival','Necromancer','Popular Question','Notable Question','Famous Question','Guru','Booster','Publicist','Enlightened','Nice Answer','Good Answer','Great Answer')
      group by name, userid
    ) b
    where badge_count > 1
  ) a
  where max_badges = badge_count
  group by name) user_max
on user_max.name = badge_counts.name
order by min_date desc

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results