Skip to main content
All Questions

Add Batch Support for Order Queries

Medium
Unlock detailed company stats for this questionUpgrade

You've joined a team whose order service has an N+1 problem. get_orders takes a list of order IDs and calls the database once per ID, so a page that shows 20 orders makes 20 round trips. Refactor it so any list of IDs costs one round trip, without changing what callers get back.

The batch call itself is easy to write. Returning exactly what callers expect from it is the harder part.

Starter code and solution
Python 3.11+ (standard library)
Download code

What's in the codebase

  • order_service.py. OrderService with get_order and get_orders. The starter get_orders calls fetch_one once per ID. This is the only file you change.
  • db_client.py. A finished mock database client holding 50 orders. fetch_one(order_id) returns one order in one round trip of about 10 ms. fetch_many(order_ids) returns many orders in one round trip of about 15 ms. It dedupes the input IDs, drops IDs that don't exist, and returns rows in arbitrary order. The client logs every call, which is how the tests count round trips.
  • test_runner.py. Seven tests: backward compatibility, one round trip, input order, duplicates, missing IDs, empty input, and a speed check of 20 IDs in under 0.12 seconds.
  • scenarios.py. A timing demo over batches of 5, 10, and 20 IDs.
  • instructions.md. Restates the contract and the run commands.

Out of the box, five tests pass and the two efficiency tests fail. The timing demo shows the cost growing with the batch size, from about 0.05 seconds at 5 IDs to about 0.20 seconds at 20.

Your task

Refactor get_orders so a call costs one database round trip, however many IDs it receives. get_order must keep its current behavior: the order for an existing ID, None for a missing one.

Callers rely on this contract:

  • get_orders returns a list with the same length and order as the input. Position i holds the order for the ID at position i.
  • A duplicate ID appears at each of its positions in the output.
  • An ID with no matching order produces None at its position. Don't raise and don't skip it.
  • get_orders([]) returns [] and makes no database calls.
  • Each call makes exactly one fetch_many call and no fetch_one calls.
get_orders(["o1", "o2", "o3"]) -> [order o1, order o2, order o3] (1 fetch_many) get_orders(["o2", "o1", "o2"]) -> [order o2, order o1, order o2] get_orders(["o1", "missing", "o2"]) -> [order o1, None, order o2] get_orders([]) -> [] (no calls)

You're done when all seven tests pass and the timing demo shows roughly the same time at every batch size.

Follow-up

Once the tests pass, the interviewer asks for a DataLoader-style layer on top of OrderService:

  • Requests for the same order made in the same pass of the event loop share one fetch.
  • Results are cached for the length of one request. A later request fetches fresh data.
  • A call with 10,000 IDs is split into bounded batches, and the caller still receives one aligned list.

There are no tests for this part. Write the code and be ready to explain where coalescing happens, what can be shared between requests, and when the cache is cleared.

Using AI on this problem

This round usually allows AI for reading and writing code, but not for the decisions that carry the signal. Use it to summarize db_client.py, or to draft the batch call once you've chosen a design. Decide for yourself how the returned rows map back to the input, then check any generated code by hand against duplicates, missing IDs, and empty input.

Related courses

Course

AI Engineering Interview Prep

Ace the AI engineering interview at top companies like Google, Anthropic, Meta, and OpenAI. Learn from detailed frameworks, example questions, and mock interviews with senior engineers. Practice system design, AI/ML concepts, AI-assisted coding, and behavioral rounds.

Course

Generative AI Interviews

Ace your interviews at AI-first and AI-powered companies. Learn to explain how Gen AI really works, where it fails, and how to use it responsibly. Understand how top tech companies build and deploy AI products and how generative AI works in production, from LLMs and RAG systems to real-world deployment.