HardBusiness Analytics High Frequency
Monthly User Cohort Retention Matrix
Frequently Asked By:AmazonGoogleMeta
Interactive In-Browser Workspace
Practice this problem on live schema tables in PostgreSQL 16 WASM with instant row verification and visual EXPLAIN plans.
Problem Statement
Build a cohort retention summary tracking the **signup cohort month** and the **activity month**.
Write a query that computes:
1. `cohort_month`: Truncate user `signup_date` to month (`DATE_TRUNC('month', u.signup_date)::date`)
2. `activity_month`: Truncate event `event_date` to month (`DATE_TRUNC('month', e.event_date)::date`)
3. `active_users`: Count of distinct `user_id`s active in that month
Join `cohort_users` to `user_events` on `user_id`.
Group by `cohort_month` and `activity_month`.
Order by `cohort_month` ascending, then by `activity_month` ascending.
Why Technical Interviewers Ask This
The holy grail of product-market fit is Cohort Retention: tracking whether users who signed up in month M come back and remain active in month M+1, M+2, etc.
Common Pitfalls & Interview Traps
Forgetting required sorting and deterministic tie-breaking.
Not accounting for NULL or zero-value denominators during calculations.
Using inefficient quadratic subqueries instead of single-pass business analytics constructs.
Canonical Modern SQL Solution
PostgreSQL 16 / ANSI SQLSELECT
DATE_TRUNC('month', u.signup_date)::date AS cohort_month,
DATE_TRUNC('month', e.event_date)::date AS activity_month,
COUNT(DISTINCT u.user_id) AS active_users
FROM cohort_users u
JOIN user_events e ON u.user_id = e.user_id
GROUP BY cohort_month, activity_month
ORDER BY cohort_month ASC, activity_month ASC;Approach Breakdown
This query addresses the problem requirements by filtering and structuring data in Business Analytics. It meets the target criteria: Cohort table displaying cohort_month, activity_month, and active_users.Edge Cases to Verbally Clarify with Your Interviewer
The dataset contains zero matching records (must return valid empty result without crashing).
Multiple records tie on sorting criteria (must evaluate tie-breaking order deterministically).
Foreign keys or secondary attributes have NULL values.
Frequently Asked Follow-Up Questions
What makes this question a favorite among Amazon and Google interviewers?
Interviewers use this problem to test whether candidates write defensive, scalable Business Analytics queries that run efficiently on multi-million row production databases without excessive memory usage.
How does the query optimizer execute this query?
Using single-pass table scans with index lookups or sort buffers. Running EXPLAIN (ANALYZE, BUFFERS) in the DB Exercises workbench visualizes the execution nodes and buffer hit cache.