LinkedIn: Running Follower Count of Company Pages

Medium
739.

LinkedIn: Running Follower Count of Company Pages

Company page admins see a follower graph that moves day by day. Rebuild it from the raw follow and unfollow events, starting from zero followers.

For each page and each day on which that page had at least one event, return page_id, event_on, net_change (follows minus unfollows that day) and followers (the running total of net changes up to and including that day). Order the rows by page_id, then event_on.

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: PageFollowEvent

ColumnTypeValues
event_idint
page_idint
event_ondate
event_typeenumfollow, unfollow

Primary key: event_id.

One row per follow or unfollow of a company page; each page started with no followers.

Example

The rows must come back in the order shown.

PageFollowEvent

event_idpage_idevent_onevent_type
112025-05-01follow
212025-05-01follow
312025-05-02unfollow
412025-05-04follow
512025-05-04unfollow
622025-05-02follow
722025-05-03follow
822025-05-03follow

Output

page_idevent_onnet_changefollowers
12025-05-0122
12025-05-02-11
12025-05-0401
22025-05-0211
22025-05-0323
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: Running Follower Count of Company Pages

Company page admins see a follower graph that moves day by day. Rebuild it from the raw follow and unfollow events, starting from zero followers.

  • Difficulty: Medium
  • Topics: Window Functions, Aggregation
  • Dialect: MySQL
  • Problem: #739

Problem statement

Company page admins see a follower graph that moves day by day. Rebuild it from the raw follow and unfollow events, starting from zero followers.

For each page and each day on which that page had at least one event, return page_id, event_on, net_change (follows minus unfollows that day) and followers (the running total of net changes up to and including that day). Order the rows by page_id, then event_on.

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: PageFollowEvent

ColumnType
event_idint
page_idint
event_ondate
event_typeenum(follow, unfollow)

Primary key: event_id.

One row per follow or unfollow of a company page; each page started with no followers.

Examples

Example 1

PageFollowEvent

event_idpage_idevent_onevent_type
112025-05-01follow
212025-05-01follow
312025-05-02unfollow
412025-05-04follow
512025-05-04unfollow
622025-05-02follow
722025-05-03follow
822025-05-03follow

Output

page_idevent_onnet_changefollowers
12025-05-0122
12025-05-02-11
12025-05-0401
22025-05-0211
22025-05-0323

How to solve LinkedIn: Running Follower Count of Company Pages

Two aggregations happen at different levels. First the events are collapsed per page and day: a CASE maps each follow to +1 and each unfollow to −1, and SUM gives the day's net change. That runs in a CTE, so the second step sees exactly one row per page and day.

The second step is a running total: SUM(net_change) OVER (PARTITION BY page_id ORDER BY event_on) adds up every day of the page from its first event to the current one. Partitioning restarts the total for each page. Since the daily rows have distinct dates within a page, the default window frame (everything up to the current row's date) includes each day once; had the window run over the raw events, rows sharing a date would all see the same total under the default RANGE frame, and the output would have one row per event rather than per day.

The alternative computes the running total with a correlated subquery that sums every event of the page up to that date — simpler to read but quadratic in the number of days; the window version sorts each page once.

Reference solution (MySQL)

WITH daily AS (
  SELECT page_id, event_on,
         SUM(CASE WHEN event_type = 'follow' THEN 1 ELSE -1 END) AS net_change
  FROM PageFollowEvent
  GROUP BY page_id, event_on
)
SELECT page_id, event_on, net_change,
       SUM(net_change) OVER (PARTITION BY page_id ORDER BY event_on) AS followers
FROM daily
ORDER BY page_id, event_on

Another way

SELECT d.page_id, d.event_on, d.net_change,
       (SELECT SUM(IF(x.event_type = 'follow', 1, -1)) FROM PageFollowEvent x WHERE x.page_id = d.page_id AND x.event_on <= d.event_on) AS followers
FROM (SELECT page_id, event_on, SUM(IF(event_type = 'follow', 1, -1)) AS net_change FROM PageFollowEvent GROUP BY page_id, event_on) d
ORDER BY d.page_id, d.event_on

← LinkedIn: Premium Free-Trial Conversion by Trial Month · LinkedIn: Members With Exactly the Same Skill Set →