diff options
Diffstat (limited to 'sql')
| -rw-r--r-- | sql/dawn.sql | 53 |
1 files changed, 51 insertions, 2 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index 85127f4d..bd318bae 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -1810,8 +1810,7 @@ for t in (df.itertuples(index=False)): $$ LANGUAGE plpython3u; - -CREATE OR REPLACE VIEW globeop_tranche_risk AS +CREATE OR REPLACE VIEW tranche_risk_serenitas AS SELECT tranche_risk.date, tranche_id as trade_id, security_desc, index, series, maturity, orig_attach, orig_detach, tranche_risk.notional, globeop_notional, @@ -1837,6 +1836,56 @@ LEFT JOIN external_marks_deriv on cpty_id=identifier and external_marks_deriv.da ORDER BY index, series, orig_attach; +CREATE OR REPLACE VIEW tranche_risk_serenitas_agg AS +SELECT date, security_desc, index, series, sum(globeop_notional) as globeop_notional, + sum(globeop_clean_nav) as globeop_clean_nav, maturity, sum(notional) as orig_ntl, + avg(tranche_factor) as tranche_factor, avg(running) as running, + sum(serenitas_clean_nav) as serenitas_clean_nav, + sum(serenitas_accrued) as serenitas_accrued, + sum(initial_margin_percentage * abs(notional)/100) as initial_margin, + avg(theta) as theta, avg(duration) as duration, avg(delta) as delta, + avg(upfront) as upfront, avg(index_refprice) as index_refprice, + avg(index_refspread) as index_refspread, + orig_attach, orig_detach, avg(index_duration) as index_duration, + avg(gamma) as gamma + FROM tranche_risk_serenitas + GROUP BY date, index, security_desc, series, orig_attach, orig_detach, maturity + ORDER BY index, series; + + +CREATE OR REPLACE VIEW tranche_risk_master AS +SELECT tranche_risk.date, tranche_id AS trade_id, fund, security_desc, index, series, maturity, orig_attach, + orig_detach, tranche_risk.notional, + clean_nav * coalesce(fx, 1.) as serenitas_clean_nav, + accrued * coalesce(fx, 1.) as serenitas_accrued, + nav AS cpty_nav, + duration, delta, gamma, theta, tranche_factor, + tranche_risk.corr_attach, tranche_risk.corr_detach, + tranche_risk.upfront, tranche_risk.running, + index_refprice, index_refspread, index_duration, initial_margin_percentage, ia as cpty_ia +FROM tranche_risk +LEFT JOIN cds ON (tranche_id=id) +LEFT JOIN index_version ON (security_id=redindexcode) +LEFT JOIN (SELECT date, 'EUR'::currency AS currency, eurusd AS fx FROM fx) fx USING (date, currency) +LEFT JOIN external_marks_deriv on cpty_id=identifier and external_marks_deriv.date=tranche_risk.date +ORDER BY date desc, index, series, orig_attach; + +CREATE OR REPLACE VIEW tranche_risk_agg AS +SELECT date, fund, security_desc, index, series, maturity, sum(notional) AS orig_ntl, + avg(tranche_factor) AS tranche_factor, avg(running) as running, + sum(serenitas_clean_nav) AS serenitas_clean_nav, + sum(serenitas_accrued) AS serenitas_accrued, + sum(cpty_nav) AS cpty_nav, + sum(initial_margin_percentage * abs(notional)/100) as initial_margin, + avg(theta) as theta, avg(duration) as duration, avg(delta) as delta, + avg(upfront) as upfront, avg(index_refprice) as index_refprice, + avg(index_refspread) AS index_refspread, + orig_attach, orig_detach, avg(index_duration) as index_duration, + avg(gamma) AS gamma + FROM tranche_risk_master + GROUP BY date, fund, index, security_desc, series, orig_attach, orig_detach, maturity + ORDER BY index, series; + CREATE TABLE fcm_im( date date NOT NULL, account text NOT NULL, -- REFERENCES accounts(cash_account) |