LinkedIn: Followers Across Each Company's Affiliated Pages
A company on LinkedIn can sit at the top of a tree of pages: showcase pages for its products, pages for its regional arms, and showcase pages under those.
- Difficulty: Hard
- Topics: Subqueries, Joins, Aggregation
- Dialect: MySQL
- Problem: #742
Problem statement
A company on LinkedIn can sit at the top of a tree of pages: showcase pages for its products, pages for its regional arms, and showcase pages under those. The Pages team reports each company's reach across its whole tree.
For every top-level page (parent_page_id NULL), return page_id, page_name, affiliated_pages (the number of pages beneath it at any depth, not counting itself), total_follows (follows of the page itself and every page beneath it) and unique_followers (the number of different members following at least one page of the tree — a member following three pages of one company counts once). Trees without follows show 0. Order the rows by unique_followers descending, then page_id ascending.
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: CompanyPage
| Column | Type |
|---|---|
| page_id | int |
| page_name | varchar |
| parent_page_id | int |
Primary key: page_id.
One row per page. parent_page_id is the page this one sits under, or NULL for a company's top-level page; it always names an existing page and the parent links never loop.
Table: PageFollow
| Column | Type |
|---|---|
| member_id | int |
| page_id | int |
Primary key: member_id, page_id.
A member following a page.
Examples
Example 1
CompanyPage
| page_id | page_name | parent_page_id |
|---|---|---|
| 1 | Northwind Labs | NULL |
| 2 | Northwind Cloud | 1 |
| 3 | Northwind Cloud India | 2 |
| 4 | Northwind Careers | 1 |
| 5 | Saffron Fintech | NULL |
| 6 | Saffron Pay | 5 |
| 7 | Cedar Robotics | NULL |
PageFollow
| member_id | page_id |
|---|---|
| 21 | 1 |
| 21 | 3 |
| 22 | 3 |
| 23 | 4 |
| 24 | 5 |
| 24 | 6 |
| 25 | 6 |
| 26 | 6 |
| 21 | 6 |
Output
| page_id | page_name | affiliated_pages | total_follows | unique_followers |
|---|---|---|---|---|
| 5 | Saffron Fintech | 1 | 5 | 4 |
| 1 | Northwind Labs | 3 | 4 | 3 |
| 7 | Cedar Robotics | 0 | 0 | 0 |
How to solve LinkedIn: Followers Across Each Company's Affiliated Pages
Pages form a tree of unknown depth — a showcase page under a regional page under the company — so no fixed chain of self joins is enough. A recursive CTE walks it: the anchor selects the top-level pages and labels each with itself as root_id; the recursive step adds every page whose parent is already in the result, copying the parent's root_id. When no new children are found the recursion stops, and every page appears once, tagged with the company tree it belongs to.
The rest is aggregation per root. COUNT(DISTINCT t.page_id) - 1 counts the pages of the tree minus the root itself. The LEFT JOIN to PageFollow keeps pages nobody follows, so a tree without follows still forms a group and gets 0; COUNT(f.member_id) counts follows, and COUNT(DISTINCT f.member_id) counts people, which is the number that matters for reach — in the example, member 21 follows two Northwind pages and is counted once there, and also counts once for Saffron.
The alternative walks upwards instead: starting from every page, it follows parent links until it reaches a page with no parent, which is that page's root, and then aggregates per root in separate CTEs. The downward walk visits each page once; the upward one visits each page once per ancestor, so it costs more on deep trees.
Reference solution (MySQL)
WITH RECURSIVE tree AS (
SELECT page_id AS root_id, page_id
FROM CompanyPage
WHERE parent_page_id IS NULL
UNION ALL
SELECT t.root_id, c.page_id
FROM tree t
JOIN CompanyPage c ON c.parent_page_id = t.page_id
)
SELECT r.page_id, r.page_name,
COUNT(DISTINCT t.page_id) - 1 AS affiliated_pages,
COUNT(f.member_id) AS total_follows,
COUNT(DISTINCT f.member_id) AS unique_followers
FROM CompanyPage r
JOIN tree t ON t.root_id = r.page_id
LEFT JOIN PageFollow f ON f.page_id = t.page_id
GROUP BY r.page_id, r.page_name
ORDER BY unique_followers DESC, r.page_idAnother way
WITH RECURSIVE up AS (
SELECT page_id, page_id AS cur, parent_page_id FROM CompanyPage
UNION ALL
SELECT u.page_id, p.page_id, p.parent_page_id FROM up u JOIN CompanyPage p ON p.page_id = u.parent_page_id
), root_of AS (
SELECT page_id, cur AS root_id FROM up WHERE parent_page_id IS NULL
), sizes AS (
SELECT root_id, COUNT(*) - 1 AS aff FROM root_of GROUP BY root_id
), reach AS (
SELECT o.root_id, COUNT(*) AS follows, COUNT(DISTINCT f.member_id) AS uniq
FROM PageFollow f JOIN root_of o ON o.page_id = f.page_id GROUP BY o.root_id
)
SELECT r.page_id, r.page_name, s.aff AS affiliated_pages, COALESCE(a.follows, 0) AS total_follows, COALESCE(a.uniq, 0) AS unique_followers
FROM CompanyPage r
JOIN sizes s ON s.root_id = r.page_id
LEFT JOIN reach a ON a.root_id = r.page_id
WHERE r.parent_page_id IS NULL
ORDER BY unique_followers DESC, r.page_id← LinkedIn: Mutual Connections Behind Pending Invitations · LinkedIn: Applications Year over Year by Company and Quarter →