Data mongo queries

MongoQueries.md

Mongo Queries

[x] a. How many smart contacts does my user have?

[x] b. How many people do they speak to on average?

[x] c. What is the percentage of outgoing calls they make?

[x] d. How much time do they spend on phone calls?

[x] e. How often do they miss a call?

[x] f. How often do they reject an incoming call?

[x] g. How often are their calls rejected?

[] h. How many people do they actively stay in touch with (> 1 conversation in 3 days)

[] i. What is their typical network like?

[] j. What telecom subscription do they have? (Jio, etc)

[] k. What is the price of the phone they use?

[] l. How many users has the user referred (Quser Count - Initial QUser count upon Signup)

[] m. What QTalk actions have they taken: i. Call recording ii. Custom call screen iii. Live interaction sessions iv. V-V calling v. V-P calling

a. smart contacts

db.events.aggregate([
	{$group:
	    { "_id": "$profile.identity",
	        "smartContacts": { $first: "$profile.profileData.totalsmartcontacts"}
	     }
	},
	{$match:
	    { "smartContacts": { "$ne": null }}
	},
])

b. number of people they speak

db.events.aggregate([
	{$group:
	    { "_id": "$profile.identity",
	        "calls": { $addToSet: "$event_props.Hashed Phone Number"}
	     }
	}
])

db.events.aggregate([
	{$group:
	    { "_id": "$profile.identity",
	        "difftNumbersUsed": { $addToSet: "$event_props.Hashed Phone Number"},
	        "totalqusercontacts": { $last: "$profile.profileData.totalqusercontacts"},
	        "totalrawcontacts": { $last: "$profile.profileData.totalrawcontacts"},
	        "totalcontacts": { $last: "$profile.profileData.totalcontacts"},
	        "totalsmartcontacts": { $last: "$profile.profileData.totalsmartcontacts"},
	     }
	}
])

c. number of outgoing calls

db.events.aggregate([
	{$match: {"event_props.Type": { $eq: "OUTGOING" }}},
	{$group: { _id: "$profile.identity", outgoingCalls: { $sum: 1 } }}
])

d. time spent on phone calls

db.events.aggregate([
	{$group: {
    _id: "$profile.identity",
    totalTime: { $sum: "$event_props.Duration" },
		avgTime: { $avg: "$event_props.Duration" }
  }}
])

e. how often do they miss a call

db.events.aggregate([
	{$match:{"event_props.Type": { $eq: "MISSED" }}},
	{$group: { _id: "$profile.identity", missedCalls: { $sum: 1 } }}
])

f. how often do they reject a call

db.events.aggregate([
	{$match:{"event_props.Type": { $eq: "INCOMING" }}},
	{$match:{"event_props.Duration": { $eq: 0 }}},
	{$group: { _id: "$profile.identity", callsTheyReject: { $sum: 1 } }}
])

g. how often are their call rejected

db.events.aggregate([
	{$match:{"event_props.Type": { $eq: "OUTGOING" }}},
	{$match:{"event_props.Duration": { $eq: 0 }}},
	{$group: { _id: "$profile.identity", callsTheyGetRejected: { $sum: 1 } }}
])