1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
|
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 mlpdb_user;
CREATE TABLE index_desc(
BasketID integer,
Index varchar(4),
Series smallint,
Maturity date,
Tenor varchar(4),
IndexFactor float,
CumulativeLoss float,
PRIMARY KEY(BasketID, Tenor)
);
GRANT ALL ON index_desc TO mlpdb_user;
CREATE TABLE tranche_desc(
TrancheID integer,
BasketID integer,
Tenor varchar(4),
Attach smallint,
Detach smallint,
Foreign Key(BasketID, Tenor) REFERENCES index_desc(BasketID, Tenor),
Primary Key(TrancheID, Tenor)
);
GRANT ALL ON tranche_desc TO mlpdb_user;
CREATE TABLE quotes (
QuoteDate date,
TrancheID integer,
BasketID integer,
RefBasketPrice float,
BasketDuration float,
TrancheDuration float,
TrancheDelta float,
CorrAtDetachment float,
Basis float,
QuoteSource varchar(4),
Upfront float,
Running float,
Tenor varchar(4),
FOREIGN KEY(BasketID, Tenor) REFERENCES index_desc(BasketID, Tenor),
FOREIGN KEY(TrancheID, Tenor) REFERENCES tranche_desc(TrancheID, Tenor)
);
GRANT ALL ON quotes TO mlpdb_user;
select * from quotes inner join (select tranche_desc.* from tranche_desc inner join (select * from index_desc where tenor='Y10' and series=9 and index='IG') b on tranche_desc.basketID = b.basketID
and tranche_desc.tenor = b.tenor where attach=7 and detach=10) a
on quotes.trancheID=a.trancheID and quotes.tenor = a.tenor order by quotedate desc;
|
