Ordinals of your badges

1

Please login or register to vote for this query.

(click on this box to dismiss)

Reverse Engineering Meta

Q&A about the site for researchers and developers who explore the principles of a system through analysis of its structure, function, and operation

-- first badges of any kind
with FirstBadges as (
    select * 
    from (select UserId,
                 Name,
                 Date,
                 Class,
                 TagBased,
                 row_number() over(partition by UserId, Name, Class order by Date asc) rn
          from Badges) as b 
    where b.rn = 1
),
-- first badges of specific users
UserBadges as (
    select *
    from FirstBadges
    where UserId = ##UserId##
)


select fb.Name as name,
       fb.Class as class,
       count(*) as ordinal,
       max(fb.Date) as date
from FirstBadges as fb
     inner join UserBadges ub on (fb.Name = ub.Name
                              and fb.Class = ub.Class
                              and fb.TagBased = ub.TagBased
                              and fb.Date <= ub.Date)
group by fb.Name, fb.Class, fb.TagBased
order by ordinal

Enter Parameters

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