SQL SELF JOIN

Join a table to itself with two aliases so rows in that table can be compared with other rows from the same source.

What & Why

A self join treats one table as two logical inputs. Queryflo aliases saas.mrr_movements as current and previous so each movement can be compared with earlier movements for the same account.

See How It Works

BUSINESS QUESTION

Finance compares each MRR movement to another movement for the same account.

id	account_id	movement_type	amount	occurred_at
21	1	new	200.00	2024-01-08 10:00:00+00
22	1	expansion	40.00	2024-03-12 09:00:00+00
23	2	new	90.00	2024-02-12 09:30:00+00
24	2	churn	-90.00	2024-05-18 12:00:00+00

Trace the relationship row by row1× speed

saas.mrr_movements · current

id	account_id	movement_type	amount	occurred_at
21	1	new	200.00	2024-01-08 10:00:00+00
22	1	expansion	40.00	2024-03-12 09:00:00+00
23	2	new	90.00	2024-02-12 09:30:00+00
24	2	churn	-90.00	2024-05-18 12:00:00+00

NO EARLIER ROW

saas.mrr_movements · previous

id	account_id	movement_type	amount	occurred_at
21	1	new	200.00	2024-01-08 10:00:00+00
22	1	expansion	40.00	2024-03-12 09:00:00+00
23	2	new	90.00	2024-02-12 09:30:00+00
24	2	churn	-90.00	2024-05-18 12:00:00+00

Result set · 1 rows

account_id	occurred_at	previous_movement_at
1	2024-01-08 10:00:00+00	NULL

Movement 21 has no earlier movement for account 1, so previous_movement_at is NULL.

EXAMPLE QUERY

SELECT
  current.account_id,
  current.occurred_at,
  previous.occurred_at AS previous_movement_at
FROM saas.mrr_movements current
LEFT JOIN saas.mrr_movements previous
  ON previous.account_id = current.account_id
 AND previous.occurred_at < current.occurred_at
ORDER BY current.account_id, current.occurred_at;

Now You Try

Practice this concept

Finance compares each MRR movement with earlier movements for the same account.

Available schema

saas

Prefix tables with saas.table_name.

account_idoccurred_atprevious_movement_at

saas.mrr_movements

Column	Type
id	integer
account_id	integer
movement_type	text
amount	numeric
occurred_at	timestamp with time zone
legacy_movement_code	text

query.sql

Intermediate business practice