db={"users":[{_id:ObjectId("000000000000000000000000"),name:"Alan Touring"},{_id:ObjectId("000000000000000000000001"),name:"Linus Torvalds"},{_id:ObjectId("000000000000000000000002"),name:"Brendan Eich"}],"games":[{_id:ObjectId("100000000000000000000000"),startingAt:ISODate("2023-10-01T18:00:00.000Z"),maxPlayers:10,players:[{userId:ObjectId("000000000000000000000000"),status:"CONFIRMED",preparedAt:ISODate("2023-09-15T00:00:00.000Z"),confirmedAt:ISODate("2023-09-15T00:01:00.000Z"),arrivedAt:ISODate("2023-10-01T17:45:00.000Z")},{userId:ObjectId("000000000000000000000002"),status:"CANCELED",preparedAt:ISODate("2023-09-15T00:00:00.000Z"),canceledAt:ISODate("2023-09-15T00:00:01.000Z")}]},{_id:ObjectId("100000000000000000000001"),startingAt:ISODate("2023-11-01T18:00:00.000Z"),maxPlayers:10,players:[{userId:ObjectId("000000000000000000000001"),status:"CONFIRMED",preparedAt:ISODate("2023-10-15T00:00:00.000Z"),confirmedAt:ISODate("2023-10-15T00:01:00.000Z"),arrivedAt:ISODate("2023-11-01T17:45:00.000Z")},{userId:ObjectId("000000000000000000000000"),status:"CANCELED",preparedAt:ISODate("2023-10-15T00:00:00.000Z"),canceledAt:ISODate("2023-10-15T00:01:00.000Z")}]},{_id:ObjectId("100000000000000000000002"),startingAt:ISODate("2023-12-01T18:00:00.000Z"),maxPlayers:10,players:[{userId:ObjectId("000000000000000000000001"),status:"CONFIRMED",preparedAt:ISODate("2023-11-15T00:00:00.000Z"),confirmedAt:ISODate("2023-11-15T00:01:00.000Z"),arrivedAt:ISODate("2023-12-01T17:45:00.000Z")},{userId:ObjectId("000000000000000000000000"),status:"CONFIRMED",preparedAt:ISODate("2023-11-15T00:00:00.000Z"),confirmedAt:ISODate("2023-11-15T00:01:00.000Z"),arrivedAt:ISODate("2023-12-01T17:45:00.000Z")}]}]}

db.games.aggregate([{$match:{_id:ObjectId("100000000000000000000002")}},{$unwind:"$players"},{$match:{"players.status":"CONFIRMED"}},{"$lookup":{"from":"games","localField":"players.userId","foreignField":"players.userId","as":"gamesByUser"}},{$unwind:"$gamesByUser"},{$unwind:"$gamesByUser.players"},{$match:{$expr:{$eq:["$gamesByUser.players.userId","$players.userId"]},"gamesByUser.players.status":"CONFIRMED"}},{$group:{_id:"$gamesByUser.players.userId",numberOfGames:{$sum:1},firstGame:{$min:{date:"$gamesByUser.startingAt",gameId:"$gamesByUser._id"}},lastGame:{$max:{date:"$gamesByUser.startingAt",gameId:"$gamesByUser._id"}}}}])