Project 2

1.
db.customers.insertMany([
{cust_id:1,cust_name:"Anjali",email:"[email protected]",city:"delhi",order_value:"2000",order_date:"2023-01-13"},
{cust_id:2,cust_name:"Swati",email:"[email protected]",city:"pune",order_value:"4000",order_date:"2023-01-13"},
{cust_id:3,cust_name:"Riya",email:"[email protected]",city:"bangalore",order_value:"1000",order_date:"2023-02-13"},
{cust_id:4,cust_name:"Abhishek",email:"[email protected]",city:"Mumbai",order_value:"2000",order_date:"2023-01-13"}
])
db.product.insertMany([
{pro_id:1,pro_name:"watch",cost:5000,qty:5},
{pro_id:2,pro_name:"TV",cost:30000,qty:4},
{pro_id:3,pro_name:"Mobile",cost:60000,qty:9},
{pro_id:4,pro_name:"Smart Watch",cost:40000,qty:5}
])
db.order.insertMany([
{order_id:1,cust_id:2,order_status:"delivered",order_date:"2022-01-13"},
{order_id:2,cust_id:1,order_status:"delivered",order_date:"2023-01-13"},
{order_id:3,cust_id:3,order_status:"not delivered",order_date:"2022-12-13"}
])
db.seller.insertMany([
{seller_id:1,seller_name:"Rahul",city:"Bangalore",products_sold:4},
{seller_id:2,seller_name:"Ashwin",city:"Chennai",products_sold:8},
{seller_id:3,seller_name:"Riya",city:"Delhi",products_sold:9}
])
db.customers.aggregate([
{$group:{_id:"cust_id",average_orders:{$avg:"order_value"}}}
])
2.
db.customers.aggregate([
{$match:{order_date:{$lt:"2023-01-01"}}},
{$sort:{order_value:-1}}
])
3.
db.order.aggregate([
{$match:{order_date:{$gte:"2022-01-01",$lte:"2022-01-30"}}},
{$group:{_id:"customer_id",orders:{$sum:1}}}
])

4.

db.customers.aggregate([
{ $group:{_id: "$city",customer_count: { $sum: 1 }}}
])

5.

db.sellers.aggregate([
{ $lookup:
{from: "orders",localField: "_id",
foreignField: "seller_id",as: "orders"}},
{ $unwind: "$orders" },
{ $lookup:{from: "customers",localField: "orders.customer_id",foreignField: "_id",as: "customer"}},
{ $unwind: "$customer" },
{ $match: { "city": "$customer.city" } },
{ $group:{_id: "$_id",num_products_sold: { $sum: 1 }}},
{ $sort: { "num_products_sold": -1 } }
])