Having Trouble to Write MongoDB Query for Multiple Collections

I am trying to rank the affiliates based on the amount their referrals has played .Here are the schema. The 'ID' are all UUID identifiers.Userslet userSchema = new Schema({ _id: { type: 'object', value: { type: 'Buffer' }, default: () => MUUID.v4() }, username: { type: String, required: true }, dateCreated: { type: Date, required: true, default: Date.now } }); AffiliatesNetworklet affiliatesNetwork = new Schema({ _id: { type: 'object', value: { type: 'Buffer' }, default: () => MUUID.v4() }, mainUserUUID: { type: 'object', value: { type: 'Buffer' }, required: true }, referralUUID: { type: 'object', value: { type: 'Buffer' }, required: true }, dateCreated: { type: Date, required: true, default: Date.now } }); Playlet playSchema = new Schema({ _id: { type: 'object', value: { type: 'Buffer' }, default: () => MUUID.v4() }, userUUID: { type: 'object', value: { type: 'Buffer' }, required: true }, amountPlayed: { type: Number, required: true }, dateCreated: { type: Date, required: true, default: Date.now } }); I have a system where if a user referred another players, a mongoDB document will be created to show the relationship in the 'AffiliatesNetwork' collection. The user will be identified as 'mainUserUUID' and his/her referral will be as 'referralUUID'. To know their username, we can use these UUID to query the 'users' collections to find their username.When anyone play a game, a document in the 'Play' collection will be created to record the amount the user played. To know this username, we again can use this userUUID and query the 'Users' collection.Keep in mind that not all players will need to be referred, and some don't have referred when they directly sign up on the site.The questions is:I am now trying to rank the users based on how much amount their referrals played the game. The ranking is a table showing Top 10 users, where each row show the username, the number of referrals they got, and the total amount their referrals has played. The user where his/her referrals played the most amount will be ranked first, followed by second, third and so on.I tried to aggregrate the 'Play' and sort by amountPlayed, and then use the userUUID to find who referred this player in the 'AffiliateNetwork'. Finally, I have to loop each of the Top 10 document with the 'Users' to find their username. But it seems not very efficient and slow. I wonder if there is better way to do so.

Submitted August 15, 2020 at 10:11AM by VickNicks