Drill — Solutions Hidden by Default

Coding Problems

Ten drillable problems flavored to FDE-DE work — extraction cleaning, schema reshaping, fuzzy entity resolution, contract-data SQL, evaluation metrics. Multiple approaches per problem with tradeoff discussion.

🎯 10 problems ⏱ ~20 min each 📊 Progress saved
0 / 10 practiced

A · Cleaning & reshaping

P1. Normalize messy contract CSV

You're given a CSV of contracts with columns id, supplier_name, effective_date, value. Dates come in mixed formats. Supplier names have inconsistent capitalization, whitespace, and suffix variance ("Acme Corp", "Acme Corp ", "Acme Corp, Inc."). Values are sometimes blank, sometimes strings like "$1,234.50". Return a cleaned list of dicts ready to load.

Show solution
clean_contracts.py
import csv
from datetime import datetime
from typing import Iterable

DATE_FORMATS = ("%Y-%m-%d", "%m/%d/%Y", "%d-%b-%Y", "%B %d, %Y", "%d/%m/%Y")
CORP_SUFFIXES = (", Inc.", " Inc.", ", LLC", " LLC", ", Ltd", " Ltd", " Corp.", ", Corp.")

def clean_date(s: str | None) -> str | None:
    if not s or not s.strip():
        return None
    s = s.strip()
    for fmt in DATE_FORMATS:
        try:
            return datetime.strptime(s, fmt).date().isoformat()
        except ValueError:
            continue
    return None

def normalize_supplier(name: str | None) -> str | None:
    if not name: return None
    cleaned = " ".join(name.split())
    for suffix in CORP_SUFFIXES:
        if cleaned.lower().endswith(suffix.lower()):
            cleaned = cleaned[:-len(suffix)].strip()
            break
    return cleaned or None

def parse_value(v: str | None) -> float | None:
    if not v or not v.strip():
        return None
    cleaned = v.replace("$", "").replace(",", "").strip()
    try:
        return float(cleaned)
    except ValueError:
        return None

def clean_contracts(rows: Iterable[dict]) -> list[dict]:
    return [
        {
            "contract_id": row["id"].strip(),
            "supplier": normalize_supplier(row.get("supplier_name")),
            "effective_date": clean_date(row.get("effective_date")),
            "value_usd": parse_value(row.get("value")),
        }
        for row in rows
    ]

The senior beat: return None on unparseable values, don't crash; let downstream flag-and-handle. Mention separately: "for a real deployment I'd log unparseable values to a side channel so we can audit."

P2. Reshape extraction JSON into rows for warehouse load

Platform produces extraction JSON like:

{"document_id": "msa-001", "fields": {"effective_date": {"value": "2024-03-15", "confidence": 0.94},
                                       "parties": {"value": ["Acme", "Co Inc"], "confidence": 0.97}}}

Reshape into one row per (document_id, field_name) for loading into a wide-narrow warehouse table.

Show solution
reshape_extractions.py
import json
from typing import Iterable

def reshape(extractions: Iterable[dict]) -> Iterable[dict]:
    for e in extractions:
        doc_id = e["document_id"]
        for field_name, payload in e.get("fields", {}).items():
            value = payload.get("value")
            # Stringify list values for narrow storage (or split if downstream requires)
            value_str = json.dumps(value) if isinstance(value, list) else value
            yield {
                "document_id": doc_id,
                "field_name": field_name,
                "value": value_str,
                "confidence": payload.get("confidence"),
            }

Discussion to volunteer: Should list-valued fields stay as JSON or split into one row per list element? Depends on downstream — analytics queries are easier on split rows; provenance is easier on JSON. Ask the customer's analytics team before deciding.

P3. Detect orphaned amendments

You have agreements (each with parent_agreement_id nullable) and documents with a doc_class field. Find amendments whose parent MSA isn't in the agreements table.

Show solution

Two approaches.

orphaned_amendments.sql
-- Approach 1: anti-join via LEFT JOIN
SELECT a.agreement_id, a.parent_agreement_id
FROM agreements a
LEFT JOIN agreements parent
  ON parent.agreement_id = a.parent_agreement_id
