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

---First to answer and accepted with score of 10 or more. This badge can be awarded multiple times.
---The results for users who are not deleted can be compared with: https://data.stackexchange.com/mathoverflow/query/1175429/users-with-most-badges-of-certain-kind?num=100&name=Enlightened
with answered_cte AS
(
SELECT
  q.Id AS qid,
  q.CreationDate AS created,
  MIN(a.CreationDate) AS answered
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
)

SELECT TOP(##num?100##)
  RANK() OVER(ORDER BY COUNT(p.Id) DESC) AS Rank,
  p.OwnerUserId AS [User Link],
  p.OwnerDisplayName,
  COUNT(p.Id) AS [Count]
FROM answered_cte a INNER JOIN Posts p ON p.CreationDate=a.answered
---only look at the first answer
  INNER JOIN Posts q ON (q.Id=p.ParentId) AND (q.Id=a.qid)
  INNER JOIN Votes v ON (v.PostId=p.Id) and (v.VoteTypeId=1)
---only accepted answers  
WHERE 
 ---(p.Id=q.AcceptedAnswerId)  --acepted answer 
 ---AND 
  (p.Score>=##min?10##) --score at least 10
GROUP BY p.OwnerUserId, p.OwnerDisplayName
ORDER BY COUNT(p.Id) DESC

fork query

created nov 14 21 Martin20528