COVAR_SAMP

1
SELECT
2
  stock_a,
3
  stock_b,
4
  ROUND(COVAR_SAMP(return_a, return_b), 6) as covariance
5
FROM (
6
  SELECT
7
    a.ticker as stock_a,
8
    b.ticker as stock_b,
9
    a.daily_return as return_a,
10
    b.daily_return as return_b
11
  FROM stock_returns a
12
  JOIN stock_returns b ON a.date = b.date
13
  WHERE a.ticker = 'AAPL' AND b.ticker = 'MSFT'
14
);

1
SELECT
2
  COVAR_SAMP(y, x) as covariance,
3
  STDDEV_SAMP(y) as stddev_y,
4
  STDDEV_SAMP(x) as stddev_x,
5
  CORR(y, x) as correlation,
6
  COVAR_SAMP(y, x) / (STDDEV_SAMP(y) * STDDEV_SAMP(x)) as corr_from_cov
7
FROM sample_data;