Month-Over-Month Active User Retention

HardSQL · PostgreSQL

Problem

Write a query to find the active user retention rate month-over-month, utilizing complex multi-table joins.

Use the provided user, session, subscription, plan, and region data. Treat a user as active in a month when they have a valid session, an active account, and an active subscription plan during that month.

Output

One row per calendar month with activity data.
Columns: month_start, active_users, previous_month_active_users, retained_users, and retention_rate_pct.
Include months from the first through the last session month, including months with no activity. Order by month_start ascending.

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
region_id	INT	User's assigned region
is_active	BOOLEAN	Whether the user account is active

user_sessions

Column	Type	Description
session_idPK	INT	Unique session identifier
user_id	INT	User who created the session
session_started_at	TIMESTAMP	Session start timestamp
session_status	BOOLEAN	Whether the session is valid

subscriptions

Column	Type	Description
subscription_idPK	INT	Unique subscription identifier
user_id	INT	Subscribed user
plan_id	INT	Subscribed plan
started_at	TIMESTAMP	Subscription start timestamp
ended_at	TIMESTAMP	Subscription end timestamp

plans

Column	Type	Description
plan_idPK	INT	Unique plan identifier
is_active	BOOLEAN	Whether the plan is currently active

regions

Column	Type	Description
region_idPK	INT	Unique region identifier
region_code	VARCHAR(10)	Short region code

Tablesusersuser_sessionssubscriptionsplansregions