Find questions or answers with the most votes cast recently (e.g. last 100 days)

0

Please login or register to vote for this query.

(click on this box to dismiss)

Craft CMS

Q&A for administrators, end users, developers and designers for Craft CMS

SELECT TOP(100) CONCAT('http://stackoverflow.com/questions/', p2.Id) AS URL,
    CASE WHEN p2.ParentId IS NULL THEN p2.Title ELSE p2p.Title END AS Title,
    pt.Name AS 'Q/A',
    p2.CreationDate,
    p2.Score,
    subq.Votes,
    CASE WHEN p2.ParentId IS NULL THEN p2.Tags ELSE p2p.Tags END AS Tags
FROM
(SELECT p.Id, COUNT(*) AS Votes
 FROM Posts p
 JOIN Votes v
 ON p.Id = v.PostId
 WHERE v.CreationDate > DATEADD(DAY, -100, GETDATE())
   AND p.ClosedDate IS NULL /* Votes cast in the last 100 days */
 GROUP BY p.Id) subq
JOIN Posts p2
ON p2.Id = subq.Id
LEFT JOIN Posts p2p
ON p2.ParentId = p2p.Id
JOIN PostTypes pt
ON pt.Id = p2.PostTypeId
ORDER BY subq.Votes DESC;

Enter Parameters

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