SQL JOINs on Multiple Keys

Require MORE than one column to match at once — genuinely useful when a single column alone isn't enough to guarantee a correct match.

What & Why

Some relationships are correct only when several conditions match together. Comparing growth.cohort_retention periods requires the same cohort_month and a period_number offset of one; either condition alone can pair unrelated rows.

See How It Works

BUSINESS QUESTION

Growth matches each cohort period to its immediately previous period using cohort month and period number together.

cohort_month	period_number	users	retained
2024-01-01	0	420	420
2024-01-01	1	420	268
2024-01-01	2	420	214
2024-02-01	0	510	510

EXAMPLE QUERY

SELECT
  current.cohort_month,
  current.period_number,
  previous.retained AS previous_retained,
  current.retained AS current_retained
FROM growth.cohort_retention current
JOIN growth.cohort_retention previous
  ON previous.cohort_month = current.cohort_month
 AND previous.period_number = current.period_number - 1
ORDER BY current.cohort_month, current.period_number;

RESULT — exact output from the displayed Queryflo rows

cohort_month	period_number	previous_retained	current_retained
2024-01-01	1	420	268
2024-01-01	2	268	214

Both cohort_month and the one-period offset must match.

Intermediate business practice