Medium
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.
Each row represents a user's activity on a specific date.
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:
2
decimal places)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:
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.
# Write your MySQL query statement below
SELECT
ft.user_id,
ROUND(ft.avg_trial, 2) AS trial_avg_duration,
ROUND(pt.avg_paid, 2) AS paid_avg_duration
FROM
(SELECT user_id, AVG(activity_duration) AS avg_trial
FROM UserActivity
WHERE activity_type = 'free_trial'
GROUP BY user_id) ft
JOIN
(SELECT user_id, AVG(activity_duration) AS avg_paid
FROM UserActivity
WHERE activity_type = 'paid'
GROUP BY user_id) pt
ON ft.user_id = pt.user_id
ORDER BY ft.user_id ASC;