You can use the tickets_logs table to report on tickets which have been reopened after an Agent or User has assigned the Resolved status.
The query below will genetate a list of reopened ticket IDs with the subject, agent name and user name.
SELECT tickets_logs.ticket.subject, tickets_logs.ticket.agent, tickets_logs.ticket.person.name
WHERE tickets_logs.action_type = 'changed_status' AND tickets_logs.id_before = '200' AND tickets_logs.id_after <> '210'
GROUP BY tickets_logs.ticket.id
In this example the id 200 represents resolved. Hence the status changed being measured is from 'Resolved' to another status.
Handling Archiving in the query
You can see we've also used the ID 210 in the query. This actually represents the archived status.
Archiving is an optional feature but if you have it enabled you would need to ensure you have tickets moved from Resolved -> Archived excluded from the stat as this would not constitute a reopen.
Hence we use the tickets.id_after function alongside the does not equal operator <> to exclude them tickets_logs.id_after <> '210'