A full coding-round problem, worked

One realistic coding-round problem, from reading the question to the finished pipeline. The useful part is not the answer - it is the order the stages get decided in, before a single line is written.

Collections:

users

{
  _id: 101,
  name: "Denish",
  country: "India"
}

orders

{
  _id: 1,
  userId: 101,
  status: "completed",

  items: [
    {
      product: "Laptop",
      price: 1000,
      quantity: 1
    }
  ]
}

Question:

Find the top 5 users by total completed-order spending, including their names.

Think first

What documents?

Completed orders

→ $match

Calculate order/item revenue?

Items are arrays.

We could:

$unwind
→ one document per item

Combine spending per user?

$group

Get user information?

$lookup

User object is one result?

$unwind

Rank?

$sort
→ $limit

Pipeline

db.orders.aggregate([
  {
    $match: {
      status: "completed"
    }
  },

  {
    $unwind: "$items"
  },

  {
    $group: {
      _id: "$userId",

      totalSpent: {
        $sum: {
          $multiply: [
            "$items.price",
            "$items.quantity"
          ]
        }
      }
    }
  },

  {
    $lookup: {
      from: "users",
      localField: "_id",
      foreignField: "_id",
      as: "user"
    }
  },

  {
    $unwind: "$user"
  },

  {
    $project: {
      _id: 0,

      userId: "$_id",
      name: "$user.name",
      totalSpent: 1
    }
  },

  {
    $sort: {
      totalSpent: -1
    }
  },

  {
    $limit: 5
  }
])

This looks long.

But it is just:

Completed orders
        ↓
Individual items
        ↓
Revenue per user
        ↓
Join users
        ↓
Format result
        ↓
Top 5