Project 2

use Ecommerce

db.Admin.insertOne([
{_id:1 , name:"Raj" , password:1234560 , city:"Bangalore" , mobile:1234567890},
{_id:2 , name:"Ram" , password:0125964 , city:"Bangalore" , mobile:2345868791}
]);

db.Customer.insertMany([
{cust_id:1 , cust_name:"Shiva" , cust_gender:"M" , cust_registered_on:"15-10-2022" , cust_registered_as_per_month:"1 Month" , cust_city:"Bangalore" , cust_mobile_no:63210101010 , cust_type: "Normal" , order_value:45000},
{cust_id:2 , cust_name:"Vishwa" , cust_gender:"M" , cust_registered_on"10-10-2022" , cust_registered_as_per_month:"1 Month" , cust_city:"Chennai" , cust_mobile_no:9010101010 , cust_type: "Normal" , order_value:40000},
{cust_id:3 , cust_name:"Raju" , cust_gender:"M" , cust_registered_on:"08-10-2022" , cust_registered_as_per_month:"1 Month" , cust_city:"Bangalore" , cust_mobile_no:880120121111 , cust_type: "Premium" , order_value:30000},
{cust_id:4 , cust_name:"Sitha" , cust_gender:"F" , cust_registered_on:"05-10-2022" , cust_registered_as_per_month:"1 Month" , cust_city:"Delhi" , cust_mobile_no:920234604100 , cust_type: "Normal" , order_value:10000},
{cust_id:5 , cust_name:"Priya" , cust_gender:"F" , cust_registered_on:"01-10-2022" , cust_registered_as_per_month:"1 Month" , cust_city:"Hyderabad" , cust_mobile_no:63210101010 , cust_type: "Premium" , order_value:20000},
{cust_id:6 , cust_name:"Shivani" , cust_gender:"F" , cust_registered_on:"15-09-2022" , cust_registered_as_per_month:"2 Month" , cust_city:"Kolkata" , cust_mobile_no:8102030100 , cust_type: "Normal" , order_value:1000},
{cust_id:7 , cust_name:"Anil" , cust_gender:"M" , cust_registered_on:"08-09-2022" , cust_registered_as_per_month:"2 Month" , cust_city:"Mumbai" , cust_mobile_no:72804724042 , cust_type: "Normal" , order_value:5000},
{cust_id:8 , cust_name:"Keshav" , cust_gender:"M" , cust_registered_on:"05-09-2022" , cust_registered_as_per_month:"2 Month" , cust_city:"Bangalore" , cust_mobile_no:9010101023 , cust_type: "Normal" , order_value:150000},
{cust_id:9 , cust_name:"Sai" , cust_gender:"M" , cust_registered_on:"02-09-2022" , cust_registered_as_per_month:"2 Month" , cust_city:"Pune" , cust_mobile_no:"1201230120 , cust_type: "Premium" , order_value:9000},
{cust_id:10, cust_name:"Venkat" , cust_gender:"M" , cust_registered_on:"01-09-2022" , cust_registered_as_per_month:2 Month" , cust_city:"Mysore" , cust_mobile_no:9563102030 , cust_type: "Normal" , order_value:7890},
{cust_id:11 , cust_name:"Ravali" , cust_gender:"F" , cust_registered_on:"01-09-2022" , cust_registered_as_per_month:"2 Month" , cust_city:"Hyderabad" , cust_mobile_no:912310101 , cust_type: "Normal" , order_value:8796},
{cust_id:12 , cust_name:"Isha" , cust_gender:"F" , cust_registered_on:"10-08-2022" , cust_registered_as_per_month:"3 Month" , cust_city:"Bangalore" , cust_mobile_no:8101000010 , cust_type: "Premium" , order_value:25000},
{cust_id:13 , cust_name:"Ganesh" , cust_gender:"M" , cust_registered_on:"05-08-2022" , cust_registered_as_per_month:"3 Month" , cust_city:"chennai" , cust_mobile_no:7020123010 , cust_type: "Premium" , order_value:4100}
]);

db.seller.insertMany([
{seller_id:1 , seller_name:"Ram" , seller_city:"Mumbai" , seller_mobile_no:5231489666 , prod_category:"furniture" , products_sold:10},
{seller_id:2 , seller_name:"Srihan" , seller_city:"Hyderabad" , seller_mobile_no:69871020 , prod_category:"Clothes" , products_sold:20},
{seller_id:3 , seller_name:"Rohith" , seller_city:"Secunderabad" , seller_mobile_no:412023010 , prod_category:"Electronic" , products_sold:30},
{seller_id:4 , seller_name:"Veer" , seller_city:"Bangalore" , seller_mobile_no:5256410230 , prod_category:"furniture" , products_sold:30},
{seller_id:5 , seller_name:"Sathya" , seller_city:"Chennai" , seller_mobile_no:43101235410 , prod_category:"Electronic" , products_sold:15}
]);

db.product.insertMany([
{prod_id:1 , prod_name:"Chair" , prod_category:"Furniture" , prod_cost:10000 , seller_id:1},
{prod_id:2 , prod_name:"Table" , prod_category:"Furniture" , prod_cost:15000 , seller_id:4},
{prod_id:3 , prod_name:"Sofa_set" , prod_category:"Furniture" , prod_cost:20000 , seller_id:1},
{prod_id:4 , prod_name:"T-shirt" , prod_category:"Clothes" , prod_cost:1000 , seller_id:2},
{prod_id:5 , prod_name:"Pant" , prod_category:"Clothes" , prod_cost:1500 , seller_id:2},
{prod_id:6 , prod_name:"Short" , prod_category:"Clothes" , prod_cost:500 , seller_id:2},
{prod_id:7 , prod_name:"Home Theater" , prod_category:"Electronic" , prod_cost:25000 , seller_id:3},
{prod_id:8 , prod_name:"Speaker" , prod_category:"Electronic" , prod_cost:5000 , seller_id:5},
{prod_id:9 , prod_name:"Bed" , prod_category:"Furniture" , prod_cost:20000 , seller_id:1},
{prod_id:10 , prod_name:{cart_id:5 , cust_id:8 , prod_id:14 , pro_cost:35000}"Rocking-Chair" , prod_category:"Furniture" , prod_cost:15000 , seller_id:4},
{prod_id:11 , prod_name:"Shetter" , prod_category:"Clothes" , prod_cost:5000 , seller_id:2},
{prod_id:12 , prod_name:"Fridge" , prod_category:"Electronic" , prod_cost:25000 , seller_id:5},
{prod_id:13 , prod_name:"Shirt" , prod_category:"Clothes" , prod_cost:1500 , seller_id:2},
{prod_id:14 , prod_name:"Television" , prod_category:"Electronic" , prod_cost:35000 , seller_id:3},
{prod_id:15 , prod_name:"Apple_Mobile" , prod_category:"Electronic" , prod_cost:20000 , seller_id:5},
{prod_id:16 , prod_name:"Track_Pant" , prod_category:"Clothes" , prods_cost:10000 , seller_id:2}
]);

db.cart.insertMany([
{cart_id:1 , cust_id:1 , prod_id:1 , pro_cost:10000},
{cart_id:2 , cust_id:3 , prod_id:3 , pro_cost:20000},
{cart_id:3 , cust_id:5 , prod_id:4 , pro_cost:1000},
{cart_id:4 , cust_id:7 , prod_id:7 , pro_cost:25000},
{cart_id:5 , cust_id:8 , prod_id:8 , pro_cost:5000},
{cart_id:6 , cust_id:10 , prod_id:10 , pro_cost:15000},
{cart_id:7 , cust_id:2 , prod_id:13 , pro_cost:20000},
{cart_id:8 , cust_id:8 , prod_id:16 , pro_cost:10000},
{cart_id:9 , cust_id:13 , prod_id:5 , pro_cost:1500},
{cart_id:10 , cust_id:6 , prod_id:8 , pro_cost:5000}
]);

db.payment.insertMany([
{payment_id:1 , cust_id:1 , order_value:20000 , payment_status:"Paid", payment_type:"Gpay"},
{payment_id:2 , cust_id:2 , order_value:15000 , payment_status:"Paid", payment_type:"Phonepay"},
{payment_id:3 , cust_id:3 , order_value:10000 , payment_status:"Paid", payment_type:"Creditcard"},
{payment_id:4 , cust_id:4 , order_value:1000 , payment_status:"Paid", payment_type:"Debitcard"},
{payment_id:5 , cust_id:5 , order_value:1500 , payment_status:"Paid", payment_type:"Gpay"}
]);

sdb.orders.insertMany([
{order_id:1 , order_value:10000 , order_date:"15-11-2022" , order_month:"This month" , order_status:"Packed" , delivery_addr:"Bangalore" , prdo_id:1 , cust_id:2 , sel_id:1},
{order_id:2 , order_value:10000 , order_date:"20-11-2022" , order_month:"This month" , order_status:"Delivered" , delivery_addr:"Chennai" , prod_id:10 , cust_id:1 , sel_id:3},
{order_id:3 , order_value:10000 , order_date:"21-11-2022" , order_month:"This month" , order_status:"Packed" , delivery_addr:"Pune" , prod_id:15 , cust_id:13 , sel_id:5},
{order_id:4 , order_value:10000 , order_date:"22-11-2022" , order_month:"This month" , order_status:"Packed" , delivery_addr:"Bangalore" , prod_id:4 , cust_id:10 , sel_id:4},
{order_id:5 , order_value:10000 , order_date:"22-11-2022" , order_month:"This month" , order_status:"Delivered" , delivery_addr:"Mumbai" , prod_id:8 , cust_id:3 , sel_id:3},
{order_id:6 , order_value:20000 , order_date:"24-11-2022" , order_month:"This month" , order_status:"Packed" , delivery_addr:"Tamilnadu" , prod_id:2 , cust_id:6 , sel_id:2},
{order_id:7 , order_value:20000 , order_date:"25-11-2022" , order_month:"This month" , order_status:"Delivered" , delivery_addr:"Hyderabad" , prod_id:13 , cust_id:13 , sel_id:1},
{order_id:8 , order_value:20000 , order_date:"26-10-2022" , order_month:"1 month" , order_status:"Packed" , delivery_addr:"Bangalore" , prod_id:5 , cust_id:2 , sel_id:2},
{order_id:9 , order_value:20000 , order_date:"30-10-2022" , order_month:"1 month" , order_status:"Delivered" , delivery_addr:"Bangalore" , prod_id:6 , cust_id:1 , sel_id:3},
{order_id:10 , order_value:20000 , order_date:"25-10-2022" , order_month:"1 month" , order_status:"Delivered" , delivery_addr:"Pune" , prod_id:10 , cust_id:3 , sel_id:4},
{order_id:11 , order_value:20000 , order_date:"24-10-2022" , order_month:"1 month" , order_status:"Delivered" , delivery_addr:"Chennai" , prod_id:2 , cust_id:4 , sel_id:5},
{order_id:12 , order_value:20000 , order_date:"23-10-2022" , order_month:"1 month" , order_status:"Packed" , delivery_addr:"Hyderabad" , prod_id:13 , cust_id:5 , sel_id:3},
{order_id:13 , order_value:2000 , order_date:"22-10-2022" , order_month:"1 month" , order_status:"Delivered" , delivery_addr:"Punjab" , prod_id:16 , cust_id:9 , sel_id:1},
{order_id:14 , order_value:2000 , order_date:"21-10-2022" , order_month:"1 month" , order_status:"Packed" , delivery_addr:"Bangalore" , prod_id:12 , cust_id:12 , sel_id:2},
{order_id:15 , order_value:2000 , order_date:"20-10-2022" , order_month:"1 month" , order_status:"Delivered" , delivery_addr:"Chennai" , prod_id:10 , cust_id:13 , sel_id:3},
{order_id:16 , order_value:2000 , order_date:"15-10-2022" , order_month:"1 month" , order_status:"Delivered" , delivery_addr:"Pune" , prod_id:9, cust_id:3 , sel_id:4}
]);

db.shipment.insertMany([
{shipment_id:1 , delivery_add:"Chennai" , shiping_status:"pending"},
{shipment_id:2 , delivery_add:"Hyderabad" , shiping_status:"processed"},
{shipment_id:3 , delivery_add:"Bangalore" , shiping_status:"delivered"},
{shipment_id:4 , delivery_add:"Pune" , shiping_status:"delivered"},
{shipment_id:5 , delivery_add:"Mumbai" , shiping_status:"processed"}
]);

Simple Queries to perform on ecommerce db

1. write a query to find the average order value for all customers

Q: db.orders.aggregate([{$group:{_id: "$cust_id",avgAmount: { $avg: "$order_value" }}}]);

2. Query your database to get top 10 customer whose orders have the highest value and who have been customers since less than 2 months

Q: db.customer.find({"cust_registered_as_per_month":{"$lt":"2 Months"}}).sort({order_value:-1}).limit(10);

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

Q: db.orders.aggregate([{$match: {order_month:"This month" }},
{$group: {_id:{month:"$order_month"},
This_Month_orders: {$sum: "$orders"}}}]);

4. Query you database to find customers count in various cities, group them by name of cities

Q: db.customer.aggregate([{"$group": {_id: {Customer_city:"$cust_city"},
"count": {"$sum": 1 }, "cust_name": {"$push": "cust_name"}}},
{"$project": {"size": 1, "customer_count_same_city": {"$size": "$cust_name"}}}]);

5. Query you 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.

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