Project 2

Dear Sir / Madam

I have complited projet-2 mongodb kindly check

Q-1, Customers Collection:

• Fields: _id, name, email, password, address, membershipType (normal/premium
• ANS-
• { "_id": ObjectId("customer_id"), "name": "John Doe", "email": "[email protected]", "password": "hashed_password", "address": "123 Main St, City", "membershipType": "normal" }

1. Products Collection:

• Fields: _id, name, price, categories, brands, segments, tags
• ANS -:
• { "_id": ObjectId("product_id"), "name": "Product Name", "price": 49.99, "categories": ["Electronics", "Gadgets"], "brands": ["Brand1"], "segments": ["SegmentA"], "tags": ["Tag1", "Tag2"] }

1. Cart Collection:

• Fields:` _id`, `customer_id`, `products` (array), `totalPrice`
• ANS-:
• { "_id": ObjectId("cart_id"), "customer_id": ObjectId("customer_id"), "products": [ { "product_id": ObjectId("product_id"), "quantity": 2 } ], "totalPrice": 99.98 }

1. Orders Collection:

• Fields:` _id`, `customer_id`, `products` (array), `orderDate`, `status`
• ANS-:
• { "_id": ObjectId("order_id"), "customer_id": ObjectId("customer_id"), "products": [ { "product_id": ObjectId("product_id"), "quantity": 2 } ], "orderDate": ISODate("2024-01-06T12:00:00Z"), "status": "Processing" }

1.Admins Collection:

• Fields: `_id`, `name, email`, `password`
• ANS-:
• { "_id": ObjectId("admin_id"), "name": "Admin Name", "email": "[email protected]", "password": "hashed_password" }

1. Sellers Collection:

• Fields: `_id`, `name`, `email`, `password`, `address`
• ANS -:
• { "_id": ObjectId("seller_id"), "name": "Seller Name", "email": "[email protected]", "password": "hashed_password", "address": "456 Seller St, City" }

Now, to create these collections and sample documents in MongoDB, you would use the MongoDB shell or a script. Below are simplified commands:// Create Collections db.createCollection("customers"); db.createCollection("products"); db.createCollection("carts"); db.createCollection("orders"); db.createCollection("admins"); db.createCollection("sellers"); // Insert Sample Data db.customers.insertOne({ /* customer document */ }); db.products.insertOne({ /* product document */ }); db.carts.insertOne({ /* cart document */ }); db.orders.insertOne({ /* order document */ }); db.admins.insertOne({ /* admin document */ }); db.sellers.insertOne({ /* seller document */ });

Simple Queries to perform on ecommerce db

1. Write a query to find the average order value for all Customers.

db.orders.aggregate([

{

$group: {

_id: null,

total: { $sum: "$totalPrice" },

count: { $sum: 1 }

}

},

{

$project: {

_id: 0,

averageOrderValue: { $divide: ["$total", "$count"] }

}

}

])

Q-2: Top 10 Customers with Highest Order Values (Customers since less than 2 months)

const twoMonthsAgo = new Date(); twoMonthsAgo.setMonth(twoMonthsAgo.getMonth() - 2); db.orders.aggregate([ { $match: { orderDate: { $gte: twoMonthsAgo } } }, { $group: { _id: "$customer_id", totalValue: { $sum: "$totalPrice" } } }, { $lookup: { from: "customers", localField: "_id", foreignField: "_id", as: "customer" } }, { $unwind: "$customer" }, { $sort: { totalValue: -1 } }, { $limit: 10 }, { $project: { _id: "$customer._id", name: "$customer.name", totalOrderValue: "$totalValue" } } ])

Q-3: Sum of All Orders Received in This Month

const currentDate = new Date(); const firstDayOfMonth = new Date(currentDate.getFullYear(), currentDate.getMonth(), 1); db.orders.aggregate([ { $match: { orderDate: { $gte: firstDayOfMonth } } }, { $group: { _id: null, totalOrdersThisMonth: { $sum: "$totalPrice" } } } ])

Q-4: Customers Count in Various Cities

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

Q-5: Sellers with the Highest Number of Products Sold to Customers in the Same City

db.orders.aggregate([ { $lookup: { from: "sellers", localField: "seller_id", foreignField: "_id", as: "seller" } }, { $unwind: "$seller" }, { $group: { _id: { seller_id: "$seller_id", customer_city: "$customer_address.city" }, totalProductsSold: { $sum: { $size: "$products" } } } }, { $sort: { totalProductsSold: -1 } }, { $project: { _id: "$_id.seller_id", sellerName: "$seller.name", customerCity: "$_id.customer_city", totalProductsSold: "$totalProductsSold" } } ])