Month-Over-Month Retention with LAG/LEAD

MediumSQL · PostgreSQL

Problem

Capgemini Invent wants to monitor month-over-month customer retention for its client portal. A retained customer is an eligible customer with portal activity in both the current month and the immediately preceding month.

Write a PostgreSQL query to calculate and compare monthly retention from January through April 2025.

Requirements

Include only active customers whose client account has portal_enabled = TRUE.
Count active customers for each calendar month, including months with no activity.
Use LAG to identify prior-month activity for each customer and to retrieve the previous month's active-customer count and retention rate.
Calculate retention as retained customers divided by the previous month's active customers, returning NULL when no prior month exists.
Return the monthly retention rate and its change from the previous month's rate, ordered chronologically.

Schema

customers

Column	Type	Description
customer_idPK	INT	Unique customer identifier
account_id	INT	Client account identifier
customer_name	VARCHAR(100)	Customer name
status	VARCHAR(20)	Customer lifecycle status

client_accounts

Column	Type	Description
account_idPK	INT	Unique client account identifier
account_name	VARCHAR(120)	Client account name
portal_enabled	BOOLEAN	Whether portal activity is eligible for retention reporting

portal_activity

Column	Type	Description
activity_idPK	INT	Unique activity event identifier
customer_id	INT	Customer who generated the activity
activity_date	DATE	Date of portal activity

Tablescustomersclient_accountsportal_activity