0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for people looking to bypass life's everyday problems with simple tricks

SELECT
  a.Score,
  COUNT(DISTINCT CASE v.VoteTypeId WHEN 3 THEN v.Id ELSE NULL END) AS Downvotes,
  COUNT(DISTINCT CASE v.VoteTypeId WHEN 2 THEN v.Id ELSE NULL END) AS Upvotes,
  DATEDIFF(day, a.CreationDate, CURRENT_TIMESTAMP) As Age,
  a.Id as [Post Link]
FROM Votes AS v
INNER JOIN Posts AS a ON v.PostId = a.Id
INNER JOIN Posts As q ON q.Id = a.ParentId
INNER JOIN PostTags AS pt ON pt.PostId = q.Id
INNER JOIN Tags AS t ON pt.TagId = t.Id
INNER JOIN Users AS u ON a.OwnerUserId = u.Id
WHERE
    v.VoteTypeId IN (2, 3)  -- Up or Down
AND a.PostTypeId = 2  -- Answer
AND a.OwnerUserId != q.OwnerUserId  -- Ignoring self-answers
AND t.TagName LIKE 'python%'  -- All Python tags
AND u.Id = 100297
GROUP BY a.Id, a.Score, a.CreationDate
HAVING COUNT(DISTINCT CASE v.VoteTypeId WHEN 3 THEN v.Id ELSE NULL END) > 0
ORDER BY 2 DESC

view current version

edited sep 20 21

created sep 20 21 Martijn Pieters