header image
[ # ] Calculate Expected Shortfall in SQL
August 20th, 2015 under Programming

A commonly used risk measure is the Expected Shortfall. Unfortunately, this function to calculate the sample expected shortfall is rarely implemented. As there is much data stored in databases accessible through SQL, I tried to calculate this value directly in SQL. It turns out this is a one-liner. Let’s assume we have a table called “LossSimulation” and a column/field named “LossValue” then the sample expected shortfall at level 95% is

1
SELECT AVG(LossValue) FROM (SELECT TOP 95 PERCENT LossValue FROM LossSimulation ORDER BY LossValue)
SELECT AVG(LossValue) FROM (SELECT TOP 95 PERCENT LossValue FROM LossSimulation ORDER BY LossValue)

Note: Microsoft Access may list more than 95% of all records because if multiple value at the 95% quantile value are the same then all these records are listed (see here).