0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

SELECT q.Id as [Post Link],
  a.CreationDate as "Posted",
  v.CreationDate as "Accepted",
  (
    SELECT COUNT(*)
    FROM Posts as ea
    WHERE q.Id = ea.ParentId
      AND ea.CreationDate < a.CreationDate
  ) as "Earlier answers",
  (
    SELECT COUNT(*)
    FROM Posts as la
    WHERE q.Id = la.ParentId
      AND la.CreationDate > a.CreationDate
  ) as "Later answers",
  (
    SELECT COUNT(*)
    FROM Posts as va
    WHERE q.ID = va.ParentId
      AND va.Score >= a.Score
  ) as ">=Score answers"
FROM Posts as q
  INNER JOIN Posts as a ON q.AcceptedAnswerId = a.Id
  INNER JOIN Votes as v ON q.AcceptedAnswerId = v.PostId
  INNER JOIN PostTags as pt ON q.Id = pt.PostId
  INNER JOIN Tags as t ON
    (pt.TagId = t.Id AND t.TagName = ##tag:string##)
    OR (t.Id = 1 AND ##tag## = 'untagged')
WHERE v.VoteTypeId = 1
  AND DATEDIFF(day, a.CreationDate, v.CreationDate) <= ##days:int##

fork query

edited aug 30 22

created aug 30 22 wizzwizz4 ♦