Analyze Subscription Conversion solution and explanation
2 more videos on this problem
Leetcode MEDIUM 3497 - AGG Filter in SQL - Analyze Subscription Conversion | Everyday Data ScienceEveryday Data Science · 7:58 · 883 views
LeetCode 3497. Analyze Subscription Conversion || LeetCode - MediumInsightVanta · 12:39 · 93 views
LeetCode 3497– Analyse Subscription Conversion | Pandas | Leetcode Interview QuestionThe Knowledge Academy · 2:25 · 53 views
The problem
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:
- 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
2decimal places) - Calculate each user's average daily activity duration during their paid subscription period (rounded to
2decimal 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:
- User 1:
- Had 3 days of free trial with durations of 45, 30, and 60 minutes.
- Average trial duration: (45 + 30 + 60) / 3 = 45.00 minutes.
- Had 3 days of paid subscription with durations of 75, 90, and 65 minutes.
- Average paid duration: (75 + 90 + 65) / 3 = 76.67 minutes.
- User 2:
- Had 3 days of free trial with durations of 55, 25, and 50 minutes.
- Average trial duration: (55 + 25 + 50) / 3 = 43.33 minutes.
- Did not convert to a paid subscription (only had free_trial and cancelled activities).
- Not included in the output because they didn't convert to paid.
- User 3:
- Had 3 days of free trial with durations of 70, 60, and 80 minutes.
- Average trial duration: (70 + 60 + 80) / 3 = 70.00 minutes.
- Had 3 days of paid subscription with durations of 50, 55, and 85 minutes.
- Average paid duration: (50 + 55 + 85) / 3 = 63.33 minutes.
- User 4:
- Had 2 days of free trial with durations of 40 and 35 minutes.
- Average trial duration: (40 + 35) / 2 = 37.50 minutes.
- Had 1 day of paid subscription with duration of 45 minutes before cancelling.
- Average paid duration: 45.00 minutes.
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.
Approach overview
Problem Overview: You get a table of user activity events, each tagged as a free trial, paid, or cancelled event with a duration. Return only the users who converted from a free trial to a paid plan, along with their average trial duration and average paid duration, rounded to two decimals.
Approach 1: Separate Aggregations Joined on user_id (O(n) time, O(u) space)
Run two grouped queries. The first groups rows where the activity type is free_trial and computes AVG(duration) per user_id. The second does the same for paid. Then join the two result sets on user_id with an equi-join. The join keeps only users who appear in both groups, which is exactly the set of converters. This is the clearest version to write and reason about. Use it when you want readable SQL and each subquery can be tested on its own. The cost is two scans of the base table, and the join needs a hash or index lookup on user_id.
Approach 2: Single-Pass Conditional Aggregation (O(n) time, O(u) space)
Scan the table once and group by user_id. Inside the aggregate, use AVG(CASE WHEN activity_type = 'free_trial' THEN duration END) and the same pattern for paid. Then filter with a HAVING clause that requires both averages to be non-null, so only users with both event types survive. This removes the join entirely and reads the table once. It is the version you should write in an interview and the one that scales best on large event logs, since the work is a single hash aggregation pass.
Recommended for interviews: Interviewers expect Approach 2. The two-subquery version shows you understand grouping and joins, but the single-pass conditional aggregation shows you can avoid a redundant scan and a join. Mention the HAVING filter for converters explicitly, and round the averages with ROUND(..., 2) as the output requires. For more practice, see database problems, SQL problems, and hash table problems, since the grouping step relies on hash-based aggregation.
Solution
Thinking
We need users who have both a trial and a paid activity, with the mean duration of each. Cancelled rows are ignored.
Drop
cancelled, average by user and type, and inner-join so both sides exist.A tiny increment before rounding to two decimals avoids float-edge cases; then sort by
user_id.
First, we filter the data in the table to exclude all records where activity_type is equal to cancelled. Then, we group the remaining data by user_id and activity_type, calculate the duration duration for each group, and store the results in table T.
Next, we filter table T to extract records where activity_type is free_trial and paid, storing them in tables F and P, respectively. Finally, we perform an equi-join on these two tables using user_id, filter the required fields as per the problem statement, and sort the results to produce the final output.
# Write your MySQL query statement below
WITH
T AS (
SELECT user_id, activity_type, ROUND(SUM(activity_duration) / COUNT(1), 2) duration
FROM UserActivity
WHERE activity_type != 'cancelled'
GROUP BY user_id, activity_type
),
F AS (
SELECT user_id, duration trial_avg_duration
FROM T
WHERE activity_type = 'free_trial'
),
P AS (
SELECT user_id, duration paid_avg_duration
FROM T
WHERE activity_type = 'paid'
)
SELECT user_id, trial_avg_duration, paid_avg_duration
FROM
F
JOIN P USING (user_id)
ORDER BY 1;import pandas as pd
def analyze_subscription_conversion(user_activity: pd.DataFrame) -> pd.DataFrame:
df = user_activity[user_activity["activity_type"] != "cancelled"]
df_grouped = (
df.groupby(["user_id", "activity_type"])["activity_duration"]
.mean()
.add(0.0001)
.round(2)
.reset_index()
)
df_free_trial = (
df_grouped[df_grouped["activity_type"] == "free_trial"]
.rename(columns={"activity_duration": "trial_avg_duration"})
.drop(columns=["activity_type"])
)
df_paid = (
df_grouped[df_grouped["activity_type"] == "paid"]
.rename(columns={"activity_duration": "paid_avg_duration"})
.drop(columns=["activity_type"])
)
result = df_free_trial.merge(df_paid, on="user_id", how="inner").sort_values(
"user_id"
)
return resultComplexity analysis
| Approach | Time | Space | When to Use |
|---|---|---|---|
| Separate Aggregations + Equi-Join | O(n) | O(u) | Readable queries, testing subqueries independently |
| Single-Pass Conditional Aggregation | O(n) | O(u) | General case, large event tables, interviews |
Frequently asked questions
Is Analyze Subscription Conversion easy or hard?
It is rated medium. The logic is straightforward once you see that conversion means a user has both a free_trial row and a paid row. Most of the difficulty comes from writing conditional aggregation correctly and rounding the output as required.
How to solve Analyze Subscription Conversion in O(n)?
Group the activity rows by user_id in one pass. Inside the aggregation, use AVG(CASE WHEN activity_type = 'free_trial' THEN activity_duration END) and the matching expression for 'paid'. Filter with HAVING so only users with both values are returned, then round the averages to two decimals.
Analyze Subscription Conversion Python or Java solution?
This is a SQL problem, so the solution is a query rather than Python or Java code. Use the single-pass conditional aggregation query with GROUP BY user_id, AVG with CASE expressions, and HAVING to filter converters. Pandas users can achieve the same result with groupby, pivot-style conditional means, and a filter on non-null values.
What is the best approach for Analyze Subscription Conversion?
The best approach is single-pass conditional aggregation. Group by user_id, compute AVG with CASE expressions for free_trial and paid durations, and keep only users where both averages are non-null using HAVING. This reads the table once and avoids a join.
What data structure is used in Analyze Subscription Conversion?
The problem uses a relational table as input and a grouped result as output. Internally, the database typically builds a hash table keyed by user_id to aggregate averages in a single pass.
What is the time complexity of Analyze Subscription Conversion?
The time complexity is O(n), where n is the number of activity rows, because the table is scanned once for grouping. The space complexity is O(u), where u is the number of distinct users, to hold the per-user aggregates.
Is Analyze Subscription Conversion asked at Google, Amazon, or Meta?
This problem is a database (SQL) question on LeetCode, and company-specific frequency data is not reliably published for it. Database and SQL aggregation questions like this one appear often in data-focused interviews at large tech companies, so practice grouping, conditional aggregation, and joins.