OneNoughtOne
0/308
Loading content...
A subscription analytics team wants to compare how engaged users are before and after conversion to a paid plan.
You are given one table:
UserActivity
A user is considered converted if they have:
Task: For every converted user, compute:
Round both averages to 2 decimal places.
Output requirements:
Supported submission environments:
UserActivity:
| 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 | cancelled | 0 |
| 3 | 2023-03-05 | free_trial | 70 |
| 3 | 2023-03-12 | paid | 50 |
| 3 | 2023-03-15 | paid | 55 |
| 4 | 2023-04-01 | paid | 45 |[
{"user_id":1,"trial_avg_duration":45.0,"paid_avg_duration":76.67},
{"user_id":3,"trial_avg_duration":70.0,"paid_avg_duration":52.5}
]Explanation
Users 1 and 3 have both free_trial and paid rows, so they are included. User 2 has no paid row, and user 4 has no free_trial row, so both are excluded.
UserActivity:
| user_id | activity_date | activity_type | activity_duration |
|---------|---------------|---------------|-------------------|
| 10 | 2024-02-01 | free_trial | 1 |
| 10 | 2024-02-02 | free_trial | 0 |
| 10 | 2024-02-03 | free_trial | 0 |
| 10 | 2024-02-04 | free_trial | 0 |
| 10 | 2024-02-05 | free_trial | 0 |
| 10 | 2024-02-06 | free_trial | 0 |
| 10 | 2024-02-07 | free_trial | 0 |
| 10 | 2024-02-08 | free_trial | 0 |
| 10 | 2024-02-09 | paid | 9 |
| 10 | 2024-02-10 | paid | 0 |
| 10 | 2024-02-11 | paid | 0 |
| 10 | 2024-02-12 | paid | 0 |
| 10 | 2024-02-13 | paid | 0 |
| 10 | 2024-02-14 | paid | 0 |
| 10 | 2024-02-15 | paid | 0 |
| 10 | 2024-02-16 | paid | 0 |
| 10 | 2024-02-17 | paid | 0 |
| 10 | 2024-02-18 | paid | 0 |
| 10 | 2024-02-19 | paid | 0 |
| 10 | 2024-02-20 | paid | 0 |
| 10 | 2024-02-21 | paid | 0 |
| 10 | 2024-02-22 | paid | 0 |
| 10 | 2024-02-23 | paid | 0 |
| 10 | 2024-02-24 | paid | 0 |
| 10 | 2024-02-25 | paid | 0 |
| 10 | 2024-02-26 | paid | 0 |
| 10 | 2024-02-27 | paid | 0 |
| 10 | 2024-02-28 | paid | 0 |
| 10 | 2024-03-01 | paid | 0 |
| 10 | 2024-03-02 | paid | 0 |
| 10 | 2024-03-03 | paid | 0 |
| 10 | 2024-03-04 | paid | 0 |
| 10 | 2024-03-05 | paid | 0 |
| 10 | 2024-03-06 | paid | 0 |
| 10 | 2024-03-07 | paid | 0 |
| 10 | 2024-03-08 | paid | 0 |
| 10 | 2024-03-09 | paid | 0 |
| 10 | 2024-03-10 | paid | 0 |
| 10 | 2024-03-11 | paid | 0 |
| 10 | 2024-03-12 | paid | 0 |
| 10 | 2024-03-13 | paid | 0 |
| 10 | 2024-03-14 | paid | 0 |
| 10 | 2024-03-15 | paid | 0 |
| 10 | 2024-03-16 | paid | 0 |
| 10 | 2024-03-17 | paid | 0 |
| 10 | 2024-03-18 | paid | 0 |
| 10 | 2024-03-19 | paid | 0 |
| 10 | 2024-03-20 | paid | 0 |
| 10 | 2024-03-21 | paid | 0 |
| 10 | 2024-03-22 | paid | 0 |
| 10 | 2024-03-23 | paid | 0 |[
{"user_id":10,"trial_avg_duration":0.13,"paid_avg_duration":0.23}
]Explanation
This case stresses tie rounding: 1/8 rounds to 0.13 and 9/40 rounds to 0.23 using HALF_UP behavior.
UserActivity:
| user_id | activity_date | activity_type | activity_duration |
|---------|---------------|---------------|-------------------|
| 77 | 2024-06-01 | free_trial | 25 |
| 77 | 2024-06-02 | free_trial | 35 |
| 77 | 2024-06-03 | paid | 40 |
| 77 | 2024-06-04 | paid | 50 |
| 77 | 2024-06-05 | cancelled | 0 |
| 88 | 2024-06-01 | free_trial | 30 |
| 88 | 2024-06-02 | cancelled | 0 |
| 99 | 2024-06-01 | paid | 80 |[
{"user_id":77,"trial_avg_duration":30.0,"paid_avg_duration":45.0}
]Explanation
Cancelled rows do not contribute to either average. Users must have at least one free_trial and one paid row to appear in the output.
Constraints