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.

Now You Try

Practice this concept

Growth wants each cohort period beside the immediately previous period from the same cohort month.

Available schema

growth

Prefix tables with growth.table_name.

cohort_monthperiod_numberprevious_retainedcurrent_retained

growth.cohort_retention

Column	Type
cohort_month	date
period_number	integer
users	integer
retained	integer
legacy_cohort_id	text

query.sql

Intermediate business practice