Calculate Monthly Customer Retention
Company: Whatnot
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
# Calculate Monthly Customer Retention
Write one PostgreSQL SELECT statement or CTE query. Do not create, alter, or modify tables.
## Schema
orders
| column | type | description |
|---|---|---|
| order_id | integer | Unique order identifier |
| customer_id | integer | Customer identifier |
| order_ts | timestamp | Time the order was placed |
| status | text | completed or cancelled |
## Task
For every calendar month represented by at least one completed order, calculate customer retention from that month to the immediately following calendar month. A customer belongs to a month if they placed at least one completed order in it. Retention is the percentage of distinct customers in the cohort month who also placed a completed order in the next calendar month. Omit the final month because its following month is not observable.
## Required Output
Return cohort_month as a date, cohort_customers, retained_customers, and retention_pct rounded to two decimal places. Sort by cohort_month ascending.
## Constraints
- Count each customer at most once per month.
- Cancelled orders do not establish activity.
- A missing calendar month means retention to that month is zero, not retention to the next observed month.
```hint Build customer-month activity
Deduplicate completed orders to one row per customer and calendar month, then self-join on exactly one month later.
```
Overview: A PostgreSQL interview problem about calculating monthly customer retention from activity data. It tests month-level cohort construction, distinct-user counting, correct denominator selection, and handling customers who return across calendar boundaries.
For every calendar month represented by at least one completed order, calculate customer retention from that month to the immediately following calendar month. A customer belongs to a month if they placed at least one completed order in it. Retention is the percentage of distinct customers in the cohort month who also placed a completed order in the next calendar month. Omit the final month because its following month is not observable. Return cohort_month as a date, cohort_customers, retained_customers, and retention_pct rounded to two decimal places. Sort by cohort_month ascending. Count each customer at most once per month. Cancelled orders do not establish activity. A missing calendar month means retention to that month is zero, not retention to the next observed month.
Tables
orders(order_id INTEGER, customer_id INTEGER, order_ts TIMESTAMP, status TEXT)
Hints
- Build customer-month activity: Deduplicate completed orders to one row per customer and calendar month, then self-join on exactly one month later.