0

Please login or register to vote for this query.

(click on this box to dismiss)

both bits are there, joining them takes too long and timesout

Q&A for professional and enthusiast programmers

;with editors as
(
  select distinct owneruserid as userid
  from suggestededits
  where approvaldate is not null
)
,
rep_hist as
(
  select p.owneruserid as [userid]
       , v.creationdate 
       , sum(case 
         when posttypeid = 1 then
              case 
              when votetypeid = 2 then 5 
              when votetypeid = 3 then -2
              when votetypeid = 8 then -bountyamount
              else 0 end
         else       
              case 
              when votetypeid = 1 then 15
              when votetypeid = 2 then 10 
              when votetypeid = 3 then -2
              when votetypeid = 9 then bountyamount
              else 0 end
         end)
         as rep
  from posts p
  inner join votes v on v.postid = p.id 
                    and v.votetypeid in (1,2,3,8,9)
  -- inner join editors e on e.userid = p.owneruserid
  where p.posttypeid in (1,2)
  group by p.owneruserid
       , v.creationdate 
)
,
running_rep_hist as
(
select userid
     , creationdate
     , sum(rep) over( partition by userid
                      order by userid,creationdate asc
                     ) runsum
from rep_hist
)

select *
from running_rep_hist
where userid in (811, 447356)
order by userid, creationdate

/*
select se.owneruserid as [User Link]
     , u.reputation
     , sum(case when approvaldate is not null then 2 else 0 end) as [potential rep.]
     , sum(case when approvaldate is not null then 1 else 0 end) as [# approved edits]
     , sum(case when rejectiondate is not null then 1 else 0 end) as [# rejected edits]
     , count(*) [total edit count]
from suggestededits se 
inner join posts p on p.id = se.postid and p.posttypeid in (1,2,4,5)
inner join users u on u.id = se.owneruserid
where (p.CommunityOwnedDate is null
or p.CommunityOwnedDate > se.creationdate)
and se.owneruserid > 0 
group by se.owneruserid
       , u.reputation
having sum(case when approvaldate is not null then 2 else 0 end) > 999
order by sum(case when approvaldate is not null then 2 else 0 end)  desc
*/

fork query

edited jun 21 18

created jun 21 18 rene