Difficulty: 🟡 Medium
Topics: —

Solution

Find Churn Risk Customers

Problem Understanding

This problem asks us to identify subscribers who appear likely to churn based on their subscription history. The data is stored in the subscription_events table, where each row represents a subscription-related action performed by a user.

Each event records the subscription state after that action. A user may start a subscription, upgrade to a more expensive plan, downgrade to a cheaper plan, or cancel entirely. The monthly_amount column always reflects the subscription price after the event occurs. For cancellation events, the amount becomes 0.

A user is considered a churn risk customer only if all four conditions are satisfied simultaneously.

First, the user must currently be active. This means their most recent event is not a cancel.

Second, the user must have performed at least one downgrade at some point in their history. This indicates a reduction in spending commitment.

Third, their current subscription revenue must be less than half of the highest revenue they have ever generated. In other words:

$$\text{current_monthly_amount} < 0.5 \times \text{max_historical_amount}$$

This condition identifies users whose spending has dropped significantly compared to their peak subscription level.

Fourth, the user must have been subscribed for at least 60 days. Subscription duration is calculated as the difference between the user's first event date and last event date.

The output should contain:

Column	Meaning
user_id	User identifier
current_plan	Current active plan
current_monthly_amount	Revenue from latest active plan
max_historical_amount	Highest monthly amount ever observed
days_as_subscriber	Days between first and last event

The result must be sorted by:

days_as_subscriber descending
user_id ascending

Key Observations

The challenge is fundamentally a per-user aggregation problem.

For every user we need:

First event date
Last event information
Whether any downgrade exists
Maximum historical revenue
Current revenue and current plan

The most important detail is determining the current state. Since events are chronological, the latest event for each user determines whether the subscription is active and what the current plan and revenue are.

Important Edge Cases

A user may have multiple downgrades. We only need to know whether at least one exists.

A user may have a downgrade but later upgrade again. The current revenue must still be compared against the historical maximum.

A user whose latest event is cancel must always be excluded, regardless of any other conditions.

A user whose current revenue is exactly 50% of their historical maximum should not be included because the requirement says "less than 50%".

A user with fewer than 60 subscription days must be excluded even if all other conditions are satisfied.

Approaches

Brute Force Approach

A straightforward solution would process each user independently.

For every distinct user, we could scan the entire table repeatedly to collect:

First event
Last event
Maximum revenue
Downgrade existence
Subscription duration

Because each user requires another pass over the table, the same rows are examined many times.

This approach is correct because every required metric can eventually be derived. However, it becomes inefficient when the number of users grows.

Optimal Approach

The key observation is that all required information can be computed in a single grouping operation.

For each user we can aggregate:

Earliest event date
Latest event date
Latest event details
Historical maximum revenue
Whether any downgrade exists

Window functions are especially useful because they allow us to identify the latest row per user while simultaneously computing group-level statistics.

After computing these values, we simply filter users according to the churn-risk conditions.

Approach Comparison

Approach	Time Complexity	Space Complexity	Notes
Brute Force	O(n²)	O(1)	Repeatedly scans table for each user
Optimal	O(n log n)	O(n)	Window functions compute all required metrics efficiently

Algorithm Walkthrough

Partition the table by user_id.
For each user, compute the first subscription date using MIN(event_date).
For each user, compute the last subscription date using MAX(event_date).
Compute the historical maximum revenue using MAX(monthly_amount) over all events belonging to the user.
Determine whether the user ever performed a downgrade by checking if any row has event_type = 'downgrade'.
Use ROW_NUMBER() ordered by descending event date to identify the most recent event for each user.
Keep only the row with row_number = 1. This row represents the user's current state.
Calculate subscription duration as the difference between the last event date and first event date.
Filter users satisfying all conditions:

Latest event is not cancel
At least one downgrade exists
Current monthly amount is less than half of historical maximum
Subscription duration is at least 60 days

Return the required columns and sort by:

days_as_subscriber DESC
user_id ASC

Why it works

The latest event completely determines a user's current subscription state. The aggregated statistics capture the user's historical behavior. Since every filtering condition depends either on the latest event or on user-wide historical aggregates, computing both sets of information and applying the filters guarantees that every qualifying churn-risk customer is returned exactly once.

SQL Solution

WITH user_stats AS (
    SELECT
        user_id,
        event_type,
        plan_name,
        monthly_amount,
        event_date,
        MIN(event_date) OVER (
            PARTITION BY user_id
        ) AS first_event_date,
        MAX(monthly_amount) OVER (
            PARTITION BY user_id
        ) AS max_historical_amount,
        MAX(
            CASE
                WHEN event_type = 'downgrade' THEN 1
                ELSE 0
            END
        ) OVER (
            PARTITION BY user_id
        ) AS has_downgrade,
        ROW_NUMBER() OVER (
            PARTITION BY user_id
            ORDER BY event_date DESC, event_id DESC
        ) AS rn
    FROM subscription_events
)
SELECT
    user_id,
    plan_name AS current_plan,
    monthly_amount AS current_monthly_amount,
    max_historical_amount,
    DATEDIFF(event_date, first_event_date) AS days_as_subscriber
