Skip to content
BranchesTags

Latest commit

 

History

4 Commits

Folders and files

NameName
Last commit message
Last commit date
Collection.png
Collection.png
 
 
ER.png
ER.png
 
 
Readme.md
Readme.md
 
 
TASKTOOLS.js
TASKTOOLS.js
 
 

Repository files navigation

TASKTOOLS

MongoDB Data Analysis Project

This document describes a data analysis project for TASKTOOLS, a company that sells office supplies. The main goal is to use MongoDB’s query and aggregation features to find useful business insights from the company’s data. By looking at connections between customers, orders, products, and sales representatives.
This project focuses on comparing ways to work with data. For each business question, I provide a solution using the modern MongoDB aggregation pipeline. Keep in mind that there are many ways to solve a problem in both SQL and MongoDB, and the solutions here are just one example.🙂

Database Schema

The analysis uses a database with six main collections, each with a specific role:

Customers: Stores information about each customer, including contact details and their sales representative.

Orders: Keeps records of all customer orders, including the date, total amount, and status.

Products: A list of all office supplies, with details like price, category, and stock quantity.

OrderDetails: Connects Orders to Products, showing which products and quantities are in each order.

Suppliers: Lists all suppliers who provide products to TASKTOOLS.

ProductSuppliers: Links Products and Suppliers, showing which supplier provides which products.

Let's Start

First, connect to DB. In this case my db is called TASKTOOLS

const db = connect("mongodb://localhost:27017/TASKTOOLS");

Then, create collections

// Create Customers collection
db.Customers.insertMany([
    { CustomerID: 1, FirstName: 'Alice', LastName: 'Johnson', Email: 'alice.johnson@example.com', Phone: '123-456-7890', Address: '123 Maple St', City: 'Springfield', Country: 'USA', SalesRepID: 2 },
    { CustomerID: 2, FirstName: 'Bob', LastName: 'Smith', Email: 'bob.smith@example.com', Phone: '234-567-8901', Address: '456 Oak St', City: 'Greenville', Country: 'USA', SalesRepID: 1 },
    { CustomerID: 3, FirstName: 'Carol', LastName: 'Williams', Email: 'carol.williams@example.com', Phone: '345-678-9012', Address: '789 Pine St', City: 'Riverdale', Country: 'Canada', SalesRepID: 3 },
    { CustomerID: 4, FirstName: 'David', LastName: 'Brown', Email: 'david.brown@example.com', Phone: '456-789-0123', Address: '101 Elm St', City: 'Lakeside', Country: 'Canada', SalesRepID: 2 },
    { CustomerID: 5, FirstName: 'Eve', LastName: 'Jones', Email: 'eve.jones@example.com', Phone: '567-890-1234', Address: '202 Birch St', City: 'Hilltown', Country: 'USA', SalesRepID: 1 },
    { CustomerID: 6, FirstName: 'Frank', LastName: 'Miller', Email: 'frank.miller@example.com', Phone: '678-901-2345', Address: '303 Cedar St', City: 'Mapleton', Country: 'Canada', SalesRepID: 3 },
    { CustomerID: 7, FirstName: 'Grace', LastName: 'Davis', Email: 'grace.davis@example.com', Phone: '789-012-3456', Address: '404 Walnut St', City: 'Oakwood', Country: 'USA', SalesRepID: 2 },
    { CustomerID: 8, FirstName: 'Henry', LastName: 'Martinez', Email: 'henry.martinez@example.com', Phone: '890-123-4567', Address: '505 Willow St', City: 'Pineville', Country: 'USA', SalesRepID: 1 },
    { CustomerID: 9, FirstName: 'Ivy', LastName: 'Garcia', Email: 'ivy.garcia@example.com', Phone: '901-234-5678', Address: '606 Cherry St', City: 'Brookside', Country: 'Canada', SalesRepID: 3 },
    { CustomerID: 10, FirstName: 'Jack', LastName: 'Wilson', Email: 'jack.wilson@example.com', Phone: '012-345-6789', Address: '707 Poplar St', City: 'Fairview', Country: 'USA', SalesRepID: 2 }
]);

