My reputation graph

0

Please login or register to vote for this query.

(click on this box to dismiss)

Space Exploration

Q&A for spacecraft operators, scientists, engineers, and enthusiasts

-- 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 [TOOT]
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

Enter Parameters

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