Is Find Churn Risk Customers easy or hard?

Find Churn Risk Customers is a medium-difficulty problem with an acceptance rate of about 48.5%. The logic is straightforward once you see the aggregation step. Most errors come from putting a condition in the wrong clause (WHERE vs HAVING) or mishandling customers with no transactions.

How to solve Find Churn Risk Customers in O(n)?

Scan the transactions table once and compute the required metrics per customer_id using GROUP BY. Then join the grouped result to the customers table and filter on the churn conditions. Each transaction is visited once, so the work is linear in the number of rows.

Find Churn Risk Customers Python or Java solution?

The problem is designed for SQL, so the reference solution is a query. In Python, you can use Pandas with groupby("customer_id").agg(...), then merge with customers and filter with boolean indexing. Java solutions would need JDBC and a SQL query, so the SQL version is the standard answer.

What is the best approach for Find Churn Risk Customers?

The best approach is to aggregate transactions per customer with GROUP BY, join the result to the customers table, and apply the churn conditions with HAVING or WHERE. This scans the transactions table once and runs in linear time. Conditional aggregation with SUM(CASE WHEN ...) lets you compute several filtered metrics in the same pass.

What data structure is used in Find Churn Risk Customers?

The database uses a hash table or sorted group structure internally to hold one aggregate record per customer during GROUP BY. In Pandas, groupby builds a similar hash-based index. You write the logic in SQL or Pandas, so you do not implement the structure yourself.

What is the time complexity of Find Churn Risk Customers?

The optimal grouped solution runs in O(T + C) time, where T is the number of transactions and C is the number of customers. It uses O(C) extra space for the group table. A correlated subquery version runs in O(C × T) without an index on customer_id.

Is Find Churn Risk Customers asked at Google, Amazon, or Meta?

SQL aggregation problems like this one appear often in data-focused interview loops at large tech companies, including Amazon, Meta, and Google. Check the company tags on the problem page for the current list, since it changes over time.

Description Solution Video 10:26

3716. Find Churn Risk Customers

Medium48.5% acceptance

Table: subscription_events

+------------------+---------+
| Column Name      | Type    | 
+------------------+---------+
| event_id         | int     |
| user_id          | int     |
| event_date       | date    |
| event_type       | varchar |
| plan_name        | varchar |
| monthly_amount   | decimal |
+------------------+---------+
event_id is the unique identifier for this table.
event_type can be start, upgrade, downgrade, or cancel.
plan_name can be basic, standard, premium, or NULL (when event_type is cancel).
monthly_amount represents the monthly subscription cost after this event.
For cancel events, monthly_amount is 0.

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.

Example:

Input:

subscription_events table:

+----------+---------+------------+------------+-----------+----------------+
| event_id | user_id | event_date | event_type | plan_name | monthly_amount |
+----------+---------+------------+------------+-----------+----------------+
| 1        | 501     | 2024-01-01 | start      | premium   | 29.99          |
| 2        | 501     | 2024-02-15 | downgrade  | standard  | 19.99          |
| 3        | 501     | 2024-03-20 | downgrade  | basic     | 9.99           |
| 4        | 502     | 2024-01-05 | start      | standard  | 19.99          |
| 5        | 502     | 2024-02-10 | upgrade    | premium   | 29.99          |
| 6        | 502     | 2024-03-15 | downgrade  | basic     | 9.99           |
| 7        | 503     | 2024-01-10 | start      | basic     | 9.99           |
| 8        | 503     | 2024-02-20 | upgrade    | standard  | 19.99          |
| 9        | 503     | 2024-03-25 | upgrade    | premium   | 29.99          |
| 10       | 504     | 2024-01-15 | start      | premium   | 29.99          |
| 11       | 504     | 2024-03-01 | downgrade  | standard  | 19.99          |
| 12       | 504     | 2024-03-30 | cancel     | NULL      | 0.00           |
| 13       | 505     | 2024-02-01 | start      | basic     | 9.99           |
| 14       | 505     | 2024-02-28 | upgrade    | standard  | 19.99          |
| 15       | 506     | 2024-01-20 | start      | premium   | 29.99          |
| 16       | 506     | 2024-03-10 | downgrade  | basic     | 9.99           |
+----------+---------+------------+------------+-----------+----------------+

