Project 2
1. Design your ecommerce database in MongoDB with the specifications provided Below :
- Every Customer would register on the ecommerce website with all their personal information
- Each Customer has a type of being normal or premium member
- The customer should be able to view products and add them to cart
- Products can belong to multiple categories, brands and segments.
- Each category can have subcategories as well.
- Each products can also have multiple tags associated with it.\
- From the cart, the customer should be able to check out and make payment and his/her order would be booked which the customer can track
- The admin of the ecommerce and the seller both can add products under multiple categories, segments and tags
- The seller would receive the orders and should be able to fulfil them.
- The order shipping details and the billing information are very important things to be stored in the database as well.
- Every Seller would register on the ecommerce website with all their personal information.
- Create your collections for your ecommerce database in MongoDB and fill it with sample information
- Submit all the queries used to create the database
-
Customers Collection
-
Products Collection
-
Categories Collection
-
Brands Collection
-
Segments Collection
-
Tags Collection
-
Orders Collection
-
Sellers Collection
-
Customers Collection:
_id: ObjectId,
name: string,
email: string,
password: string,
address: {
street: string,
city: string,
state: string,
zip: string,
country: string
},
type: string // Normal or Premium Member
}
- Products Collection:
{
_id: ObjectId,
name: string,
description: string,
price: decimal,
categories: [ObjectId],
brands: [ObjectId],
segments: [ObjectId],
tags: [ObjectId]
}
- Categories Collection:
{
_id: ObjectId,
name: string,
parent: ObjectId // Id of parent category if it is a subcategory
}
- Brands Collection:
{ _id: ObjectId, name: string }
- Segments Collection:
{
_id: ObjectId,
name: string
}
- Tags Collection:
{
_id: ObjectId,
name: string
}
- Orders Collection:
{
_id: ObjectId,
customer: ObjectId, // Id of the customer who placed the order
products: [{
product: ObjectId, // Id of the product ordered
quantity: int
}],
billing: {
name: string,
address: {
street: string,
city: string,
state: string,
zip: string,
country: string
},
payment: {
method: string,
card_number: string,
expiry_date: string,
cvv: string
}
},
shipping: {
name: string,
address: {
street: string,
city: string,
state: string,
zip: string,
country: string
},
status: string, // Processing, Shipped, Delivered
tracking_number: string
},
date_placed: datetime,
date_shipped: datetime,
date_delivered: datetime
}
- Sellers Collection:
{
_id: ObjectId,
name: string,
email: string,
password: string,
address: {
street: string,
city: string,
state: string,
zip: string
}{
$lookup: {
from: "Customers",
localField: "customer",
foreignField: "_id",
as: "customer_info"
}
},
{
$unwind: "$customer_info"
},
{
$group: {
_id: "$customer_info._id",
avg_order_value: { $avg: { $sum: "$products.price" } }
}
}
])
2.Query your database to get top 10 customers whose orders have the highest value and who have been customers since less than 2 months
SELECT customers.customer_name, SUM(orders.order_total) as total_order_value
FROM customers
JOIN orders ON customers.customer_id = orders.customer_id
WHERE customers.customer_since >= DATEADD(month, -2, GETDATE())
GROUP BY customers.customer_name
ORDER BY total_order_value DESC
LIMIT 10;
3.Query your database to find the sum of all orders received in this month
SELECT SUM(order_total) AS total_orders_received
FROM orders
WHERE MONTH(order_date) = MONTH(CURRENT_DATE())
AND YEAR(order_date) = YEAR(CURRENT_DATE());
4.Query your database to find customers count in various cities, group them by names of cities
SELECT city, COUNT(customer_id) AS customer_count
FROM customers
GROUP BY city;
5.Query your database to get all the sellers who have base in the same city in which the customers who ordered their products. Order them in the order of highest number of products sold to customers in same city to lowest.
FROM sellers
INNER JOIN orders ON sellers.id = orders.seller_id
INNER JOIN customers ON orders.customer_id = customers.id
WHERE sellers.city = customers.city
GROUP BY sellers.id, sellers.name
ORDER BY num_products_sold DESC;