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.
Table: UserActivity
| Column Name | Type |
|---|---|
| user_id | int |
| activity_date | date |
| activity_type | varchar |
| activity_duration | int |
(user_id, activity_date, activity_type) is the unique key for this table.
activity_type is one of ('free_trial', 'paid', 'cancelled').
activity_duration is the number of minutes the user spent on the platform that day.
Write a solution to:
- Find users who converted from free trial to paid subscription
- Calculate each user's average daily activity duration during their free trial period (rounded to 2 decimal places)
- 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_idin ascending order
Table Schema
| Column Name | Type | Description |
|---|---|---|
user_id
PK
|
int | User identifier |
activity_date
PK
|
date | Date of the activity |
activity_type
PK
|
varchar | Type of activity: 'free_trial', 'paid', or 'cancelled' |
activity_duration
|
int | Number of minutes the user spent on the platform that day |
Input & Output
| 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 |
| user_id | trial_avg_duration | paid_avg_duration |
|---|---|---|
| 1 | 45 | 76.67 |
| 3 | 70 | 63.33 |
User 1: Had 3 free trial days (45, 30, 60 minutes) averaging 45.00, and 3 paid days (75, 90, 65 minutes) averaging 76.67.
User 2: Had free trial but cancelled instead of converting to paid, so excluded from results.
User 3: Had 3 free trial days (70, 60, 80 minutes) averaging 70.00, and 3 paid days (50, 55, 85 minutes) averaging 63.33.
| user_id | activity_date | activity_type | activity_duration |
|---|---|---|---|
| 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 |
| user_id | trial_avg_duration | paid_avg_duration |
|---|---|---|
| 4 | 37.5 | 45 |
User 4: Had 2 free trial days (40, 35 minutes) averaging 37.50, had 1 paid day (45 minutes) before cancelling. Since they had both free_trial and paid activities, they are included in results.
Constraints
-
1 ≤ user_id ≤ 10^5 -
activity_typeis one of('free_trial', 'paid', 'cancelled') -
0 ≤ activity_duration ≤ 1440(max minutes in a day) -
Each
(user_id, activity_date, activity_type)combination is unique