diff options
Diffstat (limited to 'sql/test_queries.sql')
| -rw-r--r-- | sql/test_queries.sql | 103 |
1 files changed, 103 insertions, 0 deletions
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 |