SQL Interview Questions for Finance Analysts

Scene: a 45-minute technical screen for an analyst role at an asset manager. The interviewer shares a screen with two tables, daily prices and a trade ledger, and says: "No Excel. Four questions, in SQL." They are almost always the same four, because each one tests a different part of window-function fluency. This lesson walks through all four with answers that run on this sandbox's tables, then gives you the one candidates most often get wrong.

What it is: A window function computes a value for each row from a set of related rows (its window) without collapsing them, unlike GROUP BY. LAG, ROW_NUMBER, RANK and running SUM all use the same OVER (PARTITION BY ... ORDER BY ...) clause.

Question 1: daily returns per ticker

"Compute each stock's daily return." The answer is LAG, which reads the previous row within the same partition:

SELECT ticker, trade_date, close_price,
       ROUND(100.0 * (close_price / LAG(close_price) OVER (
           PARTITION BY ticker ORDER BY trade_date
       ) - 1), 2) AS daily_ret_pct
FROM equity_prices;

daily return = close_t / close_(t-1) - 1
example:       101.50 / 100.00 - 1 = +1.50%

What they are checking: PARTITION BY ticker (without it, the first MSFT row would be compared with the last AAPL row), and that you know the first row of each ticker is NULL.

Question 2: running total (net position)

"Show the running net position per ticker from the trade ledger." Buys add, sells subtract, and the running sum is a window with a frame:

SELECT ticker, trade_date, side, quantity,
       SUM(CASE WHEN side = 'BUY' THEN quantity ELSE -quantity END) OVER (
           PARTITION BY ticker ORDER BY trade_date, trade_id
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS net_position
FROM trade_ledger;

Worked example (illustrative quantities; the sandbox ledger is randomised on every load): SELL 727, then BUY 908, then BUY 281 gives -727, then -727 + 908 = 181, then 181 + 281 = 462. A negative number means the book is short. What they are checking: the sign flip with CASE, and an explicit ROWS frame (with ties in the ORDER BY, the default RANGE frame would add all same-date trades at once).

Question 3: month-end close and monthly return

"Give me each ticker's monthly return." Pick the last trading day of each month with ROW_NUMBER ordered by date descending, then LAG across months:

WITH month_end AS (
    SELECT ticker, substr(trade_date, 1, 7) AS month, close_price,
           ROW_NUMBER() OVER (
               PARTITION BY ticker, substr(trade_date, 1, 7)
               ORDER BY trade_date DESC
           ) AS rn
    FROM equity_prices
)
SELECT ticker, month, close_price,
       ROUND(100.0 * (close_price / LAG(close_price) OVER (
           PARTITION BY ticker ORDER BY month
       ) - 1), 2) AS monthly_ret_pct
FROM month_end
WHERE rn = 1;

The LAG here runs after WHERE rn = 1: window functions are computed on the rows that survive the WHERE clause, so it compares month-ends with month-ends.

Question 4: top N per group (your turn)

"For each ticker, list its three worst daily returns." This is the most-asked of the four, and the most-failed. The tempting answer sorts everything and takes LIMIT 3, or ranks without a partition, and returns the three worst days in the whole table, not per ticker. The right shape is: number the rows within each ticker in a CTE, then filter on that number outside it.

ROW_NUMBER() OVER (
    PARTITION BY ticker
    ORDER BY daily_ret_pct ASC
)
  → 1, 2, 3, ... restarting at 1 for every ticker

WHERE worst_rank <= 3
  → 3 rows × 5 tickers = 15 rows

Why a CTE? A window function cannot appear in the WHERE clause of the query that computes it, because WHERE is evaluated first. You compute the rank in one step and filter in the next.

ROW_NUMBER, RANK or DENSE_RANK? On ties (two days with the same return), ROW_NUMBER gives 1, 2, 3 and breaks the tie arbitrarily (here by date, as written), RANK gives 1, 1, 3, and DENSE_RANK gives 1, 1, 2. For "exactly three rows per ticker", ROW_NUMBER is the safe choice; say so in the interview.

Your Task

The starter computes daily returns in a first CTE and ranks them in a second. YOUR TURN marks the ranking window: it has no partition, so the ranks run across all five tickers together and the query returns only 3 rows.

Predict before you run: how many rows will the query return once the ranking restarts for each ticker? Then add PARTITION BY ticker to the window.