← Intuit Interview Insights

Intuit·Data Scientist·Technical Phone Screen·Intermediate

Intermediate
May 2026

Summary

Intuit data scientist interview with a pretty involved SQL question on cohort retention. Nothing behavioral, just dropped straight into the technical problem and expected a working query.

Questions Asked (1)

Q1

Given a users table and a user_activity table, write a SQL query to compute cohort-based monthly retention for months 0 through 6. Return the cohort month, months since signup, cohort size, retained users, and retention rate as a decimal.

Data ModelingProduct Analytics & MetricsAlgorithms & Data Structures
Author's notes

This one took me a minute to even set up mentally.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

First, define the cohort month for each user as the month of their signup date, then join user_activity to count distinct active users per cohort per month offset. Aggregate to get cohort size and retained users, then compute retention rate as retained users divided by cohort size, filtering for offsets 0 through 6.

Pro tip: Clarify the definition of 'active' (e.g., any activity vs. specific action) and handle edge cases like users with no activity in month 0, as retention is typically measured from the first activity month, not signup month.

1. Define cohorts and activity

Identify the cohort month (e.g., DATE_TRUNC('month', signup_date)) for each user and define what constitutes an active user in a given month (e.g., any record in user_activity).

2. Compute monthly activity per user

For each user, determine the distinct months they were active by truncating activity dates to month.

3. Calculate month offsets

For each user and each active month, compute the number of months since their cohort month (offset), ensuring offsets range from 0 to 6.

4. Aggregate retention metrics

Group by cohort month and offset, count distinct users as retained users, and get cohort size as the number of users in that cohort (constant across offsets).

5. Compute retention rate and format output

Calculate retention rate as retained_users / cohort_size, and select the required columns: cohort month, months since signup, cohort size, retained users, and retention rate.

Key Points to Mention

  • Use DATE_TRUNC or equivalent to define cohort month and activity month.
  • Count distinct users to avoid double-counting multiple activities in a month.
  • Ensure cohort size is the total number of users in the cohort, not just those active in a given month.
  • Handle months with zero retained users by using LEFT JOINs or generating a date spine to include all offsets 0-6.
  • Retention rate should be a decimal (e.g., 0.25 for 25%).
  • Consider whether to measure retention from signup month or first activity month, and state your assumption.

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