0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for information security professionals

SELECT 
  A.OwnerUserId AS [User Link],
  A.*,
  (100 * A.Approved) / A.Edits AS ApprovalRate,
  COUNT(CASE WHEN p.PostTypeId = 1 THEN 1 ELSE NULL END) AS Questions,
  COUNT(CASE WHEN p.PostTypeId = 2 THEN 1 ELSE NULL END) AS Answers
FROM 
-- SELECT TOP 100 *, (100 * A.Approved) / A.Edits AS ApprovalRate FROM 
(
  SELECT se.OwnerUserId, u.LastAccessDate, COUNT(*) AS Edits, u.Reputation,
  COUNT(CASE WHEN se.ApprovalDate IS NOT NULL THEN 1 ELSE NULL END) AS Approved,
  COUNT(CASE WHEN se.RejectionDate IS NOT NULL THEN 1 ELSE NULL END) AS Rejected
  FROM SuggestedEdits se
  LEFT JOIN Users u ON se.OwnerUserId = u.Id
  WHERE (se.ApprovalDate IS NOT NULL OR se.RejectionDate IS NOT NULL)
  AND u.Reputation < 2000
  GROUP BY se.OwnerUserId, u.Reputation, u.LastAccessDate
) A
  LEFT JOIN Posts p ON A.OwnerUserId = p.OwnerUserId
WHERE 
  A.Edits > ##MinEdits:Int?100## AND 
  (100 * A.Approved) / A.Edits > ##ApprovalRate:Int?90## AND
  A.Reputation - 2 * A.Approved > ##MinRepFromQA:Int?0##
  GROUP BY A.OwnerUserId, A.LastAccessDate, A.Edits, A.Reputation, A.Approved, A.Rejected

fork query

edited oct 30

created oct 29 PeterJames