mongo find users were subscribed at least n days
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
php mongodb mongodb-query
asked Nov 12 at 15:44
mhndev
6331024
|
show 1 more comment
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
php mongodb mongodb-query
asked Nov 12 at 15:44
mhndev
6331024
1
stackoverflow.com/questions/2943222/…
– ivanivan
Nov 12 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
– mhndev
Nov 12 at 16:13
Your expected output cannot be obtained from your sample collection. And with which fieldmax_subscription_dayscan be produced?
– Anthony Winzlet
Nov 12 at 16:28
sudo code : max_subscription_days = max(number_of_days(unsub_date - sub_date)) for each user
– mhndev
Nov 12 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 at 17:10
|
show 1 more comment
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
php mongodb mongodb-query
asked Nov 12 at 15:44
mhndev
6331024
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
php mongodb mongodb-query
php mongodb mongodb-query
asked Nov 12 at 15:44
mhndev
6331024
asked Nov 12 at 15:44
mhndev
6331024
edited Nov 12 at 17:18
asked Nov 12 at 15:44
mhndev
6331024
asked Nov 12 at 15:44
mhndev
6331024
asked Nov 12 at 15:44
mhndev
6331024
6331024
1
stackoverflow.com/questions/2943222/…
– ivanivan
Nov 12 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
– mhndev
Nov 12 at 16:13
Your expected output cannot be obtained from your sample collection. And with which fieldmax_subscription_dayscan be produced?
– Anthony Winzlet
Nov 12 at 16:28
sudo code : max_subscription_days = max(number_of_days(unsub_date - sub_date)) for each user
– mhndev
Nov 12 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 at 17:10
|
show 1 more comment
1
stackoverflow.com/questions/2943222/…
– ivanivan
Nov 12 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
– mhndev
Nov 12 at 16:13
Your expected output cannot be obtained from your sample collection. And with which fieldmax_subscription_dayscan be produced?
– Anthony Winzlet
Nov 12 at 16:28
sudo code : max_subscription_days = max(number_of_days(unsub_date - sub_date)) for each user
– mhndev
Nov 12 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 at 17:10
1
1
stackoverflow.com/questions/2943222/…
– ivanivan
Nov 12 at 15:58
stackoverflow.com/questions/2943222/…
– ivanivan
Nov 12 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
– mhndev
Nov 12 at 16:13
@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
– mhndev
Nov 12 at 16:13
Your expected output cannot be obtained from your sample collection. And with which field
max_subscription_days can be produced?– Anthony Winzlet
Nov 12 at 16:28
Your expected output cannot be obtained from your sample collection. And with which field
max_subscription_days can be produced?– Anthony Winzlet
Nov 12 at 16:28
sudo code : max_subscription_days = max(number_of_days(unsub_date - sub_date)) for each user
– mhndev
Nov 12 at 16:32
sudo code : max_subscription_days = max(number_of_days(unsub_date - sub_date)) for each user
– mhndev
Nov 12 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 at 17:10
@mhndev - example for comparisions of dates. Get today, subtract min age from it, find accounts older than that day.
– ivanivan
Nov 12 at 17:10
|
show 1 more comment
3 Answers
3
active
oldest
votes
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.
answered Nov 13 at 8:52
Behzadsh
354624
add a comment |
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
answered Nov 12 at 16:27
Meena Pintu
1357
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 .
– mhndev
Nov 12 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 at 2: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 at 9:04
add a comment |
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 at 7:15
Moe Far
2,32421633
thank you, I think this works, but what should I do with my current data ? should I run a background job and update it ?
– mhndev
Nov 13 at 7: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 at 7:25
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53265536%2fmongo-find-users-were-subscribed-at-least-n-days%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
answered Nov 13 at 8:52
Behzadsh
354624
add a comment |
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.
answered Nov 13 at 8:52
Behzadsh
354624
add a comment |
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.
answered Nov 13 at 8:52
Behzadsh
354624
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.
answered Nov 13 at 8:52
Behzadsh
354624
edited Nov 13 at 11:36
answered Nov 13 at 8:52
Behzadsh
354624
answered Nov 13 at 8:52
Behzadsh
354624
answered Nov 13 at 8:52
Behzadsh
354624
354624
add a comment |
add a comment |
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
answered Nov 12 at 16:27
Meena Pintu
1357
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 .
– mhndev
Nov 12 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 at 2: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 at 9:04
add a comment |
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
answered Nov 12 at 16:27
Meena Pintu
1357
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 .
– mhndev
Nov 12 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 at 2: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 at 9:04
add a comment |
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
answered Nov 12 at 16:27
Meena Pintu
1357
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
answered Nov 12 at 16:27
Meena Pintu
1357
edited Nov 13 at 9:06
answered Nov 12 at 16:27
Meena Pintu
1357
answered Nov 12 at 16:27
Meena Pintu
1357
answered Nov 12 at 16:27
Meena Pintu
1357
1357
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 .
– mhndev
Nov 12 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 at 2: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 at 9:04
add a comment |
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 .
– mhndev
Nov 12 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 at 2: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 at 9:04
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 .
– mhndev
Nov 12 at 16:29
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 .
– mhndev
Nov 12 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 at 2:31
@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 at 2: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 at 9:04
@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 at 9:04
add a comment |
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 at 7:15
Moe Far
2,32421633
thank you, I think this works, but what should I do with my current data ? should I run a background job and update it ?
– mhndev
Nov 13 at 7: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 at 7:25
add a comment |
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 at 7:15
Moe Far
2,32421633
thank you, I think this works, but what should I do with my current data ? should I run a background job and update it ?
– mhndev
Nov 13 at 7: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 at 7:25
add a comment |
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 at 7:15
Moe Far
2,32421633
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 at 7:15
Moe Far
2,32421633
answered Nov 13 at 7:15
Moe Far
2,32421633
answered Nov 13 at 7:15
Moe Far
2,32421633
answered Nov 13 at 7:15
Moe Far
2,32421633
2,32421633
thank you, I think this works, but what should I do with my current data ? should I run a background job and update it ?
– mhndev
Nov 13 at 7: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 at 7:25
add a comment |
thank you, I think this works, but what should I do with my current data ? should I run a background job and update it ?
– mhndev
Nov 13 at 7: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 at 7:25
thank you, I think this works, but what should I do with my current data ? should I run a background job and update it ?
– mhndev
Nov 13 at 7:19
thank you, I think this works, but what should I do with my current data ? should I run a background job and update it ?
– mhndev
Nov 13 at 7: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 at 7:25
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 at 7:25
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53265536%2fmongo-find-users-were-subscribed-at-least-n-days%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
stackoverflow.com/questions/2943222/…
– ivanivan
Nov 12 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
– mhndev
Nov 12 at 16:13
Your expected output cannot be obtained from your sample collection. And with which field
max_subscription_dayscan be produced?– Anthony Winzlet
Nov 12 at 16:28
sudo code : max_subscription_days = max(number_of_days(unsub_date - sub_date)) for each user
– mhndev
Nov 12 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 at 17:10