SQL Interview Questions for Trading & Quant Roles 2026

Why Trading Desks Test SQL So Hard

Every trading desk runs on a relational database somewhere underneath the strategy code. Positions, fills, reference data, risk limits, P&L by book - all of it sits in tables, and the people who can pull the right answer out of those tables quickly are more valuable than the headline strategy logic suggests. That is why quant developer, quant researcher and even some trading interviews now include a dedicated SQL round, separate from the general coding interview.

The questions differ from a typical backend-engineering SQL round in three ways. First, the schemas are almost always time-series shaped - trades, quotes, fills, positions - so window functions and date logic come up constantly. Second, correctness matters more than cleverness: an off-by-one in a PnL query produces a wrong number that someone might trade on. Third, interviewers expect you to reason about performance on tables with hundreds of millions of rows, not the toy datasets from a bootcamp course.

This guide collects 15 real interview-style questions, grouped by theme, with worked SQL answers. For the underlying fundamentals, see our SQL for financial data primer, and for the next level up, advanced SQL techniques covers CTEs and query optimisation in more depth.

---

Section 1: Joins and Data Modelling (Questions 1-4)

1. Trades without a matching instrument

You have a trades table and an instruments reference table. Write a query that returns every trade where the instrument is missing from the reference table (a common data-quality check).

SELECT t.trade_id, t.symbol, t.trade_date
FROM trades t
LEFT JOIN instruments i ON t.symbol = i.symbol
WHERE i.symbol IS NULL;

Why it matters: this pattern (LEFT JOIN plus a NULL check on the right-hand key) is the standard way to find orphaned records. Interviewers want to see that you reach for LEFT JOIN rather than a slower NOT IN subquery, which also breaks silently if the reference table contains NULLs.

2. Counterparty exposure across two tables

Given trades (trade_id, counterparty_id, notional, side) and counterparties (counterparty_id, name, credit_rating), return total net exposure per counterparty, sorted descending.

SELECT
    c.name,
    c.credit_rating,
    SUM(CASE WHEN t.side = 'BUY' THEN t.notional ELSE -t.notional END) AS net_exposure
FROM trades t
JOIN counterparties c ON t.counterparty_id = c.counterparty_id
GROUP BY c.name, c.credit_rating
ORDER BY net_exposure DESC;

The trap here is treating BUY and SELL as additive rather than offsetting. Candidates who sum notional without the sign flip get a plausible-looking but wrong answer, which is exactly why interviewers use it.

3. Self-join for consecutive trades

Find pairs of trades on the same symbol executed within 5 seconds of each other, which might indicate a fat-finger or a wash trade.

SELECT
    a.trade_id AS trade_1,
    b.trade_id AS trade_2,
    a.symbol,
    a.trade_time,
    b.trade_time
FROM trades a
JOIN trades b
    ON a.symbol = b.symbol
    AND a.trade_id < b.trade_id
    AND b.trade_time BETWEEN a.trade_time AND a.trade_time + INTERVAL '5 seconds';

The a.trade_id < b.trade_id condition avoids matching a trade to itself and avoids counting each pair twice.

4. Many-to-many via a bridge table

An orders table can be filled by multiple fills, and each fill references exactly one order. Write a query returning each order alongside its fill count and average fill price.

SELECT
    o.order_id,
    o.symbol,
    o.quantity AS order_quantity,
    COUNT(f.fill_id) AS fill_count,
    AVG(f.fill_price) AS avg_fill_price
FROM orders o
LEFT JOIN fills f ON o.order_id = f.order_id
GROUP BY o.order_id, o.symbol, o.quantity;

Use LEFT JOIN, not INNER JOIN - unfilled orders should still appear with a fill count of zero, and dropping them silently is a common mistake.

---

Section 2: Window Functions (Questions 5-9)

Window functions are where most candidates separate themselves. They come up in nearly every trading SQL interview because so much of finance is "compare this row to some window of other rows".

5. Running cumulative PnL by day

SELECT
    trade_date,
    daily_pnl,
    SUM(daily_pnl) OVER (ORDER BY trade_date) AS cumulative_pnl
