Compute LTV by Cohort | Dataford Interview Questions

Write a SQL query to compute customer lifetime value (LTV) by acquisition cohort.

Use the customer acquisition date to define each cohort month. Include completed order revenue across each customer's lifetime, while excluding customers without an acquisition date and orders that do not belong to a known customer.

Output

One row per acquisition cohort month
Columns: cohort_month, customers_acquired, total_ltv, and average_ltv
Include customers with no completed revenue, and order results by cohort_month ascending

Schema

customers

Column	Type	Description
customer_idPK	INT	Unique customer identifier
customer_name	VARCHAR(100)	Customer display name
acquired_at	DATE	Date the customer was acquired

orders

Column	Type	Description
order_idPK	INT	Unique order identifier
customer_id	INT	Customer associated with the order
amount	NUMERIC(10,2)	Order revenue amount
status	VARCHAR(20)	Order lifecycle status

Your question is Compute LTV by Cohort. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem