MongoDB vs Old Tools — What Changed for Business Analysts

-- SQL: customer + orders + items live in 3 tables

-- customers table
CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(200)
);

-- orders table
CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  order_date DATE,
  total_amount DECIMAL(10,2)
);

-- order_items table
CREATE TABLE order_items (
  item_id INT PRIMARY KEY,
  order_id INT,
  product_name VARCHAR(200),
  quantity INT,
  unit_price DECIMAL(10,2)
);

-- To see one customer with their orders and items, you need a 3-way join:
SELECT
  c.name,
  o.order_id,
  o.order_date,
  i.product_name,
  i.quantity,
  i.unit_price
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
JOIN order_items i ON i.order_id = o.order_id
WHERE c.customer_id = 42;

-- Three tables. Three joins. Three places to look when something is wrong.

// MongoDB: the same data lives together in one document
{
  "customer_id": 42,
  "name": "Priya Sharma",
  "email": "priya@example.com",
  "orders": [
    {
      "order_id": 1001,
      "order_date": "2026-09-10",
      "total_amount": 4500,
      "items": [
        { "product": "Wireless Mouse", "qty": 2, "price": 1200 },
        { "product": "USB Hub",        "qty": 1, "price": 2100 }
      ]
    },
    {
      "order_id": 1002,
      "order_date": "2026-09-15",
      "total_amount": 999,
      "items": [
        { "product": "Notebook", "qty": 3, "price": 333 }
      ]
    }
  ]
}

# What changed for the analyst:
# - One query returns the whole customer with their order history.
# - No joins needed for the common question.
# - The analyst can read the document and immediately understand the data.
#
# What you give up:
# - Cross-customer queries (e.g., "how many customers bought the
#   Wireless Mouse last month?") require unwinding nested arrays.
# - Joining across unrelated entities is more awkward.
# - Aggregations on nested data need explicit $unwind steps.
#
# The rule of thumb:
# - Data accessed together → embed (one document)
# - Data accessed separately → reference (multiple collections)

// A real MongoDB document from an e-commerce product catalogue
// This is the shape that makes MongoDB interesting for analysts.

{
  "_id": ObjectId("6541a2b3c4d5e6f7a8b9c0d1"),
  "sku": "WM-2026-BLK",
  "name": "Wireless Mouse",
  "category": "Accessories",
  "brand": "TechCo",
  "base_price": 1499,
  "currency": "INR",
  "attributes": {
    "color": "Black",
    "connectivity": "Bluetooth 5.3",
    "battery_life_hours": 60
  },
  "variants": [
    { "sku": "WM-2026-WHT", "color": "White", "price": 1499, "stock": 42 },
    { "sku": "WM-2026-GRY", "color": "Grey",  "price": 1549, "stock": 18 }
  ],
  "reviews_summary": {
    "avg_rating": 4.3,
    "total_reviews": 1284,
    "rating_breakdown": {
      "5": 720, "4": 380, "3": 110, "2": 40, "1": 34
    }
  },
  "created_at": ISODate("2026-08-01T10:00:00Z"),
  "updated_at": ISODate("2026-09-11T15:22:00Z")
}

# What this shape lets analysts do easily:
# - Look at a single product and see its full context.
# - Answer "what is the rating distribution for this product?" —
#   one field access, no aggregation.
# - Track variants without joining a separate table.
# - Store inconsistent fields across categories (a shoe has size,
#   a laptop has RAM) without schema changes.
#
# What this shape makes harder:
# - "Total stock across all products" requires $unwind on variants.
# - "Revenue by category" requires $match, $unwind, $group.
# - Comparing across many documents requires the aggregation pipeline.
#
# The mental model:
# - SQL asks you to normalize first, then join at query time.
# - MongoDB asks you to decide the access pattern first,
#   then model the document around it.

// Embedding vs referencing — the single most important design choice
// in MongoDB, and the one that affects analysts the most.

// === EMBEDDING — related data lives inside one document ===
// Good when:
//   - Data is always accessed together
//   - The nested data is small and bounded (e.g., line items per order)
//   - You rarely need to query across the nested data independently

{
  "_id": "order_1001",
  "customer_id": "cust_42",
  "total": 4500,
  "items": [
    { "product_id": "WM-2026-BLK", "qty": 2, "price": 1200 },
    { "product_id": "USB-2026",    "qty": 1, "price": 2100 }
  ]
}

// === REFERENCING — related data lives in another collection ===
// Good when:
//   - The related data is large or unbounded
//   - It is updated frequently
//   - You need to query it independently

