Counts how many edits a user has, broken into Question and Answer edits. Also shows how close you are to getting each editor badge.
Q&A for administrators and users of the CiviCRM Constituent Relationship Management software
-- How many edits do I have? -- Counts how many edits a user has, broken into Question and Answer edits. -- Also shows how close you are to getting each editor badge. WITH qaedits AS ( SELECT ( SELECT COUNT(*) FROM Posts WHERE PostTypeId = 1 AND LastEditorUserId = Users.Id ) AS QuestionEdits, ( SELECT COUNT(*) FROM Posts WHERE PostTypeId = 2 AND LastEditorUserId = Users.Id ) AS AnswerEdits FROM Users WHERE Id = ##UserId## ), edits AS ( SELECT QuestionEdits, AnswerEdits, QuestionEdits + AnswerEdits AS TotalEdits FROM qaedits ) SELECT QuestionEdits, AnswerEdits, TotalEdits, CASE WHEN TotalEdits >= 1 THEN 'Received' ELSE '0%' END AS EditorBadge, CASE WHEN TotalEdits >= 100 THEN 'Received' ELSE Cast(TotalEdits AS varchar) + '%' END AS StrunkAndWhiteBadge, CASE WHEN TotalEdits >= 600 THEN 'Received' ELSE Cast(TotalEdits / 6 AS varchar) + '%' END AS CopyEditorBadge FROM edits