My Post Averages

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

DECLARE @UserId INT = ##UserId##;

WITH MyPosts AS (
  SELECT P.Id, P.Score, P.PostTypeId
  FROM Posts P
  WHERE P.OwnerUserId = @UserId
),
MyVotes AS (
SELECT
  COUNT(V.Id) AS [Votes], 
  P.PostTypeId,
  V.VoteTypeId
  FROM Posts P
  INNER JOIN Votes V ON (P.Id = V.PostId)
  WHERE
    P.OwnerUserId = @UserId
    AND V.VoteTypeId IN (2,3)
  GROUP BY V.VoteTypeId, P.Id, P.PostTypeId
)
SELECT
  'Questions',
  AVG(CAST(P.Score AS FLOAT)) AS [Score],
  (SELECT SUM(CAST(MV.Votes AS FLOAT)) FROM MyVotes MV WHERE MV.VoteTypeId = 2 AND MV.PostTypeId = 1)/
    (SELECT COUNT(MP.Id) FROM MyPosts MP WHERE MP.PostTypeId=1) AS [Upvotes],
  (SELECT SUM(CAST(MV.Votes AS FLOAT)) FROM MyVotes MV WHERE MV.VoteTypeId = 3 AND MV.PostTypeId = 1)/
    (SELECT COUNT(MP.Id) FROM MyPosts MP WHERE MP.PostTypeId=1) AS [Downvotes]
FROM MyPosts P
WHERE P.PostTypeId = 1

UNION


SELECT
  'Answers',
  AVG(CAST(P.Score AS FLOAT)) AS [Score],
  (SELECT SUM(CAST(MV.Votes AS FLOAT)) FROM MyVotes MV WHERE MV.VoteTypeId = 2 AND MV.PostTypeId = 2)/
    (SELECT COUNT(MP.Id) FROM MyPosts MP WHERE MP.PostTypeId=2) AS [Upvotes],
  (SELECT SUM(CAST(MV.Votes AS FLOAT)) FROM MyVotes MV WHERE MV.VoteTypeId = 3 AND MV.PostTypeId = 2)/
    (SELECT COUNT(MP.Id) FROM MyPosts MP WHERE MP.PostTypeId=2) AS [Downvotes]
FROM MyPosts P
WHERE P.PostTypeId = 2

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results