// // Create Orders collection
db.Orders.insertMany([
    { OrderID: 1, CustomerID: 3, OrderDate: new Date('2024-06-01'), Status: 'Shipped', ShippingDate: new Date('2024-06-05'), TotalAmount: 150.00, SalesRepID: 1 },
    { OrderID: 2, CustomerID: 5, OrderDate: new Date('2024-06-02'), Status: 'Pending', ShippingDate: null, TotalAmount: 200.00, SalesRepID: 2 },
    { OrderID: 3, CustomerID: 7, OrderDate: new Date('2024-06-03'), Status: 'Delivered', ShippingDate: new Date('2024-06-07'), TotalAmount: 250.00, SalesRepID: 3 },
    { OrderID: 4, CustomerID: 2, OrderDate: new Date('2024-06-04'), Status: 'Cancelled', ShippingDate: null, TotalAmount: 100.00, SalesRepID: 2 },
    { OrderID: 5, CustomerID: 10, OrderDate: new Date('2024-06-05'), Status: 'Shipped', ShippingDate: new Date('2024-06-10'), TotalAmount: 300.00, SalesRepID: 1 },
    { OrderID: 6, CustomerID: 4, OrderDate: new Date('2024-06-06'), Status: 'Shipped', ShippingDate: new Date('2024-06-12'), TotalAmount: 400.00, SalesRepID: 3 },
    { OrderID: 7, CustomerID: 9, OrderDate: new Date('2024-06-07'), Status: 'Pending', ShippingDate: null, TotalAmount: 350.00, SalesRepID: 1 },
    { OrderID: 8, CustomerID: 6, OrderDate: new Date('2024-06-08'), Status: 'Delivered', ShippingDate: new Date('2024-06-13'), TotalAmount: 450.00, SalesRepID: 2 },
    { OrderID: 9, CustomerID: 1, OrderDate: new Date('2024-06-09'), Status: 'Shipped', ShippingDate: new Date('2024-06-14'), TotalAmount: 500.00, SalesRepID: 3 },
    { OrderID: 10, CustomerID: 8, OrderDate: new Date('2024-06-10'), Status: 'Cancelled', ShippingDate: null, TotalAmount: 550.00, SalesRepID: 1 }
]);

// // Create Products collection
db.Products.insertMany([
    { ProductID: 1, ProductName: 'Tablet', Category: 'Electronics', StockQuantity: 100, Price: 299.99 },
    { ProductID: 2, ProductName: 'Keyboard', Category: 'Accessories', StockQuantity: 300, Price: 29.99 },
    { ProductID: 3, ProductName: 'Chair', Category: 'Furniture', StockQuantity: 70, Price: 99.99 },
    { ProductID: 4, ProductName: 'Desk', Category: 'Furniture', StockQuantity: 40, Price: 399.99 },
    { ProductID: 5, ProductName: 'Monitor', Category: 'Electronics', StockQuantity: 80, Price: 199.99 },
    { ProductID: 6, ProductName: 'Headphones', Category: 'Electronics', StockQuantity: 200, Price: 89.99 },
    { ProductID: 7, ProductName: 'Smartphone', Category: 'Electronics', StockQuantity: 150, Price: 499.99 },
    { ProductID: 8, ProductName: 'Printer', Category: 'Office Supplies', StockQuantity: 60, Price: 149.99 },
    { ProductID: 9, ProductName: 'Mouse', Category: 'Accessories', StockQuantity: 250, Price: 19.99 },
    { ProductID: 10, ProductName: 'Laptop', Category: 'Electronics', StockQuantity: 50, Price: 999.99 }
]);

// // Create OrderDetails collection
db.OrderDetails.insertMany([
    { OrderDetailID: 1, OrderID: 1, ProductID: 10, Quantity: 1, UnitPrice: 999.99 },
    { OrderDetailID: 2, OrderID: 2, ProductID: 6, Quantity: 4, UnitPrice: 199.99 },
    { OrderDetailID: 3, OrderID: 3, ProductID: 4, Quantity: 2, UnitPrice: 29.99 },
    { OrderDetailID: 4, OrderID: 4, ProductID: 9, Quantity: 3, UnitPrice: 19.99 },
    { OrderDetailID: 5, OrderID: 5, ProductID: 5, Quantity: 4, UnitPrice: 199.99 },
    { OrderDetailID: 6, OrderID: 6, ProductID: 7, Quantity: 1, UnitPrice: 499.99 },
    { OrderDetailID: 7, OrderID: 7, ProductID: 8, Quantity: 2, UnitPrice: 149.99 },
    { OrderDetailID: 8, OrderID: 8, ProductID: 3, Quantity: 1, UnitPrice: 99.99 },
    { OrderDetailID: 9, OrderID: 9, ProductID: 10, Quantity: 1, UnitPrice: 999.99 },
    { OrderDetailID: 10, OrderID: 10, ProductID: 2, Quantity: 3, UnitPrice: 29.99 }
]);

