Link
Tables
erDiagram
subscription_events {
event_id int PK
user_id int
event_date date
event_type varchar "start, upgrade, downgrade, cancel"
plan_name varchar "basic, standard, premium, NULL"
monthly_amount decimal
}
Mermaid
복사
Question
Write a solution to Find Churn Risk Customers - users who show warning signs before churning. A user is considered churn risk customer if they meet ALL the following criteria:
•
Currently have an active subscription (their last event is not cancel).
•
Have performed at least one downgrade in their subscription history.
•
Their current plan revenue is less than 50% of their historical maximum plan revenue.
•
Have been a subscriber for at least 60 days.
Return the result table ordered by days_as_subscriber in descending order, then by user_id in ascending order.
The result format is in the following example.
DataFlow
last event is not cancel
downgrade
revenue -> 50%
60 days
문제의 요구조건에 맞춰서 CTE를 구성하면 쉽게 해결가능하다고 생각한다.
Answer
WITH last_event AS (
SELECT
s.user_id,
MAX(s.event_date) AS last_date,
DATEDIFF(MAX(s.event_date), MIN(s.event_date)) AS sub_days,
MAX(s.monthly_amount) AS max_rev
FROM subscription_events s
INNER JOIN (
SELECT
user_id
FROM subscription_events
WHERE event_type = 'downgrade'
) t
ON s.user_id = t.user_id
GROUP BY user_id
),
current_event AS (
SELECT
s.*
FROM subscription_events s
INNER JOIN last_event l
ON s.user_id = l.user_id
AND l.last_date = s.event_date
WHERE s.event_type != 'cancel'
AND l.sub_days >= 60
)
SELECT
c.user_id,
c.plan_name AS current_plan,
c.monthly_amount AS current_monthly_amount,
l.max_rev AS max_historical_amount,
l.sub_days AS days_as_subscriber
FROM current_event c
JOIN last_event l
ON c.user_id = l.user_id
AND c.monthly_amount / l.max_rev < 0.5
ORDER BY days_as_subscriber DESC, user_id ASC;
SQL
복사
filter 혹은 연산을 한번에 하지 않고 나눠서 하려고 노력했다.