cswork / libraryOpen practice workspace

Coding questions · DRW

Process CSV for portfolio returns and metrics

This question evaluates a candidate's competency in time-series data manipulation, financial return computation, portfolio analytics, numerical stability, modular software design (clear function boundaries and unit tests), and algorithmic complexity analysis within the Data Manipulation (SQL/Python) domain.

Given one or more CSV files containing daily asset prices or returns and optional portfolio weights, write Python (pandas) code to: a) load, clean, and align by date; b) handle missing values and outliers with documented choices; c) compute individual asset returns (simple and log) from prices if needed; d) compute a time series of portfolio returns given weights, supporting fixed-weight and periodic rebalancing; e) calculate cumulative return, annualized return, annualized volatility, Sharpe ratio (risk-free rate provided), maximum drawdown, and rolling-window metrics; f) support multiple portfolios and output a tidy summary table plus a CSV of metrics and a plot-ready time series; g) ensure numerical stability, clear function boundaries, and unit tests for edge cases (non-overlapping dates, zero weights, NaNs); h) report algorithmic complexity and justify design choices.

Compute daily asset returns from price history

You are given a table of daily closing prices for multiple assets. Write a SQL query to compute the daily simple and log returns for each asset, aligned by date. For each asset, skip the first available date (because there is no previous price). Return one row per asset and date with the columns: asset_id, price_date, simple_return, log_return.

Table asset_prices

columntypeconstraints
asset_idINTNOT NULL
asset_nameVARCHAR(50)NOT NULL
price_dateDATENOT NULL
close_priceDECIMAL(10,2)NOT NULL

Sample data:

asset_idasset_nameclose_priceprice_date
1Stock A1002025-01-01
1Stock A1012025-01-02
1Stock A992025-01-03
1Stock A1022025-01-04
1Stock A1032025-01-05
2Stock B502025-01-01
2Stock B492025-01-02
2Stock B512025-01-03
2Stock B522025-01-04
2Stock B512025-01-05
3Stock C802025-01-01
3Stock C822025-01-02
3Stock C812025-01-03
3Stock C832025-01-04
3Stock C852025-01-05

Expected output

One row per asset_id and price_date after the first date for that asset, with daily simple and log returns computed from consecutive closing prices.

asset_idlog_returnprice_datesimple_return
10.012025-01-020.01
1-0.022025-01-03-0.0198
10.02992025-01-040.0303
10.00982025-01-050.0098
2-0.02022025-01-02-0.02
20.042025-01-030.0408
20.01942025-01-040.0196
2-0.01942025-01-05-0.0192
30.02472025-01-020.025
3-0.01232025-01-03-0.0122
30.02442025-01-040.0247
30.02382025-01-050.0241

Hints

  1. Use a window function to access the previous day's price for each asset.
  2. Simple return is (today_price / yesterday_price) - 1, and log return is LN(today_price / yesterday_price).

Compute daily portfolio returns from asset returns and weights

Write a PostgreSQL query. You are given daily simple returns for each asset and daily portfolio weights over those assets for multiple portfolios. For each portfolio and date, compute the portfolio's daily simple return as the weighted sum of weight * simple_return across its constituent assets. Assume weights are already aligned by date, and any asset not listed for a portfolio on a given date has zero weight. Return one row per portfolio per date with return_date, portfolio_id, portfolio_name, and portfolio_simple_return rounded to 4 decimal places.

Table asset_returns

columntypeconstraints
asset_idINTNOT NULL
price_dateDATENOT NULL
simple_returnDECIMAL(10,4)NOT NULL
log_returnDECIMAL(10,4)NOT NULL

Sample data:

asset_idlog_returnprice_datesimple_return
10.012025-01-020.01
1-0.022025-01-03-0.0198
10.02992025-01-040.0303
10.00982025-01-050.0098
2-0.02022025-01-02-0.02
20.042025-01-030.0408
20.01942025-01-040.0196
2-0.01942025-01-05-0.0192
30.02472025-01-020.025
3-0.01232025-01-03-0.0122
30.02442025-01-040.0247
30.02382025-01-050.0241

Table portfolios

