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)