MongoDB Aggregate: Find amount of intersecting date ranges with each month

Question

Problem

I want to do a task using only MongoDB aggregate, without additional code. This has to do with the environment we are running the aggregate.

We have a users collection. In our app, users can be created and deleted. The createdAt field has the date when the user was created, and the deletedAt field has the date the user was deleted.

I want to create an aggregate that is able to calculate the amount of active (not deleted) different users we had per month, starting from the month the first user was created, up to now.

In more nerdy words, I want to find the amount of date ranges, each date range starting from createdAt and ending at deletedAt, that intersect with each month, starting from the month the first user was created. We want to avoid inputting each month manually, since it would ruin the automation we want to make, so we need to get the months programmatically, so the aggregation will work now and in the future.

Example

Let's assume my collection users has these three entries:

{
    "_id" : ObjectId("5fbf2996846471279461asd87"),
    "createdAt" : ISODate("2023-07-26T04:05:36.314Z"),
    "deletedAt" : ISODate("2023-11-15T07:06:36.314Z"),
}, {
    "_id" : ObjectId("6fbf2996846471279461asd87"),
    "createdAt" : ISODate("2023-09-05T09:05:36.314Z"),
}, {
    "_id" : ObjectId("7fbf2996846471279461asd87"),
    "createdAt" : ISODate("2023-10-14T04:05:36.314Z"),
    "deletedAt" : ISODate("2023-10-15T07:06:36.314Z"),
},

So I'd expect the output to be something like that:

{
  "month": "2023-07",
  "count": 1,
}, {
  "month": "2023-08",
  "count": 1,
}, {
  "month": "2023-09",
  "count": 2,
}, {
  "month": "2023-10",
  "count": 3,
}, {
  "month": "2023-11",
  "count": 2,
}, {
  "month": "2023-12",
  "count": 1,
}, {
  "month": "2024-01",
  "count": 1,
},

Any help is appreciated!

Thanks!

Answer 1

One option is to use the date operators with $range to create a list of months dates for each document:

db.collection.aggregate([
  {$project: {
      months: {
        $let: {
          vars: {
            startMonth: {$dateTrunc: {date: "$createdAt", unit: "month"}},
            endMonth: {$dateTrunc: {date: {$ifNull: ["$deletedAt", "$$NOW"]}, unit: "month"}}
          },
          in: {$map: {
              input: {$range: [
                  0,
                  {$add: [
                      {$dateDiff: {startDate: "$$startMonth", endDate: "$$endMonth", unit: "month"}},
                      1
                  ]}
              ]},
              in: {$dateToString: {
                  date: {$dateAdd: {
                      startDate: "$$startMonth", unit: "month", amount: "$$this"
                  }},
                  format: "%Y-%m"
              }}
            }
          }
        }
      }
  }},
  {$unwind: "$months"},
  {$group: {_id: "$months", count: {$sum: 1}}},
  {$sort: {_id: 1}},
  {$project: {createdAt: "$_id", count: 1, _id: 0}}
])

See how it works on the playground example