1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for those who study, teach, research and apply economics and econometrics

declare @vote_data table
(
    PostId int
    ,ID int
    ,date date
    ,score_change int
);
insert into
    @vote_data
select
    v.PostId
    ,v.Id
    ,v.CreationDate
    ,case vt.Id
        when 2 then 1
        when 3 then -1
        end
from
    Votes v
    join VoteTypes vt on vt.Id = v.VoteTypeId
where
    v.PostId in (4125, 4058)
    and vt.Id in (2, 3);
with score_ot as (
select
    v1.PostId
    ,min(v1.date) as date
    ,sum(v2.score_change) as score
from
    @vote_data v1
    join @vote_data v2 on v2.Id < v1.Id and v2.PostId = v1.PostId
group by
    v1.PostId, v1.Id
    ),
    scores as (
    select s1.date, max(s2.score) as Erik, max(s3.score) as NapoleonWilson
from score_ot s1
left join score_ot s2 on s1.date = s2.date and s2.postId = 4125
left join score_ot s3 on s1.date = s3.date and s3.postId = 4058
group by s1.date
)
select
  date,
  (
      select top 1 s2.Erik
      from scores s2
      where s2.Erik is not null and s2.date <= s1.date
      order by s2.date desc
  ) as Erik,
  (
      select top 1 s2.NapoleonWilson
      from scores s2
      where s2.NapoleonWilson is not null and s2.date <= s1.date
      order by s2.date desc
  ) as NapoleonWilson
from scores s1

fork query

edited jun 12

created jun 12 galacticninja