MongoDB complex aggregation

Question

I have documents with the following structure in my database:

{
    "reading_ts": ISODate(...),
    "points": 2.3,
    "user_id": 2
}

I will have more documents like this for each user_id per day...millions of them... I would like to implement the following aggregation:

1. Get the data for one month
2. Group the data for each user_id
3. Group the data for each day (so I will have the data for each day, for each user_id)
4. Get the max 'points' for each user for each day
5. Count how many users has the max points under value 10, how many between 10 and 20 and how many over 20

I can do the step 1 with a $match I can do the step 3 using this:

{
    "$group": {
        "_id": {
            "$subtract": [
                "$reading_ts",
                {
                    "$mod": [
                        {
                            "$toLong": "$reading_ts"
                        },
                        (1000 * 60 * 60 * 24)
                    ]
                }
            ]
        }
    }
}

The problem is that I don't know how to merge the steps 2 and 3 for now.

Answer 1

You can combine the steps 2,3 and 4 in a single $group stage using $dayOfMonth and $max to get the max 'points' for each user for each day.

Then, you can count the users by buckets using the $bucket operator with boudaries set to [0, 10, 20]:

db.collection.aggregate([
  {
    "$match": {
      "reading_ts": {
        "$gte": ISODate("2019-01-01"),
        "$lte": ISODate("2019-01-30")
      }
    }
  },
  {
    "$group": {
      "_id": {
        "user": "$user_id",
        "day": {
          "$dayOfMonth": "$reading_ts"
        }
      },
      "max": {
        "$max": "$points"
      }
    }
  },
  {
    "$bucket": {
      "groupBy": "$max",
      "boundaries": [
        0,
        10,
        20
      ],
      "default": 20,
      "output": {
        "users": {
          "$sum": 1
        },

      }
    }
  }
])

try it online: mongoplayground.net/p/jzZdz2V7R4-