The reasoning behind this is when the Opportunity Owner is changed and the split associated with that owner is removed manually from UI after the Opportunity is Closed-Won, the Split of the previous Owner will still continue to exist with the split percentage of 100 even though it's not visible in the UI.
And with that, the new split record for the new Split ...
Did you ever find a resolution?
While I can't reproduce your exact issue above, I am noticing that deleting an opportunity split through the related list UI is causing behavior as if the record was never deleted.
(A flow's criteria is being triggered by the supposedly deleted record)
It appears that the issue is hidden somewhere inside the serialization methods. Therefore, we are unable to properly localize it.
The query works when the Id alias is not used. But that mean that this approach cannot be used to initialize Map of AgregateResults by the aliased field.
If you haven't read through it already How do I bulkify queries for directly or indirectly related records is a good place to start.
The general idea is that you need to first gather the data that you're going to use in the WHERE clause of your query (using a loop) and then perform your query (outside of all loops). You can then iterate over the results.
Gather leads' emails in Trigger.
Query all leads with these emails.
Store them in map which connects lead email with leads' ids.
Run through your leads from Trigger once more: get stored leads ids related to current lead email, set duplicate links (check if current lead id does not equal to duplicate lead id).
Set<String> emails = new Set<String>...
This is not readily available information. Multiple Automation Studio activities can populate a Data Extension -- Imports, Queries, Filter Definitions, Filter Activities.
There's no way to search through each Automation without using the SOAP API or (WSProxy) to build some sort of inventory.