-- -*- mode: sql; sql-product: postgres; -*- CREATE TYPE bond_strat AS ENUM('M_STR_MAV', 'M_STR_SMEZZ', 'CSO_TRANCH', 'M_CLO_BB20', 'M_CLO_AAA', 'M_CLO_BBB', 'M_MTG_IO', 'M_MTG_THRU', 'M_MTG_GOOD', 'M_MTG_B4PR', 'M_MTG_RW'); CREATE TYPE cds_strat AS ENUM('HEDGE_CSO', 'HEDGE_CLO', 'HEDGE_MAC', 'HEDGE_MBS', 'SER_IGSNR', 'SER_IGMEZ', 'SER_IGEQY', 'SER_IGINX', 'SER_HYSNR', 'SER_HYMEZ', 'SER_HYEQY', 'SER_HYINX', 'SER_IGCURVE', 'MBSCDS'); CREATE TYPE asset_class AS ENUM('CSO', 'Subprime', 'CLO', 'Tranches', 'Futures', 'Cash', 'FX', 'Cleared'); CREATE TYPE action AS ENUM('NEW', 'UPDATE', 'CANCEL'); CREATE TYPE currency AS ENUM('USD', 'CAD', 'EUR', 'YEN'); CREATE TYPE bbg_type AS ENUM('Mtge', 'Corp'); CREATE type day_count AS ENUM('ACT/360', 'ACT/ACT', '30/360', 'ACT/365'); CREATE type bus_day_convention AS ENUM('Modified Following', 'Following', 'Modified Preceding', 'Preceding', 'Second-Day-After', 'End-of-Month'); CREATE type index_type AS ENUM('IG', 'HY', 'EU', 'LCDX', 'XO'); CREATE TYPE tenor AS ENUM('6mo', '1yr', '2yr', '3yr', '4yr', '5yr', '7yr', '10yr'); CREATE TYPE swap_type AS ENUM('CD_INDEX', 'CD_INDEX_TRANCHE', 'CD_BASKET_TRANCHE', 'ABS_CDS'); CREATE type isda AS ENUM('ISDA2014', 'ISDA2003Cred'); CREATE type protection AS ENUM('Buyer', 'Seller'); CREATE TABLE counterparties(code varchar(12) primary key, name text, city text, state varchar(2), location text, dtc_number integer, sales_contact text, sales_email text, sales_phone text, valuation_contact1 text, valuation_email1 text, valuation_contact2 text, valuation_email2 text, valuation_contact3 text, valuation_email3 text, valuation_contact4 text, valuation_email4 text, notes text, instructions text); CREATE INDEX ON counterparties(name); CREATE TABLE bonds(id serial primary key, dealid varchar(28), lastupdate timestamp DEFAULT now(), action action, folder bond_strat NOT NULL, custodian varchar(12) NOT NULL, cashaccount varchar(10) NOT NULL, cp_code varchar(12) NOT NULL REFERENCES counterparties(code), trade_date date NOT NULL, settle_date date NOT NULL, cusip varchar(9), isin varchar(12), identifier varchar(12), description varchar(32) NOT NULL, faceamount float NOT NULL, price float NOT NULL, accrued float NOT NULL, asset_class asset_class, ticket text, principal_payment float, accrued_payment float, CONSTRAINT bonds2_check CHECK (cusip IS NOT NULL OR isin IS NOT NULL)); CREATE OR REPLACE FUNCTION mydealid() RETURNS TRIGGER AS $$ BEGIN UPDATE bonds SET dealid = 'SC_' ||upper(left(asset_class::text,3))||id, identifier = COALESCE(identifier, cusip, isin) WHERE bonds.id = NEW.id; RETURN NEW; END; $$ language plpgsql; CREATE TRIGGER dealid AFTER INSERT ON bonds FOR EACH ROW EXECUTE PROCEDURE mydealid(); CREATE TABLE cds(id serial primary key, dealid varchar(28), lastupdate timestamp DEFAULT now(), action action, folder cds_strat NOT NULL, custodian varchar(12) NOT NULL, cashaccount varchar(10) NOT NULL, cp_code varchar(12) NOT NULL REFERENCES counterparties(code), trade_date date NOT NULL, effective_date date NOT NULL, maturity date NOT NULL, currency currency NOT NULL, payment_rolldate bus_day_convention NOT NULL, notional float NOT NULL, fixed_rate float NOT NULL, day_count day_count NOT NULL, frequency smallint NOT NULL, protection protection NOT NULL, security_id varchar(12) NOT NULL, security_desc varchar(32) NOT NULL, upfront float NOT NULL, upfront_settle_date date NOT NULL, swap_type swap_type NOT NULL, attach smallint, detach smallint, clearing_facility varchar(12), isda_definition isda, CONSTRAINT tranche_check CHECK (swap_type != 'CD_INDEX_TRANCHE' OR (attach IS NOT NULL AND detach IS NOT NULL))); ALTER TABLE cds OWNER TO dawn_user; CREATE OR REPLACE FUNCTION auto_cds_dealid() RETURNS TRIGGER AS $$ BEGIN UPDATE cds SET dealid = 'SCCDS' ||id WHERE cds.id = NEW.id AND dealid is Null; RETURN NEW; END; $$ language plpgsql; CREATE TRIGGER cds_dealid AFTER INSERT ON cds FOR EACH ROW EXECUTE PROCEDURE mydealid(); CREATE TABLE securities(identifier varchar(12) PRIMARY KEY, cusip varchar(9), isin varchar(12), description varchar(32), face_amount float, maturity date, floater boolean, spread float, coupon float, frequency smallint, day_count day_count, first_coupon_date date, pay_delay smallint, currency currency default 'USD', bbg_type bbg_type default 'Mtge', asset_class asset_class, paid_down date default 'Infinity', start_accrued_date date); ALTER TABLE securities OWNER TO dawn_user; CREATE TABLE marks(date date, identifier varchar(12) REFERENCES securities(identifier) ON DELETE CASCADE, price float, PRIMARY KEY(identifier, date)); CREATE TABLE cashflow_history( identifier varchar(12) REFERENCES securities, date date, principal_bal float, principal float, interest float, coupon float, PRIMARY KEY (identifier, date)); CREATE TABLE risk_numbers( identifier varchar(12) REFERENCES securities, date date, delta float, index_delta index_type, duration float, wal float, undiscounted_price float, model_price float, PRIMARY KEY (identifier, date)); CREATE TABLE fx(date date PRIMARY KEY, eurusd float, cadusd float); CREATE OR REPLACE function list_marks(p_date date, include_unsettled boolean DEFAULT False) RETURNS TABLE(identifier varchar(12), price float) AS $$ BEGIN RETURN QUERY SELECT a.identifier, b.price FROM list_positions(p_date, Null, include_unsettled) a LEFT JOIN (SELECT DISTINCT ON (identifier) date, marks.identifier, marks.price FROM marks WHERE date<= p_date ORDER BY identifier, date DESC) b USING (identifier); END; $$ LANGUAGE plpgsql; CREATE OR REPLACE function list_marks_var(p_date date, VARIADIC p_identifier varchar(12)[]) RETURNS TABLE(identifier varchar(12), price float) AS $$ BEGIN RETURN QUERY SELECT a.identifier, b.price FROM (SELECT unnest(p_identifier) AS identifier) a LEFT JOIN (SELECT DISTINCT ON (identifier) date, marks.identifier, marks.price FROM marks WHERE date<= p_date ORDER BY identifier, date DESC) b USING (identifier); END; $$ LANGUAGE plpgsql; CREATE OR REPLACE function list_risk_numbers(p_date date, assetclass asset_class, include_unsettled boolean DEFAULT False) RETURNS TABLE(identifier varchar(12), description varchar(32), mark float, delta float, index_delta index_type, duration float, wal float, undiscounted_price float, model_price float) AS $$ BEGIN RETURN QUERY SELECT a.identifier, a.description, b.price, c.delta, c.index_delta, c.duration, c.wal, c.undiscounted_price, c.model_price FROM list_positions(p_date, assetclass, include_unsettled) a LEFT JOIN (SELECT DISTINCT ON (identifier) date, marks.identifier, marks.price FROM marks WHERE date<= p_date ORDER BY identifier, date DESC) b USING (identifier) LEFT JOIN (SELECT DISTINCT ON (identifier) * FROM risk_numbers WHERE date<=p_date ORDER BY identifier, date DESC) c USING (identifier); END; $$ LANGUAGE plpgsql; CREATE OR REPLACE function list_positions(p_date date, p_class asset_class DEFAULT NULL, include_unsettled boolean DEFAULT True) RETURNS TABLE(identifier varchar(12), description varchar(32), notional float, strategy bond_strat, curr_cpn float, start_accrued_date date, last_settle_date date, principal_payment float, accrued_payment float, currency currency, daycount day_count, bbg_type bbg_type) AS $$ DECLARE sqlquery text; DECLARE asset_opt text; DECLARE unsettled_opt text; BEGIN IF p_class is not NULL THEN asset_opt := 'and securities.asset_class=$2 '; ELSE asset_opt := ''; END IF; IF include_unsettled THEN unsettled_opt = 'or settle_date>=$1'; ELSE unsettled_opt = ''; END IF; sqlquery := 'WITH temp AS (SELECT bonds.identifier, asset_class, settle_date, folder, principal_payment, accrued_payment, sum(faceamount*(2*buysell::int-1) ) OVER (PARTITION BY bonds.identifier) notional FROM bonds WHERE trade_date<=$1) SELECT DISTINCT ON (temp.identifier) temp.identifier, securities.description, notional, folder, securities.coupon, start_accrued_date, settle_date, temp.principal_payment, temp.accrued_payment, securities.currency, securities.day_count, securities.bbg_type FROM temp LEFT JOIN securities USING (identifier) WHERE (temp.notional>0 '||unsettled_opt||') AND paid_down>$1 '||asset_opt ||' ORDER BY identifier, settle_date desc'; RETURN QUERY EXECUTE sqlquery USING p_date, p_class; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE function risk_positions(p_date date, p_assetclass asset_class) RETURNS TABLE (description varchar(32), identifier varchar(12), notional float, price float, strategy bond_strat, factor float, local_market_value float, usd_market_value float, curr_cpn float, int_acc float, last_pay_date date, principal_payment float, accrued_payment float, last_settle_date date) AS $$ BEGIN RETURN QUERY SELECT a.description, a.identifier, a.notional, c.price, a.strategy, coalesce(b.factor, 1), c.price/100. * a.notional * (CASE WHEN coalesce(b.factor,1)=0 THEN 1 ELSE coalesce(b.factor,1) END), c.price/100. * a.notional * (CASE WHEN coalesce(b.factor,1)=0 THEN 1 ELSE coalesce(b.factor,1) END) * fxrate, b.coupon, a.notional * coalesce(b.factor,1) * fxrate * yearfrac(case WHEN start_accrued_date>=p_date+1 THEN b.prev_cpn_date ELSE start_accrued_date END, p_date+1, daycount) * b.coupon/100., b.last_pay_date, a.principal_payment, a.accrued_payment, a.last_settle_date FROM list_positions(p_date, p_assetclass) a LEFT JOIN factors_history(p_date) b USING (identifier) LEFT JOIN list_marks(p_date, True) c USING (identifier) LEFT JOIN fx_rate(p_date) USING (currency) ORDER by identifier asc; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE VIEW orig_cds AS SELECT DISTINCT ON (dealid) dealid, folder, index, series, version, tenor, fixed_rate, notional, upfront, protection, attach, detach, trade_date, upfront_settle_date FROM cds JOIN index_desc ON index_desc.redindexcode=cds.security_id AND index_desc.maturity=cds.maturity ORDER BY dealid, trade_date; CREATE OR REPLACE function query_positions(p_type text DEFAULT NULL) RETURNS text AS $$ DECLARE query text; BEGIN query := 'WITH tmp AS (SELECT cds.security_id, cds.security_desc, cds.maturity, cds.fixed_rate, %s SUM(cds.notional * (CASE WHEN cds.protection=''Buyer'' THEN -1 ELSE 1 END)) OVER (PARTITION BY cds.security_id, cds.maturity, cds.attach, cds.detach) AS notional FROM cds WHERE cds.trade_date <=$1 %s) SELECT DISTINCT ON (tmp.security_id, tmp.maturity %s) * FROM tmp WHERE tmp.notional!=0'; IF p_type = 'tranche' THEN RETURN format(query, 'cds.attach, cds.detach,', 'AND cds.attach is NOT NULL', ',tmp.attach'); ELSIF p_type = 'cds' THEN RETURN format(query, '', 'AND cds.attach is NULL AND cds.folder!=''MBSCDS''', ''); ELSIF p_type = 'abs' THEN RETURN format(query, '', 'AND cds.folder=''MBSCDS''', ''); ELSE RETURN format(query, 'cds.attach, cds.detach,', '', ',tmp.attach'); END IF; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE function list_cds_positions (p_date date) RETURNS TABLE(security_id varchar(12), security_desc varchar(32), maturity date, fixed_rate float, notional float) AS $$ BEGIN RETURN QUERY EXECUTE query_positions('cds') USING p_date; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE function list_tranche_positions(p_date date) RETURNS TABLE(security_id varchar(12), security_desc varchar(32), maturity date, fixed_rate float, attach smallint, detach smallint, notional float) AS $$ BEGIN RETURN QUERY EXECUTE query_positions('tranche') USING p_date; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE function list_abscds_positions(p_date date) RETURNS TABLE(security_id varchar(12), security_desc varchar(32), maturity date, fixed_rate float, notional float) AS $$ BEGIN RETURN QUERY EXECUTE query_positions('abs') USING p_date; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE function list_cds_marks(p_date date) RETURNS TABLE(security_id varchar(12), security_desc varchar(32), maturity date, notional float, factor float, coupon float, duration float, clean_nav float, accrued float) AS $$ DECLARE days integer; BEGIN days:=days_accrued(p_date); RETURN QUERY WITH temp AS (SELECT a.*, c.index, c.series, c.version, d.tenor, c.indexfactor/100. AS fact FROM list_cds_positions(p_date) a LEFT JOIN index_version c ON a.security_id=c.redindexcode LEFT JOIN index_desc d USING (redindexcode, maturity)), index_price AS (SELECT index, series, version, tenor, closeprice, index_quotes.duration FROM index_quotes WHERE date=p_date) SELECT temp.security_id, temp.security_desc, temp.maturity, temp.notional, temp.fact, temp.fixed_rate/100, index_price.duration, (index_price.closeprice/100.-1)*temp.notional*temp.fact, temp.notional*temp.fixed_rate/100.*temp.fact*days/360 FROM temp LEFT JOIN index_price USING (index, series, version, tenor); END $$ LANGUAGE plpgsql; CREATE OR REPLACE function list_tranche_marks(p_date date) RETURNS TABLE(security_id varchar(12), security_desc varchar(32), maturity date, notional float, factor float, coupon integer, clean_nav float, accrued float) AS $$ DECLARE days integer; BEGIN days:=days_accrued(p_date); RETURN QUERY WITH temp AS (SELECT a.*, c.index, c.series, c.version, d.basketid, d.tenor, tranche_factor(a.attach, a.detach, c.indexfactor, c.cumulativeloss) AS fact FROM list_tranche_positions(p_date) a LEFT JOIN index_version c ON a.security_id=c.redindexcode LEFT JOIN index_desc d USING (redindexcode, maturity)), tranche_price AS (SELECT DISTINCT ON (basketid, tenor, attach, detach) basketid, tenor, attach, detach, upfront_mid, tranche_spread FROM markit_tranche_quotes WHERE quotedate<=p_date ORDER by basketid, tenor, attach, detach, quotedate desc) SELECT temp.security_id, temp.security_desc, temp.maturity, temp.notional, temp.fact, tranche_spread::integer, -upfront_mid*temp.notional*temp.fact, temp.notional*tranche_spread/10000.*temp.fact*days/360 FROM temp LEFT JOIN tranche_price USING (basketid, attach, detach, tenor); END $$ LANGUAGE plpgsql; CREATE OR REPLACE function list_abscds_marks(p_date date) RETURNS TABLE(security_id varchar(12), cusip varchar(9), security_desc varchar(32), maturity date, notional float, factor float, fixed_rate float, clean_nav float, accrued float) AS $$ BEGIN RETURN QUERY WITH temp AS (SELECT a.*, b.price, c.factor, d.start_accrued_date, d.cusip FROM list_abscds_positions(p_date) a LEFT JOIN (SELECT DISTINCT ON (identifier) date, marks.identifier, marks.price FROM marks WHERE date<=p_date ORDER BY identifier, date desc) b ON a.security_id=b.identifier LEFT JOIN factors_history(p_date) c ON a.security_id=c.identifier LEFT JOIN securities d ON a.security_id=d.identifier) SELECT temp.security_id, temp.cusip, temp.security_desc, temp.maturity, temp.notional, temp.factor, temp.fixed_rate, temp.notional*temp.factor*(temp.price-100)/100, -yearfrac(temp.start_accrued_date, p_date+1, 'ACT/360')*temp.fixed_rate/100*temp.notional*temp.factor FROM temp; END $$ LANGUAGE plpgsql; CREATE OR REPLACE function days_accrued(p_date date) RETURNS integer AS $$ FROM dates import days_accrued RETURN days_accrued(p_date) $$ LANGUAGE plpython2u; CREATE MATERIALIZED VIEW factors_history AS WITH temp AS ( SELECT c.date, c.identifier, c.principal, c.principal_bal, c.interest, lead(c.coupon) OVER w AS coupon, (- c.principal) - c.principal_bal + lag(c.principal_bal) OVER w AS losses FROM cashflow_history c WINDOW w AS (PARTITION BY c.identifier ORDER BY c.date) ) SELECT temp.date AS last_pay_date, temp.date-securities.pay_delay AS prev_cpn_date, temp.identifier, temp.principal_bal / securities.face_amount AS factor, temp.principal / securities.face_amount * 100::float AS principal, temp.interest / securities.face_amount * 100::float AS interest, temp.losses / securities.face_amount * 100::float AS losses, COALESCE(temp.coupon, securities.coupon) AS coupon FROM temp JOIN securities USING (identifier); CREATE UNIQUE INDEX factors_history_pkey ON factors_history(prev_cpn_date, identifier); CREATE OR REPLACE function factors_history(p_date date) RETURNS SETOF factors_history AS $$ BEGIN RETURN QUERY SELECT DISTINCT ON (identifier) * FROM factors_history WHERE prev_cpn_date<=p_date ORDER BY identifier, prev_cpn_date desc; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION isleapyear ( D date ) RETURNS boolean AS $$ DECLARE y INTEGER; BEGIN y := extract (year from D); if (y % 4) != 0 then return false; end if; if (y % 400) = 0 then return true; end if; return (( y % 100) != 0); END; $$ LANGUAGE 'plpgsql'; CREATE OR REPLACE FUNCTION yearfrac(date1 date, date2 date, daycount day_count) RETURNS float AS $$ DECLARE factor float; y1 integer; y2 integer; m1 integer; m2 integer; d1 integer; d2 integer; BEGIN IF daycount='30/360' THEN y1 := extract(YEAR FROM date1); y2 := extract(YEAR FROM date2); m1 := extract(MONTH FROM date1); m2 := extract(MONTH FROM date2); d1 := extract(DAY FROM date1); d2 := extract(DAY FROM date2); IF d2=31 and (d1=30 or d1=31) THEN d2:=30; END IF; IF d1=31 THEN d1:=30; END IF; factor:= (360*(y2-y1) + 30*(m2-m1)+d2-d1)/360.; ELSIF daycount='ACT/365' THEN factor:=(date2-date1)/365.; ELSIF daycount='ACT/360' THEN factor:=(date2-date1)/360.; ELSIF daycount='ACT/ACT' THEN IF isleapyear(date1) THEN factor:=(date2-date1)/366.; ELSE factor:=(date2-date1)/365.; END IF; END IF; RETURN factor; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE function tranche_factor(attach smallint, detach smallint, index_factor float, cumulativeloss float) RETURNS float AS $$ DECLARE newattach float; newdetach float; BEGIN newattach:=LEAST(GREATEST((attach-100*cumulativeloss)/index_factor, 0), 1); newdetach:=LEAST(GREATEST((detach-100*cumulativeloss)/index_factor, 0), 1); RETURN (newdetach-newattach)/(detach-attach)*index_factor; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE function fx_rate(p_date date) RETURNS TABLE(currency currency, fxrate float) AS $$ BEGIN RETURN QUERY SELECT unnest(Array['USD', 'EUR', 'CAD'])::currency, unnest(Array[1, eurusd, cadusd]) FROM fx WHERE date<=p_date ORDER by date desc LIMIT 3; END; $$ LANGUAGE plpgsql; -- Not sure how to map enums so use text for now CREATE FOREIGN TABLE priced( cusip varchar(9), model_version smallint, normalization text, timestamp timestamp, pv float, pv_RnW float, pv_io float, pv_po float, modDur float, modDur_io float, modDur_po float, wal float, wal_width float, wal_io float, wal_po float, delta_hpi float, delta_ir float, delta_ir_io float, delta_ir_po float, delta_mult float, delta_yield float, delta_quantile float, delta_RnW float, tot_gamma_hpi float, tot_gamma_ir float, tot_gamma float) SERVER mysql_server OPTIONS (dbname 'rmbs_model'); -- We want to use INHERITS here, but will only be available on 9.5 CREATE FOREIGN TABLE priced_orig_ntl( cusip varchar(9), model_version smallint, normalization text, timestamp timestamp, pv float, pv_RnW float, pv_io float, pv_po float, modDur float, modDur_io float, modDur_po float, wal float, wal_width float, wal_io float, wal_po float, delta_hpi float, delta_ir float, delta_ir_io float, delta_ir_po float, delta_mult float, delta_yield float, delta_quantile float, delta_RnW float, tot_gamma_hpi float, tot_gamma_ir float, tot_gamma float) SERVER mysql_server OPTIONS (dbname 'rmbs_model'); CREATE FOREIGN TABLE priced_percentiles( cusip varchar(9), model_version smallint, percentile float, normalization text, timestamp timestamp, pv float, pv_io float, pv_po float, modDur float, modDur_io float, modDur_po float, wal float, wal_width float, wal_io float, wal_po float) SERVER mysql_server OPTIONS (dbname 'rmbs_model'); CREATE FOREIGN TABLE priced_percentiles_orig_ntl( cusip varchar(9), model_version smallint, percentile float, normalization text, timestamp timestamp, pv float, pv_io float, pv_po float, modDur float, modDur_io float, modDur_po float, wal float, wal_width float, wal_io float, wal_po float) SERVER mysql_server OPTIONS (dbname 'rmbs_model'); CREATE FOREIGN TABLE index_desc( basketid integer, index text, series smallint, version smallint, indexfactor float, cumulativeloss float, lastdate date, redindexcode text, tenor tenor, maturity date, coupon integer) SERVER postgresql_server; CREATE FOREIGN TABLE index_version( basketID serial, Index index_type, Series smallint, Version smallint, IndexFactor float, CumulativeLoss float, lastdate date, redindexcode text) SERVER postgresql_server; CREATE FOREIGN TABLE index_maturity( index index_type, series smallint, tenor tenor, maturity date) SERVER postgresql_server; CREATE FOREIGN TABLE index_quotes( date date, index index_type, series smallint, version smallint, tenor tenor, closeprice float, closespread float, modelprice float, modelspread float, adjcloseprice float, adjmodelprice float, duration float, theta float) SERVER postgresql_server; CREATE FOREIGN TABLE markit_tranche_quotes( quotedate date, basketid integer, tenor tenor, attach smallint, detach smallint, upfront_bid float, upfront_mid float, upfront_ask float, tranche_spread smallint, index_price float) SERVER postgresql_server; -- #bonds that get written down -- update securities set identifier='073879R75_A' where identifier='073879R75'; -- update bonds set identifier='073879R75_A' where identifier='073879R75'; -- refresh materialized view factors_history; CREATE OR REPLACE function list_subprime_data(p_date date, VARIADIC p_cusip varchar(9)[]) RETURNS TABLE(v1 float, v2 float, v3 float, duration float, percentile5 float, percentile25 float, percentile50 float, percentile75 float, percentile95 float, yield_delta float, wal float, io_pv float, po_pv float, rnw float, ir_io_delta float, ir_po_delta float, hpi_delta float) AS $$ BEGIN RETURN QUERY WITH left_table AS ( WITH temp AS (SELECT * from priced where date(timestamp)=p_date) SELECT a.cusip, a.pv as v1, b.pv as v2, c.pv AS v3, a.modDur, c.delta_yield, c.wal, c.pv_io, c.pv_po, c.pv_RnW, c.delta_ir_io, c.delta_ir_po, c.delta_hpi FROM (SELECT * FROM temp WHERE model_version=1) a, (SELECT * FROM temp WHERE model_version=2) b, (SELECT * FROM temp WHERE model_version=3) c WHERE a.cusip = b.cusip AND a.cusip=c.cusip), right_table AS ( WITH temp AS( SELECT cusip, PV, percentile FROM priced_percentiles WHERE timestamp BETWEEN p_date AND p_date + INTERVAL '1 day' AND model_version=3 AND percentile in (5, 25, 50, 75, 95) AND normalization ='current_notional') SELECT a.cusip, a.PV AS pv5, b.PV AS pv25, c.PV AS pv50, d.PV AS pv75, e.PV AS pv95 FROM (SELECT cusip, PV FROM temp WHERE percentile=5) a, (SELECT cusip, PV FROM temp WHERE percentile=25) b, (SELECT cusip, PV FROM temp WHERE percentile=50) c, (SELECT cusip, PV FROM temp WHERE percentile=75) d, (SELECT cusip, PV FROM temp WHERE percentile=95) e WHERE a.cusip=b.cusip AND b.cusip=c.cusip and c.cusip=d.cusip and d.cusip=e.cusip and e.cusip=a.cusip) SELECT left_table.v1, left_table.v2, left_table.v3, left_table.moddur, right_table.pv5, right_table.pv25, right_table.pv50, right_table.pv75, right_table.pv95, left_table.delta_yield, left_table.wal, left_table.pv_io, left_table.pv_po, left_table.pv_RnW, left_table.delta_ir_io, left_table.delta_ir_po, left_table.delta_hpi FROM left_table, right_table, (SELECT unnest(p_cusip) AS cusip) l WHERE left_table.cusip=right_table.cusip AND left_table.cusip=l.cusip;