columntypeconstraints
portfolio_idINTPRIMARY KEY
portfolio_nameVARCHAR(50)NOT NULL

Sample data:

portfolio_idportfolio_name
1Conservative
2Aggressive

Table portfolio_weights

columntypeconstraints
portfolio_idINTNOT NULL
asset_idINTNOT NULL
weight_dateDATENOT NULL
weightDECIMAL(5,4)NOT NULL

Sample data:

asset_idportfolio_idweightweight_date
110.52025-01-02
210.32025-01-02
310.22025-01-02
120.22025-01-02
220.52025-01-02
320.32025-01-02
110.52025-01-03
210.32025-01-03
310.22025-01-03
120.22025-01-03
220.52025-01-03
320.32025-01-03
110.52025-01-04
210.32025-01-04
310.22025-01-04
120.22025-01-04
220.52025-01-04
320.32025-01-04
110.52025-01-05
210.32025-01-05
310.22025-01-05
120.22025-01-05
220.52025-01-05
320.32025-01-05

Expected output

One row per portfolio per date, with the portfolio's daily simple return computed as sum(weight * simple_return) and rounded to 4 decimal places.

portfolio_idportfolio_nameportfolio_simple_returnreturn_date
1Conservative0.0042025-01-02
2Aggressive-0.00052025-01-02
1Conservative-0.00012025-01-03
2Aggressive0.01282025-01-03
1Conservative0.0262025-01-04
2Aggressive0.02332025-01-04
1Conservative0.0042025-01-05
2Aggressive-0.00042025-01-05

Hints

  1. Join asset_returns to portfolio_weights on both asset_id and date.
  2. Group by portfolio and date, then sum weight * simple_return to get the portfolio return.

Compute portfolio performance metrics including Sharpe ratio and max drawdown

You are given daily simple returns for multiple portfolios and a daily risk-free rate. Using the full sample period in the data, compute the following performance metrics per portfolio: (1) cumulative return over the sample, (2) annualized return, (3) annualized volatility of returns, (4) annualized Sharpe ratio using daily excess returns over the risk-free rate, and (5) maximum drawdown based on the portfolio wealth index. Assume this sample covers exactly 4 trading days and that there are 4 trading days in a year (so the sample represents one full year). Use 4 as the annualization factor in your calculations. Return one row per portfolio with: portfolio_id, portfolio_name, start_date, end_date, cumulative_return, annualized_return, annualized_volatility, annualized_sharpe_ratio, max_drawdown.

Table portfolios

columntypeconstraints
portfolio_idINTPRIMARY KEY
portfolio_nameVARCHAR(50)NOT NULL

Sample data:

portfolio_idportfolio_name
1Conservative
2Aggressive

Table portfolio_returns

columntypeconstraints
portfolio_idINTNOT NULL
return_dateDATENOT NULL
simple_returnDECIMAL(10,4)NOT NULL

Sample data:

portfolio_idreturn_datesimple_return
12025-01-020.004
12025-01-03-0.0001
12025-01-040.026
12025-01-050.004
22025-01-02-0.0005
22025-01-030.0128
22025-01-040.0233
22025-01-05-0.0004

Table risk_free_rates

columntypeconstraints
rate_dateDATENOT NULL
daily_rf_rateDECIMAL(10,4)NOT NULL

Sample data:

daily_rf_raterate_date
0.00012025-01-02
0.00012025-01-03
0.00012025-01-04
0.00012025-01-05

Expected output

One row per portfolio with sample-period performance metrics: cumulative and annualized return, annualized volatility, annualized Sharpe ratio (based on daily excess returns), and maximum drawdown.

annualized_returnannualized_sharpe_ratioannualized_volatilitycumulative_returnend_datemax_drawdownportfolio_idportfolio_namestart_date
0.03411.41440.02370.03412025-01-05-0.00011Conservative2025-01-02
0.03551.51180.0230.03552025-01-05-0.00042Aggressive2025-01-02

Hints

  1. Join portfolio_returns with risk_free_rates to get daily excess returns.
  2. Use window functions with LN and EXP to build a wealth index and compute maximum drawdown, and aggregate over the full period for summary metrics.