Although reputation on meta is taken from the main site, the badge mechanics, including the badges based on reputation, still works, giving you badges based on reputation you weren't aware off.
The following query shows the number of days a certain member of Meta Stack Overflow has hit the rep cap making you eligible for the Mortarboard badge. If this query returns more than 150 rows the Legendary badge is in the pocket as well
-- uid:Enter your UserId "As found in the url of your profile"
;
with upvQ as (
select v.creationdate
, count(*)*5
-- over (order by v.creationdate)
as score
from posts p
inner join votes v on v.postid = p.id
where v.votetypeid = 2 --UpVote
and p.posttypeid = 1 -- Q
and p.owneruserid = ##uid##
group by v.creationdate)
, upvA as (
select v.creationdate
, count(*)*10
-- over (order by v.creationdate)
as score
from posts p
inner join votes v on v.postid = p.id
where v.votetypeid = 2 --UpVote
and p.posttypeid = 2 -- A
and p.owneruserid = ##uid##
group by v.creationdate)
, dwnVQA as (
select v.creationdate
, count(*)*-2
-- over (order by v.creationdate)
as score
from posts p
inner join votes v on v.postid = p.id
where v.votetypeid = 3 --DownVote
and p.posttypeid in (1,2) -- Q and A
and p.owneruserid = ##uid##
group by v.creationdate)
, accA as (
select v.creationdate
, count(*) * 15
-- over (order by v.creationdate)
as score -- every accepted answer 15 fep
from posts p
inner join votes v on v.postid = p.id
where v.votetypeid = 1 -- Accept
and p.posttypeid = 2 -- Answer
and p.owneruserid = ##uid##
group by v.creationdate)
, sugedt as (
-- suggested edit for a user
select cast(approvaldate as date) as dateonly,
count(*) * 2
-- over (order by cast(approvaldate as date))
as score
from suggestededits
where approvaldate is not null
and owneruserid = ##uid##
group by cast(approvaldate as date))
, bounty as (
select v.creationdate
, sum( case when votetypeid =8 then bountyamount else 0 end)
-- over (order by v.creationdate)
as [start]
, sum( case when votetypeid =9 then bountyamount else 0 end)
-- over (order by v.creationdate)
as [close]
from votes v
inner join posts p on p.id = v.postid
where votetypeid in (8,9)
and ((p.posttypeid = 2 and votetypeid =9)
or (p.posttypeid = 1 and votetypeid =8))
and v.userid= ##uid##
group by v.creationdate
)
select distinct
v.creationdate
, isnull(upvq.score,0) -- as [Upvote Question]
+ isnull(upvA.score,0) -- as [Upvote Answer]
+ isnull(dwnVQA.score,0) -- as [Downvotes own Q and A]
+ isnull(accA.score,0) -- as [Accepted Answers]
+ isnull(sugedt.score,0) --as [Suggested Edits]
+ isnull(bounty.start,0) -- as [Bounties Started]
+ isnull(bounty.[close],0) as score --as [Bounties Received]
from votes v
left outer join upvQ on upvQ.creationdate = v.creationdate
left outer join upvA on upvA.creationdate = v.creationdate
left outer join dwnVQA on dwnVQA.creationdate = v.creationdate
left outer join accA on accA.creationdate = v.creationdate
left outer join sugedt on sugedt.dateonly = v.creationdate
left outer join bounty on bounty.creationdate = v.creationdate
where v.creationdate >= (
select creationdate
from users
where id = ##uid##)
and (isnull(upvq.score,0) -- as [Upvote Question]
+ isnull(upvA.score,0) -- as [Upvote Answer]
+ isnull(dwnVQA.score,0) -- as [Downvotes own Q and A]
+ isnull(accA.score,0) -- as [Accepted Answers]
+ isnull(sugedt.score,0) --as [Suggested Edits]
+ isnull(bounty.start,0) -- as [Bounties Started]
+ isnull(bounty.[close],0)) > 199
order by v.creationdate
The query is based on the one I used on an earlier answer and calculates the rep based on the available data in SEDE. Do notice that this is missing deleted posts and your own downvotes so it is not 100% accurate (and the data is refreshed only once a week, on Monday at 03:00 UTC)
