CODING CHALLENGE · N°69

Re-Engineer a Legacy ETL

Medium FDEInterviewData

You inherit an aggregation nobody can explain and the totals are wrong. Three separate bugs — an exclude-list where the spec wants an include-list, a dedupe key missing a field, and pre-seeded customers that should never appear. The FDE re-engineering round, with hidden tests.

The problem

A customer’s reporting pipeline aggregates order rows into a per-customer revenue total. The rows come from a join that duplicates a row per shipment leg, so the same order_id can appear several times for one customer with the same amount. Implement summarise(rows) so that: only rows whose status is "shipped" or "delivered" count; each (customer, order_id) pair contributes its amount exactly once; and a customer with no qualifying orders does not appear in the result at all. The starting code is the version you inherited — it is wrong in three places.

EXAMPLE 1

Input the same order duplicated 3×

Output counted once

the join fans out; the money does not

EXAMPLE 2

Input status "pending"

Output excluded

only shipped and delivered count

EXAMPLE 3

Input a customer whose only order was cancelled

Output absent from the result

not present with a 0

CONSTRAINTS

Only "shipped" and "delivered" count. Every other status is excluded, including ones not seen in the fixtures.
Dedupe on the pair (customer, order_id) — the same order reference can legitimately appear under two customers.
A customer with no qualifying orders must be absent, not present with a total of 0.
Return a mapping of customer → total amount.

SOLVE IT YOURSELF

Your turn — write it

Edit the stub, hit Run (or ⌘/Ctrl + Enter), and watch the hidden tests. Stuck? the hints are right above and Reveal solution is one click away.

YOUR TASK

Read the inherited summarise(rows), find the three defects against the spec, and fix them one at a time.

HINTS — 4 IDEAS

Bug 1 is the status test: it excludes one bad status instead of including the two good ones.
Bug 2 is the dedupe key: order_id alone silently drops a legitimate order belonging to a different customer.
Bug 3 is the pre-seeding loop: it puts every customer in the result before any filtering, so cancelled-only customers survive with 0.
Fix and re-run after each change. Knowing which fix moved which test is the point of the exercise.

CPython · WebAssembly

R1 = [                                    # provided fixtures
    {"customer": "acme", "order_id": "o1", "amount": 100, "status": "shipped"},
    {"customer": "acme", "order_id": "o1", "amount": 100, "status": "shipped"},
    {"customer": "acme", "order_id": "o2", "amount": 200, "status": "delivered"},
]
R2 = [
    {"customer": "acme", "order_id": "o1", "amount": 100, "status": "shipped"},
    {"customer": "acme", "order_id": "o3", "amount": 999, "status": "pending"},
    {"customer": "globex", "order_id": "o4", "amount": 50, "status": "cancelled"},
]
R3 = [                                        # same order ref, two customers
    {"customer": "acme", "order_id": "o9", "amount": 10, "status": "shipped"},
    {"customer": "globex", "order_id": "o9", "amount": 20, "status": "shipped"},
]

# --- INHERITED CODE. Three defects. Do not rewrite it; find them. ---
def summarise(rows):
    totals = {}
    for r in rows:
        totals.setdefault(r["customer"], 0)
    seen = set()
    for r in rows:
        if r["status"] == "cancelled":
            continue
        key = r["order_id"]
        if key in seen:
            continue
        seen.add(key)
        totals[r["customer"]] += r["amount"]
    return totals

print(summarise(R1))   # want {'acme': 300}
print(summarise(R2))   # want {'acme': 100}

// --- INHERITED CODE. Three defects. Do not rewrite it; find them. ---
function summarise(rows: any[]): Record<string, number> {
  const totals: Record<string, number> = {};
  for (const r of rows) if (!(r.customer in totals)) totals[r.customer] = 0;
  const seen = new Set<string>();
  for (const r of rows) {
    if (r.status === "cancelled") continue;
    const key = r.order_id;
    if (seen.has(key)) continue;
    seen.add(key);
    totals[r.customer] += r.amount;
  }
  return totals;
}

console.log(summarise(R1));   // want { acme: 300 }
console.log(summarise(R2));   // want { acme: 100 }

Approach, complexity & discussion — open after you solve

The approach

Re-engineering is a reading exercise, not a rewriting one. Read the inherited function fully before you touch it, then check each line against the spec one at a time. Here three separate rules are broken: the status filter is an exclude-list where the spec says include-list, the dedupe key is missing the customer, and every customer is pre-seeded into the result before filtering so empty ones survive.

Fix them independently and re-run after each — a single sweeping rewrite hides which change fixed what, which is exactly what an interviewer is watching for.

Complexity

Time O(n) over the rows, space O(n) for the dedupe set.

Common mistakes

Rewriting from scratch instead of locating the defects — you lose the behaviour that was already correct.
Deduping on order_id alone: two customers can legitimately carry the same order reference from different upstream systems.
Excluding only cancelled. The spec names the statuses that count; anything else — pending, returned, a typo — must not.
Leaving customers with a zero total in the output. Downstream, a zero looks like a real reported value.

Where this shows up

This is the shape of a genuine FDE first week: an inherited pipeline whose numbers are subtly wrong, nobody who wrote it is still there, and the customer has been reporting from it for a year. The Palantir re-engineering round and Sierra’s planted-bug debugging round both test this directly. The skill is method — reproduce, narrow, fix one thing, verify — not speed.

Explore the topic

See this challenge alongside everything else on the same subject — handbooks, system designs, algorithms and tools, in one place.

Interview Prep Forward Deployed Engineering