Output:

+----------+--------------+------------------------+-----------------------+--------------------+
| user_id  | current_plan | current_monthly_amount | max_historical_amount | days_as_subscriber |
+----------+--------------+------------------------+-----------------------+--------------------+
| 501      | basic        | 9.99                   | 29.99                 | 79                 |
| 502      | basic        | 9.99                   | 29.99                 | 70                 |
+----------+--------------+------------------------+-----------------------+--------------------+

Explanation:

User 501:
- Currently active: Last event is downgrade to basic (not cancelled)
- Has downgrades: Yes, 2 downgrades in history
- Current revenue (9.99) vs max (29.99): 9.99/29.99 = 33.3% (less than 50%)
- Days as subscriber: Jan 1 to Mar 20 = 79 days (at least 60)
- Result: Churn Risk Customer
User 502:
- Currently active: Last event is downgrade to basic (not cancelled)
- Has downgrades: Yes, 1 downgrade in history
- Current revenue (9.99) vs max (29.99): 9.99/29.99 = 33.3% (less than 50%)
- Days as subscriber: Jan 5 to Mar 15 = 70 days (at least 60)
- Result: Churn Risk Customer
User 503:
- Currently active: Last event is upgrade to premium (not cancelled)
- Has downgrades: No downgrades in history
- Result: Not at-risk (no downgrade history)
User 504:
- Currently active: Last event is cancel
- Result: Not at-risk (subscription cancelled)
User 505:
- Currently active: Last event is 'upgrade' to standard (not cancelled)
- Has downgrades: No downgrades in history
- Result: Not at-risk (no downgrade history)
User 506:
- Currently active: Last event is downgrade to basic (not cancelled)
- Has downgrades: Yes, 1 downgrade in history
- Current revenue (9.99) vs max (29.99): 9.99/29.99 = 33.3% (less than 50%)
- Days as subscriber: Jan 20 to Mar 10 = 50 days (less than 60)
- Result: Not at-risk (insufficient subscription duration)

Result table is ordered by days_as_subscriber DESC, then user_id ASC.

Note: days_as_subscriber is calculated from the first event date to the last event date for each user.

10:26

Still stuck?Read the written solution, or watch a walkthrough.

Open solution

LanguageLoading editor…

1# Write your MySQL query statement below

Starter codeMySQLSpaces: 4

Examples

Case 1

Input

{"headers":{"subscription_events":["event_id","user_id","event_date","event_type","plan_name","monthly_amount"]},"rows":{"subscription_events":[[1,501,"2024-01-01","start","premium",29.99],[2,501,"2024-02-15","downgrade","standard",19.99],[3,501,"2024-03-20","downgrade","basic",9.99],[4,502,"2024-01-05","start","standard",19.99],[5,502,"2024-02-10","upgrade","premium",29.99],[6,502,"2024-03-15","downgrade","basic",9.99],[7,503,"2024-01-10","start","basic",9.99],[8,503,"2024-02-20","upgrade","standard",19.99],[9,503,"2024-03-25","upgrade","premium",29.99],[10,504,"2024-01-15","start","premium",29.99],[11,504,"2024-03-01","downgrade","standard",19.99],[12,504,"2024-03-30","cancel",null,0.00],[13,505,"2024-02-01","start","basic",9.99],[14,505,"2024-02-28","upgrade","standard",19.99],[15,506,"2024-01-20","start","premium",29.99],[16,506,"2024-03-10","downgrade","basic",9.99]]}}

Your code is saved on this device.Solution