// // Create Suppliers collection
db.Suppliers.insertMany([
    { SupplierID: 1, SupplierName: 'Tech Supplies Inc.', ContactName: 'John Doe', ContactEmail: 'john.doe@techsupplies.com', Phone: '123-456-7890', Country: 'USA' },
    { SupplierID: 2, SupplierName: 'Office World', ContactName: 'Jane Smith', ContactEmail: 'jane.smith@officeworld.com', Phone: '234-567-8901', Country: 'USA' },
    { SupplierID: 3, SupplierName: 'Furniture Hub', ContactName: 'Mike Johnson', ContactEmail: 'mike.johnson@furniturehub.com', Phone: '345-678-9012', Country: 'Canada' },
    { SupplierID: 4, SupplierName: 'ElectroMart', ContactName: 'Emily Davis', ContactEmail: 'emily.davis@electromart.com', Phone: '456-789-0123', Country: 'Canada' }
]);

// // Create ProductSuppliers collection
db.ProductSuppliers.insertMany([
    { ProductID: 1, SupplierID: 4 },
    { ProductID: 2, SupplierID: 2 },
    { ProductID: 3, SupplierID: 3 },
    { ProductID: 4, SupplierID: 3 },
    { ProductID: 5, SupplierID: 4 },
    { ProductID: 6, SupplierID: 4 },
    { ProductID: 7, SupplierID: 1 },
    { ProductID: 8, SupplierID: 2 },
    { ProductID: 9, SupplierID: 2 },
    { ProductID: 10, SupplierID: 1 }
]);

Summary of Collection in MongoDB Compass

img

From the collections we can see relationship description as shown here

Customers (1) ----< Orders (M) ----< OrderDetails (M) >---- Products (1)
Products (M) ----< ProductSuppliers (M) >---- Suppliers (1)
Customers (M) ---- SalesReps (1)
Orders (M) ---- SalesReps (1)

ER diagram

img

Before we start queries any data, we have to use the collection by the use keyword and our collection name.

use("TASKTOOLS");

Business Questions

Q1: Find the total number of orders placed by each customer.

MongoDB:

printjson(db.Orders.aggregate([
    {
        $group: {
          _id: "$CustomerID",
          TotalOrders: {
            $sum: 1
          }
        }
    }

]).toArray());

Result:

[
  {
    _id: 9,
    TotalOrders: 1
  },
  {
    _id: 1,
    TotalOrders: 1
  },
  {
    _id: 2,
    TotalOrders: 1
  },
  {
    _id: 4,
    TotalOrders: 1
  },
  {
    _id: 3,
    TotalOrders: 1
  },
  {
    _id: 5,
    TotalOrders: 1
  },
  {
    _id: 7,
    TotalOrders: 1
  },
  {
    _id: 10,
    TotalOrders: 1
  },
  {
    _id: 6,
    TotalOrders: 1
  },
  {
    _id: 8,
    TotalOrders: 1
  }
]

Q2: List all customers who have placed more than 2 orders.

MongoDB:

printjson(
  db.Orders.aggregate([
    {
      $group: {
        _id: "$CustomerID",
        TotalOrders: { $sum: 1 },
      },
    },
    {
      $match: {
        TotalOrders: { $gt: 2 },
      },
    },
  ]).toArray()
);

Result: In the collections there's no customer who placed morn than 2 order, that's why we get empty array.

[]

Q3: Find the total sales amount for each sales representative.

MongoDB:

