diff options
Diffstat (limited to 'sql/et_tables.sql')
| -rw-r--r-- | sql/et_tables.sql | 164 |
1 files changed, 83 insertions, 81 deletions
diff --git a/sql/et_tables.sql b/sql/et_tables.sql index 9f109772..b71141b8 100644 --- a/sql/et_tables.sql +++ b/sql/et_tables.sql @@ -2,6 +2,7 @@ CREATE TYPE intex_fixedorfloat AS ENUM('FIXED', 'FLOAT'); CREATE TABLE et_collateral ( + id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY, dealname varchar(8) NOT NULL, updatedate date, Name text, @@ -17,7 +18,7 @@ CREATE TABLE et_collateral ( SecondLien boolean, LoanXID varchar(10), ET_LoanXID varchar(10) default NULL, - Cusip varchar(9), + figi varchar(12), ET_Cusip varchar(10) default NULL, IntexPrice float, IntexPriceSource text, @@ -35,7 +36,7 @@ CREATE TABLE et_collateral ( Country text, amort_schedule_dates date[] DEFAULT NULL, amort_schedule_amounts float[] DEFAULT NULL, - PRIMARY KEY (updatedate, Name, dealname) + UNIQUE (updatedate, Name, dealname) ); CREATE INDEX dealname_idx ON et_collateral(dealname, updatedate); @@ -340,8 +341,8 @@ CREATE TABLE cusip_universe_old ( GRANT ALL ON cusip_universe TO et_user; -CREATE TABLE cusip_ref( - cusip_id serial PRIMARY KEY, +CREATE TABLE tranche_ref( + id serial PRIMARY KEY, Cusip varchar(9) NOT NULL, ISIN varchar(12), bloomberg_ticker text, @@ -355,11 +356,11 @@ CREATE TABLE cusip_ref( Floater_Index text, Spread float, type text, - figi text, + figi varchar(12), UNIQUE (Cusip, dealname)); -CREATE TABLE cusip_update( - cusip_id int REFERENCES cusip_ref, +CREATE TABLE tranche_update( + id int REFERENCES tranche_ref, curr_balance float, factor float, coupon float, @@ -367,16 +368,16 @@ CREATE TABLE cusip_update( Curr_Attach float, Curr_Detach float, updatedate date, - PRIMARY KEY(cusip_id, updatedate)); + PRIMARY KEY (id, updatedate)); -CREATE OR REPLACE VIEW cusip_universe AS - SELECT a.cusip, a.isin, a.bloomberg_ticker, a.dealname, a.tranche, +CREATE OR REPLACE VIEW tranche_universe AS + SELECT a.figi, a.cusip, a.isin, a.bloomberg_ticker, a.dealname, a.tranche, a.paripassu_tranches, a.orig_balance, a.orig_moody, a.orig_attach, a.orig_detach, a.floater_index, a.spread, a.type, b.curr_balance, b.factor, b.coupon, b.curr_moody, b.curr_attach, b.curr_detach, - b.updatedate FROM cusip_ref a - JOIN cusip_update b USING (cusip_id); + b.updatedate FROM tranche_ref a + JOIN tranche_update b USING (id); CREATE OR REPLACE VIEW latest_clo_universe AS SELECT b.dealname, "Deal Name", Manager, b.orig_deal_bal, b."Curr Deal Bal", @@ -391,14 +392,14 @@ CREATE OR REPLACE VIEW latest_clo_universe AS GRANT ALL ON latest_clo_universe TO et_user; -CREATE OR REPLACE VIEW latest_cusip_universe AS -SELECT DISTINCT ON (cusip_id) a.cusip, a.isin, a.bloomberg_ticker, a.dealname, +CREATE OR REPLACE VIEW latest_tranche_universe AS +SELECT DISTINCT ON (id) a.figi, a.cusip, a.isin, a.bloomberg_ticker, a.dealname, a.tranche, a.paripassu_tranches, a.orig_balance, a.orig_moody, a.orig_attach, a.orig_detach, a.floater_index, a.spread, a.type, b.curr_balance, b.coupon, b.curr_moody, b.curr_attach, - b.curr_detach, b.updatedate from cusip_update b JOIN cusip_ref a USING (cusip_id) + b.curr_detach, b.updatedate from tranche_update b JOIN tranche_ref a USING (id) JOIN deal_indicative USING (dealname) -WHERE paid_down IS NULL ORDER BY cusip_id, updatedate DESC; +WHERE paid_down IS NULL ORDER BY id, updatedate DESC; CREATE OR REPLACE FUNCTION historical_clo_universe(p_dealname varchar(10), p_date date) @@ -420,33 +421,33 @@ CREATE OR REPLACE FUNCTION historical_clo_universe(p_dealname varchar(10), p_dat END; $$ LANGUAGE plpgsql; -CREATE OR REPLACE FUNCTION historical_cusip_universe(p_cusip varchar(9), p_date date) - RETURNS SETOF cusip_universe AS $$ +CREATE OR REPLACE FUNCTION historical_tranche_universe(p_figi varchar(12), p_date date) + RETURNS SETOF tranche_universe AS $$ DECLARE latestdate date; BEGIN - SELECT max(cusip_universe.updatedate) INTO latestdate FROM cusip_universe - WHERE cusip_universe.cusip = p_cusip AND cusip_universe.updatedate<=p_date; - RETURN QUERY SELECT a.* FROM cusip_universe a WHERE a.cusip=p_cusip + SELECT max(tranche_universe.updatedate) INTO latestdate FROM tranche_update + WHERE tranche_universe.figi = p_figi AND tranche_imoverse.updatedate<=p_date; + RETURN QUERY SELECT a.* FROM tranche_universe a WHERE a.figi=p_figi AND a.updatedate=latestdate; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION historical_dealname_universe(p_dealname varchar(10), p_date date) - RETURNS SETOF cusip_universe AS $$ + RETURNS SETOF tranche_universe AS $$ DECLARE latestdate date; BEGIN - SELECT max(cusip_universe.updatedate) INTO latestdate FROM cusip_universe - WHERE cusip_universe.dealname = p_dealname AND cusip_universe.updatedate<=p_date; - RETURN QUERY SELECT a.* FROM cusip_universe a WHERE a.dealname=p_dealname + SELECT max(tranche_universe.updatedate) INTO latestdate FROM tranche_universe + WHERE tranche_universe.dealname = p_dealname AND tranche_universe.updatedate<=p_date; + RETURN QUERY SELECT a.* FROM tranche_universe a WHERE a.dealname=p_dealname AND a.updatedate=latestdate; END; $$ LANGUAGE plpgsql; -CREATE OR REPLACE VIEW dealcusipmapping AS - select cusip, dealname from deal_indicative join cusip_ref using (dealname) where paid_down is null; +CREATE OR REPLACE VIEW dealfigimapping AS + select figi, dealname from deal_indicative join tranche_ref using (dealname) where paid_down is null; -GRANT ALL ON dealcusipmapping TO et_user; +GRANT ALL ON dealfigimapping TO et_user; CREATE VIEW latest_markit_prices AS SELECT b.* @@ -480,7 +481,7 @@ CREATE OR REPLACE FUNCTION et_latestdealinfo (p_dealname varchar(10)) CREATE OR REPLACE FUNCTION et_aggdealinfo (p_dealname varchar(10)) RETURNS TABLE(issuername text, currentbalance float, maturity date, fixedorfloat intex_fixedorfloat, assettype text, price float, grosscoupon float, spread float, - frequency varchar(1), nextpaydate date, loanxid varchar(10), cusip varchar(9), + frequency varchar(1), nextpaydate date, loanxid varchar(10), figi varchar(12), covlite boolean, secondlien boolean, iscdo boolean, defaultedflag boolean) AS $$ BEGIN RETURN QUERY SELECT a.issuername, sum(a.currentbalance) AS @@ -488,14 +489,14 @@ CREATE OR REPLACE FUNCTION et_aggdealinfo (p_dealname varchar(10)) avg(coalesce((b.bid+b.offer)/2, c.price, a.userprice, a.intexprice)) AS price, avg(a.grosscoupon) AS grosscoupon, avg(a.spread) AS spread, a.frequency, min(a.nextpaydate) AS nextpaydate, - a.loanxid, a.cusip, (max(a.covlite)='Cov_Lite' or (max(a.covlite)='COV_LITE' or + a.loanxid, a.figi, (max(a.covlite)='Cov_Lite' or (max(a.covlite)='COV_LITE' or bool_or(c.covlite))) AS covlite, (bool_or(a.secondlien) OR bool_or(c.secondlien)) AS secondlien, bool_or(a.iscdo) AS iscdo, bool_or(a.defaultedflag) AS defaultedflag FROM et_latestdealinfo(p_dealname) a LEFT JOIN latest_markit_prices b ON coalesce(a.ET_LoanXID, a.loanxid) = b.loanxid LEFT JOIN latest_bloomberg_corp c ON coalesce(a.cusip, a.ET_cusip)=c.cusip WHERE a.ReinvFlag IS NOT TRUE GROUP BY a.issuername, a.maturity, a.loanxid, a.fixedorfloat, - a.frequency, a.cusip ORDER BY issuername; + a.frequency, a.figi ORDER BY issuername; END; $$ LANGUAGE plpgsql; @@ -560,7 +561,7 @@ CREATE OR REPLACE FUNCTION et_aggdealinfo_historical (p_dealname varchar(10), p_ RETURNS TABLE(issuername text, currentbalance float, maturity date, fixedorfloat intex_fixedorfloat, assettype text, price float, grosscoupon float, spread float, frequency varchar(1), nextpaydate date, loanxid varchar(10), - cusip varchar(9), industry text, covlite boolean, secondlien boolean, iscdo boolean, defaultedflag boolean, + figi varchar(12), industry text, covlite boolean, secondlien boolean, iscdo boolean, defaultedflag boolean, et_loanxid varchar(10), et_cusip varchar(9)) AS $$ BEGIN RETURN QUERY SELECT a.issuername, sum(a.currentbalance) AS @@ -568,7 +569,7 @@ CREATE OR REPLACE FUNCTION et_aggdealinfo_historical (p_dealname varchar(10), p_ avg(coalesce((b.bid+b.offer)/2, c.price, a.userprice, a.intexprice)) AS price, avg(a.grosscoupon) AS grosscoupon, avg(a.spread) AS spread, a.frequency, min(a.nextpaydate) AS nextpaydate, - a.loanxid, a.cusip, max(a.industry), (max(a.covlite)='Cov_Lite' or (max(a.covlite)='COV_LITE' or + a.loanxid, a.figi, max(a.industry), (max(a.covlite)='Cov_Lite' or (max(a.covlite)='COV_LITE' or bool_or(c.covlite))) AS covlite, (bool_or(a.secondlien) OR bool_or(c.secondlien)) AS secondlien, bool_or(a.iscdo) AS iscdo, bool_or(a.defaultedflag) AS defaultedflag, a.et_loanxid, a.et_cusip @@ -577,14 +578,14 @@ CREATE OR REPLACE FUNCTION et_aggdealinfo_historical (p_dealname varchar(10), p_ LEFT JOIN historical_bloomberg_corp(p_date) c ON coalesce(a.cusip, a.ET_cusip)=c.cusip WHERE a.ReinvFlag IS NOT TRUE GROUP BY a.issuername, a.maturity, a.loanxid, a.fixedorfloat, - a.frequency, a.cusip, a.et_loanxid, a.et_cusip ORDER BY issuername; + a.frequency, a.figi, a.et_loanxid, a.et_cusip ORDER BY issuername; END; $$ LANGUAGE plpgsql; ALTER FUNCTION et_aggdealinfo_historical(varchar(10), date) OWNER TO et_user; -CREATE OR REPLACE FUNCTION et_cusip_details(p_cusips VARIADIC varchar(10)[]) +CREATE OR REPLACE FUNCTION et_figi_details(p_figis VARIADIC varchar(12)[]) RETURNS TABLE(orig_moody text, curr_moody text, empty1 text, issuer text, manager text, empty2 text, spread float, coupon float, issuedate date, reinvenddate date, maturity date, stale_percentage float, @@ -599,20 +600,20 @@ CREATE OR REPLACE FUNCTION et_cusip_details(p_cusips VARIADIC varchar(10)[]) e.dealspread5y, e.dealspread, a.curr_attach/100, (a.curr_detach-a.curr_attach)/100, d.mvoc, d.mvcoverage, NULL, d.updatedate, d.delta, d.duration, d.wal, d.price - FROM latest_cusip_universe a LEFT JOIN latest_clo_universe b + FROM latest_tranche_universe a LEFT JOIN latest_clo_universe b ON a.dealname = b.dealname LEFT JOIN latest_deal_model_numbers e ON a.dealname = e.dealname - RIGHT JOIN (SELECT unnest($1) AS cusip, generate_subscripts(p_cusips, 1) AS id) c - ON c.cusip = a.cusip - LEFT JOIN latest_cusip_model_numbers d ON d.cusip = c.cusip ORDER BY c.id; + RIGHT JOIN (SELECT unnest($1) AS figi, generate_subscripts(p_figis, 1) AS id) c + ON c.figi = a.figi + LEFT JOIN latest_tranche_model_numbers d ON d.figi = c.figi ORDER BY c.id; END; $$ LANGUAGE plpgsql; -ALTER FUNCTION et_cusip_details(varchar(10)[]) +ALTER FUNCTION et_tranche_details(varchar(12)[]) OWNER TO et_user; -CREATE OR REPLACE FUNCTION historical_cusip_details(p_date date, p_cusips VARIADIC varchar(10)[]) +CREATE OR REPLACE FUNCTION historical_tranche_details(p_date date, p_figis VARIADIC varchar(12)[]) RETURNS TABLE(orig_moody text, curr_moody text, issuer text, manager text, spread float, coupon float, issuedate date, reinvenddate date, maturity date, stale_percentage float, @@ -621,57 +622,58 @@ CREATE OR REPLACE FUNCTION historical_cusip_details(p_date date, p_cusips VARIAD mvoc float, mvcoverage float, pricingdate date, delta float, duration float, wal float, price float) AS $$ BEGIN - RETURN QUERY SELECT cusip_ref.orig_moody, a.curr_moody, "Deal Name", deal_indicative.manager, - cusip_ref.spread/100, a.coupon/100, deal_issue_date, reinv_end_date, deal_indicative.maturity, + RETURN QUERY SELECT a.orig_moody, a.curr_moody, "Deal Name", deal_indicative.manager, + a.spread/100, a.coupon/100, deal_issue_date, d.reinv_end_date, d.maturity, stalepercentage, cdopercentage, wapbasis, dealspread5y, dealspread, curr_attach/100, (curr_detach-curr_attach)/100, b.mvoc, b.mvcoverage, b.updatedate, b.delta, b.duration, b.wal, b.price - FROM cusip_ref + FROM (SELECT DISTINCT ON (figi) * FROM tranche_ref LEFT JOIN tranche_update USING (id) + WHERE updatedate<=p_date ORDER by figi, updatedate DESC) a LEFT JOIN - (SELECT DISTINCT ON (cusip) * FROM cusip_update WHERE updatedate<=p_date ORDER by cusip, updatedate DESC) a - USING (cusip) - LEFT JOIN - (SELECT DISTINCT ON (cusip) * FROM et_cusip_model_numbers WHERE updatedate<=p_date - ORDER by cusip, updatedate DESC) b - USING (cusip) - LEFT JOIN (SELECT DISTINCT ON (dealname) * FROM et_deal_model_numbers WHERE updatedate<=p_date - ORDER by dealname, updatedate DESC) c - USING (dealname) - LEFT JOIN deal_indicative USING (dealname) - RIGHT JOIN (SELECT unnest(p_cusips) AS cusip, generate_subscripts(p_cusips, 1) AS id) d USING (cusip) ORDER BY d.id; + (SELECT DISTINCT ON (figi) * FROM et_tranche_model_numbers WHERE updatedate<=p_date + ORDER by figi, updatedate DESC) b + USING (figi) + LEFT JOIN ( + SELECT DISTINCT ON (dealname) * FROM et_deal_model_numbers WHERE updatedate<=p_date + ORDER by dealname, updatedate DESC) c USING (dealname) + LEFT JOIN deal_indicative USING (dealname) + LEFT JOIN ( + SELECT DISTINCT ON (dealname) * FROM clo_universe WHERE "Latest Update" <= p_date + ORDER by dealname, "Latest Update" DESC) d USING (dealname) + RIGHT JOIN (SELECT unnest(p_figis) AS figi, generate_subscripts(p_figis, 1) AS id) e USING (figi) ORDER BY e.id; END; $$ LANGUAGE plpgsql; -ALTER FUNCTION historical_cusip_details(varchar(10)[]) +ALTER FUNCTION historical_tranche_details(varchar(12)[]) OWNER TO et_user; -CREATE OR REPLACE FUNCTION historical_cusip_risk(p_date date, p_cusips VARIADIC varchar(10)[]) +CREATE OR REPLACE FUNCTION historical_tranche_risk(p_date date, p_figis VARIADIC varchar(12)[]) RETURNS TABLE(duration float, delta float, price float, pricingdate date) AS $$ BEGIN RETURN QUERY SELECT b.duration, b.delta, b.price, b.updatedate FROM - (SELECT unnest(p_cusips) AS cusip, generate_subscripts(p_cusips, 1) AS id) a - LEFT JOIN (SELECT DISTINCT ON (cusip) * FROM et_cusip_model_numbers WHERE updatedate<=p_date - ORDER BY cusip, updatedate DESC) - AS b USING (cusip) ORDER BY id; + (SELECT unnest(p_figis) AS figi, generate_subscripts(p_figis, 1) AS id) a + LEFT JOIN (SELECT DISTINCT ON (figi) * FROM et_tranche_model_numbers WHERE updatedate<=p_date + ORDER BY figi, updatedate DESC) + AS b USING (figi) ORDER BY id; END - $$ LANGUAGE plpgsql; +$$ LANGUAGE plpgsql; -ALTER FUNCTION historical_cusip_risk(date, varchar(10)[]) +ALTER FUNCTION historical_tranche_risk(date, varchar(12)[]) OWNER TO et_user; -CREATE OR REPLACE FUNCTION dealname_from_cusip(p_cusips VARIADIC varchar(10)[]) +CREATE OR REPLACE FUNCTION dealname_from_figi(p_figis VARIADIC varchar(12)[]) -- NOT USED anymore, wasn't working very well RETURNS TABLE(p_dealname varchar(10)) AS $$ BEGIN - RETURN QUERY SELECT dealname FROM dealcusipmapping a - RIGHT JOIN (SELECT unnest($1) AS cusip, generate_subscripts(p_cusips, 1) AS id) b - USING (cusip) ORDER BY b.id; + RETURN QUERY SELECT dealname FROM dealfigimapping a + RIGHT JOIN (SELECT unnest($1) AS figi, generate_subscripts(p_cusips, 1) AS id) b + USING (figi) ORDER BY b.id; END; $$ LANGUAGE plpgsql; -ALTER FUNCTION dealname_from_cusip(varchar(10)[]) +ALTER FUNCTION dealname_from_figi(varchar(12)[]) OWNER TO et_user; CREATE OR REPLACE FUNCTION et_deal_subordination(p_dealname varchar(10)) @@ -759,8 +761,8 @@ CREATE TABLE et_deal_model_numbers( PRIMARY KEY(dealname, updatedate) ); -CREATE TABLE et_cusip_model_numbers( - Cusip varchar(9), +CREATE TABLE et_tranche_model_numbers( + figi varchar(12), price float, wal float, duration float, @@ -768,11 +770,11 @@ CREATE TABLE et_cusip_model_numbers( updatedate date, mvoc float, mvcoverage float, - PRIMARY KEY(Cusip, updatedate) + PRIMARY KEY(figi, updatedate) ); GRANT ALL ON et_deal_model_numbers TO et_user; -GRANT ALL ON et_cusip_model_numbers TO et_user; +GRANT ALL ON et_tranche_model_numbers TO et_user; CREATE VIEW latest_deal_model_numbers AS @@ -783,32 +785,32 @@ CREATE VIEW latest_deal_model_numbers AS GRANT ALL ON latest_deal_model_numbers TO et_user; -CREATE VIEW latest_cusip_model_numbers AS +CREATE VIEW latest_tranche_model_numbers AS SELECT b.* - FROM (SELECT MAX(updatedate) AS latestdate, cusip FROM et_cusip_model_numbers GROUP BY cusip) a - JOIN et_cusip_model_numbers b ON a.cusip = b.cusip AND a.latestdate = b.updatedate - ORDER by cusip asc; + FROM (SELECT MAX(updatedate) AS latestdate, figi FROM et_tranche_model_numbers GROUP BY figi) a + JOIN et_tranche_model_numbers b ON a.figi = b.figi AND a.latestdate = b.updatedate + ORDER by figi asc; -GRANT ALL ON latest_cusip_model_numbers TO et_user; +GRANT ALL ON latest_tranche_model_numbers TO et_user; CREATE TABLE color( ListDate date, ListInfo text, - Cusip varchar(9), + figi varchar(12), Notional float, Indications text, Cover text, ListColor text, Bid text, Bid_note text, - PRIMARY KEY(Cusip, Notional, ListDate, ListInfo) + PRIMARY KEY(figi, Notional, ListDate, ListInfo) ); GRANT ALL ON color TO et_user; CREATE VIEW latest_color AS SELECT b.* - FROM (SELECT MAX(ListDate) AS latestdate, cusip FROM color GROUP BY cusip) a - JOIN color b ON a.cusip = b.cusip AND a.latestdate=b.listdate; + FROM (SELECT MAX(ListDate) AS latestdate, cusip FROM color GROUP BY figi) a + JOIN color b ON a.figi = b.figi AND a.latestdate=b.listdate; GRANT ALL ON latest_color TO et_user; |