LinkedIn: Active Premium Members per Plan on Quarter End

Easy
725.

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.

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

ColumnTypeValues
subscription_idint
member_idint
planenumCareer, Business, Sales Navigator, Recruiter Lite
start_datedate
end_datedate

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.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

PremiumSubscription

subscription_idmember_idplanstart_dateend_date
1101Career2025-01-10NULL
2102Career2024-11-012025-03-31
3103Career2024-12-012025-03-30
4104Business2025-03-312025-06-30
5101Career2025-03-012025-04-30
6105Sales Navigator2025-04-01NULL
7106Business2024-06-01NULL

Output

planactive_members
Business2
Career2
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: 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

ColumnType
subscription_idint
member_idint
planenum(Career, Business, Sales Navigator, Recruiter Lite)
start_datedate
end_datedate

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_idmember_idplanstart_dateend_date
1101Career2025-01-10NULL
2102Career2024-11-012025-03-31
3103Career2024-12-012025-03-30
4104Business2025-03-312025-06-30
5101Career2025-03-012025-04-30
6105Sales Navigator2025-04-01NULL
7106Business2024-06-01NULL

Output

planactive_members
Business2
Career2

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 plan

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