Ok, since I couldn’t assume that there would be some sign-ups every day, I put a $unionWith
towards the front to make sure every date over the last 30 was represented, combining it with signups
var start_date = ISODate("2022-08-01T06:26:27.926Z");
db.signups.aggregate([
{$set:{num:1}}, /* count up each sign-up this many times */
{$unionWith:{ /* generate a document for each date over the last
30 days with 0 signups to ensure no dates are missed */
pipeline:[
{$documents:{$map:{
input:{$range:[0,30]},
in:{ createdAt:{$dateAdd:{
unit:"day",
amount:"$$this",
startDate: start_date
}}}
}}}
]}},
{$group:{
_id:{ $dateTrunc:{date:"$createdAt",unit:"day"}},
count:{$sum:"$num"}
}},
{$setWindowFields:{ /* count cumulative signups over last 30 days "window" */
sortBy:{_id:1},
output:{signups:{
$sum:"$count",
window: {range:[-30,"current"],
unit:"day"
}}}}},
{$lookup:{
from:"active_history",
let:{date:"$_id",date30:{$dateSubtract:{startDate:"$_id",amount:30, unit:"day"}}},
as:"activity",
pipeline:[
{$match:{$expr:{$and:[{$gte:["$openedAt","$$date30"]},{$lte:["$openedAt","$$date"]}]}}},
{$group:{_id:"$userId"}},
{$count:"activeUsers"}]}},
{$unwind:"$activity"})
With a small amount of sample data the output would look something like:
{ "_id" : ISODate("2022-08-02T00:00:00Z"), "count" : 0, "signups" : 0, "activity" : { "activeUsers" : 1 } }
{ "_id" : ISODate("2022-08-03T00:00:00Z"), "count" : 0, "signups" : 0, "activity" : { "activeUsers" : 1 } }
{ "_id" : ISODate("2022-08-04T00:00:00Z"), "count" : 0, "signups" : 0, "activity" : { "activeUsers" : 1 } }
{ "_id" : ISODate("2022-08-05T00:00:00Z"), "count" : 0, "signups" : 0, "activity" : { "activeUsers" : 1 } }
{ "_id" : ISODate("2022-08-06T00:00:00Z"), "count" : 2, "signups" : 2, "activity" : { "activeUsers" : 1 } }
{ "_id" : ISODate("2022-08-07T00:00:00Z"), "count" : 1, "signups" : 3, "activity" : { "activeUsers" : 3 } }
{ "_id" : ISODate("2022-08-08T00:00:00Z"), "count" : 2, "signups" : 5, "activity" : { "activeUsers" : 3 } }
{ "_id" : ISODate("2022-08-09T00:00:00Z"), "count" : 3, "signups" : 8, "activity" : { "activeUsers" : 3 } }
So for each day you have number of users who signed up that day (count), cumulative over previous 30 days (signups), and unique users who had activity over previous 30 days. Should be easy to transform that to whatever format you want.
Asya
P.S. I assumed all dates would be ISODate
types.