// orders collection
{
  "_id": "order_1001",
  "customer_id": "cust_42",
  "total": 4500,
  "items": [
    { "product_id": "WM-2026-BLK", "qty": 2, "price": 1200 },
    { "product_id": "USB-2026",    "qty": 1, "price": 2100 }
  ]
}

// products collection
{
  "_id": "WM-2026-BLK",
  "name": "Wireless Mouse",
  "category": "Accessories",
  "brand": "TechCo"
}

# Why this matters for business analysts:
# - If a team embeds too much, "how many of each product did we sell"
#   requires $unwind and $group on every order.
# - If a team references too much, "show me this customer's full order"
#   requires $lookup — which is slower than an embedded document.
#
# What a good business analyst asks during design review:
# 1. What is the most common question someone will ask of this data?
# 2. Is it a single-record lookup or an aggregation across records?
# 3. Does the nested data grow unbounded (e.g., activity log) or not?
# 4. Does it change frequently, or is it appended once?
#
# Answers to these four questions usually decide embed vs reference.
# This is one of the most valuable things a BA can contribute to a
# data model discussion — even without writing code.

-- SQL: total revenue by category, last 30 days, top 5
SELECT
  p.category,
  COUNT(*)         AS orders,
  SUM(o.amount)    AS revenue,
  AVG(o.amount)    AS avg_order
FROM orders o
JOIN products p ON p.product_id = o.product_id
WHERE o.order_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY p.category
ORDER BY revenue DESC
LIMIT 5;

// MongoDB equivalent — the same question, as an aggregation pipeline
db.orders.aggregate([
  // Stage 1: filter — equivalent to WHERE
  { $match: {
      order_date: { $gte: new Date(Date.now() - 30*24*60*60*1000) }
  }},

  // Stage 2: join with products — equivalent to JOIN
  { $lookup: {
      from: "products",
      localField: "product_id",
      foreignField: "_id",
      as: "product"
  }},

  // Stage 3: flatten the joined array — each order gets one product
  { $unwind: "$product" },

  // Stage 4: group and aggregate — equivalent to GROUP BY
  { $group: {
      _id: "$product.category",
      orders:  { $sum: 1 },
      revenue: { $sum: "$amount" },
      avg_order: { $avg: "$amount" }
  }},

  // Stage 5: shape the output — cleaner field names
  { $project: {
      _id: 0,
      category: "$_id",
      orders: 1,
      revenue: 1,
      avg_order: { $round: ["$avg_order", 2] }
  }},

  // Stage 6: sort and limit — equivalent to ORDER BY + LIMIT
  { $sort: { revenue: -1 } },
  { $limit: 5 }
]);

# What changed for the analyst:
# - Same logical steps as SQL: filter → join → group → sort → limit.
# - Syntax is more verbose and nested.
# - The pipeline is easier to read once you get used to it —
#   each stage does exactly one thing.
#
# What to watch out for:
# - $lookup is slower than a SQL join. Prefer $unwind on embedded data.
# - $unwind explodes documents — check counts after unwinding.
# - Field names are strings with $ prefixes — typo-friendly, so test carefully.
# - Dates are Date objects, not strings — a common source of bugs.
#
# Analogy for the SQL-minded:
# - $match    ≈ WHERE
# - $lookup   ≈ JOIN
# - $unwind   ≈ unnesting / exploding arrays
# - $group    ≈ GROUP BY
# - $project  ≈ SELECT (field selection and reshaping)
# - $sort     ≈ ORDER BY
# - $limit    ≈ LIMIT
# - $addFields≈ adding computed columns

// A real business question in MongoDB: "Which product categories are
// growing fastest in revenue, week over week, this quarter?"

