Count Users Retained on Day Seven

You have these tables:

Table	Column	Meaning
users	user_id	User identifier.
users	signup_date	The user's signup date or timestamp.
users	plan_type	The user's plan type.
events	user_id	User associated with an event.
events	event_ts	Event timestamp.

Write one read-only PostgreSQL query to count distinct users with at least one event on the calendar date seven days after their signup calendar date. Match events to users by user_id.

Compare the date components of event_ts and signup_date: time of day does not change whether an event is on day seven. This asks about the seventh calendar day, rather than any event during the first seven days. Count each qualifying user once even if that user has several events on that date.

Return exactly one row with one column named d7_retained_users. If nobody qualifies, the count is zero. Output row order is unrestricted.

Count Users Retained on Day Seven

Table users

column	type	constraints
user_id	INTEGER
signup_date	TIMESTAMP
plan_type	TEXT

Sample data:

plan_type	signup_date	user_id
free	2026-09-20 15:30:00	1
pro	2026-09-20 18:00:00	2
enterprise	2026-09-20 06:00:00	3
free	2025-12-25 23:45:00	4
trial	2024-02-22 13:00:00	5
NULL	2026-09-20 09:00:00	6
pro	NULL	7
free	2026-09-20 08:00:00	8

Table events

column	type	constraints
user_id	INTEGER
event_ts	TIMESTAMP

Sample data:

event_ts	user_id
2026-09-27 00:10:00	1
2026-09-27 23:59:00	1
2026-09-26 15:30:00	1
2026-09-26 18:00:00	2
2026-09-28 18:00:00	2
2026-09-20 06:00:00	3
2026-01-01 00:15:00	4
2024-02-29 00:00:00	5
2026-09-27 08:00:00	7
NULL	8
2026-09-27 12:00:00	99
2026-09-27 12:00:00	NULL

Expected output

d7_retained_users
3