The events table contains a list of people accessing objects. There is an event logged every time a person accesses an object which points to both the person and the object accessed. There would also be a timestamp (left out here for simplicity).
The aim of the query is to get a report that lists how many times each person accessed any object. The trick is that I only want to count the most recent person to access any particular object.
For example if the event table contained:
+----+-----------+-----------+
| id | object_id | person_id |
+----+-----------+-----------+
| 1 | 1 | 1 |
| 2 | 2 | 2 |
| 3 | 1 | 2 |
+----+-----------+-----------+
The output would be:
+-----------+-----------+
| person_id | count(id) |
+-----------+-----------+
| 2 | 2 |
+-----------+-----------+
This is because even though person 1 accessed object 1, person 2 also accessed object 1 more recently. So only person 2 gets the credit.
The sub-query gets a list containing the most recent person to access each object (The group by object_id makes sure only the most recent event for a given object is shown). Then the the outer query totals this for each person to give the final report.
I am trying to find a way to optimize the sub-query or rewrite the query entirely if there is a better way of getting the same results.