printjson(db.Orders.aggregate([
    {
        $group: {
            _id: "$SalesRepID",
            TotalAmount: { $sum: "$TotalAmount"  }
        }
    }
]).toArray());

Result:

[
  {
    _id: 3,
    TotalAmount: 1150
  },
  {
    _id: 2,
    TotalAmount: 750
  },
  {
    _id: 1,
    TotalAmount: 1350
  }
]

Q4: List all products with a price greater than $200.

MongoDB:

printjson(
  db.Products.aggregate([
    {
      $match: {
        Price: { $gt: 200 },
      },
    },
  ]).toArray()
);

Result:

[
  {
    _id: ObjectId('68c2fdf1f5612ef8b0748a73'),
    ProductID: 1,
    ProductName: 'Tablet',
    Category: 'Electronics',
    StockQuantity: 100,
    Price: 299.99
  },
  {
    _id: ObjectId('68c2fdf1f5612ef8b0748a76'),
    ProductID: 4,
    ProductName: 'Desk',
    Category: 'Furniture',
    StockQuantity: 40,
    Price: 399.99
  },
  {
    _id: ObjectId('68c2fdf1f5612ef8b0748a79'),
    ProductID: 7,
    ProductName: 'Smartphone',
    Category: 'Electronics',
    StockQuantity: 150,
    Price: 499.99
  },
  {
    _id: ObjectId('68c2fdf1f5612ef8b0748a7c'),
    ProductID: 10,
    ProductName: 'Laptop',
    Category: 'Electronics',
    StockQuantity: 50,
    Price: 999.99
  }
]

Q5: Find the average price of products in each category.

MongoDB:

printjson(
  db.Products.aggregate([
    {
      $group: {
        _id: "$Category",
        averagePrice: { $avg: "$Price" },
      },
    },
  ]).toArray()
);

Result:

[
  {
    _id: 'Accessories',
    averagePrice: 24.99
  },
  {
    _id: 'Office Supplies',
    averagePrice: 149.99
  },
  {
    _id: 'Electronics',
    averagePrice: 417.98999999999995
  },
  {
    _id: 'Furniture',
    averagePrice: 249.99
  }
]

Q6: Count the number of products that have stock quantity less than 50.

MongoDB:

let numberOfProduct = db.Products.aggregate([
    {
        $match: {
           StockQuantity: { $lt: 50}
        }
    },
    {
        $count: "Number of Product"
    }
]).toArray();
print(numberOfProduct.length)

Result:

1

Q7: Find all orders with a total amount greater than $300.

MongoDB:

printjson(
  db.Orders.aggregate([
    {
      $match: {
        TotalAmount: { $gt: 300}
      },
    },
  ]).toArray()
);

Result:

[
  {
    _id: ObjectId('68c2fdf1f5612ef8b0748a6e'),
    OrderID: 6,
    CustomerID: 4,
    OrderDate: ISODate('2024-06-06T00:00:00.000Z'),
    Status: 'Shipped',
    ShippingDate: ISODate('2024-06-12T00:00:00.000Z'),
    TotalAmount: 400,
    SalesRepID: 3
  },
  {
    _id: ObjectId('68c2fdf1f5612ef8b0748a6f'),
    OrderID: 7,
    CustomerID: 9,
    OrderDate: ISODate('2024-06-07T00:00:00.000Z'),
    Status: 'Pending',
    ShippingDate: null,
    TotalAmount: 350,
    SalesRepID: 1
  },
  {
    _id: ObjectId('68c2fdf1f5612ef8b0748a70'),
    OrderID: 8,
    CustomerID: 6,
    OrderDate: ISODate('2024-06-08T00:00:00.000Z'),
    Status: 'Delivered',
    ShippingDate: ISODate('2024-06-13T00:00:00.000Z'),
    TotalAmount: 450,
    SalesRepID: 2
  },
  {
    _id: ObjectId('68c2fdf1f5612ef8b0748a71'),
    OrderID: 9,
    CustomerID: 1,
    OrderDate: ISODate('2024-06-09T00:00:00.000Z'),
    Status: 'Shipped',
    ShippingDate: ISODate('2024-06-14T00:00:00.000Z'),
    TotalAmount: 500,
    SalesRepID: 3
  },
  {
    _id: ObjectId('68c2fdf1f5612ef8b0748a72'),
    OrderID: 10,
    CustomerID: 8,
    OrderDate: ISODate('2024-06-10T00:00:00.000Z'),
    Status: 'Cancelled',
    ShippingDate: null,
    TotalAmount: 550,
    SalesRepID: 1
  }
]

