Project 2

Task 1:

Customers collection:

{
"_id": ObjectId,
"name": "John Doe",
"email": "[email protected]",
// Other customer fields
}

----------

db.customers.insertMany([
{
"_id": ObjectId("customer1_id"),
"name": "John Doe",
"email": "[email protected]",
"address": "123 Main Street",
"city": "New York",
"state": "NY",
"country": "USA",
"postal_code": "10001",
"phone": "+1 123-456-7890",
"membership_type": "normal"
},
{
"_id": ObjectId("customer2_id"),
"name": "Jane Smith",
"email": "[email protected]",
"address": "456 Elm Street",
"city": "Los Angeles",
"state": "CA",
"country": "USA",
"postal_code": "90001",
"phone": "+1 987-654-3210",
"membership_type": "premium"
}
])

--------------------

Products collection:

{
"_id": ObjectId,
"name": "Product Name",
"description": "Product Description",
"price": 10.99,
"categories": [ObjectId], // Array of category ObjectId references
"brands": [ObjectId], // Array of brand ObjectId references
"segments": [ObjectId], // Array of segment ObjectId references
"tags": [String] // Array of tag names
// Other product fields
}

-----------------------

db.products.insertMany([
{
"_id": ObjectId("product1_id"),
"seller_id": ObjectId("seller1_id"),
"name": "Product 1",
"description": "This is product 1",
"price": 10.99,
"categories": [ObjectId("category1_id"), ObjectId("category2_id")],
"segments": [ObjectId("segment1_id")],
"tags": ["tag1", "tag2"]
},
{
"_id": ObjectId("product2_id"),
"seller_id": ObjectId("seller2_id"),
"name": "Product 2",
"description": "This is product 2",
"price": 19.99,
"categories": [ObjectId("category2_id")],
"segments": [ObjectId("segment2_id")],
"tags": ["tag2", "tag3"]
}
])

-------------------------

Carts collection:

{
"_id": ObjectId,
"customer_id": ObjectId,
"items": [
{
"product_id": ObjectId,
"quantity": 2
},
// Additional product items
]
}

Category collection:

{
"_id": ObjectId,
"name": "Category Name",
"parent_category_id": ObjectId, // Reference to parent category ObjectId
"subcategories": [ObjectId] // Array of subcategory ObjectId references
// Other category fields
}

Sub Category:

{
"_id": ObjectId("category1_id"),
"name": "Electronics",
"parent_category_id": null,
"subcategories": [
ObjectId("category2_id"),
ObjectId("category3_id")
]
}

{
"_id": ObjectId("category2_id"),
"name": "Computers",
"parent_category_id": ObjectId("category1_id"),
"subcategories": [
ObjectId("category4_id"),
ObjectId("category5_id")
]
}

{
"_id": ObjectId("category3_id"),
"name": "Mobiles",
"parent_category_id": ObjectId("category1_id"),
"subcategories": []
}

{
"_id": ObjectId("category4_id"),
"name": "Laptops",
"parent_category_id": ObjectId("category2_id"),
"subcategories": []
}

{
"_id": ObjectId("category5_id"),
"name": "Desktops",
"parent_category_id": ObjectId("category2_id"),
"subcategories": []
}

Orders collection:

{
"_id": ObjectId,
"customer_id": ObjectId,
"order_date": ISODate,
"status": "pending", // or "confirmed", "shipped", etc.
"payment": {
"method": "credit_card",
"status": "paid" // or "pending", "failed", etc.
},
"items": [
{
"product_id": ObjectId,
"quantity": 2,
"price": 10.99,
// Other item fields
},
// Additional product items
]
}

Seller's Collection:

{
"_id": ObjectId,
"name": "Seller Name",
"email": "[email protected]",
"password": "hashed_password",
"address": "Seller Address",
"phone": "Seller Phone Number",
// Other seller fields
}

----------------------

db.sellers.insertMany([
{
"_id": ObjectId("seller1_id"),
"name": "Seller 1",
"email": "[email protected]",
"address": "789 Oak Street",
"city": "Chicago",
"state": "IL",
"country": "USA",
"postal_code": "60601",
"phone": "+1 111-222-3333"
},
{
"_id": ObjectId("seller2_id"),
"name": "Seller 2",
"email": "[email protected]",
"address": "987 Pine Street",
"city": "Houston",
"state": "TX",
"country": "USA",
"postal_code": "77001",
"phone": "+1 444-555-6666"
}
])

-------------------------

Task 2:

1.db.orders.aggregate([
{
$group: {
_id: "$customer_id",
totalAmount: { $sum: "$total_amount" },
orderCount: { $sum: 1 }
}
},
{
$group: {
_id: null,
averageOrderValue: { $avg: "$totalAmount" }
}
}
])

2.

db.orders.aggregate([
{
$match: {
order_date: {
$gte: new Date(Date.now() - (60 * 60 * 24 * 60 * 1000 * 60)) // Filter orders from the last 2 months
}
}
},
{
$group: {
_id: "$customer_id",
totalAmount: { $sum: "$total_amount" }
}
},
{
$lookup: {
from: "customers",
localField: "_id",
foreignField: "_id",
as: "customer"
}
},
{
$unwind: "$customer"
},
{
$sort: { totalAmount: -1 }
},
{
$limit: 10
}
])

3.

db.orders.aggregate([
{
$match: {
order_date: {
$gte: new Date(new Date().getFullYear(), new Date().getMonth(), 1),
$lt: new Date(new Date().getFullYear(), new Date().getMonth() + 1, 1)
}
}
},
{
$group: {
_id: null,
totalAmount: { $sum: "$total_amount" }
}
}
])

4.

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

5.

db.orders.aggregate([
{
$lookup: {
from: "customers",
localField: "customer_id",
foreignField: "_id",
as: "customer"
}
},
{
$lookup: {
from: "products",
localField: "product_id",
foreignField: "_id",
as: "product"
}
},
{
$unwind: "$customer"
},
{
$unwind: "$product"
},
{
$lookup: {
from: "sellers",
localField: "product.seller_id",
foreignField: "_id",
as: "seller"
}
},
{
$unwind: "$seller"
},
{
$match: {
$expr: { $eq: ["$customer.city", "$seller.city"] }
}
},
{
$group: {
_id: "$seller._id",
sellerName: { $first: "$seller.name" },
productsSold: { $sum: 1 }
}
},
{
$sort: { productsSold: -1 }
}
])