db.orders.aggregate([
  // 1. Filter to this quarter
  { $match: {
      order_date: { $gte: ISODate("2026-07-01T00:00:00Z") }
  }},

  // 2. Join to products to get category
  { $lookup: {
      from: "products",
      localField: "product_id",
      foreignField: "_id",
      as: "product"
  }},
  { $unwind: "$product" },

  // 3. Compute the week and extract category
  { $addFields: {
      week: { $isoWeek: "$order_date" },
      category: "$product.category"
  }},

  // 4. Group by category and week
  { $group: {
      _id: { category: "$category", week: "$week" },
      revenue: { $sum: "$amount" }
  }},

  // 5. Reshape into per-week documents
  { $group: {
      _id: "$_id.category",
      weeks: {
        $push: { week: "$_id.week", revenue: "$revenue" }
      }
  }},

  // 6. Compute week-over-week growth per category
  { $addFields: {
      sorted_weeks: { $sortArray: { input: "$weeks", sortBy: { week: 1 } } }
  }},
  { $addFields: {
      growth: {
        $map: {
          input: { $range: [1, { $size: "$sorted_weeks" }] },
          as: "i",
          in: {
            week: { $arrayElemAt: ["$sorted_weeks", "$$i"] }.week,
            revenue: { $arrayElemAt: ["$sorted_weeks", "$$i"] }.revenue,
            growth_pct: {
              $multiply: [
                { $divide: [
                    { $subtract: [
                        { $arrayElemAt: ["$sorted_weeks", "$$i"] }.revenue,
                        { $arrayElemAt: ["$sorted_weeks", { $subtract: ["$$i", 1] }] }.revenue
                    ]},
                    { $arrayElemAt: ["$sorted_weeks", { $subtract: ["$$i", 1] }] }.revenue
                ]},
                100
              ]
            }
          }
        }
      }
  }},

  // 7. Average growth per category, then rank
  { $addFields: {
      avg_growth: { $avg: "$growth.growth_pct" }
  }},
  { $project: {
      category: "$_id",
      avg_growth: { $round: ["$avg_growth", 1] }
  }},
  { $sort: { avg_growth: -1 } }
]);

# What this shows:
# - The pipeline can express complex business questions.
# - Each stage is a named transformation, not nested subqueries.
# - Week-over-week growth is a common business metric in MongoDB,
#   and it is expressive once you learn the operators.
#
# What makes this hard for BAs coming from SQL:
# - The syntax is denser than SQL window functions.
# - Many operators ($map, $range, $arrayElemAt) require practice.
# - It is worth writing this once for a real question, then
#   reusing the pattern for similar questions.
#
# The trade-off:
# - SQL window functions are still easier for time-series analysis.
# - But MongoDB is easier for reshaped nested data — where the same
#   question in SQL would need 4 joins and a self-join.
#
# Conclusion: For deep time-series analytics, SQL still wins.
# For questions about nested or variable-shape data, MongoDB wins.

# Running MongoDB queries from Python — the way BAs actually use it
# This is what a business analyst does when they need to pull data
# from MongoDB into a pandas DataFrame for analysis.

import pandas as pd
from pymongo import MongoClient
from datetime import datetime, timedelta

# 1. Connect (assume the connection string is in an env variable)
client = MongoClient("mongodb+srv://user:pass@cluster.mongodb.net")
db = client["ecommerce"]

# 2. Build the aggregation — the same pipeline you would run in Compass
thirty_days_ago = datetime.utcnow() - timedelta(days=30)

pipeline = [
    { "$match": { "order_date": { "$gte": thirty_days_ago } } },
    { "$lookup": {
        "from": "products",
        "localField": "product_id",
        "foreignField": "_id",
        "as": "product"
    }},
    { "$unwind": "$product" },
    { "$group": {
        "_id": "$product.category",
        "orders": { "$sum": 1 },
        "revenue": { "$sum": "$amount" },
    }},
    { "$sort": { "revenue": -1 } },
    { "$limit": 10 },
]

# 3. Run the query and load the result into a DataFrame
results = list(db.orders.aggregate(pipeline))
df = pd.DataFrame(results)

# 4. Clean up for analysis
df = df.rename(columns={"_id": "category"})
df["revenue"] = df["revenue"].astype(float).round(2)
df["avg_order"] = (df["revenue"] / df["orders"]).round(2)

print(df.sort_values("revenue", ascending=False).to_string(index=False))

# 5. Export for the dashboard or report
df.to_csv("revenue_by_category_last30.csv", index=False)

# What this pattern shows:
# - A business analyst does not need to master every MongoDB operator.
# - The workflow is: build a pipeline → export to DataFrame → analyse.
# - Pandas handles the cleaning; MongoDB handles the aggregation.
# - The same pattern works for any database — the analyst is
#   just changing the source, not the analysis.
#
# What to watch out for:
# - Dates: MongoDB stores Date objects, not strings. Convert carefully.
# - ObjectId: needs conversion with `str()` when exporting to CSV.
# - Nested fields: `.pop()` in $project or flatten in pandas.
# - Big datasets: use `allowDiskUse=True` for large aggregations.
#
# The value of this skill for a BA:
# - You can pull real production data without waiting for the
#   data engineering team every time.
# - You can answer questions faster, and iterate on hypotheses
#   without a two-week backlog.
# - You can validate data quality yourself before reporting.