Q8: List all suppliers located in the USA.

MongoDB:

printjson(db.Suppliers.aggregate([
    {
        $match: {
           Country: { $regex: "USA"}
        }
    }
]).toArray());

Result:

[
  {
    _id: ObjectId('68c2fdf1f5612ef8b0748a87'),
    SupplierID: 1,
    SupplierName: 'Tech Supplies Inc.',
    ContactName: 'John Doe',
    ContactEmail: 'john.doe@techsupplies.com',
    Phone: '123-456-7890',
    Country: 'USA'
  },
  {
    _id: ObjectId('68c2fdf1f5612ef8b0748a88'),
    SupplierID: 2,
    SupplierName: 'Office World',
    ContactName: 'Jane Smith',
    ContactEmail: 'jane.smith@officeworld.com',
    Phone: '234-567-8901',
    Country: 'USA'
  }
]

Q9: Find the maximum price of any product.

MongoDB:

printjson(db.Products.aggregate([
    {
        $group: {
            _id: null,
            maxPrice : {
                $max: "$Price"
            }
        }
    }
]).toArray());

Result:

[
  {
    _id: null,
    maxPrice: 999.99
  }
]

Q10: List all products supplied by 'Office World'.

MongoDB:

let find_sup = db.Suppliers.find(
  {
    SupplierName: { $regex: "Office World" },
  },
  { _id: 0, SupplierID: 1 }
).toArray();

let supID = find_sup.map((e) => e.SupplierID);

printjson(
  db.Products.aggregate([
    {
      $lookup: {
        from: "ProductSuppliers",
        localField: "ProductID",
        foreignField: "ProductID",
        as: "pdSupply",
      },
    },
    {
      $unwind: "$pdSupply",
    },
    {
      $match: {
        "pdSupply.SupplierID": { $in: supID },
      },
    },
    {
      $project: {
        pdSupply: 0,
      },
    },
  ]).toArray()
);

Result:

[
  {
    _id: ObjectId('68c2fdf1f5612ef8b0748a74'),
    ProductID: 2,
    ProductName: 'Keyboard',
    Category: 'Accessories',
    StockQuantity: 300,
    Price: 29.99
  },
  {
    _id: ObjectId('68c2fdf1f5612ef8b0748a7a'),
    ProductID: 8,
    ProductName: 'Printer',
    Category: 'Office Supplies',
    StockQuantity: 60,
    Price: 149.99
  },
  {
    _id: ObjectId('68c2fdf1f5612ef8b0748a7b'),
    ProductID: 9,
    ProductName: 'Mouse',
    Category: 'Accessories',
    StockQuantity: 250,
    Price: 19.99
  }
]

Q11: Count the number of distinct categories of products.

MongoDB:

let distinctProduct = db.Products.distinct("Category");
print(distinctProduct.length);

Result:

4

Q12: Customers who have never placed an order

MongoDB:

let getCustomerIDs = db.Orders.distinct("CustomerID");
printjson(
  db.Customers.aggregate([
    {
      $match: {
        CustomerID : { $nin: getCustomerIDs}
      },
    },
  ]).toArray()
);

Result:

[]

Again, recall from question 2 that means no customer has placed zero order or more than 2 order. So in this example collection we have 1 order per customer. 🙀

Q13: List all orders along with the products ordered in each.

MongoDB:

let getOrderIds = db.Orders.find({}, { _id: 0, OrderID: 1 })
  .map((e) => e.OrderID)
  .toArray();

printjson(
  db.OrderDetails.aggregate([
    {
      $lookup: {
        from: "Products",
        localField: "ProductID",
        foreignField: "ProductID",
        as: "product",
      },
    },
    {
      $unwind: "$product",
    },
    {
      $match: {
        OrderID: { $in: getOrderIds },
      },
    },
    {
      $project: {
        _id: 0,
        OrderID: 1,
        ProductName: "$product.ProductName",
      },
    },
  ]).toArray()
);

