Is it possible to get a report on post approvals?

feature
7

For the record, here are the queries I’ve come up with using the data explorer plugin. (I’d appreciate code review on these.)

Approvals and rejections by moderator in the last 30 days:

SELECT count(q.id), username, state
FROM queued_posts q
JOIN users u on q.approved_by_id = u.id
  OR q.rejected_by_id = u.id
WHERE queue='default'
  AND q.created_at > now() - interval '30 days'
GROUP BY username, state
ORDER BY username

Average time to approval:

SELECT sum(approved_at - created_at)/count(id)
FROM queued_posts
WHERE queue='default' and state = 2
  AND created_at > now() - interval '30 days'

Average time to rejection:

SELECT sum(rejected_at - created_at)/count(id)
FROM queued_posts
WHERE queue='default' and state = 3
  AND created_at > now() - interval '30 days'

For us, we had a 37 minute time to approval and a 56 minute time to rejection in the last 30 days. (I suspect that rejection time may be longer because sometimes when moderators see a questionable post, they may decide to pass the buck and leave it in the queue for another moderator.)

8 Likes