Original practice scenario inspired by The Charles Schwab Corporation's public business context; this is not a reported interview question or a description of its internal systems. A fictional brokerage analytics view stores trades, cancellations, and position adjustments. Explain an as-of position query that separates trade date, settlement date, and ingestion time, without presenting the result as investment advice.
Read model answer +
State which position definition is required before summing quantities. Trade-date and settled positions answer different operational questions, while ingestion time determines what information was known at the report cutoff.
Work through it
Normalize signed quantities and stable trade identities. Represent cancellations or corrections through explicit linked events rather than deleting history invisibly.
Filter by the selected business-date rule and knowledge cutoff, then aggregate by account and instrument. Keep unresolved or late-arriving corrections visible in reconciliation.
Test a trade settling after the cutoff, a later cancellation, duplicate delivery, and an adjustment without a trade. Compare the result with a small hand-reconciled ledger.
Tradeoffs to discuss
Historical as-known snapshots and restated positions can both be useful, but must be labeled separately. Corporate actions may require additional domain-specific transformations.
Common pitfalls
Using settlement date for one table and trade date for another creates inconsistent totals.
A latest-row query alone does not reconstruct all signed position changes.
Original practice scenario inspired by The Charles Schwab Corporation's public business context; this is not a reported interview question or a description of its internal systems. A synthetic investing dashboard receives quotes from a live feed and serves cached snapshots during interruptions. Design the freshness contract and failure behavior for an informational display.
Read model answer +
Expose source event time and receipt time with each quote. A responsive cache can still hold old prices, so latency and freshness require separate service measurements and user-visible states.
Work through it
Define instrument-specific stale thresholds and market-session handling. Sequence or version feed updates so a delayed older quote cannot replace a newer one.
Serve the last known value only with its as-of time and stale indicator when permitted. Use unavailable status when no trustworthy value exists, rather than substituting zero.
Detect feed gaps, reconnect with snapshots, and reconcile sequence continuity. Test delayed updates, clock skew, market closure, and a prolonged outage.
Tradeoffs to discuss
Aggressive refresh increases load without guaranteeing fresher source data. Informational displays and order-execution decisions need different correctness and availability contracts.
Common pitfalls
Browser receipt time is not the exchange event time.
A green connection indicator does not prove that every instrument is receiving current quotes.
