Project 2

project-

use ecommerce

db.customer.insertMany([
{cust_id:1,cust_name:"Arjun",email:"[email protected]",city:"Mumbai",order_value:2000,order_date:"2022-05-05"},
{cust_id:2,cust_name:"Bunny",email:"[email protected]",city:"chennai",order_value:4000,order_date:"2021-08-30"},
{cust_id:3,cust_name:"Rolex",email:"[email protected]",city:"Hyderabad",order_value:5000,order_date:"2023-07-25"},
{cust_id:4,cust_name:"Vikram",email:"[email protected]",city:"Kerala",order_value:2000,order_date:"2020-06-15"}
])

db.products.insertMany([
{pro_id:1,pro_name:"mercedes",cost:2050000,Quantity:8},
{pro_id:2,pro_name:"Porsche",cost:3000000,Quantity:4},
{pro_id:3,pro_name:"BMW",cost:1000000,Quantity:5},
{pro_id:4,pro_name:"Mclaren",cost:20800000,Quantity:3}
])

db.order.insertMany([
{order_id:1,cust_id:1,order_status:"Delivered",order_date:"2022-03-13"},
{order_id:2,cust_id:2,order_status:"Delivered",order_date:"2022-04-21"},
{order_id:3,cust_id:3,order_status:"Delivered",order_date:"2020-02-12"},
{order_id:4,cust_id:4,order_status:"Not Delivered",order_date:"2023-01-01"}
])

db.Cart.insertMany([
{cust_id:1,pro_id:1,Quantity:10},
{cust_id:2,pro_id:2,Quantity:100},
{cust_id:3,pro_id:3,Quantity:90},
{cust_id:4,pro_id:4,Quantity:110}
])

db.seller.insertMany([
{seller_id:1,seller_name:"Abhi",city:"chennai",products_sold:8},
{seller_id:2,seller_name:"laxman",city:"Hyderabad",products_sold:10},
{seller_id:3,seller_name:"Rakesh",city:"chennai",products_sold:80}
])

1.db.customer.aggregate([
{$group: {_id:"cust_id",
average_orders: { $avg: "$order_value"}}}])

2.db.customer.aggregate([
{$match:{order_date:{$lt:"2023-01-01"}}},
{$sort:{order_value:-1}}])

3.db.customer.aggregate([
{$match:{order_date:{$gte:"2020-08-21",$lte:"2023-01-13"}}},
{$group:{_id:"cust_id",orders:{$sum:1}}}
])

4.db.customer.aggregate([
{
$group: {
_id: "$city",
count: { $sum: 1 }
}
}
])

5.db.seller.aggregate([
{
$lookup: {
from: "customer",
localField: "city",
foreignField: "city",
as: "customers"
}
},
{
$lookup: {
from: "cart",
localField: "seller_id",
foreignField: "pro_id",
as: "sold_products"
}
},
{
$addFields: {
customers_in_city: {
$filter: {
input: "$customers",
as: "customer",
cond: { $eq: ["$$customer.city", "$city"] }
}
},
total_sold_to_customers_in_city: {
$sum: {
$filter: {
input: "$sold_products",
as: "product",
cond: {
$in: ["$$product.cust_id", "$customers_in_city.cust_id"]
}
}
}
}
}
},
{
$sort: { total_sold_to_customers_in_city: -1 }
}
])