Question or answer score over time

0

Please login or register to vote for this query.

(click on this box to dismiss)

Arqade Meta

Q&A about the site for passionate videogamers on all platforms

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 (11468, 11472)
    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 DCShannon, max(s3.score) as badp
from score_ot s1
left join score_ot s2 on s1.date = s2.date and s2.postId = 11468
left join score_ot s3 on s1.date = s3.date and s3.postId = 11472
group by s1.date
)
select
  date,
  (
      select top 1 s2.DCShannon
      from scores s2
      where s2.DCShannon is not null and s2.date <= s1.date
      order by s2.date desc
  ) as DCShannon,
  (
      select top 1 s2.badp
      from scores s2
      where s2.badp is not null and s2.date <= s1.date
      order by s2.date desc
  ) as badp
from scores s1

Enter Parameters

Options:
Switch to main site
loading Hold tight while we fetch your results