SQL Covariance

Correlation's raw, unnormalized ancestor — same direction of relationship, but the number isn't bounded between -1 and 1.

What & Why

Covariance measures the same "do these move together" question as correlation, but without normalizing the result — the number's scale depends entirely on the units of the two variables, which makes it hard to compare across different pairs of variables. Correlation exists specifically to fix that by normalizing covariance into the -1-to-+1 range.

See How It Works

BUSINESS QUESTION

Measure sample covariance between email opens and clicks.

id	campaign_id	sent_at	recipients	opens	clicks	unsubscribes	bounces
201	1	2024-01-20 15:00:00+00	1200	540	180	9	24
202	2	2024-02-15 16:30:00+00	800	420	96	5	12
203	3	2024-03-18 13:00:00+00	1500	610	225	14	31
204	4	2024-04-08 14:00:00+00	0	0	0	0	0

EXAMPLE QUERY

SELECT
  COVAR_SAMP(opens, clicks) AS opens_clicks_covariance,
  COUNT(*) AS sends
FROM marketing.email_sends
WHERE opens IS NOT NULL
  AND clicks IS NOT NULL;

RESULT — opens and clicks covariance

opens_clicks_covariance	sends
26042.5	4

COVAR_SAMP uses the four representative opens/clicks pairs.

This lesson's practice is part of Pro.

See what's included →

Advanced business practice