FROM daily_pnl_by_book
ORDER BY trade_date;

6. PnL by day per trading book, ranked

Return the top 3 traders by PnL for each day.

SELECT trade_date, trader_id, daily_pnl, rnk
FROM (
    SELECT
        trade_date,
        trader_id,
        daily_pnl,
        RANK() OVER (PARTITION BY trade_date ORDER BY daily_pnl DESC) AS rnk
    FROM daily_trader_pnl
) ranked
WHERE rnk <= 3
ORDER BY trade_date, rnk;

RANK() rather than ROW_NUMBER() is the correct choice when ties should share a position - if two traders tie for third, both should show as rank 3, and a candidate who defaults to ROW_NUMBER() without thinking about ties will be asked to justify the choice.

7. Rolling 20-day volatility

SELECT
    trade_date,
    symbol,
    daily_return,
    STDDEV(daily_return) OVER (
        PARTITION BY symbol
        ORDER BY trade_date
        ROWS BETWEEN 19 PRECEDING AND CURRENT ROW
    ) AS rolling_20d_vol
FROM daily_returns;

8. Detecting a gap in trading activity

Using LAG, flag any symbol that has gone more than 3 trading days without a print.

SELECT
    symbol,
    trade_date,
    LAG(trade_date) OVER (PARTITION BY symbol ORDER BY trade_date) AS prev_trade_date,
    trade_date - LAG(trade_date) OVER (PARTITION BY symbol ORDER BY trade_date) AS gap_days
FROM (SELECT DISTINCT symbol, trade_date FROM trades) daily_activity
QUALIFY gap_days > 3;

Not every database supports QUALIFY (Snowflake and BigQuery do; Postgres does not), so be ready to wrap the same logic in a subquery with a WHERE clause on the outer query if asked to do it in standard SQL.

9. Percentage of daily volume per trade

SELECT
    trade_id,
    symbol,
    trade_date,
    quantity,
    ROUND(
        100.0 * quantity / SUM(quantity) OVER (PARTITION BY symbol, trade_date),
        2
    ) AS pct_of_daily_volume
FROM trades;

---

Section 3: Time-Series Aggregation (Questions 10-12)

10. Resampling tick data to 1-minute OHLC bars

SELECT
    symbol,
    DATE_TRUNC('minute', trade_time) AS bar_time,
    (ARRAY_AGG(price ORDER BY trade_time ASC))[1] AS open_price,
    MAX(price) AS high_price,
    MIN(price) AS low_price,
    (ARRAY_AGG(price ORDER BY trade_time DESC))[1] AS close_price,
    SUM(quantity) AS volume
FROM ticks
GROUP BY symbol, DATE_TRUNC('minute', trade_time)
ORDER BY symbol, bar_time;

This is the classic "build OHLC bars from raw ticks" question. The open and close require ordering within the aggregate, which is why ARRAY_AGG ... ORDER BY (or a window function with FIRST_VALUE/LAST_VALUE) shows up rather than a plain MIN/MAX, which only works for high and low.

11. Month-on-month PnL comparison

WITH monthly AS (
    SELECT
        DATE_TRUNC('month', trade_date) AS month,
        SUM(daily_pnl) AS monthly_pnl
    FROM daily_pnl_by_book
    GROUP BY DATE_TRUNC('month', trade_date)
)
SELECT
    month,
    monthly_pnl,
    LAG(monthly_pnl) OVER (ORDER BY month) AS prev_month_pnl,
    monthly_pnl - LAG(monthly_pnl) OVER (ORDER BY month) AS mom_change
FROM monthly
ORDER BY month;

12. Filling gaps in a daily time series

Given a table with sparse daily observations, generate a continuous calendar and left-join the data, forward-filling the last known value.

The trick is to build a per-symbol calendar (calendar × symbols) and forward-fill within each symbol. In PostgreSQL, one portable pattern uses a "grouping key" that increments every time a non-null appears, and then propagates the last non-null via MAX:

