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
| Column | Type |
|---|---|
| event_id | int |
| page_id | int |
| event_on | date |
| event_type | enum(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_id | page_id | event_on | event_type |
|---|---|---|---|
| 1 | 1 | 2025-05-01 | follow |
| 2 | 1 | 2025-05-01 | follow |
| 3 | 1 | 2025-05-02 | unfollow |
| 4 | 1 | 2025-05-04 | follow |
| 5 | 1 | 2025-05-04 | unfollow |
| 6 | 2 | 2025-05-02 | follow |
| 7 | 2 | 2025-05-03 | follow |
| 8 | 2 | 2025-05-03 | follow |
Output
| page_id | event_on | net_change | followers |
|---|---|---|---|
| 1 | 2025-05-01 | 2 | 2 |
| 1 | 2025-05-02 | -1 | 1 |
| 1 | 2025-05-04 | 0 | 1 |
| 2 | 2025-05-02 | 1 | 1 |
| 2 | 2025-05-03 | 2 | 3 |
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_onAnother 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 →