Python
Q1. A pandas job on a large dataset runs out of memory or is too slow. How do you investigate?
Q1. A pandas job on a large dataset runs out of memory or is too slow. How do you investigate?
- Memory: Check dtypes (numeric precision, categorical for strings), read only the columns you need, and process in chunks.
- Speed: Use a profiler to find hot spots, avoid row-by-row iteration, and use vectorized and grouped operations.
- Format and storage: Switch to a columnar file format with partitioning and read only the partitions you need.
- Beyond one machine: Consider in-database computation or distributed tools.
Q2. Why can NumPy code be much faster than pure Python, and what are the common ways to lose that speed?
Q2. Why can NumPy code be much faster than pure Python, and what are the common ways to lose that speed?
- Looping over elements in Python instead of using array operations
- Creating many temporary arrays in a long expression
- Accidental copies caused by non-contiguous slices or mixed dtypes
- Converting between Python objects and arrays repeatedly
- Using object dtype, which falls back to slow Python objects
Q3. Why does Python's GIL matter for research code? How do you use multiple cores?
Q3. Why does Python's GIL matter for research code? How do you use multiple cores?
SQL for trading data
Q4. A SQL query on a large price table suddenly became slow. How do you diagnose it?
Q4. A SQL query on a large price table suddenly became slow. How do you diagnose it?
- Read the execution plan (explain plan): is there a full table scan, and is the join order sensible?
- Check whether indexes and partitions match the query filters (symbol and time range).
- Check whether table statistics are out of date or the data volume suddenly grew.
- Check whether a function applied to a column prevents index use, or a join produces unexpected duplicate rows.
Q5. Trading SQL rounds use schemas like trades, quotes, orders, fills and positions. What question types should you expect?
Q5. Trading SQL rounds use schemas like trades, quotes, orders, fills and positions. What question types should you expect?
- Data-quality checks: Find trades with no matching instrument in a reference table. Use an anti-join (a left join filtered to missing matches). Prefer it to a NOT IN subquery, which behaves unexpectedly when NULL values are present.
- Exposure and netting: Net positions by counterparty. Apply a sign to buys and sells before summing.
- Order-to-fill summaries: Count fills and average price per order. Use a left join so unfilled orders still appear.
- Top-N per group and running totals: Use ranking or row-number functions partitioned by day (choose based on how ties should be treated), and ordered windowed sums for cumulative P&L.
- Share of total: Each trade’s share of daily volume, using a windowed sum over a partition.
- Fill rates: Classify orders as full, partial or unfilled with conditional counts, then take ratios.
Q6. How would you compute rolling statistics, build OHLC bars from ticks, and detect gaps in a time series in SQL?
Q6. How would you compute rolling statistics, build OHLC bars from ticks, and detect gaps in a time series in SQL?
- Rolling statistics (for example a 20-day volatility): a windowed aggregate partitioned by symbol, with an explicit frame of the previous N rows.
- OHLC bars: Group by truncated time bucket. High and low are the maximum and minimum price, while open and close are the first and last prices in time order within each bucket.
- Gap detection: Compare each timestamp with the previous one for the same symbol (lag), then filter for differences larger than a threshold.
- Forward-filling sparse data: Build a calendar-by-symbol grid, left join the observations, and carry the last known value forward.
- Period-over-period change: Aggregate in a common table expression first, then compare each period with the previous one.
Q7. Compute the VWAP of each order's fills and its slippage against the arrival price.
Q7. Compute the VWAP of each order's fills and its slippage against the arrival price.
- VWAP per order: sum of price times quantity divided by the sum of quantity, grouped by order. Filter out cancelled or busted fills first.
- Arrival price: join each order to the mid quote at or just before the order’s creation time (an as-of join, as in Q6).
- Slippage: VWAP minus arrival price, with the sign flipped for sells so that a positive number always means a cost. Express it in basis points of the arrival price so orders of different sizes and prices compare.
- Weighting the summary: average slippage weighted by notional, not by order count, or small orders dominate.
Q8. In pandas, what goes wrong when you use an as-of merge to join trades to quotes?
Q8. In pandas, what goes wrong when you use an as-of merge to join trades to quotes?
- Sorting: both tables must be sorted on the time key, or the merge fails or gives wrong matches.
- Grouping: without matching on the symbol (the “by” key), a trade can be matched to another instrument’s quote.
- Direction: the default looks backward, which is what you want. A forward or nearest match introduces look-ahead.
- Tolerance: without a maximum age, a trade can be paired with a quote from hours earlier. Set a tolerance and treat unmatched trades explicitly.
- Equal timestamps: decide whether a quote with exactly the trade’s timestamp is allowed, because the order of events within the same timestamp may be unknown.
Concurrency
Q9. Position updates arrive from several threads or processes. What can go wrong and how do you design for it?
Q9. Position updates arrive from several threads or processes. What can go wrong and how do you design for it?
- Race conditions: Two threads read and then write the same position, and one update overwrites the other.
- Duplicate messages: A network retry causes the same fill to be processed twice.
- Out-of-order messages: Fill and cancel acknowledgements arrive in a different order from reality.
Q10. Two threads transfer money between the same two accounts and the program sometimes hangs. What is happening?
Q10. Two threads transfer money between the same two accounts and the program sometimes hangs. What is happening?
- Lock ordering: Always acquire locks in one global order, for example by account ID, regardless of transfer direction.
- Fewer locks: Use one lock for the whole operation if contention is low, or a single-writer design that avoids locking.
- Timeouts and detection: Acquire with a timeout and back off, and log lock waits so hangs can be diagnosed.
Q11. When do you use a lock-free structure and when a mutex? What is the ABA problem?
Q11. When do you use a lock-free structure and when a mutex? What is the ABA problem?
- Lock-free: Suited to latency-critical paths such as market data feeds and order entry, where blocking is unacceptable. It is harder to write and verify correctly.
- Mutex: Simpler and perfectly adequate when contention is low and occasional blocking is acceptable.
- Spinlock versus mutex: A spinlock burns CPU but avoids sleeping, so it suits very short critical sections. A mutex uses less CPU but has higher wake-up latency, which suits longer sections.
Q12. When would you choose threads, processes or asyncio for a data service?
Q12. When would you choose threads, processes or asyncio for a data service?
- asyncio or threads: I/O-bound work such as many concurrent connections, downloads or database calls. Async avoids thread overhead for many mostly idle connections.
- Processes: CPU-bound work such as heavy numerical computation, because each process has its own interpreter and avoids the GIL. Budget for serialization costs when moving data between processes. On a free-threaded build, threads become an option for CPU-bound work too.
- Native extensions: Libraries that release the GIL give thread-level parallelism for numerical code.
- Mixed designs: An async front end that hands CPU-heavy work to a process pool.
Related pages
Sources
- SQL Interview Questions for Trading and Quant Roles (Quantt)
- What Interview Questions Test Quant Development Skills (OfflinePixel)
- Practice for quant developer interviews (TraderMath)
- PEP 779: Criteria for supported status for free-threaded Python
- PEP 779 acceptance by the Steering Council (discuss.python.org)

