PracHub
QuestionsPremiumCoachesLearningGuidesInterview Prep

Quick Overview

This question evaluates a data scientist's competency in cohort-based retention analysis, time-based data manipulation, and aggregation using Data Manipulation (SQL/Python) techniques.

  • medium
  • Intuit
  • Data Manipulation (SQL/Python)
  • Data Scientist

Compute Cohort Retention Rate

Company: Intuit

Role: Data Scientist

Category: Data Manipulation (SQL/Python)

Difficulty: medium

Interview Round: Technical Screen

You are given two tables: - `users(user_id BIGINT, signup_ts TIMESTAMP)` — one row per user. - `user_activity(user_id BIGINT, activity_ts TIMESTAMP, event_name VARCHAR)` — zero or more activity rows per user. Assume all timestamps are in UTC. Define a user's cohort as the calendar month of `signup_ts`. A user is retained in month `k` if they have at least one row in `user_activity` during the calendar month that is `k` months after their cohort month. Month `0` is the signup month. Write a SQL query to compute cohort-based monthly retention for `k = 0, 1, 2, 3, 4, 5, 6`. Return these columns: - `cohort_month` — first day of the signup month - `months_since_signup` — integer from 0 to 6 - `cohort_size` — number of users in the cohort - `retained_users` — number of distinct users active in that relative month - `retention_rate` — `retained_users / cohort_size` as a decimal Make sure each user is counted at most once per cohort-month and relative month.

Quick Answer: This question evaluates a data scientist's competency in cohort-based retention analysis, time-based data manipulation, and aggregation using Data Manipulation (SQL/Python) techniques.

Last updated: May 7, 2026

Loading coding console...

PracHub

Master your tech interviews with 8,000+ real questions from top companies.

Product

  • Questions
  • Learning Tracks
  • Interview Guides
  • Resources
  • Premium
  • For Universities
  • Student Access

Browse

  • By Company
  • By Role
  • By Category
  • Topic Hubs
  • SQL Questions
  • Compare Platforms
  • Discord Community

Support

  • support@prachub.com
  • (916) 541-4762

Legal

  • Privacy Policy
  • Terms of Service
  • About Us

© 2026 PracHub. All rights reserved.

Related Coding Questions

  • Calculate Cohort Retention - Intuit (medium)
  • Compute monthly signups, conversion, and YoY growth - Intuit (easy)
  • Pivot daily users and revenue by platform - Intuit (easy)
  • Design an idempotent churn ETL pipeline - Intuit (Medium)
  • Compute churn and revenue churn in SQL - Intuit (Medium)