Today’s practice involved joining two collections (users and orders) and mastering lookup, unwind, group, sort, and limit stages in aggregation pipelines!

Here’s the full practice breakdown ⤵️

🗄️ Database Structure:
Collection: users

[
  { "_id": 1, "name": "Alice", "country": "USA" },
  { "_id": 2, "name": "Bob", "country": "Canada" },
  { "_id": 3, "name": "Charlie", "country": "India" }
]

Collection: orders

[
  { "_id": 101, "userId": 1, "amount": 250 },
  { "_id": 102, "userId": 1, "amount": 300 },
  { "_id": 103, "userId": 2, "amount": 400 },
  { "_id": 104, "userId": 3, "amount": 150 },
  { "_id": 105, "userId": 3, "amount": 200 }
]

🧩 Challenge Questions and Answers:

1️⃣ Join users with their orders

[
  {
    $lookup: {
      from: "orders",
      localField: "_id",
      foreignField: "userId",
      as: "orders"
    }
  }
]

✅ Result: Each user document will now have an array of their related orders under "orders".

2️⃣ Unwind the orders

[
  {
    $lookup: {
      from: "orders",
      localField: "_id",
      foreignField: "userId",
      as: "orders"
    }
  },
  {
    $unwind: "$orders"
  }
]

✅ Result: Each order becomes a separate document with user data attached (no longer an array).

3️⃣ Calculate total spend by each user

[
  {
    $lookup: {
      from: "orders",
      localField: "_id",
      foreignField: "userId",
      as: "orders"
    }
  },
  {
    $unwind: "$orders"
  },
  {
    $group: {
      _id: "$_id",
      totalSpend: { $sum: "$orders.amount" },
      name: { $first: "$name" }
    }
  }
]

✅ Result: Each user now has a totalSpend field showing how much they spent in total!

4️⃣ Calculate average spend per country

[
  {
    $lookup: {
      from: "orders",
      localField: "_id",
      foreignField: "userId",
      as: "orders"
    }
  },
  {
    $unwind: "$orders"
  },
  {
    $group: {
      _id: "$country",
      avgSpent: { $avg: "$orders.amount" }
    }
  },
  {
    $project: {
      country: "$_id",
      avgSpent: 1,
      _id: 0
    }
  }
]

✅ Result: Average order amount for each country!

5️⃣ Find the user who spent the most (Most Expensive User)

[
  {
    $lookup: {
      from: "orders",
      localField: "_id",
      foreignField: "userId",
      as: "orders"
    }
  },
  {
    $unwind: "$orders"
  },
  {
    $group: {
      _id: "$_id",
      totalSpend: { $sum: "$orders.amount" },
      name: { $first: "$name" }
    }
  },
  {
    $sort: {
      totalSpend: -1
    }
  },
  {
    $limit: 1
  },
  {
    $project: {
      name: 1,
      totalSpend: 1,
      _id: 0
    }
  }
]

✅ Result: The top spender in the users list!

🎯 Key MongoDB Operators Practiced:

$lookup ➔ for joining collections
$unwind ➔ flatten arrays
$group ➔ aggregate data
$sort ➔ order results
$limit ➔ restrict number of outputs
$project ➔ customize final output

💬 Final Thoughts:
Working with lookup and aggregation pipelines makes MongoDB a very powerful tool for analytics, reporting, and complex data transformations!

🔥 Practice like this strengthens your real-world NoSQL database skills!