mongo find users were subscribed at least n days

Question

consider I have collection of user_events as follow :

[
  {
      "_id" : ObjectId("5be99dd1da8d4a596423a2d2"),
      "user_id" : 203302,
      "action" : "subscribe",
      "created_at" : ISODate("2018-11-12T15:35:45.000Z"),
  },
  {
      "_id" : ObjectId("5be99dd1da8d4a596423a2d2"),
      "user_id" : 203302,
      "action" : "unsubscribe",
      "created_at" : ISODate("2018-10-12T15:35:45.000Z"),
  },
  ...
]

I need to find users who were subscribed to our service for at least n days. each user can subscribe and unsubscribe for unlimited number of times. for example It is possible for user A to subscribe 100 times and unsubscribe 100 times.

as you can see my documents have a field called action. so in other words, I need to find records for a user with at least n day date interval.

so my result would be sth like this :

[

  {
    "user_id": 2,
    "max_subscription_days": 2
  },
  {
    "user_id(user A)": 5,
    "max_subscription_days": 3
  },
    {
    "user_id": 11,
    "max_subscription_days": 3
  }
]

but I need users who were part of service at least n days. consider user A subscribe to my service and after 3 days unsubscribe.

and next time user A subscribe to my service again and unsubscribe after 5 days. so for this user max subscription days is 5 .

my stack : mongodb : 4.0.0

php : 7.2

https://stackoverflow.com/questions/2943222/find-objects-between-two-dates-mongodb — ivanivan, Nov 12 '18 at 15:58
@ivanivan please read the question again. I don't want records between two specific time. I need users who were part of service for at least x ( e.x. 2 ) days — Majid Abdolhosseini, Nov 12 '18 at 16:13
Your expected output cannot be obtained from your sample collection. And with which field `max_subscription_days` can be produced? — Ashh, Nov 12 '18 at 16:28
sudo code : max_subscription_days = max(number_of_days(unsub_date - sub_date)) for each user — Majid Abdolhosseini, Nov 12 '18 at 16:32
@mhndev - example for comparisions of dates. Get today, subtract min age from it, find accounts older than that day. — ivanivan, Nov 12 '18 at 17:10
look I don't need today. consider there is a user that subscribed 4 days ago and unsubscribed 1 day ago. this user still counts in my query result. — Majid Abdolhosseini, Nov 12 '18 at 17:13

Behzadsh · Accepted Answer · 2018-11-13T11:36:36.240

I think this might be the aggregation you looking for:

db.user_events.aggregate([
    {
        $group: {
            _id: "$user_id",
            "events": {
                $push: {
                    $cond: {
                        if: { $eq: [ "$action", "subscribe" ] },
                        then: {"date":"$created_at", "event": "subscribe"},
                        else: {"date":"$created_at", "event": "unsubscribe"}
                    }
                }
            }
        }
    },
    {
        $project : {
            events: { $reverseArray : "$events" }
        }
    },
    {
        $project : {
            user_id: "$_id",
            max_subscription_days: {
                $reduce : {
                    input: "$events",
                    initialValue: {date: null, max: 0},
                    in : {
                        date: {
                            $cond: {
                                if: { $eq : ["$$this.event", "unsubscribe"] },
                                then : "$$this.date",
                                else : null
                            }
                        },
                        max: {
                            $cond: {
                                if: { $eq : ["$$this.event", "unsubscribe"] },
                                then : "$$value.max",
                                else : {
                                    $cond : {
                                        if : { $gt : [ { $divide: [ { $subtract: [ "$$value.date", "$$this.date" ] }, 24 * 60 * 60 * 1000] }, "$$value.max" ] },
                                        then : { $divide: [ { $subtract: [ "$$value.date", "$$this.date" ] }, 24 * 60 * 60 * 1000] },
                                        else : "$$value.max"
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }
    },
    {
        $match : { "max_subscription_days.max" : { $gt : n } }
    }
])

and the result would be like:

[
    {
        "_id" : 203302,
        "user_id" : 203302,
        "max_subscription_days" : 10.0
    },
    {
        "_id" : 203301,
        "user_id" : 203301,
        "max_subscription_days" : 4.0
    }
]

I tested with some sample documents, and it worked well. Hope it works for you.

Meena Pintu · Answer 2 · 2018-11-13T09:06:18.140

1

Aggregate function will return list of subscribe and unsubscribe events with date of a user_id

db.getCollection('').aggregate([
     {
       $group:
         {
           _id: "$user_id",

           "subscribe_unsubscribe" :
                        {
                          $push:
                            {
                                $cond: { if: { $eq: [ "$action", "subscribe" ] }, then: {"s":"$created_at"}, else: {"u":"$created_at"} }

                            }
                         }
         }
     }]
   )

Output will be like

[{
    "_id" : "3334",
    "subscribe_unsubscribe" : [ 
        {
            "s" : 2000-11-12 00:00:00.000Z
        }, 
        {
            "u" : 2000-11-13 00:00:00.000Z
        },
        {
            "s" : 2000-11-16 00:00:00.000Z
        }, 
        {
            "u" : 2000-11-20 00:00:00.000Z
        }
    ]
},
...
]

Now you can use server script to get max no of days. or you can write another pipeline operation which will get max difference of patterned consecutive elements.

Checkout mongodDB aggregate

edited Nov 13 '18 at 09:06

answered Nov 12 '18 at 16:27

Meena Pintu

162
1
9

look, your query result is records with type of subscription and event date is greater than specific date. but I need users who were part of service at least n days. consider user A subscribe to my service and after 3 days unsubscribe. and next time user A subscribe to my service again and unsubscribe after 5 days. so for this user max subscription days is 5 . – Majid Abdolhosseini Nov 12 '18 at 16:29
@mhndev , i've updated according to your need , hope it helps you .now server script calculations would be easy. Improvements are still there. – Meena Pintu Nov 13 '18 at 02:31
@mhndev , i'd suggest you to use cursor with this aggregator . because if you have suppose 10k users then there would be 10k (like example ) items . – Meena Pintu Nov 13 '18 at 09:04

score 0 · Answer 3 · answered Nov 13 '18 at 07:15

0

I believe it's more accurate if you handle each period after each unsubscribe action for a particular user. As an illustration, if user A does unsubscribe each time, you will calculate the last period of subscribing and update it on a subscription-user junction. I contend you can manage if you have more than one subscription per user and you always can track the exact time of each subscription for each customer.

However, you can track this time via a job which is run in an exact time slice.

answered Nov 13 '18 at 07:15

Moe Far

2,742
2
23
41

thank you, I think this works, but what should I do with my current data ? should I run a background job and update it ? – Majid Abdolhosseini Nov 13 '18 at 07:19
populating the spent time of each subscription could be done by a query, but for the next actions, I think it could be better to handle in your application. I will think on the query – Moe Far Nov 13 '18 at 07:25

mongo find users were subscribed at least n days

3 Answers3