Project 2

db.ecommerce.insertMany([
{_id:1,"cust_name":"ravi","cust_email":"[email protected]","cust_city":"visakapatnam","cust_no":"9111111111","cust_type":"premium","ord_no":"106"},
{_id:2,"cust_name":"raj","cust_email":"[email protected]","cust_city":"patna","cust_no":"9111111112","cust_type":"normal","ord_no":"103"},
{_id:3,"cust_name":"anjali","cust_email":"[email protected]","cust_city":"munnar","cust_no":"9111111113","cust_type":"premium","ord_no":"104"},
{_id:4,"cust_name":"john","cust_email":"[email protected]","cust_city":"mumbai","cust_no":"9111111114","cust_type":"normal","ord_no":"101"},
{_id:5,"cust_name":"balu","cust_email":"[email protected]","cust_city":"nagpur","cust_no":"9111111115","cust_type":"premium","ord_no":"102"},
{_id:6,"cust_name":"jyothi","cust_email":"[email protected]","cust_city":"nagpur","cust_no":"9111111116","cust_type":"premium","ord_no":"105"}])

db.products.insertMany([
{_id:301,"prod_name":"mens wear","prod_type":"shirts,jeans,trousers","brand":"puma"},
{_id:302,"prod_name":"women wear","prod_type":"shirts,jeans,trousers","brand":"louis"},
{_id:303,"prod_name":"electronics","prod_type":"mobiles,watches,Tv's","brand":"realme"},
{_id:304,"prod_name":"home appliances","prod_type":"mixer grinders,washing machine ,fridge","brand":"whirlpool"}])

db.seller.insertMany([
{_id:201,"seller_name":"stephen","seller_email":"[email protected]","seller_ml_no":9222222222,"seller_city":"rajam"},
{_id:202,"seller_name":"stuart","seller_email":"[email protected]","seller_ml_no":9222222221,"seller_city":"visakapatnam"},
{_id:203,"seller_name":"robert","seller_email":"[email protected]","seller_ml_no":9222222223,"seller_city":"pune"},
{_id:204,"seller_name":"shaw","seller_email":"[email protected]","seller_ml_no":9222222224,"seller_city":"patna"}])

db.admin.insertMany([
{_id:401,"admin_name":"raisy","admin_email":"[email protected]","admin_mbl_no":9333333333,"admin_city":"jamshedpur"},
{_id:402,"admin_name":"dravid","admin_email":"[email protected]","admin_mbl_no":9333333334,"admin_city":"kolkata"}])

db.billing.insertMany([
{_id:21,"payment_status":"payment done","order_place":"visakahapatnam","no_of_products":7},
{_id:22,"payment_status":"payment pending","order_place":"mumbai","no_of_products":2},
{_id:23,"payment_status":"payment done","order_place":"munnar","no_of_products":5},
{_id:24,"payment_status":"payment pending","order_place":"nagpur","no_of_products":10},
{_id:25,"payment_status":"payment done","order_place":"patna","no_of_products":11}])

db.orders.insertMany([
{_id:101,"order_date":2023-02-25,"order_status":"delivered","customer_id":4,"product_id":302,"no_of_orders":3},
{_id:102,"order_date":2023-01-25,"order_status":"returned","customer_id":5,"product_id":301,"no_of_orders":6},
{_id:103,"order_date":2023-03-10,"order_status":"shipped","customer_id":2,"product_id":304,"no_of_orders":2},
{_id:104,"order_date":2023-01-25,"order_status":"delivered","customer_id":3,"product_id":301,"no_of_orders":8}])

1a)db.billing.aggregate($group:{"_id":"_id",AverageValue:{$avg:"$orders"}}})

2a)db.ecommerce.find({"customer_arrival":{"$lt":"2months"}}).sort({order_value:-1}).limit(10)

3a)db.order.aggregate($match:{order_date:{"$gte":"2023-01-01","$lte":"2023-03-31"},{$group:{_id:"$customer_id","no_of_orders"{$sum:"$order successfully"}}}})

4a)db.customer.aggregate($group:{"cust_city":"city",{"$match":{"city"}}})

5a)db.getCollection("seller").aggregate([{$lookup:{"customer",localfield:"seller_city",foreignfield:"cust_city" as:"cust_details"}},{"$sort":{"product_sold":-1}}])