0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for people who build and repair bicycles, people who train cycling, or commute on bicycles

--SET SHOWPLAN_TEXT ON
--GO

WITH UpvotedPosts AS (SELECT
  COUNT(*) as [Num Posts],
  CASE p.PostTypeId WHEN 1 THEN 'Question' ELSE 'Answer' END as [Post Type],
  'Up' as [Vote Type]
FROM Posts p
WHERE
  (p.PostTypeId = 1 OR -- question
  p.PostTypeId = 2) AND -- answer
  EXISTS(SELECT NULL From Votes v WHERE v.PostId = p.Id AND v.VoteTypeId = 2) AND
  p.Id < 1000000
GROUP BY
  p.PostTypeId
),
DownvotedPosts AS (SELECT
  COUNT(*) as [Num Posts],
  CASE p.PostTypeId WHEN 1 THEN 'Question' ELSE 'Answer' END as [Post Type],
  'Down' as [Vote Type]
FROM Posts p
WHERE
  (p.PostTypeId = 1 OR -- question
  p.PostTypeId = 2) AND -- answer
  EXISTS(SELECT NULL From Votes v WHERE v.PostId = p.Id AND v.VoteTypeId = 3) AND
  p.Id < 1000000
GROUP BY
  p.PostTypeId
)


SELECT * FROM UpvotedPosts
UNION ALL
SELECT * FROM DownvotedPosts
ORDER BY
  [Vote Type] DESC,
  [Post Type] DESC

  -- COUNT(*) as [Upvoted posts]
  --COUNT(Downvoted) as [Downvoted posts]
  --COUNT(IIF(PostTypeId = 1 AND Upvoted = 1, 1, NULL)) as [Upvoted Qstns]
  --SUM(IsQuestion * Downvoted) as [Downvoted Qstns],
  --SUM(IsAnswer * Upvoted) as [Upvoted Answers],
  --SUM(IsAnswer * Downvoted) as [Downvoted Answers]

view current version

edited jun 8 23

created may 16 23 jon.doe33355