Compute early signals for user retention

You are given two tables that track signups and user transactions.

Tables

users

• user_id (STRING, PK) — unique user
• signup_ts (TIMESTAMP) — time the user signed up (assume UTC)

transactions

• transaction_id (STRING, PK)
• user_id (STRING, FK → users.user_id)
• transaction_ts (TIMESTAMP) — time of transaction (UTC)
• amount (NUMERIC) — transaction amount

Task

You want an early signal that predicts Day-30 retention.

1. Define an early engagement metric as the number of transactions a user makes in the first 7 days after signup:

• early_txn_cnt = count of transactions where transaction_ts >= signup_ts and transaction_ts < signup_ts + 7 days.

2. Define Day-30 retention as whether the user has at least one transaction in the window [signup + 30 days, signup + 37 days).

• is_retained_d30 ∈ {0,1}

3. Write a SQL query that outputs retention performance by early-engagement bucket:

• Bucket early_txn_cnt into: 0, 1, 2, 3+

Required output

Return one row per bucket with:

• early_txn_bucket
• users_in_bucket
• retained_users
• retention_rate (= retained_users / users_in_bucket, as a decimal)

Notes / assumptions

• Include users with zero transactions.
• If a user has transactions before signup_ts, ignore them.
• If multiple transactions occur in the retention window, the user still counts as retained once.

Early-activity signals and retention rate by bucket

You are given two tables: users and transactions.

Define an "early signal" of user engagement as the number of DISTINCT days a user made at least one transaction during their first 7 days after signup (inclusive): signup_date through signup_date + 6 days.

Define a user as "retained" if they made at least one transaction during days 8–30 after signup (inclusive): signup_date + 7 days through signup_date + 29 days.

Assume the "current date" is 2025-06-01. To ensure a full 30-day observation window, only include users with signup_date <= 2025-05-02.

Task:

1. Compute each included user's early distinct transaction days (0, 1, 2, ...).
2. Bucket users into:
   • '0' for 0 early active days
   • '1' for 1 early active day
   • '2+' for 2 or more early active days
3. For each bucket, output:
   • early_active_days_7d_bucket
   • users (number of users in the bucket)
   • retained_users (number of retained users)
   • retention_rate (retained_users / users as a decimal)

Return one row per bucket.

Table users

column	type	constraints
user_id	INT	PRIMARY KEY, NOT NULL
signup_date	DATE	NOT NULL
country	VARCHAR(2)	NOT NULL

Sample data:

country	signup_date	user_id
US	2025-04-01	1
US	2025-04-05	2
CA	2025-04-10	3
US	2025-04-15	4
GB	2025-05-01	5
US	2025-05-15	6

Table transactions

column	type	constraints
txn_id	INT	PRIMARY KEY, NOT NULL
user_id	INT	NOT NULL
txn_ts	TIMESTAMP	NOT NULL
amount	DECIMAL(10,2)	NOT NULL

Sample data:

amount	txn_id	txn_ts	user_id
20	101	2025-04-01 10:00:00	1
15	102	2025-04-03 12:00:00	1
30	103	2025-04-09 09:00:00	1
5	201	2025-04-05 08:00:00	2
7	202	2025-04-06 11:00:00	2
10	203	2025-04-07 16:30:00	2
12	301	2025-04-20 14:15:00	3
50	401	2025-04-16 09:00:00	4
25	402	2025-04-16 18:45:00	4
40	403	2025-05-10 10:00:00	4
8	501	2025-05-02 13:20:00	5
9	502	2025-05-07 19:05:00	5
11	503	2025-05-08 09:30:00	5
100	601	2025-05-15 08:00:00	6
60	602	2025-05-25 12:00:00	6

Expected output

One row per early-activity bucket showing number of users, number retained, and retention rate among users with signup_date <= 2025-05-02.

early_active_days_7d_bucket	retained_users	retention_rate	users
0	1	1	1
1	1	1	1
2+	2	0.6666666667	3

Hints

1. Use conditional aggregation to count distinct transaction dates in the first 7 days after signup.
2. Compute retention as an existence check (MAX of a 0/1 flag) over the day 8–30 window.
3. Filter to users with signup_date <= '2025-05-02' so every included user can have a full 30-day window before 2025-06-01.