The first step in the query is to count all events and list the unique values of field 'd' for all usernames in old_index. The second step is to count all events for each username in new_index. Finally, the two sets of results are merged based on common username values. If there is no match, the count from new_index is retained.
Here's one alternative query that should be more efficient.
| stats count as old_count values(d) as d by username
| append [search index=new_index | stats count as new_count by username ]
| stats values(*) as * by username
If this reply helps you, Karma would be appreciated.