2

Please login or register to vote for this query.

(click on this box to dismiss)

An attempt to make a query showing the days on which the reputation cap was hit for a given user. I am no SQL wizard, so there might be some issues with it and there probably are more efficient ways of doing it. (Updated to remove votes on CW posts from the score) (Updated to have the result ordered by date)

Q&A about the site for students, teachers, and linguists wanting to discuss the finer points of the Italian language

-- On what days did I hit the reputation cap?
-- An attempt to make a query showing the days on which the reputation cap was
-- hit for a given user. I am no SQL wizard, so there might be some issues with
-- it and there probably are more efficient ways of doing it.
-- 
-- (Updated to remove votes on CW posts from the score)

-- 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 * from scoreperday
where score >=200
order by TheDate

fork query

created sep 2 10 Fredrik Mörk