scripts/useful-queries/churn-by-room.mongo

   1 rs.slaveOk()
   2
   3 var roomIds = db.troupes.find({ lcUri: { $in: [
   4     'marionettejs/backbone.marionette',
   5     'freecodecamp/freecodecamp',
   6     'angular/angular.js',
   7     'webpack/webpack',
   8     'gulpjs/gulp',
   9     'minio/minio',
  10     'JuliaLang/julia',
  11     'home-assistant/home-assistant'
  12   ] }
  13  }).map(function(f) { return f._id });
  14
  15 var a = db.chatmessages.aggregate([{
  16   $match: {
  17     toTroupeId: { $in: roomIds },
  18     sent: { $type: 'date' }
  19   }
  20 }, {
  21   $group: {
  22     _id: {
  23       userId: '$fromUserId',
  24       troupeId: '$toTroupeId',
  25     },
  26     firstSent: { $min: '$sent'},
  27     lastSent: { $max: '$sent'},
  28   },
  29 }, {
  30   $project: {
  31     firstSent: 1,
  32     lastSent: 1,
  33     duration: { $divide: [{ $subtract: ['$lastSent', '$firstSent']}, 86400000] }
  34   },
  35 }, {
  36   $group: {
  37     _id: '$_id.troupeId',
  38     totalActiveUserCount: { $sum: 1 },
  39     firstSent: { $min: '$firstSent'},
  40     lastSent: { $max: '$lastSent'},
  41     avgDuration: { $avg: '$duration' }
  42   }
  43 }, {
  44   $project: {
  45     firstSent: 1,
  46     lastSent: 1,
  47     totalActiveUserCount: 1,
  48     totalDuration: { $divide: [{ $subtract: ['$lastSent', '$firstSent']}, 86400000] },
  49     avgDuration: 1,
  50     avgOverLifetime: { $divide: ['$avgDuration', { $divide: [{ $subtract: ['$lastSent', '$firstSent']}, 86400000] }] }
  51   },
  52 }, {
  53   $lookup: {
  54     from: "troupes",
  55     localField: "_id",
  56     foreignField: "_id",
  57     as: "troupe"
  58   },
  59 }, {
  60   $unwind: "$troupe"
  61 }, {
  62   $project: {
  63     _id: 1,
  64     uri: '$troupe.uri',
  65     firstSent: 1,
  66     lastSent: 1,
  67     totalActiveUserCount: 1,
  68     totalDuration: 1,
  69     avgDuration: 1,
  70     avgOverLifetime: 1
  71   },
  72 }]);
  73
  74 printjson(a.toArray());