Order Book Data Validation
Updated
Problem statement
An operations team keeps two tables from a trading system. The orders table has one row per limit order: its side ('B' to buy, 'S' to sell), its limit_price, the number of shares in quantity, the time it was created in created_ms and, if it was cancelled, the cancel time in cancelled_ms (NULL when it was never cancelled). The fills table has one row per execution against an order: the order_id it belongs to, the shares filled, the fill price and the fill time filled_ms. All times are milliseconds after midnight.
Before the data is used, it must pass these checks. Report one row for every failed check:
unknown_order: a fill whoseorder_idis not inorders. Such a fill gets no other check.fill_before_order: a fill whosefilled_msis earlier than its order'screated_ms.fill_after_cancel: a fill on a cancelled order whosefilled_msis later than the order'scancelled_ms.price_outside_limit: a fill on a buy order at a price above the limit price, or a fill on a sell order at a price below the limit price.overfilled: an order whose fills add up to more shares than itsquantity.
A fill at exactly the creation time, the cancel time or the limit price passes. For the first four checks, report table_name = 'fills' and record_id = the fill_id. For overfilled, report table_name = 'orders' and record_id = the order_id. A fill that fails several checks appears once per failed check. Return the columns table_name, record_id and issue, in any order.
Table schema
Use the same input data with any supported language. Open the Schema tab in the editor to see the generated SQL setup or Pandas DataFrames.
orders
One row per limit order.
| Column | Type | Nullable | Description |
|---|---|---|---|
| order_idPK | Integer | No | Unique order ID. |
| side | Text | No | 'B' for a buy order, 'S' for a sell order. |
| limit_price | Decimal | No | Worst price the order accepts. |
| quantity | Integer | No | Shares the order asks for. |
| created_ms | Integer | No | Creation time, milliseconds after midnight. |
| cancelled_ms | Integer | Yes | Cancel time, or NULL if never cancelled. |
fills
One row per execution against an order.
| Column | Type | Nullable | Description |
|---|---|---|---|
| fill_idPK | Integer | No | Unique fill ID. |
| order_id | Integer | No | Order the fill belongs to; it may be missing from orders. |
| quantity | Integer | No | Shares filled. |
| price | Decimal | No | Fill price per share. |
| filled_ms | Integer | No | Fill time, milliseconds after midnight. |
Expected result
Your query or function must return these columns.
| Column | Type | Nullable | Description |
|---|---|---|---|
| table_name | Text | No | — |
| record_id | Integer | No | — |
| issue | Text | No | — |
Row order: any order is accepted. Numeric tolerance: 0.
Constraints
- Each table has between
1and1000rows. order_idvalues inordersare unique, andfill_idvalues infillsare unique.sideis'B'or'S'.1 <= quantity <= 1000000in both tables.0 < limit_price, price <= 100000, with at most 4 digits after the decimal point.0 <= created_ms, filled_ms <= 86400000, andcancelled_msisNULLor betweencreated_msand86400000.- The answer has at least one row.
More Jane Street interview questions
- Code Editor FoldingONSITE INTERVIEW · Seen Oct 2026
- Exact-Sixty Dice Game ValueONSITE INTERVIEW · Seen Oct 2026
- Expected Value of Modified DiceONSITE INTERVIEW · Seen Oct 2026
- Spaceship Equipment LoadoutONSITE INTERVIEW · Seen Oct 2026
- Two-Tower Strategy GameONSITE INTERVIEW · Seen Oct 2026
- Wizard Scroll Allocation StrategyONSITE INTERVIEW · Seen Oct 2026
- Sort TradesPHONE SCREEN · ONSITE INTERVIEW · Seen Jul 2026
- Order Matching EngineONSITE INTERVIEW · Seen Jul 2026
- Biggest Daily Move and Longest Rising StreakOA · ONSITE INTERVIEW · Seen Apr 2021
- Maximum-Scoring Consecutive Tree LevelsPHONE SCREEN · Seen Nov 2026
Similar questions at other companies
- Daily USD Totals with Effective-Dated Exchange RatesMicrosoft · ONSITE INTERVIEWMicrosoft interview questions
- Most Recent Order for Each CustomerCapgemini · ONSITE INTERVIEWCapgemini interview questions
- Tenth-Highest Distinct SalaryInfosys · ONSITE INTERVIEWInfosys interview questions
- Fifth Highest Salary In A DepartmentZS · ONSITE INTERVIEWZS interview questions
- In-Memory Database QueriesOpenAI · PHONE SCREEN · ONSITE INTERVIEWOpenAI interview questions
- Nth-Highest Distinct Salary Without Ranking FunctionsMastercard · ONSITE INTERVIEWMastercard interview questions
- Contest Participants with Long Totals and Short QuestionsThe Walt Disney Company · ONSITE INTERVIEWThe Walt Disney Company interview questions
- First Order per Merchant per DateAmazon · ONSITE INTERVIEW
- Block Transaction Count TrendsChainalysis · ONSITE INTERVIEWChainalysis interview questions
- Third-Highest Distinct SalaryExpedia · ONSITE INTERVIEWExpedia interview questions