Project 2
use ecommerce_db
db.createCollection("admin")
db.createCollection("customers")
db.createCollection("sellers")
db.createCollection("products")
db.createCollection("categories")
db.createCollection("orders")
db.createCollection("carts")
db.customers.insertMany([
{
_id: 1,
name: "Steve Johnson",
email: "[email protected]",
membershipType: "normal",
city: "Chennai",
},
{
_id: 2,
name: "Bob Smith",
email: "[email protected]",
membershipType: "premium",
city: "Delhi",
},
{
_id: 3,
name: "Clijo Guiden",
email: "[email protected]",
membershipType: "premium",
city: "Mumbai",
},
{
_id: 4,
name: "Mck Staan",
email: "[email protected]",
membershipType: "premium",
city: "Kochi",
},
]);
db.admin.insertMany([
{
name: "Derik",
email: "[email protected]",
city: "Chennai",
},
{
name: "Layola",
email: "[email protected]",
city: "Chennai",
},
]);
db.sellers.insertMany([
{
_id:1,
name: "Raj",
email: "[email protected]",
city:"Kochi",},
{
_id: 2,
name: "Shankar",
email: "[email protected]",
city:"Chennai",},
{_id:3,
name:"Dhoni",
email:"[email protected]",
city:"Mumbai",},
])
db.products.insertMany([
{
_id: 101,
name: "Laptop",
description: "High-performance laptop with a fast SSD and long battery life ",
price:25000,
categories: ["Electronics", "Gadgets"],
brands: ["HP"],
segments: ["Electronics"],
tags: ["High-end", "Mate finish"],
},
{
_id: 102,
name: "T-Shirt",
description: "Comfortable and stylish cotton t-shirt suitable for everyday wear",
price: 599.99,
categories: ["Fashion", "Clothing"],
brands: ["Nike"],
segments: ["Fashion"],
tags: ["fit", "fashionable"],
},
{
_id: 103,
name: "smart phone",
description: "The latest smartphone with 5G support, a stunning display, and a powerful camera for capturing moments",
price: 12000,
categories: ["Electronics", "Gadgets"],
brands: ["Motrola"],
segments: ["Electronis"],
tags: ["5G", "Water proof"],
},
{
_id: 104
,
name: "Shoe",
description: "flexible and comfort to wear",
price: 2000,
categories: ["Fashion", "Clothing"],
brands: ["New Blance"],
segments: ["foot wear"],
tags: ["light weight", "stylish"],
},
])
db.categories.insertMany([
{
_id: 201,
name: "Fashion",
subcategories: ["Clothing", "Accessories","foot wears"],
tags: ["size", "colours"],
},
{
_id: 203,
name: "Electronics",
subcategories: ["Gadgets", "appliances","accessories"],
tags: ["performance", "price"],
},
])
db.orders.insertMany([
{
customerId: 2,
sellerId: 2,
products: [{ productId: 102, quantity: 1 }],
orderDate: new Date("2023-08-15"),
orderTotal: 599.99,
shippingInfo: {
address: "123 MG Rd",
city: "Pondichery",
postalCode: "12345",
country: "India",
},
billingInfo: {
cardType: "Visa",
cardNumber: "**** **** **** 1234",
billingAddress: "456 MG St",
billingCity: "Kochi",
billingPostalCode: "505999",
billingCountry: "India",
},
},
{
customerId: 3,
sellerId: 2,
products: [{ productId: 103, quantity: 2 }],
orderDate: new Date("2023-08-20"),
orderTotal: 12000,
shippingInfo: {
address: "789 Nehru Rd",
city: "Delhi",
postalCode: "678904",
country: "India",
},
billingInfo: {
cardType: "MasterCard",
cardNumber: "**** **** **** 5678",
cardHolder: "Jane Smith",
billingAddress: "101 Park Ave",
billingCity: "Mumbai",
billingPostalCode: "636372",
billingCountry: "India",
},
},
{
customerId: 4,
sellerId: 3,
products: [{ productId: 104, quantity: 3 }],
orderDate: new Date("2023-08-25"),
orderTotal: 2000,
shippingInfo: {
address: "33/54 OMR",
city: "Chennai",
postalCode: "600119",
country: "India",
},
billingInfo: {
cardType: "American Express",
cardNumber: "**** **** **** 9876",
cardHolder: "Robert Johnson",
billingAddress: "202, Mount Rd",
billingCity: "Chennai",
billingPostalCode: "600019",
billingCountry: "India",
},
},
{
customerId: 10,
sellerId: 2,
products: [{ productId: 102, quantity: 1 }],
orderDate: new Date("2023-08-15"),
orderTotal: 599.99,
shippingInfo: {
address: "123 MG Rd",
city: "Pondichery",
postalCode: "12345",
country: "India",
},
billingInfo: {
cardType: "Visa",
cardNumber: "**** **** **** 1234",
billingAddress: "456 MG St",
billingCity: "Kochi",
billingPostalCode: "505999",
billingCountry: "India",
},
},
{
customerId: 8,
sellerId: 1,
products: [{ productId: 103, quantity: 2 }],
orderDate: new Date("2023-05-20"),
orderTotal: 24000,
shippingInfo: {
address: "789 Nehru Rd",
city: "Delhi",
postalCode: "678904",
country: "India",
},
billingInfo: {
cardType: "MasterCard",
cardNumber: "**** **** **** 5678",
cardHolder: "Dani",
billingAddress: "101 Park Ave",
billingCity: "Mumbai",
billingPostalCode: "636372",
billingCountry: "India",
},
},
{
customerId: 7,
sellerId: 3,
products: [{ productId: 102, quantity: 3 }],
orderDate: new Date("2023-08-21"),
orderTotal: 6000,
shippingInfo: {
address: "33/54 OMR",
city: "Chennai",
postalCode: "600119",
country: "India",
},
billingInfo: {
cardType: "American Express",
cardNumber: "**** **** **** 9876",
cardHolder: "Mark antony",
billingAddress: "202, Mount Rd",
billingCity: "Chennai",
billingPostalCode: "600019",
billingCountry: "India",
},
}
]);
db.carts.insertMany([
{
customerId: 2,
items: [{ productId: 102, quantity: 1 }],
},
{
customerId: 3,
items: [{ productId: 103, quantity: 2 }],
},
])
1. db.orders.aggregate([
{
$group: {
_id: "$customerId",
totalOrderValue: { $sum: "$orderTotal" },
orderCount: { $sum: 1 }
}
},
{
$group: {
_id: null,
totalOrderValue: { $sum: "$totalOrderValue" },
totalOrderCount: { $sum: "$orderCount" }
}
},
{
$project: {
_id: 0,
averageOrderValue: { $divide: ["$totalOrderValue", "$totalOrderCount"] }
}
}
]);
2. const currentDate = new Date();
const twoMonthsAgo = new Date();
twoMonthsAgo.setUTCMonth(twoMonthsAgo.getUTCMonth() - 2);
db.orders.aggregate([
{
$match: {
orderDate: { $gte: twoMonthsAgo }
}
},
{
$group: {
_id: "$customerId",
totalOrders: { $sum: "$orderTotal" }
}
},
{
$sort: {
totalOrders: -1
}
},
{
$limit: 10
}
]);
4.db.customers.aggregate([
{
$group: {
_id: "$city",
customerCount: { $sum: 1 }
}
},
{
$project: {
cityName: "$_id",
customerCount: 1,
_id: 0
}
}
]);
3. var currentDate = new Date();
var currentMonth = currentDate.getMonth() + 1;
db.orders.aggregate([
{
$match: {
$expr: {
$eq: [{ $month: "$orderDate" }, currentMonth]
}
}
},
{
$group: {
_id: null,
totalOrders: { $sum: "$orderTotal" }
}
}
]);
5. db.sellers.aggregate([
{
$lookup: {
from: "orders",
localField: "_id",
foreignField: "sellerId",
as: "sellerOrders"
}
},
{
$unwind: "$sellerOrders"
},
{
$lookup: {
from: "customers",
localField: "sellerOrders.customerId",
foreignField: "_id",
as: "customerData"
}
},
{
$unwind: "$customerData"
},
{
$match: {
$expr: {
$eq: ["$city", "$customerData.city"]
}
}
},
{
$group: {
_id: "$_id",
sellerName: { $first: "$name" },
numberOfProductsSold: { $sum: 1 }
}
},
{
$sort: {
numberOfProductsSold: -1
}
}
]);