Order Fill Rate From Partial-Execution Records, and What Drives It

You receive a CSV export of order activity from a stock-trading app. Its columns are roughly `dt`, `order_id`, `execution_price`, `execution_quantity`, `order_quantity` and `order_status`. One `order_id` can appear on several rows, one for each partial execution. Using Python (pandas is fine): 1. Compute the order fill rate, `order_fill_rate`. 2. Analyze which factors affect the order fill rate. The report notes that the first step needs aggregation by `order_id` and is not as simple as it looks, and that the interviewer cared more about how the candidate approached the data than about finishing every step. Assumed column meanings, to be confirmed with the interviewer: | Column | Assumed meaning | | --- | --- | | `dt` | Timestamp of the event the row records | | `order_id` | Order identifier, repeated on every row of the order | | `execution_price` | Price of this row's execution; empty when the row records no execution | | `execution_quantity` | Quantity executed in this row | | `order_quantity` | Total quantity the order asked for, repeated on every row of the order | | `order_status` | Status recorded on the row, such as filled, partially filled or cancelled; the exact values are not given | ### Clarifying Questions - Is the fill rate meant per order (executed quantity over ordered quantity), as the share of orders that fill completely, or as total executed over total ordered quantity across all orders? - Is `execution_quantity` the quantity of each individual execution, or a running total for the order? - Can `order_quantity` change during an order's life, for example when the order is amended? - Are there rows with no execution, such as a cancellation or a rejection, and are some orders still open at the end of the export? - Are other columns available, such as the symbol, the buy or sell side, or the order type? ### Part 1 — Prepare the data and compute the fill rate Load the file, aggregate it to one row per order, and compute `order_fill_rate` per order and as one overall figure. Explain each preprocessing choice. ```hint One row per order Before aggregating, decide for each column whether its values should be summed, counted once, or taken from the order's latest row. ``` #### What This Part Should Cover - An order-level aggregation that does not count the requested quantity more than once - Checks on the raw rows: duplicates, missing prices, running totals versus increments, executed quantity above the ordered quantity - A stated definition of the overall fill rate and why it was chosen over the alternatives ### Part 2 — Factors that affect the fill rate Using the order-level table, analyze which factors are associated with higher or lower fill rates, and explain how you would present what you find. ```hint Known before or after Separate the attributes known when the order is placed from those that exist only because of how the order filled. ``` #### What This Part Should Cover - Candidate factors that can be built from the available columns, and the missing data that would matter most - Segment comparisons with sample sizes and uncertainty, followed by a multivariable view - Leakage from outcome columns, selection bias in prices that exist only for executed orders, and correlation versus causation ### What a Strong Answer Covers - Working pandas code for the order-level table and the fill-rate metrics - An explicit fill-rate definition and its trade-offs: quantity-weighted, averaged over orders, or share fully filled - Defensive handling of messy rows instead of assuming the file is clean - A factor analysis that avoids leakage and states what it can and cannot conclude - Sensible prioritization under time pressure: a correct metric first, deeper analysis second ### Follow-up Questions - How would you compute the same order-level fill rate in SQL? - How would you define the fill rate if an order's quantity can be amended while it is open? - The overall fill rate dropped last week. How would you find out why? - How would you treat orders that are still open when the export was taken?