1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118
| db.orders.aggregate([ { $match: { "orderDate": { $gte: ISODate("2024-01-01"), $lt: ISODate("2024-08-01") }, "status": "completed" } }, { $project: { "customerId": 1, "totalAmount": 1, "orderDate": 1, "items.category": 1 } }, { $group: { "_id": "$customerId", "totalOrders": { $sum: 1 }, "totalSpent": { $sum: "$totalAmount" }, "avgOrderValue": { $avg: "$totalAmount" }, "firstOrder": { $min: "$orderDate" }, "lastOrder": { $max: "$orderDate" } } }, { $addFields: { "customerLifetime": { $divide: [ { $subtract: ["$lastOrder", "$firstOrder"] }, 1000 * 60 * 60 * 24 ] } } }, { $sort: { "totalSpent": -1 } }, { $limit: 100 }, { $lookup: { "from": "users", "localField": "_id", "foreignField": "_id", "as": "userInfo", "pipeline": [ { $project: { "username": 1, "email": 1, "profile.firstName": 1, "profile.lastName": 1 } } ] } }, { $unwind: "$userInfo" }, { $project: { "customerId": "$_id", "username": "$userInfo.username", "email": "$userInfo.email", "fullName": { $concat: [ "$userInfo.profile.firstName", " ", "$userInfo.profile.lastName" ] }, "totalOrders": 1, "totalSpent": 1, "avgOrderValue": { $round: ["$avgOrderValue", 2] }, "customerLifetime": { $round: ["$customerLifetime", 0] }, "_id": 0 } } ])
db.orders.aggregate([ { $match: { "status": "completed" } } ], { hint: { "status": 1, "orderDate": -1 } })
db.orders.aggregate([ ], { allowDiskUse: true })
|