with upVQ as ( select distinct p.OwnerUserId, DATEPART(mo...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Moderators

Q&A for community managers, administrators, and moderators

with upVQ as (
  select distinct
  p.OwnerUserId,
  DATEPART(month, v.creationdate) as Month,
  DATEPART(year, v.creationdate) as Year,
  sum(count(v.Id)*5) OVER(PARTITION BY DATEPART(year, v.creationdate) ORDER by DATEPART(month, v.creationdate)) as score
  from posts p 
  inner join votes v on v.postid = p.id
  where v.votetypeid = 2 
  AND p.OwnerUserId in ('16007','182668','134554','223992','9204','15416','3501','65863','139985','179910','252228','225074','7432','3043','143305','218196','22656','87189','69545','107090','50617','2988','65387','149392','33518','23354','6309','13508','1338','115145','18771','14860','476','131930','16406','15168','146325','89817','140750','13302','1968','19068') 
  and p.posttypeid = 1 -- Q
  and DATEPART(year, v.creationdate) >= 2010 and DATEPART(year, v.creationdate) <= 2020
  group by p.OwnerUserId, DATEPART(month, v.creationdate), DATEPART(year, v.creationdate)
  ),
  upvA as (
     select distinct
        p.OwnerUserId,
        DATEPART(month, v.creationdate) as Month,
        DATEPART(year, v.creationdate) as Year,
        sum(count(v.Id)*10) OVER(PARTITION BY DATEPART(year, v.creationdate) ORDER by DATEPART(month, v.creationdate)) as score
     from posts p 
     inner join votes v on v.postid = p.id
     where v.votetypeid = 2 
     AND p.OwnerUserId in ('16007','182668','134554','223992','9204','15416','3501','65863','139985','179910','252228','225074','7432','3043','143305','218196','22656','87189','69545','107090','50617','2988','65387','149392','33518','23354','6309','13508','1338','115145','18771','14860','476','131930','16406','15168','146325','89817','140750','13302','1968','19068') 
     and p.posttypeid = 2 
     and DATEPART(year, v.creationdate) >= 2010 and DATEPART(year, v.creationdate) <= 2020
     group by p.OwnerUserId, DATEPART(year, v.creationdate), DATEPART(month, v.creationdate)
  ),
 dwnVQA as (
     select distinct
       p.OwnerUserId,
       DATEPART(month, v.creationdate) as Month,
       DATEPART(year, v.creationdate) as Year,
       sum(count(v.Id)*-2) OVER(PARTITION BY DATEPART(year, v.creationdate) ORDER by DATEPART(month, v.creationdate)) as score
     from posts p 
     inner join votes v on v.postid = p.id
     where v.votetypeid = 3 --DownVote
     and p.posttypeid in (1,2) -- Q and A
     and p.owneruserid in ('16007','182668','134554','223992','9204','15416','3501','65863','139985','179910','252228','225074','7432','3043','143305','218196','22656','87189','69545','107090','50617','2988','65387','149392','33518','23354','6309','13508','1338','115145','18771','14860','476','131930','16406','15168','146325','89817','140750','13302','1968','19068')
     and DATEPART(year, v.creationdate) >= 2010 and DATEPART(year, v.creationdate) <= 2020
     group by p.OwnerUserId, DATEPART(year, v.creationdate), DATEPART(month, v.creationdate)
   ),
accA as (
     select distinct
      p.OwnerUserId,
      DATEPART(month, v.creationdate) as Month,
      DATEPART(year, v.creationdate) as Year,
      sum(count(v.Id)*15) OVER(PARTITION BY DATEPART(year, v.creationdate) ORDER by DATEPART(month, v.creationdate)) as score
     from posts p 
     inner join votes v on v.postid = p.id
     where v.votetypeid = 1 -- Accept
     and p.posttypeid = 2 -- Answer 
     and DATEPART(year, v.creationdate) >= 2010 and DATEPART(year, v.creationdate) <= 2020
     and  p.owneruserid in ('16007','182668','134554','223992','9204','15416','3501','65863','139985','179910','252228','225074','7432','3043','143305','218196','22656','87189','69545','107090','50617','2988','65387','149392','33518','23354','6309','13508','1338','115145','18771','14860','476','131930','16406','15168','146325','89817','140750','13302','1968','19068')
     group by p.OwnerUserId, DATEPART(year, v.creationdate), DATEPART(month, v.creationdate)
   ), 
sugedt as (
    select distinct
     OwnerUserId,
     DATEPART(month, approvalDate) as Month,
     DATEPART(year, approvalDate) as Year,
     sum(count(*) * 2) OVER(PARTITION BY DATEPART(year, approvalDate) ORDER BY DATEPART(month, approvalDate)) as score
    from suggestededits
    where approvaldate is not null
    and owneruserid in ('16007','182668','134554','223992','9204','15416','3501','65863','139985','179910','252228','225074','7432','3043','143305','218196','22656','87189','69545','107090','50617','2988','65387','149392','33518','23354','6309','13508','1338','115145','18771','14860','476','131930','16406','15168','146325','89817','140750','13302','1968','19068')
    and DATEPART(year, approvalDate) >= 2010 and DATEPART(year, approvalDate) <= 2020
    group by OwnerUserId, DATEPART(month, approvalDate), DATEPART(year, approvalDate)
 ),
bounty as (
   select distinct
    p.OwnerUserId,
    DATEPART(month, v.creationdate) as Month,
    DATEPART(year, v.creationdate) as Year,
    sum(sum( case when votetypeid = 8  then bountyamount else 0 end))  OVER(PARTITION BY DATEPART(year, v.creationdate) ORDER BY DATEPART(month, v.creationdate)) as [start],
    sum( sum( case when votetypeid = 9 then bountyamount else 0 end)) OVER(PARTITION BY DATEPART(year, v.creationdate) ORDER BY DATEPART(month, v.creationdate)) as [close]
  from votes v
  inner join posts p on p.id = v.postid 
  where votetypeid in (8,9)
  and DATEPART(year, v.creationdate) >= 2010 and DATEPART(year, v.creationdate) <= 2020
  and ((p.posttypeid = 2 and votetypeid =9 and p.owneruserid in ('16007','182668','134554','223992','9204','15416','3501','65863','139985','179910','252228','225074','7432','3043','143305','218196','22656','87189','69545','107090','50617','2988','65387','149392','33518','23354','6309','13508','1338','115145','18771','14860','476','131930','16406','15168','146325','89817','140750','13302','1968','19068'))
  or (p.posttypeid = 1 and votetypeid =8 and v.userid in ('16007','182668','134554','223992','9204','15416','3501','65863','139985','179910','252228','225074','7432','3043','143305','218196','22656','87189','69545','107090','50617','2988','65387','149392','33518','23354','6309','13508','1338','115145','18771','14860','476','131930','16406','15168','146325','89817','140750','13302','1968','19068') ))
  group by p.OwnerUserId, DATEPART(year, v.creationdate), DATEPART(month, v.creationdate)
)
  SELECT
  distinct
  upvq.OwnerUserId,
  upvq.Month,
  upvq.Year,
  upva.score as answerUpVotes,
  upvq.score as QuestionUpVotes,
  acca.score as AcceptedAnswerUpVotes,
  sugedt.score as SuggestedEdits,
  bounty.start as BountiesStarted,
  bounty.[close] as BountiesReceived
  FROM upVQ
  LEFT OUTER JOIN upvA ON upvA.OwnerUserId = upVQ.OwnerUserId and upvq.Month = upva.Month and upvq.Year = upva.Year
  LEFT OUTER JOIN dwnVQA ON dwnVQA.OwnerUserId = upvq.OwnerUserId and dwnVQA.Month = upvq.Month and dwnVQA.Year = upvq.Year
  LEFT OUTER JOIN accA ON accA.OwnerUserId = upvq.OwnerUserId and accA.Month = upvq.Month and accA.Year = upvq.Year
  LEFT OUTER JOIN sugedt ON sugedt.OwnerUserId = upvq.OwnerUserId and sugedt.Month = upvq.Month and sugedt.Year = upvq.Year
  LEFT OUTER JOIN bounty ON bounty.OwnerUserId = upvq.OwnerUserId and bounty.Month = upvq.Month and bounty.Year = upvq.Year
  ORDER BY upvq.Year

Enter Parameters

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