Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitd2de2a9

Browse files
authored
Added task 3716
1 parent7efe200 commitd2de2a9

File tree

3 files changed

+215
-0
lines changed

3 files changed

+215
-0
lines changed
Lines changed: 104 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,104 @@
1+
3716\. Find Churn Risk Customers
2+
3+
Medium
4+
5+
Table:`subscription_events`
6+
7+
+------------------+---------+
8+
| Column Name | Type |
9+
+------------------+---------+
10+
| event_id | int |
11+
| user_id | int |
12+
| event_date | date |
13+
| event_type | varchar |
14+
| plan_name | varchar |
15+
| monthly_amount | decimal |
16+
+------------------+---------+
17+
event_id is the unique identifier for this table.
18+
event_type can be start, upgrade, downgrade, or cancel.
19+
plan_name can be basic, standard, premium, or NULL (when event_type is cancel).
20+
monthly_amount represents the monthly subscription cost after this event.
21+
For cancel events, monthly_amount is 0.
22+
23+
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:
24+
25+
* Currently have an**active subscription** (their last event is not cancel).
26+
* Have performed**at least one** downgrade in their subscription history.
27+
* Their**current plan revenue** is less than`50%` of their historical maximum plan revenue.
28+
* Have been a subscriber for**at least**`60` days.
29+
30+
Return_the result table ordered by_`days_as_subscriber`_in**descending** order, then by_`user_id`_in**ascending** order_.
31+
32+
The result format is in the following example.
33+
34+
**Example:**
35+
36+
**Input:**
37+
38+
subscription\_events table:
39+
40+
+----------+---------+------------+------------+-----------+----------------+
41+
| event_id | user_id | event_date | event_type | plan_name | monthly_amount |
42+
+----------+---------+------------+------------+-----------+----------------+
43+
| 1 | 501 | 2024-01-01 | start | premium | 29.99 |
44+
| 2 | 501 | 2024-02-15 | downgrade | standard | 19.99 |
45+
| 3 | 501 | 2024-03-20 | downgrade | basic | 9.99 |
46+
| 4 | 502 | 2024-01-05 | start | standard | 19.99 |
47+
| 5 | 502 | 2024-02-10 | upgrade | premium | 29.99 |
48+
| 6 | 502 | 2024-03-15 | downgrade | basic | 9.99 |
49+
| 7 | 503 | 2024-01-10 | start | basic | 9.99 |
50+
| 8 | 503 | 2024-02-20 | upgrade | standard | 19.99 |
51+
| 9 | 503 | 2024-03-25 | upgrade | premium | 29.99 |
52+
| 10 | 504 | 2024-01-15 | start | premium | 29.99 |
53+
| 11 | 504 | 2024-03-01 | downgrade | standard | 19.99 |
54+
| 12 | 504 | 2024-03-30 | cancel | NULL | 0.00 |
55+
| 13 | 505 | 2024-02-01 | start | basic | 9.99 |
56+
| 14 | 505 | 2024-02-28 | upgrade | standard | 19.99 |
57+
| 15 | 506 | 2024-01-20 | start | premium | 29.99 |
58+
| 16 | 506 | 2024-03-10 | downgrade | basic | 9.99 |
59+
+----------+---------+------------+------------+-----------+----------------+
60+
61+
**Output:**
62+
63+
+---------+--------------+------------------------+-----------------------+--------------------+
64+
| user_id | current_plan | current_monthly_amount | max_historical_amount | days_as_subscriber |
65+
+---------+--------------+------------------------+-----------------------+--------------------+
66+
| 501 | basic | 9.99 | 29.99 | 79 |
67+
| 502 | basic | 9.99 | 29.99 | 69 |
68+
+---------+--------------+------------------------+-----------------------+--------------------+
69+
70+
**Explanation:**
71+
72+
***User 501**:
73+
* Currently active: Last event is downgrade to basic (not cancelled)
74+
* Has downgrades: Yes, 2 downgrades in history
75+
* Current revenue (9.99) vs max (29.99): 9.99/29.99 = 33.3% (less than 50%)
76+
* Days as subscriber: Jan 1 to Mar 20 = 79 days (at least 60)
77+
* Result:**Churn Risk Customer**
78+
***User 502**:
79+
* Currently active: Last event is downgrade to basic (not cancelled)
80+
* Has downgrades: Yes, 1 downgrade in history
81+
* Current revenue (9.99) vs max (29.99): 9.99/29.99 = 33.3% (less than 50%)
82+
* Days as subscriber: Jan 5 to Mar 15 = 70 days (at least 60)
83+
* Result:**Churn Risk Customer**
84+
***User 503**:
85+
* Currently active: Last event is upgrade to premium (not cancelled)
86+
* Has downgrades: No downgrades in history
87+
* Result:**Not at-risk** (no downgrade history)
88+
***User 504**:
89+
* Currently active: Last event is cancel
90+
* Result:**Not at-risk** (subscription cancelled)
91+
***User 505**:
92+
* Currently active: Last event is 'upgrade' to standard (not cancelled)
93+
* Has downgrades: No downgrades in history
94+
* Result:**Not at-risk** (no downgrade history)
95+
***User 506**:
96+
* Currently active: Last event is downgrade to basic (not cancelled)
97+
* Has downgrades: Yes, 1 downgrade in history
98+
* Current revenue (9.99) vs max (29.99): 9.99/29.99 = 33.3% (less than 50%)
99+
* Days as subscriber: Jan 20 to Mar 10 = 50 days (less than 60)
100+
* Result:**Not at-risk** (insufficient subscription duration)
101+
102+
Result table is ordered by days\_as\_subscriber DESC, then user\_id ASC.
103+
104+
**Note:** days\_as\_subscriber is calculated from the first event date to the last event date for each user.
Lines changed: 29 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,29 @@
1+
# Write your MySQL query statement below
2+
# #Medium #Database #2025_10_16_Time_256_ms_(96.87%)_Space_0.0_MB_(100.00%)
3+
WITH UserStatsAS (
4+
SELECT
5+
user_id,
6+
MIN(CASE WHEN event_type='start' THEN event_date END)AS start_date,
7+
MAX(event_date)AS last_event,
8+
MAX(monthly_amount)AS max_revenue,
9+
SUM(CASE WHEN event_type='downgrade' THEN1 ELSE0 END)AS downgrade_count
10+
FROM subscription_events
11+
GROUP BY user_id
12+
)
13+
SELECT
14+
us.user_id,
15+
se.plan_nameAS current_plan,
16+
se.monthly_amountAS current_monthly_amount,
17+
us.max_revenueAS max_historical_amount,
18+
TIMESTAMPDIFF(DAY,us.start_date,us.last_event)AS days_as_subscriber
19+
FROM UserStats us
20+
JOIN subscription_events se
21+
ONus.user_id=se.user_id
22+
ANDus.last_event=se.event_date
23+
WHEREse.event_type<>'cancel'
24+
ANDus.downgrade_count>0
25+
ANDse.monthly_amount*2<us.max_revenue
26+
AND TIMESTAMPDIFF(DAY,us.start_date,us.last_event)>60
27+
ORDER BY
28+
TIMESTAMPDIFF(DAY,us.start_date,us.last_event)DESC,
29+
us.user_idASC;
Lines changed: 82 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,82 @@
1+
packageg3701_3800.s3716_find_churn_risk_customers;
2+
3+
importstaticorg.hamcrest.CoreMatchers.equalTo;
4+
importstaticorg.hamcrest.MatcherAssert.assertThat;
5+
6+
importjava.io.BufferedReader;
7+
importjava.io.FileNotFoundException;
8+
importjava.io.FileReader;
9+
importjava.sql.Connection;
10+
importjava.sql.ResultSet;
11+
importjava.sql.SQLException;
12+
importjava.sql.Statement;
13+
importjava.util.stream.Collectors;
14+
importjavax.sql.DataSource;
15+
importorg.junit.jupiter.api.Test;
16+
importorg.zapodot.junit.db.annotations.EmbeddedDatabase;
17+
importorg.zapodot.junit.db.annotations.EmbeddedDatabaseTest;
18+
importorg.zapodot.junit.db.common.CompatibilityMode;
19+
20+
@EmbeddedDatabaseTest(
21+
compatibilityMode =CompatibilityMode.MySQL,
22+
initialSqls =
23+
"CREATE TABLE subscription_events ("
24+
+" event_id INTEGER PRIMARY KEY,"
25+
+" user_id INTEGER NOT NULL,"
26+
+" event_date DATE NOT NULL,"
27+
+" event_type VARCHAR(20) NOT NULL,"
28+
+" plan_name VARCHAR(20),"
29+
+" monthly_amount DECIMAL(10,2) NOT NULL"
30+
+");"
31+
+"INSERT INTO subscription_events (event_id, user_id, event_date, "
32+
+"event_type, plan_name, monthly_amount) VALUES"
33+
+"(1, 501, '2024-01-01', 'start', 'premium', 29.99),"
34+
+"(2, 501, '2024-02-15', 'downgrade', 'standard', 19.99),"
35+
+"(3, 501, '2024-03-20', 'downgrade', 'basic', 9.99),"
36+
+"(4, 502, '2024-01-05', 'start', 'standard', 19.99),"
37+
+"(5, 502, '2024-02-10', 'upgrade', 'premium', 29.99),"
38+
+"(6, 502, '2024-03-15', 'downgrade', 'basic', 9.99),"
39+
+"(7, 503, '2024-01-10', 'start', 'basic', 9.99),"
40+
+"(8, 503, '2024-02-20', 'upgrade', 'standard', 19.99),"
41+
+"(9, 503, '2024-03-25', 'upgrade', 'premium', 29.99),"
42+
+"(10, 504, '2024-01-15', 'start', 'premium', 29.99),"
43+
+"(11, 504, '2024-03-01', 'downgrade','standard', 19.99),"
44+
+"(12, 504, '2024-03-30', 'cancel', NULL, 0.00),"
45+
+"(13, 505, '2024-02-01', 'start', 'basic', 9.99),"
46+
+"(14, 505, '2024-02-28', 'upgrade', 'standard', 19.99),"
47+
+"(15, 506, '2024-01-20', 'start', 'premium', 29.99),"
48+
+"(16, 506, '2024-03-10', 'downgrade','basic', 9.99);"
49+
+"")
50+
classMysqlTest {
51+
@Test
52+
voidtestScript(@EmbeddedDatabaseDataSourcedataSource)
53+
throwsSQLException,FileNotFoundException {
54+
try (finalConnectionconnection =dataSource.getConnection()) {
55+
try (finalStatementstatement =connection.createStatement();
56+
finalResultSetresultSet =
57+
statement.executeQuery(
58+
newBufferedReader(
59+
newFileReader(
60+
"src/main/java/g3701_3800/"
61+
+"s3716_find_churn_risk_customers/"
62+
+"script.sql"))
63+
.lines()
64+
.collect(Collectors.joining("\n"))
65+
.replaceAll("#.*?\\r?\\n",""))) {
66+
assertThat(resultSet.next(),equalTo(true));
67+
assertThat(resultSet.getString(1),equalTo("501"));
68+
assertThat(resultSet.getString(2),equalTo("basic"));
69+
assertThat(resultSet.getString(3),equalTo("9.99"));
70+
assertThat(resultSet.getString(4),equalTo("29.99"));
71+
assertThat(resultSet.getString(5),equalTo("79"));
72+
assertThat(resultSet.next(),equalTo(true));
73+
assertThat(resultSet.getString(1),equalTo("502"));
74+
assertThat(resultSet.getString(2),equalTo("basic"));
75+
assertThat(resultSet.getString(3),equalTo("9.99"));
76+
assertThat(resultSet.getString(4),equalTo("29.99"));
77+
assertThat(resultSet.getString(5),equalTo("70"));
78+
assertThat(resultSet.next(),equalTo(false));
79+
}
80+
}
81+
}
82+
}

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp