There isn't going to be one query, unless you UNION ALL together every An and Dn table of every user in the database instance, joining in the states and state_lineages table. It would be an exceedingly ugly query, probably involving at least two WITH clauses (because UPDATEs are a combination of both Add and Delete, they would have to be queried separately, then conjoined in an additional query.
This would take me a day or two to work out, and I've been a SDBE/SDE/ArcSDE/geodatabase subject matter expert for thirty years. If you want to go down this path, you'll need to provide what you have so far. You'll need to compile a list of all the versioned tables, queries that generate lists of table_name, state, An/Dn row and impacted objectid and a query that can summarize INSERT/UPDATE/DELETE events by version-table.
It might be easier to run all those queries from Python, and collate there, but it's still an ambitious task.
- V
