← Chime Interview Insights

Chime·Data Scientist·Technical Phone Screen·Intermediate

Intermediate
Jun 2026

Summary

SQL take-home style question from Chime for a Data Scientist role. The problem was meaty enough that it felt more like a mini project than a screen, covering cohort definitions, bucketing, and retention logic all in one query.

Questions Asked (1)

Q1

Given a users table and a transactions table, write a SQL query that buckets users by how many transactions they made in their first 7 days after signup (0, 1, 2, 3+), then computes Day-30 retention rate per bucket, where retention means having at least one transaction in the window from day 30 to day 37 post-signup.

Product Analytics & MetricsData Modeling
Author's notes

The join logic is straightforward but the window math trips you up if you're not careful.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by defining the user cohort and their signup date, then compute the number of transactions in the first 7 days and bucket users accordingly. Next, determine Day-30 retention by checking for any transaction between day 30 and day 37 post-signup, and finally aggregate retention rate per bucket.

Pro tip: Clarify the definition of 'day' (e.g., calendar day vs. 24-hour period) and ensure you handle time zones consistently; also consider using a LEFT JOIN to include users with zero transactions in the first 7 days.

1. Identify user cohort and signup date

Select all users with their signup date, ensuring you have a clear definition of the cohort (e.g., all users or a specific time range).

2. Count transactions in first 7 days

Join transactions to users and count transactions where transaction date is between signup date and signup date + 7 days. Use LEFT JOIN to include users with zero transactions.

3. Bucket users by transaction count

Create buckets: 0, 1, 2, 3+ based on the count from step 2. Use a CASE statement to assign each user to a bucket.

4. Determine Day-30 retention

For each user, check if they have at least one transaction between day 30 and day 37 post-signup. Use a LEFT JOIN or EXISTS subquery to flag retained users.

5. Compute retention rate per bucket

Group by bucket and calculate the retention rate as the number of retained users divided by total users in the bucket, multiplied by 100 for percentage.

Key Points to Mention

  • Define the cohort and time windows clearly (e.g., first 7 days inclusive of signup day or not).
  • Use LEFT JOIN to include users with zero transactions in the first 7 days.
  • Handle date arithmetic correctly, considering time zones and inclusive/exclusive boundaries.
  • Use conditional aggregation (CASE WHEN) to bucket users and flag retention.
  • Consider performance implications and indexing on user_id and transaction_date.
  • Validate results with sanity checks (e.g., total users per bucket sums to total cohort).

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.