Solution: Grouping Across Collections

db.orders.aggregate([
  // (Optional) limit to 2025
  {
    $match: {
      orderDate: { $gte: ISODate("2025-01-01T00:00:00Z") }
    }
  },
  // One row per product name in the order
  { 
    $unwind: "$items" 
  },
  // Join to products by name
  {
    $lookup: {
      from: "products",
      localField: "items",      // product name in orders
      foreignField: "name",     // product name in products
      as: "product"
    }
  },
  { 
    $unwind: "$product" 
  },
  // Assume quantity = 1 per occurrence; take price from products
  {
    $project: {
      productName: "$product.name",
      productId: "$product._id",
      price: "$product.price",
      quantity: { $literal: 1 },
      revenue: { $multiply: [ "$product.price", 1 ] }
    }
  },
  // Summaries per product
  {
    $group: {
      _id: "$productId",
      totalQuantity: { $sum: "$quantity" },
      totalRevenue: { $sum: "$revenue" }
    }
  },
  { 
    $sort: { totalRevenue: -1 } 
  }
])