MongoDB Exercises

// 1. Schema for a small marketplace - orders + items + payments
//    Decide: embed vs reference for items. Hint: bounded list per order, embed.

// Orders collection
{
  _id: ObjectId('...'),
  customerId: ObjectId('...'),
  status: 'paid',                  // open | paid | shipped | cancelled
  items: [
    { sku: 'A-100', qty: 2, unitPrice: 19.95 },
    { sku: 'B-770', qty: 1, unitPrice: 49.00 }
  ],
  total: 88.90,
  payment: { provider: 'stripe', chargeId: 'ch_abc' },
  createdAt: ISODate(),
  paidAt:    ISODate()
}

// Indexes
db.orders.createIndex({ customerId: 1, createdAt: -1 });
db.orders.createIndex({ status: 1, createdAt: -1 });


// 2. Aggregation - revenue by month, last 12 months
db.orders.aggregate([
  { $match: { status: 'paid', paidAt: { $gte: new Date(Date.now() - 365 * 86_400_000) } } },
  {
    $group: {
      _id: { y: { $year: '$paidAt' }, m: { $month: '$paidAt' } },
      revenue: { $sum: '$total' },
      orders:  { $sum: 1 }
    }
  },
  { $sort: { '_id.y': 1, '_id.m': 1 } },
  {
    $project: {
      _id: 0,
      month: { $dateFromParts: { year: '$_id.y', month: '$_id.m', day: 1 } },
      revenue: 1,
      orders: 1
    }
  }
]);


// 3. Diagnose a slow query
// db.orders.find({ status: 'paid' }).sort({ createdAt: -1 }).limit(50).explain('executionStats');

// Look at executionStats:
// - totalDocsExamined vs nReturned (should be near 1:1)
// - stage: IXSCAN (good) vs COLLSCAN (bad)
// - executionTimeMillis

// Fix: ensure { status: 1, createdAt: -1 } compound index is the chosen one.
// The order of fields matters: equality fields first, then sort field.


// Stretch
// - Add a TTL index to auto-delete orders in 'open' state after 7 days
// - Convert one frequent aggregation into a materialised view with $merge

// Fill in the missing operator.
db.products.find({ price: { ____: 50 } });