Cайт вопросов и ответов для лингвистов и энтузиастов русского языка
-- UserId:Enter a UserId "As found in the url of your profile" ; WITH upvQ AS ( SELECT v.creationdate, SUM(count(*)*5) OVER (ORDER BY v.creationdate) AS score FROM posts p INNER JOIN votes v ON v.postid = p.id WHERE v.votetypeid = 2 --UpVote AND p.posttypeid = 1 -- Q AND p.owneruserid = ##UserId## GROUP BY v.creationdate ), upvA AS ( SELECT v.creationdate , SUM(count(*)*10) OVER (ORDER BY v.creationdate) AS score FROM posts p INNER JOIN votes v ON v.postid = p.id WHERE v.votetypeid = 2 --UpVote AND p.posttypeid = 2 -- A AND p.owneruserid = ##UserId## GROUP BY v.creationdate ), dwnVQA AS ( SELECT v.creationdate , SUM(count(*)*-2) OVER (ORDER BY 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 = ##UserId## GROUP BY v.creationdate ), accA AS ( SELECT v.creationdate , SUM(count(*) * 15) OVER (ORDER BY v.creationdate) AS score -- every accepted answer 15 fep FROM posts p INNER JOIN votes v ON v.postid = p.id WHERE v.votetypeid = 1 -- Accept AND p.posttypeid = 2 -- Answer AND p.owneruserid = ##UserId## GROUP BY v.creationdate ), sugedt AS ( -- suggested edit FOR a USER SELECT cast(approvaldate AS date) AS dateonly, SUM(count(*) * 2) OVER (ORDER BY cast(approvaldate AS date)) AS score FROM suggestededits WHERE approvaldate IS NOT NULL AND owneruserid = ##UserId## GROUP BY cast(approvaldate AS date) ), bounty AS ( SELECT v.creationdate , SUM(SUM( CASE WHEN votetypeid =8 THEN bountyamount ELSE 0 END)) OVER (ORDER BY v.creationdate) AS [start] ,SUM( SUM( CASE WHEN votetypeid =9 THEN bountyamount ELSE 0 END)) OVER (ORDER BY v.creationdate) AS [close] FROM votes v INNER JOIN posts p ON p.id = v.postid WHERE votetypeid IN (8,9) AND ((p.posttypeid = 2 AND votetypeid =9) OR (p.posttypeid = 1 AND votetypeid =8)) AND v.userid= ##UserId## GROUP BY v.creationdate ) SELECT DISTINCT v.creationdate , upvq.score + upvA.score + dwnVQA.score + accA.score + sugedt.score + bounty.start + bounty.[close] AS [REP] FROM votes v LEFT OUTER JOIN upvQ ON upvQ.creationdate = v.creationdate LEFT OUTER JOIN upvA ON upvA.creationdate = v.creationdate LEFT OUTER JOIN dwnVQA ON dwnVQA.creationdate = v.creationdate LEFT OUTER JOIN accA ON accA.creationdate = v.creationdate LEFT OUTER JOIN sugedt ON sugedt.dateonly = v.creationdate LEFT OUTER JOIN bounty ON bounty.creationdate = v.creationdate WHERE v.creationdate >= ( SELECT creationdate FROM users WHERE id = ##UserId##) ORDER BY v.creationdate