WHERE a.parent_agreement_id IS NOT NULL
  AND parent.agreement_id IS NULL;

-- Approach 2: NOT EXISTS (NULL-safer)
SELECT a.agreement_id, a.parent_agreement_id
FROM agreements a
WHERE a.parent_agreement_id IS NOT NULL
  AND NOT EXISTS (
    SELECT 1 FROM agreements p WHERE p.agreement_id = a.parent_agreement_id
  );

Approach 2 is more defensive against weird NULL semantics. Mention both.

B · Fuzzy & entity resolution

P4. Fuzzy-match raw supplier names to a canonical master

Given a list of raw supplier names extracted from contracts and a canonical supplier master list, return mappings with confidence above a threshold. Handle the case where no match exists.

Show solution
fuzzy_match.py
from difflib import SequenceMatcher

def similarity(a: str, b: str) -> float:
    return SequenceMatcher(None, a.lower().strip(), b.lower().strip()).ratio()

def match_suppliers(
    raw_names: list[str],
    canonical: list[str],
    threshold: float = 0.8,
) -> list[dict]:
    """Map each raw name to its best canonical match above threshold, or None."""
    results = []
    for raw in raw_names:
        scored = [(c, similarity(raw, c)) for c in canonical]
        scored.sort(key=lambda x: -x[1])
        if scored and scored[0][1] >= threshold:
            results.append({"raw": raw, "canonical": scored[0][0], "confidence": scored[0][1]})
        else:
            results.append({"raw": raw, "canonical": None, "confidence": scored[0][1] if scored else 0.0})
    return results

Tradeoffs to volunteer: O(N × M) is fine for thousands; at 100k+ canonical entries, prefix-bucket or use trigram pre-filtering or move to RapidFuzz / vector similarity. At customer-deployment scale this is typically a one-shot job, so the simple version usually wins.

P5. Resolve supplier hierarchies (parent rollup)

Given suppliers(supplier_id, parent_supplier_id), return for each supplier its root parent.

Show solution
supplier_hierarchy.sql
WITH RECURSIVE chain AS (
  SELECT supplier_id, supplier_id AS root_id, 0 AS depth
  FROM suppliers WHERE parent_supplier_id IS NULL
  UNION ALL
  SELECT s.supplier_id, c.root_id, c.depth + 1
  FROM suppliers s
  JOIN chain c ON c.supplier_id = s.parent_supplier_id
)
SELECT supplier_id, root_id, depth FROM chain;

Discussion: cycle protection — what if a customer's data has a supplier pointing to itself transitively? Add a depth cap or detect cycles with a visited set in app code.

P6. Dedupe extraction outputs across multiple model runs

The pipeline ran extraction twice on the same documents with different model versions. For each (document_id, field) pair, keep the latest extraction (by extracted_at), but if confidence dropped by more than 0.1, keep both with a flag.

Show solution
dedupe_extractions.py
from collections import defaultdict
from datetime import datetime

def dedupe(extractions: list[dict]) -> list[dict]:
    """Keep latest per (document_id, field). Flag drops > 0.1."""
    grouped = defaultdict(list)
    for e in extractions:
        grouped[(e["document_id"], e["field"])].append(e)

    result = []
    for key, items in grouped.items():
        items.sort(key=lambda x: x["extracted_at"], reverse=True)
        latest = items[0]
        result.append(latest)
        # If a prior extraction had meaningfully higher confidence, flag both
        if len(items) > 1:
            prior = items[1]
            if prior["confidence"] - latest["confidence"] > 0.1:
                result[-1]["confidence_regressed"] = True
                result.append({**prior, "shadow": True})
    return result

C · SQL over contract data

P7. Renewals due in next 90 days with notice deadlines

List active agreements ending within 90 days, with their notice deadline (renewal end minus notice-period clause).

Show solution

See 07 §obligations for the full version. Key beat: surface the notice deadline as the primary actionable date, not the renewal date itself.

P8. Off-contract spend per supplier

Compute total spend per supplier where no active agreement existed at the time of the spend.

