Explain MongoDB aggregation pipeline and how it differs from SQL GROUP BY with practical examples

MongoDB Aggregation Pipeline

db.applications.aggregate([
  // Stage 1: Filter
  { $match: { status: { $in: ["OFFER", "REJECTED", "WITHDRAWN"] } } },

  // Stage 2: Group by month + company
  {
    $group: {
      _id: {
        month: { $dateToString: { format: "%Y-%m", date: "$createdAt" } },
        company: "$company",
      },
      total: { $sum: 1 },
      offers: {
        $sum: { $cond: [{ $eq: ["$status", "OFFER"] }, 1, 0] }
      },
      avgDaysToOffer: {
        $avg: {
          $cond: [
            { $eq: ["$status", "OFFER"] },
            { $divide: [
              { $subtract: ["$offerDate", "$dateApplied"] },
              86400000 // ms in a day
            ]},
            null
          ]
        }
      },
    },
  },

  // Stage 3: Calculate success rate
  {
    $addFields: {
      successRate: { $multiply: [{ $divide: ["$offers", "$total"] }, 100] },
    },
  },

  // Stage 4: Sort
  { $sort: { "_id.month": -1, total: -1 } },

  // Stage 5: Limit
  { $limit: 100 },
]);

SQL Equivalent

SELECT
  TO_CHAR(date_applied, 'YYYY-MM') AS month,
  company,
  COUNT(*) AS total,
  SUM(CASE WHEN status = 'OFFER' THEN 1 ELSE 0 END) AS offers,
  ROUND(
    100.0 * SUM(CASE WHEN status = 'OFFER' THEN 1 ELSE 0 END) / COUNT(*),
    2
  ) AS success_rate,
  AVG(
    CASE WHEN status = 'OFFER'
    THEN EXTRACT(EPOCH FROM (offer_date - date_applied)) / 86400
    END
  ) AS avg_days_to_offer
FROM applications
WHERE status IN ('OFFER', 'REJECTED', 'WITHDRAWN')
GROUP BY TO_CHAR(date_applied, 'YYYY-MM'), company
ORDER BY month DESC, total DESC
LIMIT 100;

Pipeline Stages Cheat Sheet

Stage	SQL Equivalent	Purpose
$match	WHERE	Filter documents
$group	GROUP BY + aggregate functions	Group and calculate
$sort	ORDER BY	Sort results
$limit	LIMIT	Limit results
$skip	OFFSET	Pagination
$project	SELECT	Shape output
$lookup	JOIN	Join collections
$unwind	—	Flatten arrays
$facet	—	Multiple pipelines in one
$addFields	computed columns	Add calculated fields

Performance Tips

1. Put $match first — Reduces documents flowing through pipeline
2. Use indexes — $match can use indexes if first stage
3. Avoid $unwind on large arrays — Creates a document per element
4. Use $facet for multiple aggregations — Runs in parallel on same input

db.applications.aggregate([
  { $facet: {
    byStatus: [{ $group: { _id: "$status", count: { $sum: 1 } } }],
    byMonth: [{ $group: { _id: { $month: "$createdAt" }, count: { $sum: 1 } } }],
    totalApplications: [{ $count: {} }],
  }}
]);