diff options
| -rw-r--r-- | sql/dawn.sql | 18 |
1 files changed, 11 insertions, 7 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index b55e0258..ad72c3eb 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -840,6 +840,7 @@ CREATE TABLE cashflow_history( principal float, interest float, coupon float, + factor float, PRIMARY KEY (identifier, date)); CREATE TABLE risk_numbers( @@ -1921,20 +1922,23 @@ CREATE MATERIALIZED VIEW factors_history AS 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 + (- c.principal) - c.principal_bal + lag(c.principal_bal) OVER w AS losses, + c.principal_bal / NULLIF(c.factor, 0.) AS face_amount, + c.factor 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, + temp.factor, + temp.principal / COALESCE(temp.face_amount, securities.face_amount) * 100::float AS principal, + temp.interest / COALESCE(temp.face_amount, securities.face_amount) * 100::float AS interest, + temp.losses / COALESCE(temp.face_amount, securities.face_amount) * 100::float AS losses, COALESCE(temp.coupon, securities.coupon) AS coupon - FROM temp - JOIN securities ON temp.identifier=securities.figi; + FROM temp + JOIN securities ON temp.identifier=securities.figi +; CREATE UNIQUE INDEX factors_history_pkey ON factors_history(prev_cpn_date, identifier); |