1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for members of the workforce navigating the professional setting

---minimal id is just an approximation of "first answer"
---I am not sure how the badge works for merged questions
---I am not sure what happens if the first answer was deleted
; with firstanswer_cte AS
(
SELECT
  q.Id AS qid,  
  MIN(a.Id) AS aid
FROM Posts q INNER JOIN Posts a ON a.ParentId=q.Id
WHERE (q.PostTypeId=1) and (q.AnswerCount>0)
---only question which have at least one answer
GROUP BY q.Id, q.CreationDate
), enlightened_cte AS (SELECT
  q.Id As qid,
  q.AcceptedAnswerId AS aid  
FROM Posts q INNER JOIN firstanswer_cte f ON f.aid=q.AcceptedAnswerId
  INNER JOIN Posts a ON q.AcceptedAnswerId=a.Id
WHERE (q.PostTypeId=1) AND (q.AcceptedAnswerId IS NOT NULL) AND (a.Score>=10)
)

SELECT
  q.Id AS [Post Link], 
  a.Id AS [Post Link],
  e.aid AS [Post Link]
FROM Posts q INNER JOIN Posts a ON ((a.ParentId=q.Id) AND (a.OwnerUserId=##userid##))
INNER JOIN enlightened_cte e ON q.Id=e.qid
WHERE (a.Id<>e.aid)

fork query

edited apr 21

created apr 21 Martin20528