1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for those who study, teach, research and apply economics and econometrics

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;

fork query

edited jun 24

created jun 24 jon.doe44985