Project 2

use ecommerce;

db.createCollection('categories');
db.categories.insertMany([{productCategoryId:1, categoryName:'Mobile'},{productCategoryId:2, categoryName:'Accessories'},{productCategoryId:3, categoryName:'Clothes'}]);

db.createCollection('seller');
db.seller.insertMany([
{sellerId:101,
sellerPersonalInfo:{'First Name':'Radha','Last Name': 'Krishan','D.O.B':'21-06-1998'},
sellerContactInfo:{'Email Id': '[email protected]','Phone Number':'101101'},
sellerAddress:{'City':'Vrindavan','State':'Uttar Pradesh'},
productCategoryId:ObjectId("6388cc02720ebf9d8685fe7e")},
{sellerId:101,
sellerPersonalInfo:{'First Name':'Sita','Last Name': 'Ram','D.O.B':'21-06-1997'},
sellerContactInfo:{'Email Id': '[email protected]','Phone Number':'101102'},
sellerAddress:{'City':'Ayodha','State':'Uttar Pradesh'},
productCategoryId:ObjectId("6388cc02720ebf9d8685fe7f")},
{sellerId:103,
sellerPersonalInfo:{'First Name':'Gori','Last Name': 'Sankar','D.O.B':'21-06-1996'},
sellerContactInfo:{'Email Id': '[email protected]','Phone Number':'103103'},
sellerAddress:{'City':'Palwal','State':'Haryana'},
productCategoryId:ObjectId("6388cc02720ebf9d8685fe80")}]);

db.createCollection('product');
db.product.insertMany([
{productId:201,
sellerInfo:ObjectId("6388d053720ebf9d8685fe81"),
productName:'Mobile',
productSizeAvailable:'5.6 inch',
quantityAvailable:32,
productPrice:12000,
productWeight:'800 gram',
currentDiscount:1160,
productCategoryInfo:ObjectId("6388cc02720ebf9d8685fe7e"),
productImage:'image_1',
productLocation:'Delhi',
productDescription:'Mobile Phone'},
{productId:202,
sellerInfo:ObjectId("6388d053720ebf9d8685fe82"),
productName:'Belt',
productSizeAvailable:'38',
quantityAvailable:42,
productPrice:600,
productWeight:'500 gram',
currentDiscount:200,
productCategoryInfo:ObjectId("6388cc02720ebf9d8685fe7f"),
productImage:'image_2',
productLocation:'Uttrakhand',
productDescription:"Men's Belt"},
{productId:203,
sellerInfo:ObjectId("6388d053720ebf9d8685fe83"),
productName:'Shirt',
productSizeAvailable:'XXL',
quantityAvailable:22,
productPrice:1200,
productWeight:'100 gram',
currentDiscount:400,
productCategoryInfo:ObjectId("6388cc02720ebf9d8685fe80"),
productImage:'image_3',
productLocation:'Gujarat',
productDescription:"Men's shirt"}]);

db.createCollection('paymentinfo');
db.paymentinfo.insertMany([
{customerInfo:ObjectId("6388deb1720ebf9d8685fe87"),
transactionId:'12FS54T178',
payment:'Paid'},
{customerInfo:ObjectId("6388deb1720ebf9d8685fe88"),
transactionId:'186S54T596',
payment:'Paid'},
{customerInfo:ObjectId("6388deb1720ebf9d8685fe89"),
transactionId:'567FS5T173',
payment:'Paid'}]);

db.createCollection('admin');
db.admin.insertMany([
{adminId:1001,
adminPersonalInfo:{'First Name':'Radha','Last Name': 'Krishan','D.O.B':'21-06-1998'},
adminContactInfo:{'Email Id': '[email protected]','Phone Number':'101101'},
adminAddress:{'City':'Vrindavan','State':'Uttar Pradesh'},
productCategoryInfo:ObjectId("6388cc02720ebf9d8685fe7e")},
{adminId:1002,
adminPersonalInfo:{'First Name':'Sita','Last Name': 'Ram','D.O.B':'21-06-1997'},
adminContactInfo:{'Email Id': '[email protected]','Phone Number':'101102'},
adminAddress:{'City':'Ayodha','State':'Uttar Pradesh'},
productCategoryInfo:ObjectId("6388cc02720ebf9d8685fe7f")},
{adminId:1003,
adminPersonalInfo:{'First Name':'Gori','Last Name': 'Sankar','D.O.B':'21-06-1996'},
adminContactInfo:{'Email Id': '[email protected]','Phone Number':'103103'},
adminAddress:{'City':'Palwal','State':'Haryana'},
productCategoryInfo:ObjectId("6388cc02720ebf9d8685fe80")}]);

db.createCollection('orderDetails');
db.orderDetails.insertMany([
{orderId:2001,
customerInfo:ObjectId("6388deb1720ebf9d8685fe87"),
productInfo:ObjectId("6388da2b720ebf9d8685fe84"),
orderQuantity:2,
orderAmount:1156,
orderStatus:'ordered',
shipping:{shipName:'Hello Ship',ShipAddress:'Palwal, Haryana'},
orderDate:'26-11-2022',
orderDeliveryDate:'05-12-2022',
orderTrackingNumber:'7857896',
invoice:131201},
{orderId:6431,
customerInfo:ObjectId("6388deb1720ebf9d8685fe88"),
productInfo:ObjectId("6388da2b720ebf9d8685fe85"),
orderQuantity:1,
orderAmount:999,
orderStatus:'ordered',
shipping:{shipName:'Hello Ship',ShipAddress:'Mathura, U.P'},
orderDate:'26-11-2022',
orderDeliveryDate:'05-12-2022',
orderTrackingNumber:'635788',
invoice:371237},
{orderId:5013,
customerInfo:ObjectId("6388deb1720ebf9d8685fe89"),
productInfo:ObjectId("6388da2b720ebf9d8685fe86"),
orderQuantity:5,
orderAmount:9980,
orderStatus:'ordered',
shipping:{shipName:'Hello Ship',ShipAddress:'kurukshetra, Haryana'},
orderDate:'27-11-2022',
orderDeliveryDate:'04-12-2022',
orderTrackingNumber:'8857899',
invoice:15200}]);

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

db.orderDetails.aggregate([{"$group":{"_id":{customerInfo:'$customerInfo'},'Average Value':{'$avg':'$orderAmount'}}}]);

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
Solution:

db.orderDetails.aggregate({'$match':{'orderMonth':'November'}},
{"$group":{"_id":{'customerInfo':'$customerInfo','orderAmount':{'$sum':'$orderAmount'}}}},{$sort:{orderAmount:-1}},{$limit: 10});

3: Query your database to find the sum of all orders received in this month.
Solution:

db.orderDetails.aggregate([{"$group":{"_id":{orderMonth: '$orderMonth'},'sum': '$sum':'$orderAmount'}}}]);

4: Query your database to find customers count in various cities, group them by names of cities.
Solution:

db.customer.aggregate({"$group":{"_id":{'city':'$customerAddress.city'},'count':{'$sum':1}}});

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.
Solution:

db.seller.aggregate([{
$lookup:
{
from:'customer',
localField:'sel_city',
foreignField:'cust_city',
as:'customerdetails'
}
},
{
"$sort":{
"products_sold":-1
}},
{ $match : { "customerdetails" : { $ne : []}}},
]);