0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

-- Lists all users with more rejected than approved edits, 
-- sorted by percentage rejected of total.

-- Try: MinimumRejected = 10

SELECT r.OwnerUserId AS [User ID], u.DisplayName AS [Username],
       r.RejectionCount AS [Rejection Count], 
       ISNULL(a.ApprovedCount,0) AS [Approved Count], 
       r.RejectionCount*100/(ISNULL(a.ApprovedCount,0)+r.RejectionCount) AS [Percentage Rejected]
FROM 
    -- Approved edits.
    (
        SELECT OwnerUserId, COUNT(OwnerUserId) AS RejectionCount
        FROM SuggestedEdits
        WHERE OwnerUserId IS NOT NULL
          AND RejectionDate IS NOT NULL
        GROUP BY OwnerUserId
    ) r
LEFT JOIN 
    -- Rejected edits.
    (
        SELECT OwnerUserId, COUNT(OwnerUserId) AS ApprovedCount
        FROM SuggestedEdits
        WHERE OwnerUserId IS NOT NULL
          AND ApprovalDate IS NOT NULL
        GROUP BY OwnerUserId
    ) a 
ON r.OwnerUserId = a.OwnerUserId
LEFT JOIN Users u ON r.OwnerUserId = u.Id

WHERE r.OwnerUserId = 2801037

ORDER BY [Percentage Rejected] DESC, r.RejectionCount DESC

fork query

created aug 7 14 TheBanana