P3 Group by key · P4 Dedupe, keep latest

FreeLesson 58 of 78

P3Group by keyDEEASY

Sounds like: "total per order", "list of items per user", "reconcile header vs lines"

Worked question: Group order_items by order, total each basket, and check every total against orders.amount.

Approach: 1. defaultdict(list) to group 2. aggregate each group 3. compare with the other source

SOLUTIONPYTHON 3.12

import csv
from collections import defaultdict
from decimal import Decimal

lines = defaultdict(list)
for r in csv.DictReader(open("order_items.csv")):
    lines[r["order_id"]].append(r)

totals = {oid: sum(Decimal(r["qty"]) * Decimal(r["unit_price"])
                   for r in rows)
          for oid, rows in lines.items()}
orders = {r["order_id"]: Decimal(r["amount"])
          for r in csv.DictReader(open("orders.csv"))}

bad = [o for o in orders if totals.get(o) != orders[o]]
print(len(orders), "orders checked, mismatches:", bad)
biggest = max(lines, key=lambda o: len(lines[o]))
print("biggest basket:", biggest, len(lines[biggest]), "items")

Output

15 orders checked, mismatches: []
biggest basket: 114 3 items

P4Dedupe, keep latestDEMEDIUM

Sounds like: "remove duplicates", "latest version of each record", "the CDC feed has repeats"

Worked question: A change feed repeats orders, and one older record arrives after a newer one. Keep the latest version per order.

Approach: 1. the key is order_id 2. the winner is the newest ts, not the last arrival 3. one dict, one pass

SOLUTIONPYTHON 3.12

feed = [
  {"order_id": 101, "status": "placed",    "ts": "2025-01-10 09:00"},
  {"order_id": 101, "status": "delivered", "ts": "2025-01-12 18:30"},
  {"order_id": 102, "status": "placed",    "ts": "2025-01-15 11:00"},
  {"order_id": 103, "status": "delivered", "ts": "2025-02-04 20:00"},
  {"order_id": 103, "status": "placed",    "ts": "2025-02-02 08:15"},
]                                     # last record arrived late

latest = {}
for rec in feed:
    cur = latest.get(rec["order_id"])
    if cur is None or rec["ts"] > cur["ts"]:
        latest[rec["order_id"]] = rec
for rec in latest.values():
    print(rec["order_id"], rec["status"], rec["ts"])

print(list(dict.fromkeys([3, 1, 3, 2, 1])))   # keep first, keep order

Output

101 delivered 2025-01-12 18:30
102 placed 2025-01-15 11:00
103 delivered 2025-02-04 20:00
[3, 1, 2]

Follow-ups interviewers ask

They ask	You answer
"Group by two columns."	Use a tuple key, groups[(customer_id, month)]. Same one pass.
"Keep the latest, but ties on ts?"	Add a tiebreaker to the comparison, such as a sequence number or file offset, so the winner is repeatable.
"Dedupe 500 million ids."	A set of 500 million ints is tens of GB. Partition ids by hash into files, dedupe each file, or push it to Spark or SQL.

Practice before you move on

Solve the two practice questions for P3 and P4 in Coding Practice, under Python.
Change P3 to report the orders whose basket has more than two lines.

In Coding Practice

Solve this pattern on fresh data, under Python. Hidden tests grade it.