3497\. Analyze Subscription Conversion

Medium

Table:`UserActivity`

A subscription service wants to analyze user behavior patterns. The company offers a`7`\-day**free trial**, after which users can subscribe to a**paid plan** or**cancel**. Write a solution to:

1. Find users who converted from free trial to paid subscription

2. Calculate each user's**average daily activity duration** during their**free trial** period (rounded to`2` decimal places)

3. Calculate each user's**average daily activity duration** during their**paid** subscription period (rounded to`2` decimal places)

Return_the result table ordered by_`user_id`_in**ascending** order_.

The result format is in the following example.

**Example:**

**Input:**

UserActivity table:

| user_id| activity_date| activity_type| activity_duration|

|---------|---------------|---------------|-------------------|

| 1| 2023-01-01| free_trial| 45|

| 1| 2023-01-02| free_trial| 30|

| 1| 2023-01-05| free_trial| 60|

| 1| 2023-01-10| paid| 75|

| 1| 2023-01-12| paid| 90|

| 1| 2023-01-15| paid| 65|

| 2| 2023-02-01| free_trial| 55|

| 2| 2023-02-03| free_trial| 25|

| 2| 2023-02-07| free_trial| 50|

| 2| 2023-02-10| cancelled| 0|

| 3| 2023-03-05| free_trial| 70|

| 3| 2023-03-06| free_trial| 60|

| 3| 2023-03-08| free_trial| 80|

| 3| 2023-03-12| paid| 50|

| 3| 2023-03-15| paid| 55|

| 3| 2023-03-20| paid| 85|

| 4| 2023-04-01| free_trial| 40|

| 4| 2023-04-03| free_trial| 35|

| 4| 2023-04-05| paid| 45|

| 4| 2023-04-07| cancelled| 0|

**Output:**

| user_id| trial_avg_duration| paid_avg_duration|

|---------|--------------------|-------------------|

| 1| 45.00| 76.67|

| 3| 70.00| 63.33|

| 4| 37.50| 45.00|

**Explanation:**

***User 1:**

* Had 3 days of free trial with durations of 45, 30, and 60 minutes.

* Average trial duration: (45 + 30 + 60) / 3 = 45.00 minutes.

* Had 3 days of paid subscription with durations of 75, 90, and 65 minutes.

* Average paid duration: (75 + 90 + 65) / 3 = 76.67 minutes.

***User 2:**

* Had 3 days of free trial with durations of 55, 25, and 50 minutes.

* Average trial duration: (55 + 25 + 50) / 3 = 43.33 minutes.

* Did not convert to a paid subscription (only had free\_trial and cancelled activities).

* Not included in the output because they didn't convert to paid.

***User 3:**

* Had 3 days of free trial with durations of 70, 60, and 80 minutes.

* Average trial duration: (70 + 60 + 80) / 3 = 70.00 minutes.

* Had 3 days of paid subscription with durations of 50, 55, and 85 minutes.

* Average paid duration: (50 + 55 + 85) / 3 = 63.33 minutes.

***User 4:**

* Had 2 days of free trial with durations of 40 and 35 minutes.

* Average trial duration: (40 + 35) / 2 = 37.50 minutes.

* Had 1 day of paid subscription with duration of 45 minutes before cancelling.

* Average paid duration: 45.00 minutes.

The result table only includes users who converted from free trial to paid subscription (users 1, 3, and 4), and is ordered by user\_id in ascending order.

ft.user_id,

ROUND(ft.avg_trial,2)AS trial_avg_duration,

ROUND(pt.avg_paid,2)AS paid_avg_duration

(SELECT user_id,AVG(activity_duration)AS avg_trial

FROM UserActivity

WHERE activity_type='free_trial'

GROUP BY user_id) ft

(SELECT user_id,AVG(activity_duration)AS avg_paid

FROM UserActivity

WHERE activity_type='paid'

GROUP BY user_id) pt

ONft.user_id=pt.user_id

ORDER BYft.user_idASC;

packageg3401_3500.s3497_analyze_subscription_conversion;

importstaticorg.hamcrest.CoreMatchers.equalTo;

importstaticorg.hamcrest.MatcherAssert.assertThat;

importjava.io.BufferedReader;

importjava.io.FileNotFoundException;

importjava.io.FileReader;

importjava.sql.Connection;

importjava.sql.ResultSet;

importjava.sql.SQLException;

importjava.sql.Statement;

importjava.util.stream.Collectors;

importjavax.sql.DataSource;

importorg.junit.jupiter.api.Test;

importorg.zapodot.junit.db.annotations.EmbeddedDatabase;

importorg.zapodot.junit.db.annotations.EmbeddedDatabaseTest;

importorg.zapodot.junit.db.common.CompatibilityMode;

@EmbeddedDatabaseTest(

compatibilityMode =CompatibilityMode.MySQL,

initialSqls =

+" user_id INT,"

+" activity_date date,"

+" activity_type VARCHAR(100),"

+" activity_duration INT"

+");"

+"INSERT INTO UserActivity (user_id, activity_date, activity_type, activity_duration)"

+"VALUES"

+" (1, '2023-01-01', 'free_trial', 45),"

+" (1, '2023-01-02', 'free_trial', 30),"

+" (1, '2023-01-05', 'free_trial', 60),"

+" (1, '2023-01-10', 'paid', 75),"

+" (1, '2023-01-12', 'paid', 90),"

+" (1, '2023-01-15', 'paid', 65),"

+" (2, '2023-02-01', 'free_trial', 55),"

+" (2, '2023-02-03', 'free_trial', 25),"

+" (2, '2023-02-07', 'free_trial', 50),"

+" (2, '2023-02-10', 'cancelled', 0),"

+" (3, '2023-03-05', 'free_trial', 70),"

+" (3, '2023-03-06', 'free_trial', 60),"

+" (3, '2023-03-08', 'free_trial', 80),"

+" (3, '2023-03-12', 'paid', 50),"

+" (3, '2023-03-15', 'paid', 55),"

+" (3, '2023-03-20', 'paid', 85),"

+" (4, '2023-04-01', 'free_trial', 40),"

+" (4, '2023-04-03', 'free_trial', 35),"

+" (4, '2023-04-05', 'paid', 45),"

+" (4, '2023-04-07', 'cancelled', 0);")

classMysqlTest {

voidtestScript(@EmbeddedDatabaseDataSourcedataSource)

throwsSQLException,FileNotFoundException {

try (finalConnectionconnection =dataSource.getConnection()) {

try (finalStatementstatement =connection.createStatement();

finalResultSetresultSet =

statement.executeQuery(

newBufferedReader(

newFileReader(

+"s3497_analyze_subscription_conversion/"

+"script.sql"))

.lines()

.collect(Collectors.joining("\n"))

.replaceAll("#.*?\\r?\\n",""))) {

checkRow(resultSet,newString[] {"1","45.0","76.67"});

checkRow(resultSet,newString[] {"3","70.0","63.33"});

checkRow(resultSet,newString[] {"4","37.5","45.0"});

assertThat(resultSet.next(),equalTo(false));

}

}

}

privatevoidcheckRow(ResultSetresultSet,String[]values)throwsSQLException {

assertThat(resultSet.next(),equalTo(true));

assertThat(resultSet.getNString(1),equalTo(values[0]));

assertThat(resultSet.getNString(2),equalTo(values[1]));

assertThat(resultSet.getNString(3),equalTo(values[2]));

}

}