Analyze Subscription Conversion - Problem
A subscription service wants to understand user behavior patterns to optimize their conversion strategy. They offer a 7-day free trial after which users can either subscribe to a paid plan or cancel their subscription.
Given a table UserActivity that tracks daily user activities, you need to analyze the engagement patterns of users who successfully converted from free trial to paid subscription.
📊 Your Task:
- Identify users who converted from
free_trialtopaidsubscription - Calculate each user's average daily activity duration during their free trial period
- Calculate each user's average daily activity duration during their paid subscription period
- Return results rounded to 2 decimal places, ordered by
user_id
📋 Table Schema:
UserActivity +------------------+---------+ | Column Name | Type | +------------------+---------+ | user_id | int | | activity_date | date | | activity_type | varchar | | activity_duration| int | +------------------+---------+
activity_type can be: 'free_trial', 'paid', or 'cancelled'activity_duration represents minutes spent on the platform
Input & Output
example_1.sql — Basic Conversion Analysis
$
Input:
UserActivity:
user_id=1: free_trial(45,30,60), paid(75,90,65)
user_id=2: free_trial(55,25,50), cancelled
user_id=3: free_trial(70,60,80), paid(50,55,85)
›
Output:
user_id=1: trial_avg=45.00, paid_avg=76.67
user_id=3: trial_avg=70.00, paid_avg=63.33
💡 Note:
User 1 converted with 3 trial days averaging 45 minutes and 3 paid days averaging 76.67 minutes. User 2 cancelled so is excluded. User 3 converted with higher trial engagement (70.00) but slightly lower paid engagement (63.33).
example_2.sql — Mixed Conversion Patterns
$
Input:
UserActivity:
user_id=4: free_trial(40,35), paid(45), cancelled
user_id=5: free_trial(20), paid(60,80,70)
user_id=6: free_trial(30,25,35), cancelled
›
Output:
user_id=4: trial_avg=37.50, paid_avg=45.00
user_id=5: trial_avg=20.00, paid_avg=70.00
💡 Note:
User 4 had brief trial and paid periods before cancelling - still counts as converter. User 5 had minimal trial (1 day) but strong paid engagement. User 6 only cancelled, so excluded.
example_3.sql — Edge Case - Single Day Activities
$
Input:
UserActivity:
user_id=7: free_trial(100), paid(120)
user_id=8: free_trial(50,60,70), free_trial(80), paid(90)
›
Output:
user_id=7: trial_avg=100.00, paid_avg=120.00
user_id=8: trial_avg=65.00, paid_avg=90.00
💡 Note:
User 7 represents minimal engagement (1 day each) but still counts. User 8 shows multiple trial days with varying durations averaged correctly (50+60+70+80)/4 = 65.00.
Visualization
Tap to expand
Understanding the Visualization
1
Data Collection
Gather all user activities across free trial, paid, and cancelled states
2
Conversion Identification
Filter users who have both trial AND paid activities (successful converters)
3
Engagement Analysis
Calculate average daily engagement for trial period vs paid period
4
Business Insights
Compare trial vs paid engagement to optimize conversion strategy
Key Takeaway
🎯 Key Insight: Use CTEs with conditional aggregation to process conversion funnels efficiently - one query can identify converters and calculate their engagement metrics across multiple phases simultaneously.
Time & Space Complexity
Time Complexity
O(n)
Single pass through data with efficient aggregation
✓ Linear Growth
Space Complexity
O(n)
Temporary storage for grouping and aggregation results
⚡ Linearithmic Space
Constraints
- 1 ≤ number of rows ≤ 104
- 1 ≤ user_id ≤ 106
- 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
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code