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.