Window functions with $setWindowFields

conceptThe concept

$setWindowFields (5.0+) adds computed fields to every document based on a window of related documents. $group collapses many documents into one; window functions keep every document and decorate it.

• partitionBy: the expression that splits documents into independent groups (per customer, per device). Omit it for one global partition.
• sortBy: the order inside each partition. Required for ranking, $shift and position-based windows.
• output: one field per window computation, each with an operator and an optional window.
• window.documents: position bounds such as ["unbounded", "current"] (running total) or [-2, 0] (current row and two before). window.range bounds by the sortBy value instead, with unit for dates (for example the last 7 days).

whyWhy it matters

Reports such as "month-over-month revenue growth", "each customer's order sequence number" or "7-day moving average latency" used to need application code or self-joins. Window functions keep them in one pipeline, close to the data, and they are a frequent interview topic for analytics-heavy roles.

Use caseReal-world scenario

A finance dashboard shows monthly revenue, the 3-month moving average and the change versus the previous month. One pipeline groups orders by month, then $setWindowFields sorted by month adds ma3 with a [-2, 0] window and prev with $shift; a final $set computes the delta.

Try itExamples

Run and edit these in the interactive lesson.

db.orders.aggregate([
  { $match: { customerId: { $in: [1, 2] } } },
  { $setWindowFields: {
      partitionBy: "$customerId",
      sortBy: { createdAt: 1 },
      output: {
        orderNo: { $documentNumber: {} },
        runningSpend: { $sum: "$total", window: { documents: ["unbounded", "current"] } }
      }
  } },
  { $project: { _id: 0, customerId: 1, createdAt: 1, total: 1, orderNo: 1, runningSpend: { $round: ["$runningSpend", 2] } } },
  { $limit: 6 }
])

db.orders.aggregate([
  { $match: { status: { $in: ["paid", "shipped", "delivered"] } } },
  { $group: { _id: { $dateTrunc: { date: "$createdAt", unit: "month" } }, revenue: { $sum: "$total" } } },
  { $setWindowFields: {
      sortBy: { _id: 1 },
      output: {
        ma3: { $avg: "$revenue", window: { documents: [-2, 0] } },
        prev: { $shift: { output: "$revenue", by: -1, default: null } }
      }
  } },
  { $project: { month: { $dateToString: { date: "$_id", format: "%Y-%m" } }, _id: 0,
      revenue: { $round: ["$revenue", 0] }, ma3: { $round: ["$ma3", 0] },
      change: { $cond: [{ $eq: ["$prev", null] }, null, { $round: [{ $subtract: ["$revenue", "$prev"] }, 0] }] } } },
  { $sort: { month: -1 } },
  { $limit: 6 }
])

db.orders.aggregate([
  { $match: { status: "delivered" } },
  { $group: { _id: { customerId: "$customerId", country: "$shipping.country" }, spend: { $sum: "$total" } } },
  { $setWindowFields: {
      partitionBy: "$_id.country",
      sortBy: { spend: -1 },
      output: { rank: { $rank: {} } }
  } },
  { $match: { rank: { $lte: 2 } } },
  { $project: { _id: 0, country: "$_id.country", customerId: "$_id.customerId", spend: { $round: ["$spend", 2] }, rank: 1 } },
  { $sort: { country: 1, rank: 1 } }
])

internalsUnder the hood

Each partition is sorted and processed in order; documents-based windows keep only the rows in the window, but unbounded windows and large partitions hold state in memory and spill to disk when needed. Put a $match first, and if the pipeline sorts by an indexed field within a filtered set, an index on { partitionField: 1, sortField: 1 } lets the sort be served by the index.

Avoid / preferDo this, not that

// running total in the application
let sum = 0
rows.forEach(r => { sum += r.revenue; r.running = sum })

db.orders.aggregate([
  { $group: { _id: { $dateTrunc: { date: "$createdAt", unit: "month" } }, revenue: { $sum: "$total" } } },
  { $setWindowFields: { sortBy: { _id: 1 }, output: {
      running: { $sum: "$revenue", window: { documents: ["unbounded", "current"] } } } } }
])

{ $setWindowFields: { output: { rank: { $rank: {} } } } }

{ $setWindowFields: { partitionBy: "$tier", sortBy: { spend: -1 }, output: { rank: { $rank: {} } } } }

PitfallsCommon mistakes

• Forgetting sortBy, which makes ranks and position windows meaningless or an error.
• Using $group when every original document is still needed, then joining back.
• Unbounded windows over huge partitions without a $match first.
• Confusing documents windows (row positions) with range windows (value distance).

LimitsWhen not to use it

Window functions over a whole large collection sort everything, which may spill to disk. Filter first with $match, partition sensibly, and precompute rollups (for example monthly totals with $merge) for dashboards that refresh often.

SecuritySecurity notes

Analytical pipelines are expensive. Do not let report users run arbitrary pipelines on the primary: point them at a secondary or an analytics node with readPreference and tags, and give them read-only roles scoped to the collections they need.

ChallengeTry it yourself

db.orders.aggregate([
  { $match: { status: { $in: ["paid", "shipped", "delivered"] } } },
  { $group: { _id: { $dateTrunc: { date: "$createdAt", unit: "month" } }, revenue: { $sum: "$total" } } },
  { $setWindowFields: { sortBy: { _id: 1 }, output: {
      avg3: { $avg: "$revenue", window: { documents: [-2, 0] } } } } },
  { $project: { _id: 0, month: { $dateToString: { date: "$_id", format: "%Y-%m" } }, revenue: { $round: ["$revenue", 0] }, avg3: { $round: ["$avg3", 0] } } },
  { $sort: { revenue: -1 } },
  { $limit: 3 }
])

InterviewQuestions you should be able to answer

QuizCheck your understanding

4 questions with instant feedback in the interactive lesson; a wrong answer goes into your review deck.