Problem Statement
How would you use aggregation to find the top 5 customers by total purchase amount? Explain each stage.
Explanation
To find the top 5 customers by total purchase amount, you would use a multi-stage aggregation pipeline. First, use dollar match to filter only completed orders, ensuring you are counting actual sales, not pending or cancelled orders.
Second, use dollar group to group orders by customer ID and calculate the total amount using dollar sum. The underscore id field in dollar group specifies the grouping key, which is the customer ID. The accumulator dollar sum adds up all order amounts for each customer.
Third, use dollar sort to order customers by total amount in descending order, with negative 1 indicating descending sort. This places the highest spending customers first. Fourth, use dollar limit to restrict the output to only the top 5 customers.
Optionally, you can add a dollar lookup stage to join with the customers collection and include customer details like name and email. Finally, use dollar project to reshape the output, including only the fields you need and making the results more readable.
Code Solution
SolutionRead Only
db.orders.aggregate([
// Stage 1: Filter completed orders
{ $match: { status: "completed" } },
// Stage 2: Group by customer and sum amounts
{
$group: {
_id: "$customerId",
totalAmount: { $sum: "$amount" },
orderCount: { $sum: 1 }
}
},
// Stage 3: Sort by total in descending order
{ $sort: { totalAmount: -1 } },
// Stage 4: Get top 5
{ $limit: 5 },
// Stage 5: Lookup customer details
{
$lookup: {
from: "customers",
localField: "_id",
foreignField: "_id",
as: "customerInfo"
}
}
])