LinkedIn: Followers Across Each Company's Affiliated Pages

Hard
742.

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. 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

ColumnType
page_idint
page_namevarchar
parent_page_idint

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

ColumnType
member_idint
page_idint

Primary key (together): member_id, page_id.

A member following a page.

Example

The rows must come back in the order shown.

CompanyPage

page_idpage_nameparent_page_id
1Northwind LabsNULL
2Northwind Cloud1
3Northwind Cloud India2
4Northwind Careers1
5Saffron FintechNULL
6Saffron Pay5
7Cedar RoboticsNULL

PageFollow

member_idpage_id
211
213
223
234
245
246
256
266
216

Output

page_idpage_nameaffiliated_pagestotal_followsunique_followers
5Saffron Fintech154
1Northwind Labs343
7Cedar Robotics000
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: 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

ColumnType
page_idint
page_namevarchar
parent_page_idint

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

ColumnType
member_idint
page_idint

Primary key: member_id, page_id.

A member following a page.

Examples

Example 1

CompanyPage

page_idpage_nameparent_page_id
1Northwind LabsNULL
2Northwind Cloud1
3Northwind Cloud India2
4Northwind Careers1
5Saffron FintechNULL
6Saffron Pay5
7Cedar RoboticsNULL

PageFollow

member_idpage_id
211
213
223
234
245
246
256
266
216

Output

page_idpage_nameaffiliated_pagestotal_followsunique_followers
5Saffron Fintech154
1Northwind Labs343
7Cedar Robotics000

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_id

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