diff options
| -rw-r--r-- | sql/dawn.sql | 7 |
1 files changed, 7 insertions, 0 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index 75f1181b..c4b6ad94 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -2004,3 +2004,10 @@ CREATE TABLE curve_risk( "VaR" float, currency currency, PRIMARY KEY (date, strategy)) + +CREATE OR REPLACE VIEW tranche_pnl AS +WITH temp AS ( + SELECT date, tranche_id, fund, clean_nav + accrued - lag(clean_nav+accrued, -1, -cds.upfront) over (partition by tranche_id ORDER BY date DESC) AS daily_pnl + FROM tranche_risk JOIN cds ON tranche_id=id +) +SELECT date, fund, tranche_id, daily_pnl, sum(daily_pnl) OVER (PARTITION BY tranche_id, date_trunc('month', date) ORDER BY date) AS mtd_pnl FROM temp; |