diff options
Diffstat (limited to 'sql/serenitasdb.sql')
| -rw-r--r-- | sql/serenitasdb.sql | 227 |
1 files changed, 227 insertions, 0 deletions
diff --git a/sql/serenitasdb.sql b/sql/serenitasdb.sql new file mode 100644 index 00000000..fbc6641c --- /dev/null +++ b/sql/serenitasdb.sql @@ -0,0 +1,227 @@ +CREATE TABLE tranche_data (
+ QuoteDate date,
+ TrancheId integer,
+ BasketId integer,
+ Maturity date,
+ Tenor varchar(4),
+ RefBasketPrice float,
+ BPS varchar(1),
+ BasketDuration float,
+ TQC varchar(1),
+ TrancheDuration float,
+ TrancheDelta float,
+ CorrAtDetachment float,
+ Basis float,
+ QuoteSource varchar(2),
+ Index varchar(4),
+ Series smallint,
+ Upfront float,
+ Running float,
+ Attach smallint,
+ Detach smallint,
+ IndexFactor float,
+ CumulativeLoss float
+);
+
+GRANT ALL ON tranche_data TO serenitas_user;
+
+CREATE TABLE index_version(
+ BasketID serial,
+ Index index_type,
+ Series smallint,
+ Version smallint,
+ IndexFactor float,
+ CumulativeLoss float,
+ PRIMARY KEY(BasketID)
+);
+
+CREATE TABLE index_maturity(
+ Index index_type,
+ series smallint,
+ tenor tenor,
+ maturity date,
+ PRIMARY KEY(index, series, tenor)
+);
+
+CREATE OR REPLACE VIEW index_desc AS
+ SELECT b.basketid, b.index, b.series, b.version, a.tenor, a.maturity, b.indexfactor, b.cumulativeloss, b.lastdate
+ FROM index_maturity a
+ JOIN index_version b ON a.index = b.index AND a.series = b.series;
+
+GRANT ALL ON index_maturity TO serenitas_user;
+GRANT ALL ON index_version TO serenitas_user;
+GRANT ALL ON index_desc TO serenitas_user;
+
+
+CREATE TABLE quotes (
+ QuoteDate timestamp,
+ Index index_type,
+ Series smallint,
+ Version smallint,
+ tenor tenor,
+ attach smallint,
+ detach smallint,
+ RefBasketPrice float,
+ Upfront float,
+ Running float,
+ BasketDuration float,
+ TrancheDuration float,
+ TrancheDelta float,
+ CorrAtDetachment float,
+ Basis float,
+ QuoteSource varchar(4)
+);
+
+CREATE TABLE tranche_quotes (
+ QuoteDate timestamp,
+ Index index_type,
+ Series smallint,
+ Version smallint,
+ Tenor tenor,
+ Attach smallint,
+ Detach smallint,
+ TrancheUpfrontBid float,
+ TrancheUpfrontMid float,
+ TrancheUpfrontAsk float,
+ TrancheRunningBid float,
+ TrancheRunningMid float,
+ TrancheRunningAsk float,
+ IndexRefPrice real,
+ IndexRefSpread smallint,
+ IndexDuration real,
+ TrancheDuration real,
+ TrancheDelta real,
+ CorrAtDetachment real,
+ Basis real,
+ QuoteSource varchar(4),
+ PRIMARY KEY(QuoteDate, Index, Series, Version, Tenor, Attach, Detach, QuoteSource)
+);
+
+GRANT ALL ON quotes TO serenitas_user;
+GRANT ALL ON tranche_quotes TO serenitas_user;
+
+-- Idiosyncracies:
+
+-- for Itraxx 9 index:
+
+-- tr.id coupon type
+-- 3-6 98155 all running
+-- 98154 U+500
+-- 6-9 98160 all running
+-- 166395 U+300
+-- 98159 U+500
+
+-- For IG 9 index:
+
+-- tr.id coupon type
+-- 3-7 162290 all running
+-- 162291 U+500
+-- 7-10 162298 all running
+-- 162300 U+500
+-- 10-15 162301 all running
+-- 162304 U+500
+-- 15-30 162305 all running
+-- 162306 U+500
+-- 30-100 162307 all running
+-- 162308 U+500
+
+-- All other tranches should have a unique trancheid for a given basketid, attach, detach combination
+
+CREATE TYPE curr AS ENUM('USD', 'EUR', 'JPY', 'GBP', 'CAD');
+CREATE TYPE sen AS ENUM('Senior', 'Subordinated');
+CREATE TYPE tier AS ENUM('SNRFOR', 'SECDOM', 'SUBLT2', 'PREFT1', 'JRSUBUT2');
+CREATE TYPE bbgSource AS ENUM('MSG1', 'CBIN', 'CBGN', 'MKIT');
+CREATE TYPE DocClause AS ENUM('No Restructuring', 'Modified Modified Restructurin', 'Full Restructuring');
+CREATE TYPE tenor AS ENUM('6mo', '1yr', '2yr', '3yr', '4yr', '5yr', '7yr', '10yr');
+
+CREATE TABLE CDS_Issuers(
+ Name text,
+ company_id integer,
+ ticker text,
+ currency curr,
+ seniority sen,
+ doc_clause DocClause,
+ cds_curve text[8],
+ index_list integer[],
+ markit_ticket text,
+ markit_tier tier,
+ PRIMARY KEY(company_id));
+
+GRANT ALL ON CDS_Issuers to serenitas_user;
+
+CREATE TABLE cds_quotes(
+ Date Date,
+ curve_ticker text,
+ UpfrontBid float,
+ UpfrontAsk float,
+ RunningBid float,
+ RunningAsk float,
+ Source bbgSource,
+ Recovery float,
+ PRIMARY KEY(curve_ticker, Date));
+
+GRANT ALL ON cds_quotes to serenitas_user;
+
+CREATE OR REPLACE FUNCTION nameToBasketID(index_name varchar(4), p_date date) RETURNS integer AS $$
+ DECLARE
+ p_index index_type;
+ p_series smallint;
+ p_basketid integer;
+ BEGIN
+ p_index := upper(left(index_name, 2))::index_type;
+ p_series := right(index_name, -2);
+ SELECT MIN(basketid) INTO p_basketid FROM index_version WHERE Index=p_index and
+ Series=p_series and lastdate>=p_date;
+ RETURN p_basketid;
+ END;
+ $$ LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION index_members(index_name varchar(4), p_date date)
+ RETURNS SETOF CDS_Issuers AS $$
+ DECLARE
+ basketid integer;
+ BEGIN
+ SELECT nameToBasketID(index_name, p_date) INTO basketid;
+ RETURN QUERY SELECT * FROM CDS_Issuers WHERE index_list @> Array[basketid];
+ END;
+ $$ LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION historical_cds_quotes(date) RETURNS SETOF cds_quotes AS $$
+ BEGIN
+ RETURN QUERY SELECT b.*
+ FROM (SELECT max(c.Date) AS latestdate, c.curve_ticker FROM cds_quotes c
+ WHERE c.Date<=$1 GROUP BY c.curve_ticker) a
+ JOIN cds_quotes b ON a.curve_ticker = b.curve_ticker AND a.latestdate=b.Date;
+ END;
+ -- simpler query but slower
+ -- BEGIN
+ -- RETURN QUERY SELECT DISTINCT ON (curve_ticker) * from cds_quotes where date<=$1 ORDER BY
+ -- curve_ticker, DATE desc;
+ -- END;
+ $$ LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION curve_quotes(varchar(4), date) RETURNS
+ TABLE(cds_ticker text, date date, spread_curve float[], upfront_curve float[], recovery_curve float[]) AS $$
+ BEGIN
+ RETURN QUERY SELECT max(markit_ticker) AS t, max(a.date),
+ array_agg((a.runningbid+a.runningask)/2 ORDER BY tenor),
+ array_agg((a.upfrontbid+a.upfrontask)/2 ORDER BY tenor),
+ array_agg(a.Recovery ORDER BY tenor) FROM historical_cds_quotes($2) a RIGHT JOIN
+ (SELECT generate_series(1, 8) AS tenor, unnest(cds_curve) AS curve_ticker, markit_ticker, company_id
+ FROM index_members($1, $2)) b
+ ON b.curve_ticker = a.curve_ticker GROUP by company_id ORDER BY t;
+ END;
+ $$ LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION curve_quotes_fmt(varchar(4), date) RETURNS
+ TABLE(cds_ticker text, date date, spread_curve text, upfront_curve text, recovery_curve text) AS $$
+ BEGIN
+ RETURN QUERY SELECT max(markit_ticker) AS t, max(a.date),
+ string_agg(to_char((a.runningbid+a.runningask)/2, 'FM999'), ',' ORDER BY tenor),
+ string_agg(to_char((a.upfrontbid+a.upfrontask)/2, 'FM99D99'), ',' ORDER BY tenor),
+ string_agg(to_char(a.Recovery, 'FM0D99'), ',' ORDER BY tenor) FROM historical_cds_quotes($2) a RIGHT JOIN
+ (SELECT generate_series(1, 8) AS tenor, unnest(cds_curve) AS curve_ticker, markit_ticker, company_id
+ FROM index_members($1, $2)) b
+ ON b.curve_ticker = a.curve_ticker GROUP by company_id ORDER BY t;
+ END;
+ $$ LANGUAGE plpgsql;
|