Q&A for scientists using computers to solve scientific problems
WITH ClosedPosts AS ( SELECT p.Id, p.Title, p.Score AS PostScore, p.ClosedDate, ROW_NUMBER() OVER (ORDER BY p.ClosedDate DESC) AS RowNum FROM Posts p JOIN PostTags pt ON pt.PostId = p.Id JOIN Tags t ON t.Id = pt.TagId WHERE p.PostTypeId = 1 AND p.ClosedDate IS NOT NULL AND t.TagName IN ('python', 'machine-learning', 'neural-network', 'keras', 'classification') AND ( SELECT COUNT(*) FROM PostTags pt2 JOIN Tags t2 ON t2.Id = pt2.TagId WHERE pt2.PostId = p.Id AND t2.TagName NOT IN ('python', 'machine-learning', 'neural-network', 'keras', 'classification') ) = 0 ), BestAnswers AS ( SELECT q.Id AS QuestionId, MAX(a.Score) AS MaxScore FROM Posts q JOIN Posts a ON a.ParentId = q.Id WHERE q.PostTypeId = 1 AND a.PostTypeId = 2 AND q.ClosedDate IS NOT NULL GROUP BY q.Id ) SELECT 'https://stackoverflow.com/questions/' + CAST(cp.Id AS VARCHAR) AS [Post Link], cp.Title, cp.PostScore, a.Score AS [Answer Score], a.Body AS [Best Answer] FROM ClosedPosts cp JOIN BestAnswers ba ON ba.QuestionId = cp.Id JOIN Posts a ON a.ParentId = cp.Id AND a.Score = ba.MaxScore WHERE cp.RowNum <= 10 ORDER BY cp.ClosedDate DESC;