0

Please login or register to vote for this query.

(click on this box to dismiss)

Illuminator badge holders ranked by their effectiveness; percentage of their answers that qualify for the badge times the number of qualifying answers. This gives a better ranking for how effectively people have improved the original question (how often they edited the question compared to the number of answers they gave).

Q&A about the site for administrators, end users, developers and designers for Craft CMS

SELECT TOP 100
       AOwnerId AS [User Link],
       COUNT(*) AS [Total Answers],
       ct AS [Illuminating],
       FORMAT(ct / (count(a.Id) * 1.0), 'P') AS [I Rate],
       accept AS [Also Accepted],
       FORMAT(accept / (ct * 1.0), 'P') AS [IA Rate],
       FORMAT(ct * ct / (count(a.Id) * 1.0), 'N') AS [Weighting]
FROM  (SELECT a.OwnerUserId AOwnerId,
              count(*) AS ct,
              sum(CASE WHEN q.AcceptedAnswerId = a.Id THEN 1 ELSE 0 END) AS accept
       FROM   Posts q 
       JOIN   Posts a on a.ParentId = q.Id
       WHERE  q.PostTypeId = 1 
       AND    q.ClosedDate is null
       AND    a.PostTypeId = 2 
       AND    a.Score >= 1
       AND    IsNull(q.OwnerUserId, -1) <> a.OwnerUserId
       AND    EXISTS (
          SELECT 1 
          FROM   PostHistory ph 
          WHERE  ph.PostHistoryTypeId in (4,5) 
          AND    ph.PostId = a.ParentId 
          AND    ph.UserId = a.OwnerUserId 
          AND    abs(datediff(hh, ph.CreationDate, a.CreationDate)) < 12
          )
       GROUP  BY a.OwnerUserId
       HAVING count(*) >= 10
      ) ia
JOIN   Posts a ON a.OwnerUserId = ia.AOwnerId
GROUP  BY ia.AOwnerId, ia.ct, ia.accept
ORDER  BY ct * ct / (count(a.Id) * 1.0) DESC

fork query

edited aug 30 19 anonymous

created oct 2 14 Martijn Pieters