LinkedIn: Active Premium Members per Plan on Quarter End
Finance closes the quarter by counting how many members held each Premium plan on 2025-03-31.
- Difficulty: Easy
- Topics: Aggregation, Dates
- Dialect: MySQL
- Problem: #725
Problem statement
Finance closes the quarter by counting how many members held each Premium plan on 2025-03-31.
A subscription is active on that day when start_date is on or before 2025-03-31 and end_date is NULL (still running) or on or after 2025-03-31. For each plan with at least one active subscription, return plan and active_members, the number of different members with an active subscription to it (a member with two overlapping rows of one plan counts once). Return the rows in any order.
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: PremiumSubscription
| Column | Type |
|---|---|
| subscription_id | int |
| member_id | int |
| plan | enum(Career, Business, Sales Navigator, Recruiter Lite) |
| start_date | date |
| end_date | date |
Primary key: subscription_id.
One row per subscription period. end_date is the last day of access, or NULL while the subscription is still running.
Examples
Example 1
PremiumSubscription
| subscription_id | member_id | plan | start_date | end_date |
|---|---|---|---|---|
| 1 | 101 | Career | 2025-01-10 | NULL |
| 2 | 102 | Career | 2024-11-01 | 2025-03-31 |
| 3 | 103 | Career | 2024-12-01 | 2025-03-30 |
| 4 | 104 | Business | 2025-03-31 | 2025-06-30 |
| 5 | 101 | Career | 2025-03-01 | 2025-04-30 |
| 6 | 105 | Sales Navigator | 2025-04-01 | NULL |
| 7 | 106 | Business | 2024-06-01 | NULL |
Output
| plan | active_members |
|---|---|
| Business | 2 |
| Career | 2 |
How to solve LinkedIn: Active Premium Members per Plan on Quarter End
A subscription covers a day when the day sits inside its period. The start test is plain (start_date <= '2025-03-31'), but the end has two shapes: a real date, which must be on or after the day, and NULL, which means the subscription is still open and therefore covers every day from its start. Testing end_date >= '2025-03-31' alone would silently drop every open subscription, because a comparison with NULL is never true — hence the OR end_date IS NULL.
Both ends are inclusive: a period ending on 2025-03-31 still covers that day, and one starting on it already does. After the filter, group by plan and count distinct members, since a member who renewed early can have two rows of one plan covering the same day. The alternative turns the open end into a far-future date with COALESCE so that a single BETWEEN does the test, then de-duplicates the (plan, member) pairs in a derived table before counting. Either is one scan.
Reference solution (MySQL)
SELECT plan, COUNT(DISTINCT member_id) AS active_members
FROM PremiumSubscription
WHERE start_date <= '2025-03-31'
AND (end_date IS NULL OR end_date >= '2025-03-31')
GROUP BY planAnother way
SELECT plan, COUNT(*) AS active_members FROM (SELECT DISTINCT plan, member_id FROM PremiumSubscription WHERE '2025-03-31' BETWEEN start_date AND COALESCE(end_date, '9999-12-31')) t GROUP BY plan← LinkedIn: Open-to-Work Members With a Blank Headline · LinkedIn: Skills Endorsed by Three or More Different People →