Write SQL for cohort retention and ARPU

Using PostgreSQL, compute monthly signup cohort D30 retention and 30-day ARPU. Cohort month = date_trunc('month', signup_date). D30 retention = users with any event on the calendar day signup_date + 30 days (ignore events before signup_date as data errors). 30D ARPU = sum of payments with pay_ts < signup_date + interval '30 days' divided by cohort size. Deduplicate exact duplicate events by (user_id, event_ts, event_name). Schema:

users(user_id INT PRIMARY KEY, signup_date DATE, country TEXT)
events(user_id INT, event_ts DATE, event_name TEXT)
payments(user_id INT, pay_ts DATE, amount NUMERIC(10,2))
Sample data:
users
+---------+-------------+---------+
| user_id | signup_date | country |
+---------+-------------+---------+
| 1 | 2025-06-01 | US |
| 2 | 2025-06-15 | US |
| 3 | 2025-07-03 | CA |
| 4 | 2025-07-20 | US |
| 5 | 2025-07-31 | GB |
+---------+-------------+---------+
events
+---------+------------+------------+
| user_id | event_ts | event_name |
+---------+------------+------------+
| 1 | 2025-07-01 | login |
| 1 | 2025-07-01 | login | (duplicate)
| 2 | 2025-07-15 | purchase |
| 3 | 2025-08-02 | login |
| 4 | 2025-08-19 | browse |
| 5 | 2025-08-30 | login |
+---------+------------+------------+
payments
+---------+------------+--------+
| user_id | pay_ts | amount |
+---------+------------+--------+
| 1 | 2025-06-20 | 10.00 |
| 1 | 2025-07-10 | 20.00 |
| 2 | 2025-06-16 | 15.00 |
| 3 | 2025-07-20 | 5.00 |
| 5 | 2025-08-15 | 12.00 |
+---------+------------+--------+

Write a single SQL query returning: cohort_month, d30_retention_rate, arpu_30d. Explain how your query avoids duplicates and late events.

Monthly signup cohort D30 retention and 30-day ARPU (PostgreSQL)

Using PostgreSQL, compute monthly signup cohort D30 retention and 30-day ARPU.

Definitions:

• Cohort month = date_trunc('month', signup_date).
• D30 retention: a user is retained if they have ANY event on the calendar day (signup_date + 30 days). Ignore events before signup_date (treat them as data errors).
• Deduplicate events by exact duplicates on (user_id, event_ts, event_name) before using them for retention.
• 30-day ARPU: for each cohort, sum payments where pay_ts < (signup_date + interval '30 days') (also ignore payments before signup_date as data errors), then divide by cohort size.

Write a single SQL query that returns:

• cohort_month
• d30_retention_rate
• arpu_30d

Return one row per cohort_month.

Table users

column	type	constraints
user_id	INT	PRIMARY KEY, NOT NULL
signup_date	DATE	NOT NULL
country	TEXT	NOT NULL

Sample data:

country	signup_date	user_id
US	2025-06-01	1
US	2025-06-15	2
CA	2025-07-03	3
US	2025-07-20	4
GB	2025-07-31	5

Table events

column	type	constraints
user_id	INT	NOT NULL
event_ts	DATE	NOT NULL
event_name	TEXT	NOT NULL

Sample data:

event_name	event_ts	user_id
login	2025-07-01	1
login	2025-07-01	1
purchase	2025-07-15	2
login	2025-08-02	3
browse	2025-08-19	4
login	2025-08-30	5

Table payments

column	type	constraints
user_id	INT	NOT NULL
pay_ts	DATE	NOT NULL
amount	NUMERIC(10,2)	NOT NULL

Sample data:

amount	pay_ts	user_id
10	2025-06-20	1
20	2025-07-10	1
15	2025-06-16	2
5	2025-07-20	3
12	2025-08-15	5

Expected output

One row per signup cohort month, with D30 retention rate (retained users / cohort size) and 30-day ARPU (sum of qualifying payments / cohort size). cohort_month is returned as the first day of the month (DATE).

arpu_30d	cohort_month	d30_retention_rate
12.5	2025-06-01	1
5.67	2025-07-01	1

Hints

1. Use date_trunc('month', signup_date) to build cohorts, then aggregate per cohort_month.
2. Deduplicate events with SELECT DISTINCT on (user_id, event_ts, event_name) before computing retention.