Building time-bound leaderboard in Node.JS and MongoDB

Question

We have a document for each user. We award them "oranges" (in-game points) for completing activities.

We store the points given like this:

We have their epoch time along with the oranges earned. We currently have the overall totalOranges to build an all-time leaderboard.

However, we are struggling to identify how to make a 24-hour/past-7-days leaderboard. We can calculate for each user, but the reads would be insane (10k+ users). Is there a smarter way to go about it?

Answer 1

To do calculations on an object based on dynamic keys the easiest way is to convert the object to an array format using $objectToArray and then process the array

steps i used

1. Convert the object field to an array, and $filter the array to get an array of elements where the timestamp is within last 7(or n) days. This is now the input array to $reduce
2. $reduce can be used to sum the array. save the sum in a separate field
3. sort by the new field

db.collection.aggregate([
  {
    $addFields: {
      inbetweenTimePeriod: {
        $reduce: {
          input: {
            $filter: {
              input: { $objectToArray: "$sectional" },
              as: "item",
              cond: {
                $gte: [
                  { $toLong: "$$item.k" },
                  {
                    $divide: [ { $subtract: [ { $toLong: "$$NOW" }, 604800000 ] }, 1000 ]
                    //(epoch now in ms - 7days in ms) in seconds
                  }
                ]
              }
            }
          },
          initialValue: 0,
          in: { $add: [ "$$value", "$$this.v" ] }
        }
      }
    }
  },
  { $sort: { inbetweenTimePeriod: -1 } }
])

playground