4103. Find Churn Risk Customers
My accepted SQL solution to LeetCode problem 4103, Find Churn Risk Customers, running in 331ms.
- Difficulty: Medium
- SQL
- Runtime 331ms
- Memory 0.0B
- Updated
Read the problem on LeetCode View on GitHub
The problem statement is LeetCode’s and stays on their site. What follows is my accepted solution.
SQL
Accepted on LeetCode — runtime 331ms, memory 0.0B, accepted 2025-12-31.
# Write your MySQL query statement below
WITH user_events AS (
SELECT
user_id,
event_date,
event_type,
plan_name,
monthly_amount,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_date DESC, event_id DESC) AS rn,
MIN(event_date) OVER (PARTITION BY user_id) AS first_event_date,
MAX(event_date) OVER (PARTITION BY user_id) AS last_event_date,
MAX(monthly_amount) OVER (PARTITION BY user_id) AS max_historical_amount
FROM subscription_events
),
latest_events AS (
SELECT
user_id,
event_type AS latest_event_type,
plan_name AS current_plan,
monthly_amount AS current_monthly_amount,
first_event_date,
last_event_date,
max_historical_amount
FROM user_events
WHERE rn = 1
),
downgrade_check AS (
SELECT DISTINCT user_id
FROM subscription_events
WHERE event_type = 'downgrade'
)
SELECT
l.user_id,
l.current_plan,
l.current_monthly_amount,
l.max_historical_amount,
DATEDIFF(l.last_event_date, l.first_event_date) AS days_as_subscriber
FROM latest_events l
JOIN downgrade_check d ON l.user_id = d.user_id
WHERE l.latest_event_type != 'cancel'
AND l.current_monthly_amount < 0.5 * l.max_historical_amount
AND DATEDIFF(l.last_event_date, l.first_event_date) >= 60
ORDER BY days_as_subscriber DESC, l.user_id ASC;