Mortarboard and more

0

Please login or register to vote for this query.

(click on this box to dismiss)

http://meta.stackexchange.com/questions/78603/should-i-really-have-been-awarded-mortarboard-on-a-per-site-meta

Stack Overflow Meta en español

Q&A for programadores y profesionales de la informática

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

Enter Parameters

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