LTV Prediction
ASKED IN INTERVIEWNetflix
Problem Statement
Calculate the total Lifetime Value (LTV) for every user. LTV is the sum of all successful payments made by the user.
Rules:
- Output columns: user_id, total_ltv.
- Filter: status = Success.
- Handling empty data: Return 0 for users with no successful payments.
- Sort by total_ltv DESC.
Tests your understanding of
Aggregation, Joins, COALESCE and Summation
Input Tables
users
| user_id(INTEGER) | user_name(VARCHAR) | created_at(TIMESTAMP) |
|---|---|---|
| 1 | Alice | 2023-01-01 10:00:00 |
| 2 | Bob | 2023-05-10 12:00:00 |
| 3 | Charlie | 2024-01-01 08:00:00 |
payments
| payment_id(INTEGER) | user_id(INTEGER) | amount(DECIMAL) | status(VARCHAR) | payment_date(TIMESTAMP) |
|---|---|---|---|---|
| 501 | 1 | 15.99 | Success | 2023-02-01 00:00:00 |
| 502 | 1 | 15.99 | Success | 2023-03-01 00:00:00 |
| 503 | 2 | 19.99 | Success | 2023-06-01 00:00:00 |
| 504 | 2 | 19.99 | Failed | 2023-07-01 00:00:00 |
subscriptions
| user_id(INTEGER) | plan_type(VARCHAR) |
|---|---|
| 1 | Standard |
Expected Output
| user_id(INTEGER) | total_ltv(DECIMAL) |
|---|---|
| 1 | 31.98 |
| 2 | 19.99 |
| 3 | 0 |
Tags
HardASKED IN INTERVIEWAggregationJoinsCOALESCESummation
15-25 min
48%