Aggregation Practice : ECommerce

Question 1: What is the total number of users in the database?

db.Users.aggregate([
  { $count: "totalUsers" }
]);

• $count: This stage returns a single document containing a count of the documents that are passed into it. The string "totalUsers" is the name of the field that will hold the final count.

• Alternative (Non-Aggregation): For this specific task, db.Users.countDocuments() is a simpler, non-aggregation command that achieves the same result.

What is the total number of orders placed

db.Orders.aggregate([
  { $count: "totalOrders" }
]);

Question 2: How many products are in each category?

db.Products.aggregate([
  { $group: { _id: "$category", count: { $sum: 1 } } },
  { $sort: { count: -1 } } // sorting by count
]);

$group: This stage groups documents by a specified key.

• _id: "$category": We tell MongoDB to create a group for each unique value in the category field.
• count: { $sum: 1 }: For every document that enters a group, we add 1 to the count field. This effectively counts the documents in each category.

Question 3: What is the total sales revenue from all orders?

db.Orders.aggregate([
  { $group: { 
	  _id: null, 
	  totalRevenue: { $sum: "$totalAmount" } 
	  } 
   }
]);

$group:

• _id: null: Using null as the group key tells MongoDB to group all documents in the collection into a single group, allowing us to calculate a grand total.
• totalRevenue: { $sum: "$totalAmount" }: For this single group, we sum the values from the totalAmount field of every document.

Question 4: How many products are currently in stock?

db.Products.aggregate([
  { $match: { inStock: true } },
  { $count: "inStockProducts" }
]);

• $match: First, we filter the documents, allowing only those where inStock is true to pass to the next stage.

• $count: This stage then counts the documents that remain after the $match filter.

Question 6: Who are the top 3 users by total amount spent?

db.Orders.aggregate([
  { $group: { _id: "$userId", totalSpent: { $sum: "$totalAmount" } } },
  { $sort: { totalSpent: -1 } },
  { $limit: 3 }
]);

• $group: We group the orders by userId and calculate the totalSpent for each user by summing their totalAmount.

• $sort: We sort the resulting documents by totalSpent in descending order (-1), so the highest spenders are first.

• $limit: We limit the output to the first 3 documents from the sorted list.

Question 7: How many orders has each user placed?

db.Orders.aggregate([
  { $group: { _id: "$userId", orderCount: { $sum: 1 } } },
  { $sort: { orderCount: -1 } }
]);

• $group: We group by userId and use { $sum: 1 } to count the number of documents (orders) in each group, assigning the result to orderCount.

• $sort: We sort the results to see who has placed the most orders.

Question 8: How many units of each product have been sold across all orders?

db.Orders.aggregate([
  // Step 1: Deconstruct the 'items' array into separate documents
  { $unwind: "$items" },

  // Step 2: Group by product ID and sum the quantities
  {
    $group: {
      _id: "$items.productId",
      totalUnitsSold: { $sum: "$items.quantity" }
    }
  },

  // Step 3: Sort to see the best-selling products first
  { $sort: { totalUnitsSold: -1 } }
]);

• $unwind: This is the crucial first step. It breaks down the items array so that if an order has 3 items, it becomes 3 separate documents. This allows us to access each item's productId and quantity individually.

• $group: We group by the items.productId field and use $sum on the items.quantity field to get the total units sold for each product.

Question 9: List all users and their city.

db.Users.aggregate([
  {
    $project: {
      _id: 0,
      name: 1,
      city: "$address.city"
    }
  }
]);

$project: We use this stage to reshape the output.

• _id: 0: Excludes the default _id field.

• name: 1: Includes the name field as is.

• city: "$address.city": Creates a new field named city and populates it with the value from the nested city field inside the address object.

Question 10: Join orders with user information to show the customer's name for each order.

db.Orders.aggregate([
  // Step 1: Join with the Users collection
  {
    $lookup: {
      from: "Users",
      localField: "userId",
      foreignField: "_id",
      as: "customerInfo"
    }
  },

  // Step 2: Deconstruct the resulting array
  { $unwind: "$customerInfo" },

  // Step 3: Project for a clean final output
  {
    $project: {
      _id: 1,
      totalAmount: 1,
      status: 1,
      customerName: "$customerInfo.name"
    }
  }
]);

• $lookup: This performs a "left join" to the Users collection. It matches documents where Orders.userId equals Users._id and adds the matching user document into a new array field called customerInfo.

• $unwind: Since $lookup creates an array and we expect only one user per order, we use $unwind to deconstruct the customerInfo array, making it a simple object.

• $project: We clean up the final output to show key order details and the customer's name.