Katkat
Back

Calculate Monthly Customer Retention

WhatnotData Scientist · Technical Screen
MediumData ScientistTechnical ScreenData Manipulation (SQL/Python)

Last updated

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.

Calculate Monthly Customer Retention

Write one PostgreSQL SELECT statement or CTE query. Do not create, alter, or modify tables.

Schema

orders

columntypedescription
order_idintegerUnique order identifier
customer_idintegerCustomer identifier
order_tstimestampTime the order was placed
statustextcompleted 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.

Hints

Hint 1

Build customer-month activity: Deduplicate completed orders to one row per customer and calendar month, then self-join on exactly one month later.