diff options
Diffstat (limited to 'sql')
| -rw-r--r-- | sql/et_tables.sql | 367 | ||||
| -rw-r--r-- | sql/test_queries.sql | 103 |
2 files changed, 470 insertions, 0 deletions
diff --git a/sql/et_tables.sql b/sql/et_tables.sql new file mode 100644 index 00000000..797468cf --- /dev/null +++ b/sql/et_tables.sql @@ -0,0 +1,367 @@ +CREATE TYPE intex_fixedorfloat AS ENUM('FIXED', 'FLOAT'); + +CREATE TABLE et_collateral ( + dealname varchar(8) NOT NULL, + updatedate date, + Name text, + IssuerName text, + CurrentBalance float, + Maturity date, + AssetSubtype varchar(10), + AssetType varchar(25), + GrossCoupon float default NULL, + Spread float default NULL, + Frequency varchar(1), + NextPaydate date, + SecondLien boolean, + LoanXID varchar(10), + ET_LoanXID varchar(10) default NULL, + Cusip varchar(9), + ET_Cusip varchar(10) default NULL, + IntexPrice float, + IntexPriceSource text, + IntexPriceDate date, + UserPrice float default NULL, + UserPriceDate date default NULL, + FixedOrFloat intex_fixedorfloat, + DefaultedFlag boolean, + CovLite text, + isCDO boolean, + PRIMARY KEY (updatedate, Name, dealname) +); +CREATE INDEX dealname_idx ON et_collateral(dealname, updatedate); + +ALTER TABLE et_collateral OWNER TO et_user; + +CREATE TABLE cusip_user_mapping ( + IssuerName text, + Maturity date, + GrossCoupon float default NULL, + Spread float default NULL, + LoanXID varchar(8) default NULL, + Cusip varchar(9) default NULL +); + +CREATE TABLE loanx_user_mapping ( + IssuerName text, + Maturity date, + Spread float default NULL, + LoanxID varchar(8) default NULL +); + +ALTER TABLE cusip_user_mapping OWNER TO et_user; +ALTER TABLE loanx_user_mapping OWNER TO et_user; + +CREATE TABLE markit_prices ( + LoanXID varchar(8), + Issuer text, + DealName text, + Facility text, + Industry text, + SP varchar(4), + Moodys varchar(4), + Amount float, + Maturity date, + Spread float, + Bid float, + Offer float, + Depth int, + STM float, + PricingDate date, + PRIMARY KEY (LoanXID, PricingDate) +); + +ALTER TABLE markit_prices OWNER TO et_user; + +CREATE TABLE markit_facility ( + LoanXID varchar(8), + PMDID float(10), + IssuerName varchar(100), + dealname varchar(60), + facility_type varchar(40), + loanx_facility_type varchar(50), + industry varchar(50), + initial_amount float(15), + initial_spread float(7), + maturity date, + created_time date, + modified_time date +); + + +CREATE TYPE bloomberg_source AS ENUM('TRAC', 'BGN', 'MSG1', 'BVAL', 'EXCH', 'BCMP', 'LCPR', 'BFV'); + +CREATE TYPE bloomberg_cpntype AS ENUM('FIXED', 'FLOATING', 'EXCHANGED', 'DEFAULTED', +'PAY-IN-KIND', 'VARIABLE', 'ZERO COUPON', 'STEP CPN', 'FLAT TRADING', 'NONE', 'FUNGED', +'STEP', 'ZERO'); + +CREATE TABLE bloomberg_corp ( + Cusip varchar(9), + Price float, + PricingDate date, + Issuer text, + Maturity date, + Coupon float, + CouponType bloomberg_cpntype, + Frequency int, + Spread float, + Libor_floor float, + loan_margin float, + covlite boolean, + secondlien boolean, + defaulted boolean, + Source bloomberg_source, + PRIMARY KEY (Cusip, PricingDate) +); + +GRANT ALL ON bloomberg_corp TO et_user; + +CREATE TABLE bloomberg_mtge ( + Cusip varchar(9), + Issuer text, + Maturity date, + Coupon float, + CouponType bloomberg_cpntype, + Frequency int, + Spread float, + Moody text, + InitialMoody text, + PRIMARY KEY (CUSIP) +); + +GRANT ALL ON bloomberg_mtge TO et_user; + +CREATE TABLE clo_universe ( + dealname varchar(10), + "Deal Name" text, + Manager text, + "Orig Deal Bal" float, + "Curr Deal Bal" float, + "Orig Collat Bal" float, + "Curr Collat Bal" float, + "Tranche Factor" float, + "Principal Bal" float, + "Interest Bal" float, + "CDO Percentage" float, + "Defaulted Bal" float, + "Curr Coupon" float, + "Deal Issue Date" date, + Maturity date, + "Deal Next Pay Date" date, + "Reinv End Date" date, + "Latest Update" date, + "Deal Cusip List" text, + PaidDown date, + PRIMARY KEY (dealname, "Latest Update") +); + +GRANT ALL ON clo_universe TO et_user; + +CREATE TABLE cusip_universe ( + Cusip varchar(9), + ISIN varchar(12), + dealname varchar(10), + tranche text, + Coupon float, + Orig_Balance float, + Curr_Balance float, + Factor float, + Orig_Moody text, + Curr_Moody text, + Orig_Attach float, + Orig_Detach float, + Curr_Attach float, + Curr_Detach float, + Floater_Index text, + Spread float, + PRIMARY KEY(Cusip) +); + +GRANT ALL ON cusip_universe TO et_user; + +CREATE VIEW latest_clo_universe AS + SELECT b.* + FROM (SELECT MAX("Latest Update") AS latestdate, dealname FROM clo_universe GROUP BY dealname) a + JOIN clo_universe b ON a.dealname = b.dealname AND a.latestdate= b."Latest Update" + ORDER by dealname asc; + +GRANT ALL ON latest_clo_universe TO et_user; + +CREATE TABLE dealcusipmapping ( + dealname varchar(10), + Cusip varchar(9), + PRIMARY KEY(Cusip) +); + +GRANT ALL ON dealcusipmapping TO et_user; + +CREATE VIEW latest_markit_prices AS + SELECT b.* + FROM (SELECT MAX(pricingdate) AS latestdate, loanxid FROM markit_prices GROUP BY loanxid) a + JOIN markit_prices b ON a.loanxid = b.loanxid AND a.latestdate= b.pricingdate; + +GRANT ALL ON latest_markit_prices TO et_user; + +CREATE OR REPLACE FUNCTION et_historical_collateral(p_date date) + RETURNS TABLE(dealname varchar(8), updatedate date, name text, issuername text, + currentbalance float, maturity date, assetsubtype varchar(10), assettype varchar(25), + grosscoupon float, spread float, frequency varchar(1), nextpaydate date, + secondlien boolean, loanxid varchar(10), et_loanxid varchar(10), cusip varchar(9), + et_cusip varchar(10), intexprice float, intexpricesource text, intexpricedate date, + userprice float, userparicedate date, fixedorfloat intex_fixedorfloat, + defaultedflag boolean, covlite text, iscdo boolean) AS $$ + DECLARE latestdate date; + BEGIN + RETURN QUERY SELECT a.* FROM (SELECT max(et_collateral.updatedate) AS latestdate,et_collateral.dealname + FROM et_collateral GROUP BY et_collateral.dealname) b + JOIN et_collateral a ON a.dealname=b.dealname AND a.updatedate=b.latestdate; + END; + $$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION best_price (price1 float, pricedt1 date, price2 float, pricedt2 date) + RETURNS float +AS $$ + if price1 is None: + return price2 + if price2 is None: + return price1 + return price1 if pricedt1> pricedt2 else price2 +$$ LANGUAGE plpythonu; + +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), + covlite boolean, secondlien boolean, iscdo boolean, defaultedflag boolean) AS $$ + BEGIN + RETURN QUERY SELECT a.issuername, sum(a.currentbalance) AS + currentbalance, a.maturity, a.fixedorfloat, max(a.assettype), + avg(coalesce((b.bid+b.offer)/2, c.price)) 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 + 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_collateral a + left join latest_markit_prices b on a.loanxid=b.loanxid left + join bloomberg_corp c on a.cusip=c.cusip where a.dealname = p_dealname + group by a.issuername, a.maturity, a.loanxid, a.fixedorfloat, + a.frequency, a.cusip ORDER BY issuername; + END; + $$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION et_latestdealinfo (p_dealname varchar(10), p_date date) + RETURNS TABLE(dealname varchar(8), updatedate date, name text, issuername text, + currentbalance float, maturity date, assetsubtype varchar(10), assettype varchar(25), + grosscoupon float, spread float, frequency varchar(1), nextpaydate date, + secondlien boolean, loanxid varchar(10), et_loanxid varchar(10), cusip varchar(9), + et_cusip varchar(10), intexprice float, intexpricesource text, intexpricedate date, + userprice float, userparicedate date, fixedorfloat intex_fixedorfloat, + defaultedflag boolean, covlite text, iscdo boolean) AS $$ + DECLARE latestdate date; + BEGIN + SELECT max(et_collateral.updatedate) INTO latestdate FROM et_collateral + WHERE et_collateral.dealname = p_dealname + AND et_collateral.updatedate <= p_date; + RETURN QUERY SELECT a.* FROM et_collateral a WHERE a.dealname = p_dealname AND + a.updatedate=latestdate ORDER BY lower(a.issuername); + END; + $$ LANGUAGE plpgsql; + + +CREATE OR REPLACE FUNCTION et_ReadMapped(p_issuername text, p_maturity date, p_spread float) + RETURNS TABLE(loanxid varchar(10), cusip varchar(9)) AS $$ + BEGIN + RETURN QUERY SELECT loanxid, cusip from et_user_mapping a where a.issuername = p_issuername and a.maturity = p_maturity and a.spread = p_spread; + END; + $$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION dealname_from_cusip(p_cusip varchar(9), OUT p_dealname varchar(8)) AS $$ + BEGIN + SELECT dealname INTO p_dealname from dealcusipmapping where cusip=p_cusip; + END; + $$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION historical_markit_prices(p_date date) + RETURNS TABLE(loanxid varchar(8), issuer text, dealname text, facility text, + industry text, sp varchar(4), moodys varchar(4), amount float, maturity date, + spread float, bid float, offer float, depth integer, stm float, pricingdate date) AS $$ + BEGIN + RETURN QUERY SELECT b.loanxid, b.issuer, b.dealname, b.facility, b.industry, b.sp, + b.moodys, b.amount, b.maturity, b.spread, b.bid, b.offer, b.depth, b.stm, + a.latestdate + FROM (SELECT MAX(c.pricingdate) AS latestdate, c.loanxid FROM markit_prices c + WHERE c.pricingdate<=p_date GROUP BY c.loanxid) a + JOIN markit_prices b ON a.loanxid = b.loanxid AND a.latestdate= b.pricingdate; + END; + $$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION historical_bloomberg_corp(p_date date) + RETURNS TABLE(cusip character varying, price double precision, pricingdate date, issuer text, maturity date, coupon double precision, coupontype bloomberg_cpntype, frequency integer, spread double precision, libor_floor double precision, loan_margin double precision, covlite boolean, secondlien boolean, defaulted boolean, source bloomberg_source) AS +$BODY$ + BEGIN + RETURN QUERY SELECT b.* FROM + (SELECT MAX(c.pricingdate) AS latestdate, c.cusip FROM bloomberg_corp c + WHERE c.pricingdate<=p_date GROUP BY c.cusip) a + JOIN bloomberg_corp b ON a.cusip = b.cusip AND a.latestdate= b.pricingdate; + END; + $BODY$ + LANGUAGE plpgsql; + +-- CREATE OR REPLACE FUNCTION historical_bloomberg_mtge(p_date date) +-- RETURNS TABLE(CUSIP varchar(9), updatedate date, issuer text, maturity date, +-- coupon float, coupontype bloomberg_cpntype, frequency integer, spread float, Moody text, +-- InitialMoody text) AS $$ +-- BEGIN +-- RETURN QUERY SELECT b.* FROM +-- (SELECT MAX(c.updatedate) AS updatedate, c.cusip FROM bloomberg_mtge c +-- WHERE c.updatedate<=p_date GROUP BY c.cusip) a +-- JOIN bloomberg_mtge b ON a.cusip = b.cusip AND a.updatedate= b.updatedate; +-- END; +-- $$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION et_aggdealinfo_historical (p_dealname varchar(10), p_date date) + 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), + 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 + currentbalance, a.maturity, a.fixedorfloat, max(a.assettype), + avg(coalesce((b.bid+b.offer)/2, c.price)) 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 + 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 from et_latestdealinfo(p_dealname, p_date) a + left join historical_markit_prices(p_date) b on a.loanxid=b.loanxid left + join historical_bloomberg_corp(p_date) c on a.cusip=c.cusip where a.dealname = p_dealname + group by a.issuername, a.maturity, a.loanxid, a.fixedorfloat, + a.frequency, a.cusip, 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)[]) + RETURNS TABLE(orig_moody text, curr_moody text, empty1 unknown, issuer text, + manager text, empty2 unknown, spread float, issuedate date, + reinvenddate date, maturity date, stale_percentage float, + cdo_percentage float, wap_basis float, portfolio_spread float, + subordination float, thickness float ) AS $$ + BEGIN + RETURN QUERY SELECT a.Orig_Moody, a.Curr_Moody, NULL, b."Deal Name", b.manager,NULL, + a.Spread/100, b."Deal Issue Date", b."Reinv End Date", b.Maturity, + CAST(NULL AS float), Cast(NULL AS float), CAST(NULL AS FLOAT), CAST(NULL AS float), + a.Curr_Attach/100, (a.Curr_Detach-a.Curr_Attach)/100 + FROM cusip_universe a LEFT JOIN latest_clo_universe b + ON a.dealname = b.dealname + JOIN (SELECT unnest($1) AS cusip, generate_subscripts(p_cusips, 1) AS id) c + ON a.cusip = c.cusip ORDER BY c.id; + END; + $$ LANGUAGE plpgsql; + +ALTER FUNCTION et_cusip_details(varchar(10)[]) + OWNER TO et_user; diff --git a/sql/test_queries.sql b/sql/test_queries.sql new file mode 100644 index 00000000..af656d5b --- /dev/null +++ b/sql/test_queries.sql @@ -0,0 +1,103 @@ +select sum(currentbalance* coalesce(b.bid,c.price))/sum(currentbalance) as wap, sum(currentbalance) as pricedbalance from et_collateral a + left join latest_markit_prices b on a.loanxid=b.loanxid + left join bloomberg_prices c on a.cusip=c.cusip + where a.dealname='octagon8' and coalesce(b.bid,c.price) is not Null; + +select issuername, sum(currentbalance) AS currentbalance, a.maturity, fixedorfloat, + avg(coalesce((b.bid+b.offer)/2, c.price)) as price, avg(grosscoupon) as grosscoupon, + avg(a.spread) as grossmargin, a.frequency, min(a.nextpaydate) AS nextpaydate, + a.loanxid, a.cusip, (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(defaultedflag) as defaultedflag + from et_collateral a left join latest_markit_prices b on a.loanxid=b.loanxid left join bloomberg_prices c + on a.cusip=c.cusip where a.dealname='babs062' +group by issuername, a.maturity, a.loanxid, fixedorfloat, a.frequency, a.cusip order by issuername; + +select issuername, sum(currentbalance) AS currentbalance, a.maturity, fixedorfloat, a.dealname, + avg(coalesce((b.bid+b.offer)/2, c.price)) as price, avg(grosscoupon) as grosscoupon, + avg(a.spread) as grossmargin, a.frequency, min(a.nextpaydate) AS nextpaydate, + a.loanxid, a.cusip, (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(defaultedflag) as defaultedflag + from et_collateral a left join latest_markit_prices b on a.loanxid=b.loanxid left join bloomberg_corp c + on a.cusip=c.cusip +group by issuername, a.dealname, a.maturity, a.loanxid, fixedorfloat, a.frequency, a.cusip order by dealname; + +select * from latest_markit_prices; +select * from bloomberg_prices where cusip='038521AG5'; +select distinct cusip from et_collateral; +select count(a.currentbalance) from et_collateral a where dealname='octagon8'; +select dealname from clo_universe; + +--query latest prices from markit_prices +select b.loanxid,b.issuer,b.dealname,b.facility,b.industry,b.sp, b.moodys,b.amount,b.maturity,b.spread,b.bid,b.offer,b.depth,b.stm,a.latestdate from (select max(pricingdate) as latestdate, loanxid from markit_prices group by loanxid) a join markit_prices b + on a.loanxid = b.loanxid and a.latestdate= b.pricingdate; + +select * from dealcusipmapping; + +select * from et_aggdealinfo('octagon8'); + +select * from et_ReadMapped('abcde',2016-4-12,3); + +insert into et_user_mapping (issuername, maturity, grosscoupon, spread, loanxid) VALUES ('Aramark','1/24/2014',2.34,1.88,'LX063469'); + +select * from cusip_user_mapping; + + +select * from et_collateral where; + +select * from latest_markit_prices where issuer like '%Acxiom%'; + +insert into loanx_user_mapping (issuername, maturity, spread, loanxid) VALUES ('RH Donnelley','10/24/2014','4.25','LX113368'); +insert into loanx_user_mapping (issuername, maturity, spread, loanxid) VALUES ('RH Donnelley','10/24/2014','5.25','LX213368'); +update loanx_user_mapping SET issuername = 'RH Donnelley', maturity = '10/24/2014', spread = '4.25', loanxid = 'LX113368' WHERE issuername = 'RH Donnelley' and maturity = '10/24/2014'and spread = '4.25'and loanxid = 'LX113368' + +select * from loanx_user_mapping; +delete from loanx_user_mapping; + +select distinct loanxid,cusip from et_collateral where loanxid is not NULL and cusip is not NULL; + +select * from bloomberg_corp; + +select * from et_latestdealinfo('abrlf','8/24/2012'); + +UPDATE et_collateral a SET et_loanxid = 'LX050789' WHERE a.dealname = 'abrlf' and a.name = 'AVIO S.p.A - Term Loan B2' and a.maturity = '2014-12-13'; +select * from et_collateral where dealname = 'abrlf' and et_loanxid= 'LX050789'; +select * from et_collateral where dealname = 'abrlf'; + +Drop function et_aggdealinfo_historical (varchar(10), date); +select count(*) from loanx_user_mapping a where a.issuername = 'RH Donnelley' and a.maturity = '10/24/2014' and a.spread = '4.25' +count(loanxid) from loanx_user_mapping a where a.issuername = 'RH Donnelley' and a.maturity = '10/24/2014' and a.spread = '4.25' + +CREATE OR REPLACE FUNCTION et_aggdealinfo_historical (p_dealname varchar(10), p_date date) + 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), + 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 + currentbalance, a.maturity, a.fixedorfloat, max(a.assettype), + avg(coalesce((b.bid+b.offer)/2, c.price)) 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 + 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 from et_latestdealinfo(p_dealname, p_date) a + left join historical_markit_prices(p_date) b on b.loanxid=Coalesce(a.loanxid,a.et_loanxid) left + join historical_bloomberg_corp(p_date) c on a.cusip=c.cusip where a.dealname = p_dealname + group by a.issuername, a.maturity, a.loanxid, a.fixedorfloat, + a.frequency, a.cusip, a.et_loanxid, a.et_cusip ORDER BY issuername; + END; + $$ LANGUAGE plpgsql; + +select * from et_aggdealinfo_historical('abrlf', '8/27/2012'); + +CREATE TABLE loanx_user_mapping ( + IssuerName text, + Maturity date, + Spread float default NULL, + LoanxID varchar(8) default NULL +); + +Drop TABLE loanx_user_mapping;
\ No newline at end of file |