Analyze Subscription Conversion - Problem

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 NameType
user_idint
activity_datedate
activity_typevarchar
activity_durationint

(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_id in ascending order

Table Schema

UserActivity
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
Primary Key: (user_id, activity_date, activity_type)
Note: Each row represents a user's activity on a specific date. Users can have free_trial, paid, or cancelled activity types.

Input & Output

Example 1 — Multiple Users with Different Conversion Patterns
Input 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
Output:
user_id trial_avg_duration paid_avg_duration
1 45 76.67
3 70 63.33
💡 Note:

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.
Example 2 — User with Short Paid Period
Input Table:
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
Output:
user_id trial_avg_duration paid_avg_duration
4 37.5 45
💡 Note:

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_type is 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

Visualization

Tap to expand
Analyze Subscription Conversion INPUT users {id:1, trial_start:'01-01'} {id:2, trial_start:'01-02'} {id:3, trial_start:'01-03'} ... subscriptions {user:1, plan:'paid', date:'01-08'} {user:3, plan:'paid', date:'01-10'} ... activity_logs {user:1, date:'01-01', mins:45} {user:1, date:'01-02', mins:60} {user:1, date:'01-08', mins:30} {user:3, date:'01-03', mins:55} ... ALGORITHM STEPS 1 Find Converted Users JOIN users with paid subscriptions after 7-day trial period 2 Classify Activity Periods Trial: days 1-7 from trial_start Paid: after subscription date 3 Calculate Daily Averages SUM(duration) / COUNT(days) for each period type 4 Aggregate Results GROUP BY user_id with trial_avg and paid_avg User Timeline Trial (7 days) Paid Period Start Convert FINAL RESULT user_id trial_avg paid_avg 1 52.5 35.0 3 48.3 42.0 5 61.2 55.8 Conversion Summary Trial Avg: 54.0 min Paid Avg: 44.3 min 3 users converted OK - Analysis Complete Converted users identified with activity averages calculated Key Insight: Use window functions or subqueries to classify activity logs into trial vs paid periods based on dates. The optimal approach joins tables once and uses CASE WHEN to partition data, avoiding multiple scans. Time complexity: O(n log n) for sorting, Space: O(n) for intermediate results. TutorialsPoint - Analyze Subscription Conversion | Optimal Solution

Related Problems

Asked in
Netflix 12 Spotify 8 Adobe 6
23.4K Views
Medium Frequency
~12 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen