How do you design a SQL query for an API endpoint that returns a list with nested summary data?

Shape SQL results for an API read model

Shapes an API list from its contract and cardinality, projecting only required values while preserving authorization, stable pagination, bounded nesting, index support, and measurable query cost.

1Say itAnswer out loud first

2CompareCompare with Core answer

3RefineRefine with Stronger and Advanced

TL;DR

I start from the response contract and select only the fields needed for the page. For an order list with customer name, item count, and total, I project those scalar values rather than loading complete entities and navigation graphs. Filters, authorization scope, stable ordering, and pagination run in the database. I reason about relationship cardinality before joining: many-to-one data usually joins cleanly, while one-to-many data can multiply parent rows and break both payload size and paging.

Full answer below

Practice focus

Projection, DTO shape, joins, aggregation, row multiplication, query splitting, and read-model boundaries.

Answer first

Answer path

Core answer first, then checkpoints toward a stronger answer.

Start
Core answer
Start here. This is the direct spoken answer to practice first.
Why this question matters
An API response is not a serialized table graph. The query should produce the bounded client shape directly while keeping row multiplication, permissions, ordering, pagination, and database work explicit.
I start from the response contract and select only the fields needed for the page. For an order list with customer name, item count, and total, I project those scalar values rather than loading complete entities and navigation graphs. Filters, authorization scope, stable ordering, and pagination run in the database. I reason about relationship cardinality before joining: many-to-one data usually joins cleanly, while one-to-many data can multiply parent rows and break both payload size and paging.
Checkpoint
Stronger answer
Optional upgrade with examples, trade-offs, and sharper phrasing.
A practical design first selects the bounded parent page with a unique tie-breaker, then obtains nested summaries with grouped aggregation or one second set-based query keyed by those parent ids. That avoids paginating a large joined rowset where one order appears once per line. Provider-supported JSON aggregation can work when its plan and contract remain clear, but I do not force every nested shape into one statement. Counts and totals use the same permission and status semantics as the list, and total-count queries are optional when their cost is not worth the user experience.
Finish
Advanced answer
Optional deeper angle for production judgment and nuance.
In production the endpoint contract and index design evolve together. Tenant or ownership predicates are applied inside the query, not after materialization, so forbidden rows never enter an application-side filter. The cursor encodes the actual deterministic order and remains opaque to clients. I compare one query, split queries, and a dedicated read model using command count, rows read, bytes transferred, execution plans, consistency needs, and change frequency. Two queries can observe slightly different moments unless the transaction or data model defines a snapshot, while one giant join can create cartesian expansion and repeated values; that trade-off is explicit. I project nullability and enum values deliberately, bound page and nested collection sizes, propagate cancellation, and avoid evaluating user-defined filter expressions as arbitrary SQL. Load tests use realistic tenant skew and child counts, and telemetry records query shape, duration, command count, rows, and result size without sensitive parameters. When a frequently requested response needs expensive joins and aggregates, a maintained read model can trade write complexity and freshness lag for predictable reads. I revisit the query when the public contract changes instead of adding another Include and hoping.

Review signals

Use these to catch weak phrasing, shallow reasoning, and risky claims.

Common mistakesCandidateCommon mistakesMistakes that weaken the answer or signal shallow understanding.Open details

Details

Paging after joining a one-to-many collection and returning partial or duplicate parents.
Loading entities and applying tenant authorization in memory.
Returning an expensive exact total count on every page without product need.
Adding nested fields without measuring rows read and bytes transferred.

Red flagsCandidateRed flagsSignals that an interviewer may treat as risky.Open details

Details

Cannot reason from API contract back to SQL projection.
No answer for nested one-to-many data.
Ignores pagination and filtering in the database.
Has no measurement plan for the endpoint.

Follow-up prompts

Quick interviewer prompts inside this drill.

Follow-up questionsCandidateFollow-up questionsLikely next questions after the main answer.Open details

Details

Treat these as the next interviewer turn. Answer one out loud, then save the gap in your practice notes.

When would you split a query into two SQL calls?
How would you avoid over-fetching?
What metrics reveal a bad API query shape?

Continue practice

No explicit flow link is set yet, so these are nearby drills from the same path or taxonomy.