Producing a chart of total membership over time given joining an leaving dates

[John_Sewell]

I found something similar here:

How project all day between two date?

First, having date as string rather than the data data type is not a good idea. String takes more space, are slower to compare and you cannot use the rich date API. What you are trying to do is probably easier to do in your application code rather that try to do it on the server. Also doing on the server will increase the data transferred from the server to the application. To transform an array from one form to the other you need to use $map. The issue you have is to generate all the days …

and

How create an array of dates?

It seems that is related to the number of days between the 2 dates rather than the month/year crossing. Here is a slightly modified algorithm based on the same principals as the one posted. The difference is that rather than having a range based on the millisecs between the 2 dates, it creates a range of the number of days between the 2 dates. startDate: new ISODate("2014-06-01") endDate:new ISODate("2014-07-22") one_day = 24 * 60 * 60 * 1000 set_start_end = { "$set" : { startDate , endDate }…

And the answer by @ Asya_Kamsky or
@ steevej would do what you wanted.

I took it apart slightly to re-work it for my understanding, but in effect you want to create a list of days that each member was active, you have their start and end date, so if member 1 was active from 1 Jan 2023 to 5 Jan 2023 you want to get a list of days:

1 Jan 2023
2 Jan 2023
3 Jan 2023
4 Jan 2023
5 Jan 2023

And if member 2 was active from the 3rd to 4th:
3 Jan 2023
4 Jan 2023

Combining this data you get
1 Jan 2023
2 Jan 2023
3 Jan 2023
3 Jan 2023
4 Jan 2023
4 Jan 2023
5 Jan 2023

And grouping by date you get membership per day:
1 Jan 2023 : 1
2 Jan 2023 : 1
3 Jan 2023 : 2
4 Jan 2023 : 2
5 Jan 2023 : 1

The way this is done in the linked example was to use the $range and $map operators to build a list of days to add to each start date to generate a list of days they are active.
If you subtract the start from end, you get time active, which you can then convert to days, you then map over this, adding each to the start date to build an array of days that the user was a member.

If you then $unwind the array and group all data up, you get a collated list of days that a user was active.

Mongo playground

You can simplify the above, but I spread things out a bit to let me run it in stages to check each stage.

So we start with:

Then calculate the days

Now the map and range operators blow the days into an array of days each user is active:

The $unwind turns this into a list of dates that a user was active:

Finally we can group this up, counting how many users were active on each date:

With a lot of members who are members are active for a long time this could get big quickly, you could re-calculate on a periodic basis and exclude members who have left before the start calculation point to just calculate it weekly or something.

Also I didn’t take into account partial days or limits on the end, so you would need to adjust for your requirements.

You didn’t post actual documents so other things to take away from the other posts are to STORE DATES AS DATES! And when doing operations on dates, remember that you need to use a scalar to the period of interest, be it days, hours, minute, seconds or ms etc.