Katkat
Back

Define and Analyze Early Engagement

ChimeData Scientist · Technical Screen
MediumData ScientistTechnical ScreenData Manipulation (SQL/Python)

Last updated

Define an early user activation metric for a fintech platform, write a SQL query to measure monthly cohort activation rates across acquisition channels, and account for lifecycle maturity bias.

Scenario

In consumer digital banking, a user who signs up but never funds or uses their account provides little long-term value. Product teams frequently track an early engagement (activation) metric to evaluate onboarding health and acquisition channel quality.

Define an "activated user" as a user who completes at least 3 successful debit transactions totaling at least $50.00 within their first 30 days of registration (inclusive of day 0 through day 30).

Database Schema

users

ColumnTypeDescription
user_idINTPrimary key
signup_dateDATEDate the user opened their account
acquisition_channelVARCHARMarketing channel (e.g., 'organic', 'paid_search', 'referral')

transactions

ColumnTypeDescription
transaction_idINTPrimary key
user_idINTForeign key to users.user_id
transaction_dateDATEDate of the transaction
transaction_typeVARCHARType: 'debit', 'deposit', 'transfer'
statusVARCHARStatus: 'completed', 'pending', 'declined', 'reversed'
amountDECIMAL(10, 2)Transaction amount in USD (positive value)

Task

Write a SQL query that produces a monthly cohort report showing activation performance by acquisition channel.

Your query should return:

  1. cohort_month: First day of the signup month formatted as 'YYYY-MM-01'.
  2. acquisition_channel: The acquisition channel name.
  3. total_users: Total number of users registered in that cohort and channel.
  4. activated_users: Total number of users who met the activation criteria within 30 days of signup.
  5. activation_rate_pct: Percentage of users activated, rounded to 2 decimal places (activated_users * 100.0 / total_users).

Constraints & Edge Cases

  • Only consider transactions where status = 'completed' and transaction_type = 'debit'.
  • Transactions must occur between signup_date and signup_date + INTERVAL '30 days' (inclusive).
  • Exclude users who signed up within the last 30 days relative to CURRENT_DATE to prevent maturity bias (incomplete observation window).
  • Order results by cohort_month ASC, then acquisition_channel ASC.

Example Output

cohort_month | acquisition_channel | total_users | activated_users | activation_rate_pct
-------------+---------------------+-------------+-----------------+--------------------
2023-01-01   | organic             |        1200 |             480 |               40.00
2023-01-01   | paid_search         |        2500 |             750 |               30.00
2023-01-01   | referral            |         800 |             440 |               55.00
2023-02-01   | organic             |        1350 |             567 |               42.00

Hints

Hint 1

Filter out users whose signup date is within 30 days of CURRENT_DATE early to prevent immature cohorts from skewing activation rates downwards.

Hint 2

Join transactions on both user_id and a date condition that restricts transaction_date to between signup_date and signup_date + INTERVAL '30 days'.

Hint 3

Use a subquery or CTE to aggregate user-level transaction count and spend before joining back to the cohort table, or use conditional aggregation with user-level grouping.

Follow-up questions

  • How would you handle or model users who signed up fewer than 30 days ago if product managers need real-time, weekly visibility without waiting a full month?
  • If referral cohorts show a 55% activation rate compared to 30% for paid search, how would you design an experiment or observational study to verify whether referrals causally drive higher retention versus self-selection?
  • How would you optimize this query to run efficiently if the transactions table has 500 million rows partitioned by transaction_date?