Show solution
off_contract_spend.sql
SELECT
  sp.supplier_id,
  SUM(sp.amount) AS off_contract_total
FROM spend sp
LEFT JOIN agreements a
  ON a.supplier_id = sp.supplier_id
 AND a.status = 'active'
 AND sp.accrued_at BETWEEN a.effective_date AND COALESCE(a.current_term_end, '9999-12-31')
WHERE a.agreement_id IS NULL
GROUP BY sp.supplier_id
ORDER BY off_contract_total DESC;

Senior touch: sub-categorize the result — was the spend with a supplier who has no agreement at all (unknown supplier), or with a supplier whose agreement had expired? The second is usually preventable; surface it separately.

D · Evaluation metrics

P9. Compute field-level accuracy against a gold set

You have extracted values and a gold-set of true values for the same (document, field) pairs. Compute per-field accuracy.

Show solution
field_accuracy.py
from collections import defaultdict

def field_accuracy(
    extracted: list[dict],
    gold: list[dict],
    tolerance: dict[str, float] | None = None,
) -> dict[str, float]:
    """
    extracted, gold: list of {document_id, field, value}
    tolerance: per-field tolerance (e.g., {"amount": 0.01} for 1% of value)
    Returns per-field accuracy.
    """
    gold_lookup = {(g["document_id"], g["field"]): g["value"] for g in gold}
    per_field_correct: dict[str, int] = defaultdict(int)
    per_field_total: dict[str, int] = defaultdict(int)
    for e in extracted:
        key = (e["document_id"], e["field"])
        if key not in gold_lookup:
            continue
        per_field_total[e["field"]] += 1
        gold_val = gold_lookup[key]
        if matches(e["value"], gold_val, tolerance.get(e["field"]) if tolerance else None):
            per_field_correct[e["field"]] += 1
    return {f: per_field_correct[f] / per_field_total[f] for f in per_field_total}

def matches(extracted, gold, tol: float | None) -> bool:
    if extracted == gold:
        return True
    if tol is not None and isinstance(extracted, (int, float)) and isinstance(gold, (int, float)):
        return abs(extracted - gold) <= abs(gold) * tol
    return False

Discussion to volunteer: exact-match is brittle for dates ("2024-03-15" vs "March 15, 2024") and string fields with capitalization variance. Real systems normalize before comparison. Mention that the contract defines the match criteria explicitly.

P10. Plot a reliability diagram from extraction confidence + outcomes

Given extractions with (confidence, was_correct), compute the calibration in 10 buckets (deciles of confidence) — bucket mean predicted vs bucket mean actual accuracy.

Show solution
reliability_diagram.py
from collections import defaultdict

def reliability(extracted: list[dict], n_bins: int = 10) -> list[dict]:
    """Each extracted item: {confidence: float, was_correct: bool}"""
    buckets = defaultdict(list)
    for e in extracted:
        bucket = min(int(e["confidence"] * n_bins), n_bins - 1)
        buckets[bucket].append(e)
    out = []
    for b in range(n_bins):
        items = buckets.get(b, [])
        if not items:
            continue
        mean_conf = sum(i["confidence"] for i in items) / len(items)
        mean_acc = sum(1 for i in items if i["was_correct"]) / len(items)
        out.append({
            "bucket": b, "n": len(items),
            "mean_confidence": mean_conf, "actual_accuracy": mean_acc,
            "gap": mean_acc - mean_conf,
        })
    return out

Discussion to volunteer: calibration matters because HITL threshold tuning depends on it. If buckets above 0.85 confidence are actually only 75% accurate, your auto-approval rules are leaking error. Calibration is fixable via isotonic regression or Platt scaling on a held-out set.

Drill protocol

How to use this page

Enable drill mode. Read each problem. Give yourself 15–20 minutes. Code in a notebook with example data. Talk through approach + tradeoffs out loud before writing. Reveal, compare, mark practiced. Aim for 7/10 cleared before an FDE-DE loop. The cleaning/reshaping problems (A) and SQL problems (C) are the most-likely-to-be-tested categories.