ISA-015 — Customer Lifetime Revenue
🟢 Beginner
⏱ 25 Minutes
⭐ 20 Points
🔓 Free
💻 SQL | PySpark
🏢 Retail Analytics
🎯 INNER JOIN
🎯 GROUP BY
🎯 SUM
Business Context
A retail company wants to understand the total revenue generated by each customer.
Management uses this information to:
- Identify high-value customers
- Design loyalty programs
- Target premium customers
- Improve customer retention
Customer information and order information are stored in separate datasets.
The analytics team must calculate lifetime revenue for every customer.
Business Impact
Customer Lifetime Revenue (CLV) is one of the most important business metrics.
It helps organizations:
- Identify top customers
- Improve retention strategies
- Increase profitability
- Prioritize marketing investments
Dataset
customers
| customer_id | customer_name |
|---|---|
| 101 | John |
| 102 | Alice |
| 103 | Bob |
orders
| order_id | customer_id | amount |
|---|---|---|
| O001 | 101 | 500 |
| O002 | 101 | 700 |
| O003 | 102 | 300 |
| O004 | 103 | 900 |
| O005 | 103 | 100 |
Task
Calculate total revenue generated by each customer.
Return:
- customer_name
- lifetime_revenue
Expected Output
| customer_name | lifetime_revenue |
|---|---|
| John | 1200 |
| Alice | 300 |
| Bob | 1000 |
Constraints
- Join customers and orders using customer_id.
- Aggregate revenue at customer level.
- Return one row per customer.
Supported Languages
✅ SQL
✅ PySpark
Data Engineering Pattern
Customer KPI Aggregation Pattern
This pattern is commonly used for:
- Customer dashboards
- CRM systems
- Loyalty programs
- Revenue analytics
A customer dimension is combined with transaction facts to produce customer-level KPIs.
Concepts Tested
- INNER JOIN
- GROUP BY
- SUM
- Customer Analytics
- Business KPIs
Hint
Combine customers and orders first.
Then aggregate revenue by customer.
Solution
🔒 Premium Solution
Premium members receive:
- SQL Solution
- PySpark Solution
- Step-by-Step Explanation
- KPI Walkthrough
- Alternative Approaches