LinkedIn: Premium Free-Trial Conversion by Trial Month

Medium
738.

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.

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

ColumnType
trial_idint
member_idint
trial_startdate
paid_ondate

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.

Example

The rows must come back in the order shown.

PremiumTrial

trial_idmember_idtrial_startpaid_on
1412025-01-032025-02-02
2422025-01-152025-02-15
3432025-01-20NULL
4442025-02-012025-02-20
5452025-02-102025-02-11
6462025-02-28NULL
7472025-03-052025-05-01

Output

trial_monthtrialsconvertedconversion_pct
2025-013133.3
2025-023266.7
2025-03100
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
trial_idint
member_idint
trial_startdate
paid_ondate

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_idmember_idtrial_startpaid_on
1412025-01-032025-02-02
2422025-01-152025-02-15
3432025-01-20NULL
4442025-02-012025-02-20
5452025-02-102025-02-11
6462025-02-28NULL
7472025-03-052025-05-01

Output

trial_monthtrialsconvertedconversion_pct
2025-013133.3
2025-023266.7
2025-03100

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_month

Another 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 →