Result:

[
  {
    OrderID: 1,
    ProductName: 'Laptop'
  },
  {
    OrderID: 2,
    ProductName: 'Headphones'
  },
  {
    OrderID: 3,
    ProductName: 'Desk'
  },
  {
    OrderID: 4,
    ProductName: 'Mouse'
  },
  {
    OrderID: 5,
    ProductName: 'Monitor'
  },
  {
    OrderID: 6,
    ProductName: 'Smartphone'
  },
  {
    OrderID: 7,
    ProductName: 'Printer'
  },
  {
    OrderID: 8,
    ProductName: 'Chair'
  },
  {
    OrderID: 9,
    ProductName: 'Laptop'
  },
  {
    OrderID: 10,
    ProductName: 'Keyboard'
  }
]

Q14: Find the total quantity of 'Keyboard' sold.

MongoDB:

let getKeybordId = db.Products.find(
  { ProductName: "Keyboard" },
  { _id: 0, ProductID: 1 }
)
  .map((e) => e.ProductID)
  .toArray();

printjson(
  db.OrderDetails.aggregate([
    {
        $match: {
            ProductID: {$in: getKeybordId}
        }
    },
    {
      $group: {
        _id: null,
        totalQuantity: { $sum: "$Quantity" },
      },
    },
    {
      $project: {
        ProductID: 1,
        totalQuantity: "$totalQuantity"
      },
    },
  ]).toArray()
);

Result:

[
  {
    _id: null,
    totalQuantity: 3
  }
]

Q15: Average total amount of orders for each customer

MongoDB:

printjson(db.Orders.aggregate([
    {
        $group: {
            _id: "$CustomerID",
            avgTotalAmount: { $avg: "$TotalAmount"}
        }
    }

]).toArray());

Result:

[
  {
    _id: 1,
    avgTotalAmount: 500
  },
  {
    _id: 4,
    avgTotalAmount: 400
  },
  {
    _id: 3,
    avgTotalAmount: 150
  },
  {
    _id: 5,
    avgTotalAmount: 200
  },
  {
    _id: 7,
    avgTotalAmount: 250
  },
  {
    _id: 2,
    avgTotalAmount: 100
  },
  {
    _id: 10,
    avgTotalAmount: 300
  },
  {
    _id: 6,
    avgTotalAmount: 450
  },
  {
    _id: 8,
    avgTotalAmount: 550
  },
  {
    _id: 9,
    avgTotalAmount: 350
  }
]

Q16: List all products ordered by each customer.

MongoDB:

// join 3 collections -> group -> project
printjson(
  db.Orders.aggregate([
    {
      $lookup: {
        from: "OrderDetails",
        localField: "OrderID",
        foreignField: "OrderID",
        as: "OrderInfo",
      },
    },
    {
      $unwind: "$OrderInfo",
    },
    {
      $lookup: {
        from: "Products",
        localField: "OrderInfo.ProductID",
        foreignField: "ProductID",
        as: "ProductInfo",
      },
    },
    {
      $unwind: "$ProductInfo",
    },
    {
      $lookup: {
        from: "Customers",
        localField: "CustomerID",
        foreignField: "CustomerID",
        as: "CustomerInfo",
      },
    },
    {
      $unwind: "$CustomerInfo",
    },
    {
      $group: {
        _id: {
          customerID: "$CustomerInfo.CustomerID",
          productName: "$ProductInfo.ProductName",
          orderID: "$OrderID",
        },
        quantity: { $sum: "$OrderInfo.Quantity" },
      },
    },
    {
      $project: {
        _id: 0,
        ProductName: "$_id.productName",
        Quantity: "$quantity",
        CustomerID: "$_id.customerID",
        OrderID: "$_id.orderID",
      },
    },
  ]).toArray()
);

Result:

