Is it possible to get a report on post approvals?

feature
12

Approvals and rejections by moderator in the last 30 days:

SELECT username, r.status, count(r.id)
FROM reviewables r
JOIN (
  SELECT max(h.id) as history_id, reviewable_id
  FROM reviewable_histories h
  GROUP BY reviewable_id
) latest
  ON r.id = latest.reviewable_id
JOIN reviewable_histories h
  ON h.id = latest.history_id
JOIN users u
  ON h.created_by_id = u.id
WHERE r.type = 'ReviewableQueuedPost'
AND r.created_at > now() - interval '30 days'
GROUP BY username, r.status
ORDER BY username, r.status

Average time to approval/rejection:

SELECT sum(h.created_at - r.created_at)/count(r.id), r.status
FROM reviewables r
JOIN (
  SELECT max(h.id) as history_id, reviewable_id
  FROM reviewable_histories h
  GROUP BY reviewable_id
) latest
  ON r.id = latest.reviewable_id
JOIN reviewable_histories h
  ON h.id = latest.history_id
WHERE r.type = 'ReviewableQueuedPost'
AND r.created_at > now() - interval '30 days'
GROUP BY r.status
ORDER BY r.status
4 Likes