hist_qry8.sql

TRADING_SYMBOL    TRADING_SYMBOL    CORRELATION
AAI               AAH                 -4.29E+09
AAI               AAC                 -3.65E+09
AAF               AAH                 -3.04E+09
AAI               AAB                 -2.92E+09
AAF               AAC                 -2.58E+09
AAD               AAH                 -2.57E+09
AAG               AAH                 -2.56E+09
AAD               AAC                 -2.24E+09
AAG               AAC                 -2.22E+09
AAF               AAB                 -2.07E+09
AAJ               AAH                 -1.80E+09
AAA               AAH                 -1.79E+09
AAD               AAB                 -1.72E+09
AAG               AAB                 -1.71E+09
AAE               AAH                 -1.68E+09
AAJ               AAC                 -1.67E+09
AAE               AAC                 -1.63E+09
AAA               AAC                 -1.62E+09
AAI               AAE                 -1.29E+09
AAA               AAB                 -1.21E+09
AAJ               AAB                 -1.15E+09
AAE               AAB                 -1.11E+09
AAF               AAE                 -9.21E+08
AAI               AAJ                 -8.34E+08
AAG               AAE                 -6.35E+08
...

-- Find the pair-wise coefficients of correlation in a set of 10 securities
-- for a 2 year period. Sort the securities by the coefficient of correlation,
-- indicating the pair of securities corresponding to that row.

commit
;

SELECT a.TRADING_SYMBOL,b.TRADING_SYMBOL, 
(Count(*) * sum(a.CLOSE_PRICE * b.CLOSE_PRICE) - sum(a.CLOSE_PRICE)
* sum(b.CLOSE_PRICE)/sqrt(count(*) * sum(a.CLOSE_PRICE * a.CLOSE_PRICE )
- (sum(a.CLOSE_PRICE) * sum(a.CLOSE_PRICE)))
* sqrt(count(*) * sum(b.CLOSE_PRICE * b.CLOSE_PRICE )
- (sum(b.CLOSE_PRICE) * sum(b.CLOSE_PRICE)))) as CORRELATION
from (Select TRADING_SYMBOL, TRADE_DATE,CLOSE_PRICE 
from 
STOCK_HISTORY AS B 
WHERE B.TRADING_SYMBOL BETWEEN 'AAA' AND 'AAJ'
AND LENGTH(B.TRADING_SYMBOL) = 3
and B.TRADE_DATE BETWEEN '2005-02-08'
and '2007-02-07' 
) a,
(Select TRADING_SYMBOL, TRADE_DATE,CLOSE_PRICE 
from 
STOCK_HISTORY AS B 
WHERE B.TRADING_SYMBOL BETWEEN 'AAA' AND 'AAJ'
AND LENGTH(B.TRADING_SYMBOL) = 3
and B.TRADE_DATE BETWEEN '2005-02-08'
and '2007-02-07'   ) b
where a.TRADE_DATE = b.TRADE_DATE
group by a.TRADING_SYMBOL, b.TRADING_SYMBOL
order by correlation
;