FROM user_stats
WHERE rn = 1
    AND event_type <> 'cancel'
    AND has_downgrade = 1
    AND monthly_amount < max_historical_amount * 0.5
    AND DATEDIFF(event_date, first_event_date) >= 60
ORDER BY
    days_as_subscriber DESC,
    user_id ASC;

Implementation Explanation

The user_stats CTE computes all user-level metrics.

MIN(event_date) finds the subscriber's first event date. This is later used to calculate subscription duration.

MAX(monthly_amount) determines the highest revenue level ever reached by the user.

The downgrade flag is created using a windowed MAX(CASE...) expression. If any row contains a downgrade event, the result becomes 1.

ROW_NUMBER() identifies the most recent event for every user. Ordering by both event_date DESC and event_id DESC guarantees deterministic behavior when multiple events occur on the same date.

After computing these values, only the latest row (rn = 1) is retained. This row contains the user's current plan, current revenue, and current subscription status.

The final WHERE clause applies the churn-risk criteria, and the result is sorted according to the required ordering.

Worked Example

Consider user 501.

Event History

Event Date	Event Type	Plan	Amount
2024-01-01	start	premium	29.99
2024-02-15	downgrade	standard	19.99
2024-03-20	downgrade	basic	9.99

Aggregated Values

Metric	Value
First Event Date	2024-01-01
Last Event Date	2024-03-20
Max Historical Amount	29.99
Has Downgrade	1
Current Plan	basic
Current Amount	9.99
Days As Subscriber	79

Condition Evaluation

Condition	Result
Active subscription	True
Has downgrade	True
9.99 < 29.99 × 0.5	True
79 ≥ 60	True

User 501 is included.

User 506

Metric	Value
First Event Date	2024-01-20
Last Event Date	2024-03-10
Days As Subscriber	50
Current Amount	9.99
Max Amount	29.99

Although the revenue drop and downgrade conditions are satisfied, the subscription duration is only 50 days, so the user is excluded.

Complexity Analysis

Measure	Complexity	Explanation
Time	O(n log n)	Window functions require partition ordering
Space	O(n)	Intermediate window-function results are stored
Output	O(k)	k qualifying users returned

The dominant cost comes from the partitioned ordering needed by ROW_NUMBER(). All other aggregations are computed during the same window-processing stage.

Test Cases

# Example from problem statement
# Expected users: 501 and 502

# Single active user with downgrade and sufficient duration
# Should be included

# User with downgrade but latest event is cancel
# Should be excluded

# User with no downgrade history
# Should be excluded

# User whose current amount equals exactly 50% of maximum
# Should be excluded because condition is strictly less than

# User with duration exactly 60 days
# Should be included

# User with duration 59 days
# Should be excluded

# Multiple downgrades and upgrades
# Latest active plan still compared against historical maximum

# Multiple events on same day
# event_id tie-breaker determines latest event

# User whose maximum amount occurs before a downgrade
# Historical maximum must still be used

# User with many upgrades after downgrade
# Current amount may no longer satisfy revenue-drop condition

Test Summary

Test	Why
Problem example	Verifies all filtering conditions together
Single qualifying user	Basic correctness
Cancelled subscriber	Validates active subscription requirement
No downgrade history	Validates downgrade requirement
Exactly 50% revenue	Checks strict inequality
Exactly 60 days	Checks boundary inclusion
59 days	Checks boundary exclusion
Multiple upgrades and downgrades	Validates historical aggregation
Same-day events	Tests deterministic latest-event selection
Historical max before downgrade	Validates maximum revenue logic

Edge Cases

Latest Event Is Cancel

A user may have a long subscription history, multiple downgrades, and a significant revenue drop, but if their most recent event is cancel, they are no longer an active subscriber. A common bug is to compute all historical metrics correctly but forget to verify the latest status. The solution avoids this by selecting the latest event with ROW_NUMBER() and filtering out rows where event_type = 'cancel'.

Current Revenue Equals Exactly Half of Historical Maximum

The requirement states that current revenue must be less than 50% of historical maximum revenue. Equality does not qualify. Using <= instead of < would incorrectly include these users. The query explicitly uses:

monthly_amount < max_historical_amount * 0.5

which correctly enforces the strict comparison.

Multiple Events on the Same Date

Two events may occur on the same day. If ordering only uses event_date DESC, the latest event becomes ambiguous. The solution orders by both event_date DESC and event_id DESC, ensuring deterministic selection of the current state.

Downgrade Followed by Upgrade

A user may downgrade and later upgrade again. The downgrade requirement is historical, not current. Therefore, we must check whether a downgrade ever occurred rather than whether the latest event is a downgrade. The windowed downgrade flag correctly captures this condition across the entire subscription history.

Maximum Revenue Before Current Plan

The user's highest revenue may have occurred years before the current subscription level. A naive solution might compare against the previous event's revenue instead of the historical maximum. The windowed MAX(monthly_amount) ensures that the comparison always uses the highest revenue ever achieved by the user.

LeetCode 3716 - Find Churn Risk Customers