LinkedIn: Premium Free-Trial Conversion by Trial Month
Premium offers a free trial, and the subscriptions team tracks how many trials turn into a paid plan soon after they start.
- Difficulty: Medium
- Topics: Aggregation, Dates, Conditional Logic
- Dialect: MySQL
- Problem: #738
Problem statement
Premium offers a free trial, and the subscriptions team tracks how many trials turn into a paid plan soon after they start.
A trial converts when paid_on is not NULL and is at most 30 days after trial_start. For each month in which trials started, return trial_month (as 'YYYY-MM'), trials, converted and conversion_pct (converted ÷ trials × 100, rounded to one decimal place). Order the rows by trial_month.
A practice scenario set at LinkedIn: the tables and data are invented, and this is not a question from, or endorsed by, LinkedIn.
Tables
Table: PremiumTrial
| Column | Type |
|---|---|
| trial_id | int |
| member_id | int |
| trial_start | date |
| paid_on | date |
Primary key: trial_id.
One row per free trial. paid_on is the date of the first paid charge after the trial, or NULL if the member never paid.
Examples
Example 1
PremiumTrial
| trial_id | member_id | trial_start | paid_on |
|---|---|---|---|
| 1 | 41 | 2025-01-03 | 2025-02-02 |
| 2 | 42 | 2025-01-15 | 2025-02-15 |
| 3 | 43 | 2025-01-20 | NULL |
| 4 | 44 | 2025-02-01 | 2025-02-20 |
| 5 | 45 | 2025-02-10 | 2025-02-11 |
| 6 | 46 | 2025-02-28 | NULL |
| 7 | 47 | 2025-03-05 | 2025-05-01 |
Output
| trial_month | trials | converted | conversion_pct |
|---|---|---|---|
| 2025-01 | 3 | 1 | 33.3 |
| 2025-02 | 3 | 2 | 66.7 |
| 2025-03 | 1 | 0 | 0 |
How to solve LinkedIn: Premium Free-Trial Conversion by Trial Month
Every trial belongs to the month it started in, labelled with DATE_FORMAT(trial_start, '%Y-%m'); grouping by that label gives one row per month. Within a month, COUNT(*) is the number of trials and a conditional SUM counts the conversions — a payment exists and arrived within 30 days, inclusive, of the start. The percentage divides the two and rounds last.
NULL needs thought. A trial that never paid has paid_on NULL; it must stay in the denominator but add 0 to the numerator. The CASE does this explicitly. In the alternative, IF(paid_on <= DATE_ADD(trial_start, INTERVAL 30 DAY), 1, 0) gets the same result implicitly: the comparison with NULL is unknown, and IF treats unknown as false. That version builds the month label with LEFT(trial_start, 7) in a derived table first, which also avoids repeating the condition.
Payments 31 days after the start, like trial 2 in the example, do not count. The query is one grouped scan; with an index on trial_start a date-range filter would narrow it further.
Reference solution (MySQL)
SELECT DATE_FORMAT(trial_start, '%Y-%m') AS trial_month,
COUNT(*) AS trials,
SUM(CASE WHEN paid_on IS NOT NULL AND DATEDIFF(paid_on, trial_start) <= 30 THEN 1 ELSE 0 END) AS converted,
ROUND(100 * SUM(CASE WHEN paid_on IS NOT NULL AND DATEDIFF(paid_on, trial_start) <= 30 THEN 1 ELSE 0 END) / COUNT(*), 1) AS conversion_pct
FROM PremiumTrial
GROUP BY trial_month
ORDER BY trial_monthAnother way
SELECT trial_month, COUNT(*) AS trials, SUM(ok) AS converted, ROUND(100 * SUM(ok) / COUNT(*), 1) AS conversion_pct
FROM (SELECT LEFT(trial_start, 7) AS trial_month, IF(paid_on <= DATE_ADD(trial_start, INTERVAL 30 DAY), 1, 0) AS ok FROM PremiumTrial) t
GROUP BY trial_month
ORDER BY trial_month← LinkedIn: Applicants Holding Every Required Skill · LinkedIn: Running Follower Count of Company Pages →