SQL Correlation

Does one variable tend to move with another? A single number from -1 to +1 answers that.

What & Why

Correlation measures whether two variables move together. +1 means they move in perfect lockstep upward, -1 means one rises exactly as the other falls, and 0 means no linear relationship at all.

See How It Works

BUSINESS QUESTION

Measure the relationship between email recipients 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
  CORR(recipients, clicks) AS recipients_clicks_correlation,
  COUNT(*) AS sends
FROM marketing.email_sends
WHERE recipients IS NOT NULL
  AND clicks IS NOT NULL;

RESULT — recipients and clicks

recipients_clicks_correlation	sends
0.9926923075388216	4

The coefficient is calculated from the four representative email-send pairs.

This lesson's practice is part of Pro.

See what's included →

Advanced business practice