[
  {
    ProductName: 'Headphones',
    Quantity: 4,
    CustomerID: 5,
    OrderID: 2
  },
  {
    ProductName: 'Chair',
    Quantity: 1,
    CustomerID: 6,
    OrderID: 8
  },
  {
    ProductName: 'Keyboard',
    Quantity: 3,
    CustomerID: 8,
    OrderID: 10
  },
  {
    ProductName: 'Desk',
    Quantity: 2,
    CustomerID: 7,
    OrderID: 3
  },
  {
    ProductName: 'Monitor',
    Quantity: 4,
    CustomerID: 10,
    OrderID: 5
  },
  {
    ProductName: 'Laptop',
    Quantity: 1,
    CustomerID: 1,
    OrderID: 9
  },
  {
    ProductName: 'Mouse',
    Quantity: 3,
    CustomerID: 2,
    OrderID: 4
  },
  {
    ProductName: 'Laptop',
    Quantity: 1,
    CustomerID: 3,
    OrderID: 1
  },
  {
    ProductName: 'Smartphone',
    Quantity: 1,
    CustomerID: 4,
    OrderID: 6
  },
  {
    ProductName: 'Printer',
    Quantity: 2,
    CustomerID: 9,
    OrderID: 7
  }
]

Q17: Find the total revenue generated by each product category.

MongoDB:

printjson(db.Products.aggregate([
    {
        $lookup: {
            from: "OrderDetails",
            localField: "ProductID",
            foreignField: "ProductID",
            as: "OrderDetailsInfo"
        }
    },
    {
        $unwind: "$OrderDetailsInfo"
    },
    {
        $project: {
            Category: 1,
            TotalRevenue: {
                $multiply: ["$OrderDetailsInfo.UnitPrice","$OrderDetailsInfo.Quantity"]
            }
        }
    },
    {
        $group: {
            _id: "$Category",
            revenue: { $sum: "$TotalRevenue"}
        }
    }

]).toArray());

Result:

[
  {
    _id: 'Accessories',
    revenue: 149.94
  },
  {
    _id: 'Office Supplies',
    revenue: 299.98
  },
  {
    _id: 'Electronics',
    revenue: 4099.89
  },
  {
    _id: 'Furniture',
    revenue: 159.97
  }
]

Q18: Find the total number of orders with status 'Shipped'.

MongoDB:

let statusShipped = db.Orders.findOne({Status: { $regex: "Shipped"}});
print(db.Orders.countDocuments({Status: statusShipped.Status}));

Result:

4

Q19: Find the products that have been ordered more than 5 times.

MongoDB:

printjson(
  db.OrderDetails.aggregate([
    {
      $group: {
        _id: "$ProductID",
        count: { $sum: 1 },
      },
    },
    {
      $match: {
        count: { $gt: 5 },
      },
    },
    {
      $project: {
        Order: "$count",
      },
    },
  ]).toArray()
);

Result:

[]

Q20: Find the average quantity ordered for each product.

MongoDB:

printjson(
  db.OrderDetails.aggregate([
    {
      $group: {
        _id: "$ProductID",
        averageQuantity: { $avg: "$Quantity" },
      },
    },
    {
      $lookup: {
        from: "Products",
        localField: "_id",
        foreignField: "ProductID",
        as: "ProductInfo",
      },
    },
    {
      $unwind: "$ProductInfo",
    },    
    {
        $project: {
            _id: 0,
            averageQuantity: "$averageQuantity",
            productName: "$ProductInfo.ProductName"
            
        }
    }
  ]).toArray()
);

Result:

[
  {
    averageQuantity: 3,
    productName: 'Keyboard'
  },
  {
    averageQuantity: 3,
    productName: 'Mouse'
  },
  {
    averageQuantity: 2,
    productName: 'Printer'
  },
  {
    averageQuantity: 4,
    productName: 'Headphones'
  },
  {
    averageQuantity: 1,
    productName: 'Laptop'
  },
  {
    averageQuantity: 4,
    productName: 'Monitor'
  },
  {
    averageQuantity: 1,
    productName: 'Smartphone'
  },
  {
    averageQuantity: 2,
    productName: 'Desk'
  },
  {
    averageQuantity: 1,
    productName: 'Chair'
  }
]

And that is the end of our project!

🙏 Thank You!

Thanks for reading! I hope you learned something useful.
Happy coding and happy querying! 🚀

Back to Top

About

No description, website, or topics provided.

Resources

Readme
Activity

Stars

0 stars

Watchers

0 watching

Forks

0 forks
Report repository

Releases

Packages

Contributors

Languages