WITH calendar AS (
    SELECT generate_series(
        (SELECT MIN(trade_date) FROM positions),
        (SELECT MAX(trade_date) FROM positions),
        INTERVAL '1 day'
    )::date AS cal_date
),
symbols AS (
    SELECT DISTINCT symbol FROM positions
),
joined AS (
    SELECT c.cal_date, s.symbol, p.position_qty
    FROM calendar c
    CROSS JOIN symbols s
    LEFT JOIN positions p
      ON p.trade_date = c.cal_date
     AND p.symbol    = s.symbol
),
grouped AS (
    SELECT
        cal_date, symbol, position_qty,
        COUNT(position_qty) OVER (
            PARTITION BY symbol ORDER BY cal_date
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS grp
    FROM joined
)
SELECT
    cal_date,
    symbol,
    MAX(position_qty) OVER (PARTITION BY symbol, grp) AS filled_position
FROM grouped
ORDER BY symbol, cal_date;

On dialects that support it (Snowflake, BigQuery, and Postgres 16+), you can replace the grouped/MAX step with the more compact LAST_VALUE(position_qty) IGNORE NULLS OVER (PARTITION BY symbol ORDER BY cal_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW).

Forward-fill questions come up because position and holdings tables are usually only written on the days something changes, not every day - so any report that needs a daily view has to reconstruct the gaps. Two things interviewers listen for: partitioning by symbol so the fill does not bleed across instruments, and building a proper calendar × symbol grid rather than a single left join.

---

Section 4: PnL and Fill-Rate Queries (Questions 13-15)

13. Daily realised PnL from fills

SELECT
    trade_date,
    symbol,
    SUM(
        CASE WHEN side = 'SELL' THEN quantity * price ELSE -quantity * price END
    ) AS realised_pnl
FROM fills
GROUP BY trade_date, symbol
ORDER BY trade_date, symbol;

Interviewers will often push further: "this ignores opening positions carried from the prior day, how would you handle that?" The honest answer is that realised PnL needs a cost-basis method (FIFO, LIFO, or average cost) applied against carried positions, which usually means joining to a running position table rather than computing PnL from fills alone.

14. Fill rate by order type

What percentage of orders were fully filled, partially filled, or unfilled, broken down by order type?

SELECT
    order_type,
    COUNT(*) AS total_orders,
    SUM(CASE WHEN filled_quantity = order_quantity THEN 1 ELSE 0 END) AS fully_filled,
    SUM(CASE WHEN filled_quantity > 0 AND filled_quantity < order_quantity THEN 1 ELSE 0 END) AS partially_filled,
    SUM(CASE WHEN filled_quantity = 0 THEN 1 ELSE 0 END) AS unfilled,
    ROUND(100.0 * SUM(CASE WHEN filled_quantity = order_quantity THEN 1 ELSE 0 END) / COUNT(*), 2) AS fill_rate_pct
FROM orders
GROUP BY order_type
ORDER BY fill_rate_pct DESC;

15. Slippage versus arrival price

For each order, compute slippage as the difference between the average fill price and the price at order arrival.

SELECT
    o.order_id,
    o.symbol,
    o.arrival_price,
    AVG(f.fill_price) AS avg_fill_price,
    CASE
        WHEN o.side = 'BUY' THEN AVG(f.fill_price) - o.arrival_price
        ELSE o.arrival_price - AVG(f.fill_price)
    END AS slippage_per_share
FROM orders o
JOIN fills f ON o.order_id = f.order_id
GROUP BY o.order_id, o.symbol, o.arrival_price, o.side;

The sign flip for BUY versus SELL is the detail most candidates miss under time pressure. Positive slippage should always mean "cost more than expected", regardless of side, and getting the sign backwards for sells is a very common interview mistake.

---

SQL and Python: What the Hybrid Round Looks Like

Many quant developer interviews in 2026 do not test SQL in isolation. A common format: you are given a database connection and asked to pull data with SQL, then finish the analysis in Python with pandas. The SQL side should do the heavy lifting - filtering, joining, and aggregating at the database level - rather than pulling a raw table and doing everything client-side, which is slow and occasionally impossible if the table has hundreds of millions of rows.

A typical prompt: "Pull all fills for the past 30 days, compute daily PnL by trader, and identify traders whose PnL volatility increased more than 50% versus the prior 30 days." The efficient path aggregates to daily PnL in SQL, then does the volatility comparison in pandas where multi-step statistical logic is easier to write and debug. Interviewers are checking that you know which tool suits which job, not that you can force everything through one language.

---

A Two-Week Prep Plan

Days 1-3: Joins and aggregation. Rebuild the classic INNER, LEFT, and self-join patterns from memory. Practise until GROUP BY with multiple aggregates and a HAVING clause is automatic.

Days 4-7: Window functions. This is the highest-yield topic. Drill RANK, ROW_NUMBER, LAG/LEAD, running sums, and rolling windows with explicit frame clauses (ROWS BETWEEN ... PRECEDING AND CURRENT ROW) until you do not need to look up the syntax.

Days 8-10: Time-series specifics. OHLC bar construction, gap-filling, and month-over-month or day-over-day comparisons. These are finance-specific patterns that generic SQL practice sites rarely cover.

Days 11-14: Mixed timed practice. Set a 10-minute timer per question and solve problems cold, including at least one PnL and one fill-rate question each session. For broader interview context alongside the SQL round, our quant developer interview questions and quant coding interview questions guides cover what else to expect in the same interview loop.

---

A note on dialects and interview conventions

The queries in this guide are written to be portable across major SQL dialects where possible, with PostgreSQL 14+ as the reference dialect. Some patterns - notably QUALIFY, LAST_VALUE ... IGNORE NULLS, and certain WINDOW shorthands - are only available in Snowflake, BigQuery or newer Postgres versions; those are called out inline where they appear. Interview conventions vary by firm, so treat these solutions as a scaffold rather than the only valid answer, and always confirm which dialect an interviewer expects before optimising for one.

---

Frequently Asked Questions

How much SQL do quant developer interviews actually test?

Most quant developer loops in 2026 include one dedicated SQL round, often 30-45 minutes, separate from the general coding round. The bar is joins, aggregation, and window functions applied to time-series-shaped tables (trades, fills, positions). Very few roles ask for advanced database internals like index tuning, though it can come up for infrastructure-focused positions.

Which SQL dialect should I practise?

PostgreSQL is the safest default because it is free, widely used in practice questions, and close enough to what most firms actually run. Some trading firms use kdb+/q instead of standard SQL for tick data - if you know the firm uses kdb, it is worth a separate look at our kdb+/q tutorial, though most interviews will still test standard SQL even at kdb shops.

Do I need to know query optimisation for a trading SQL interview?

Junior roles rarely require deep optimisation knowledge, but you should be able to explain why an index helps, why SELECT * is bad practice at scale, and why a function applied to a column in a WHERE clause can prevent index usage. Senior quant developer roles may ask you to read an EXPLAIN plan and diagnose why a query is slow.

Are window functions really that important?

Yes. Of the fifteen questions above, eight rely on a window function in some form. Financial data is inherently ordered by time, and almost every interesting question ("what changed since yesterday", "what is the running total", "who ranked highest this month") is naturally expressed with a window function rather than a plain aggregate.

What is the biggest mistake candidates make in SQL interviews?

Sign errors on BUY/SELL logic and forgetting that HAVING filters after aggregation while WHERE filters before it. Both mistakes produce syntactically valid queries that silently return the wrong number, which is worse than a query that fails to run at all because the error is much harder to catch under interview time pressure.

Should I practise on LeetCode or on finance-specific problems?

Both, but weight finance-specific practice higher if you are short on time. LeetCode's SQL section builds general fluency with joins and aggregation, but it rarely tests the time-series patterns (OHLC bars, gap-filling, rolling windows partitioned by symbol) that dominate actual trading desk interviews. Working through problems against a realistic trades-and-fills schema, like the ones in this guide, transfers more directly.