0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for builders and users of networked sensors and control devices in the contexts of smart homes, industry automation, or environmental sensors

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;

fork query

edited aug 11

created aug 11 15 anonymous