I want to find last the number of maximum consecutive number of days for user id.

user_id     active_date
------------------------------------------      
| 1       | 2022-01-18 
| 1       | 2022-01-17 
| 1       | 2022-01-16 <---- Gap here | so user 1 last 3 days active
| 1       | 2022-01-14 



user_id     active_date
------------------------------------------      
| 2       | 2022-01-18 
| 2       | 2022-01-15 <---- Gap here | so user 2 last 1 days active
| 2       | 2022-01-14 
| 2       | 2022-01-13 

CodePudding user response：

first convert date to numeric value using $toLong

then using $setWindowFileds extract the user active_days ranges

mongoplayground

db.collection.aggregate([
  {
    "$addFields": {
      "active_date": {
        "$toLong": "$active_date"
      }
    }
  },
  {
    $setWindowFields: {
      partitionBy: "$user_id",
      sortBy: {
        active_date: 1
      },
      output: {
        days: {
          $push: "$active_date",
          window: {
            range: [
              -86400000, // one day in millisecond
              0
            ]
          }
        }
      }
    }
  },
  {
    "$set": {
      "days": {
        "$cond": [
          {
            "$gt": [
              {
                "$size": "$days"
              },
              1
            ]
          },
          0,
          1
        ]
      }
    }
  },
  {
    $setWindowFields: {
      partitionBy: "$user_id",
      sortBy: {
        active_date: 1
      },
      output: {
        count: {
          $sum: "$days",
          window: {
            documents: [
              "unbounded",
              "current"
            ]
          }
        }
      }
    }
  },
  {
    "$group": {
      "_id": {
        user_id: "$user_id",
        count: "$count"
      },
      "active_days": {
        $sum: 1
      },
      "to": {
        "$max": "$active_date"
      },
      "from": {
        "$min": "$active_date"
      }
    }
  }
])

and at the end get the latest active_day range by adding these two stages:

{
  "$sort": {
    to: -1
  }
},
{
  "$group": {
    "_id": "$_id.user_id",
    "last_active_days": {
      "$first": "$active_days"
    }
  }
}