Project 2
Answers:
Simple Queries to perform on ecommerce db
Find the average order value for all Customers:
db.orders.aggregate([
{
$group: {
_id: null,
totalOrderValue: { $avg: "$total_value" }
}
}
]);
2. Get the top 10 customers with the highest order values, who have been customers for less than 2 months:
var twoMonthsAgo = new Date();
twoMonthsAgo.setMonth(twoMonthsAgo.getMonth() - 2);
db.customers.aggregate([
{
$match: {
registration_date: { $gt: twoMonthsAgo }
}
},
{
$lookup: {
from: "orders",
localField: "_id",
foreignField: "customer_id",
as: "customer_orders"
}
},
{
$unwind: "$customer_orders"
},
{
$group: {
_id: "$_id",
customer_name: { $first: "$name" },
total_order_value: { $sum: "$customer_orders.total_value" }
}
},
{
$sort: { total_order_value: -1 }
},
{
$limit: 10
}
]);
3. Find the sum of all orders received in the current month:
var currentDate = new Date();
var startOfMonth = new Date(currentDate.getFullYear(), currentDate.getMonth(), 1);
var endOfMonth = new Date(currentDate.getFullYear(), currentDate.getMonth() + 1, 0);
db.orders.aggregate([
{
$match: {
order_date: {
$gte: startOfMonth,
$lte: endOfMonth
}
}
},
{
$group: {
_id: null,
totalOrderValue: { $sum: "$total_value" }
}
}
]);
4. Count the number of customers in various cities, grouped by city names:
db.customers.aggregate([
{
$group: {
_id: "$city",
customerCount: { $sum: 1 }
}
}
]);
5. Get all the sellers who have their base in the same city as the customers who ordered their products, ordered by the number of products sold to customers in the same city (highest to lowest):
db.orders.aggregate([
{
$lookup: {
from: "sellers",
localField: "seller_id",
foreignField: "_id",
as: "seller_info"
}
},
{
$unwind: "$seller_info"
},
{
$group: {
_id: {
seller_id: "$seller_id",
seller_city: "$seller_info.city"
},
totalProductsSold: { $sum: 1 }
}
},
{
$sort: { totalProductsSold: -1 }
}
]);