8

Please login or register to vote for this query.

(click on this box to dismiss)

Shows the days on which the reputation cap was hit for a given user. (Updated to display all days with scores)

Q&A about the site for music historians, critics, and fans

-- On what days did I hit the reputation cap?
-- Shows the days on which the reputation cap was
-- hit for a given user.
-- 
-- (Updated to display all days with scores)


-- get my answers
with myanswers as (
select Id from Posts
where OwnerUserId = ##UserId##
and CommunityOwnedDate is null
and PostTypeId = 2),

-- get my questions
myquestions as (
select Id from Posts
where OwnerUserId = ##UserId##
and CommunityOwnedDate is null
and PostTypeId = 1),

-- get answer score per day
myanswervotes as (
select sum(
case 
 when v.VoteTypeId = 2 then 10
 when v.VoteTypeId = 3 then -2
 else 0
end) as 'score', CreationDate from Votes v
inner join myanswers a on a.Id = v.PostId
group by CreationDate),

-- get question score per day
myquestionvotes as (
select sum(
case 
 when v.VoteTypeId = 2 then 5
 when v.VoteTypeId = 3 then -2
 else 0
end) as 'score', CreationDate from Votes v
inner join myquestions a on a.Id = v.PostId
group by CreationDate),

-- get total score per day
scoreperday as (
select 
case
 when a.score IS NOT NULL and q.score IS NULL then a.score
 when a.score IS NULL and q.score IS NOT NULL then q.score 
 when a.score IS NOT NULL and q.score IS NOT NULL then a.score + q.score
 else 0
end as 'score',
case
    when NOT a.CreationDate IS NULL then a.CreationDate
    else q.CreationDate
end as 'TheDate'
from myanswervotes a
full outer join myquestionvotes q on a.CreationDate = q.CreationDate)

-- select the days with a total score of 200 or more
select *, case when score =200 then 'at'when score >200 then'Over'else''end as Status from scoreperday
order by TheDate desc

fork query

created jun 16 10 Mark Hurd