FastPrepClient Markout Analysis
Problem · Database

Client Markout Analysis

● MediumMillennium logoMillenniumNEW GRADOA

Problem statement

The trades table contains completed client trades and the corresponding mid-price at a fixed future measurement horizon. Calculate each client's average markout and identify clients whose flow lost money for the desk on average.

Markout convention

  • BUY means the client bought and the desk sold, so desk markout is fill_price - future_mid_price.
  • SELL means the client sold and the desk bought, so desk markout is future_mid_price - fill_price.

For each client, return the trade count, the unweighted arithmetic mean of per-trade markout, and is_invalid = true exactly when that mean is negative.

Table schema

MySQLPostgreSQLPandas

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.

trades

ColumnTypeNullableDescription
trade_idPKIntegerNo—
client_idTextNo—
sideTextNo—
fill_priceDecimalNo—
future_mid_priceDecimalNo—

Expected result

Your query or function must return these columns.

ColumnTypeNullableDescription
client_idTextNo—
trade_countIntegerNo—
mean_markoutDecimalNo—
is_invalidBooleanNo—

Row order: must match exactly. Numeric tolerance: 0.000001.

Constraints

  • The table contains between 1 and 2000 trades.
  • trade_id values are unique, and side is exactly BUY or SELL.
  • The future mid-price is prejoined at the desk's fixed evaluation horizon; both prices are non-null positive exact decimals.
  • Use an unweighted mean: every trade contributes once regardless of client, asset, or notional.
  • A zero mean is valid and must not be flagged.
  • Return one row per client in ascending client_id order. Numeric results are compared with absolute tolerance 0.000001.

More Millennium problems