Meta-style Company ChallengeMediumVerified answerSQLite live

Page Follower Share

For each page, how many tracked followers do they have in the dataset, and what percentage of all tracked follows does that represent?

  • Joins
  • Subqueries
  • Aggregation
  • Numeric functions
  • NULL handling

Challenge brief

Understand the request

Ads Platform Team is building an audience quality dashboard and needs to know what share of tracked follows each page holds.

Show each page with tracked follower count and its percentage share of all tracked follows.

Return

  • page_name
  • followers_count (platform total)
  • tracked_followers (count in page_followers table)
  • follower_share_pct (% of all tracked follows, rounded 2)

Constraints

  • Include pages with no tracked follower rows
  • Share = page tracked follows / all tracked follows * 100
  • A dataset with no tracked follows must not cause division by zero
  • Show more tracked followers first; resolve ties by page ID

Data you will use

Review the relevant tables before deciding how to join, filter, or aggregate them.

pages

  • page_idINTEGER
  • page_nameVARCHAR(100)
  • followers_countINTEGER

page_followers

  • follow_idINTEGER
  • page_idINTEGER

Hints, when you need them

Open one clue at a time so you still do the reasoning.

Hint 1

Page names are in pages. Tracked individual follows are in page_followers. You need per-page tracked count AND the total across all pages as a percentage denominator. Use a scalar subquery for the total.

Hint 2

LEFT JOIN pages to page_followers. GROUP BY page. COUNT(pf.follow_id) for tracked. follower_share_pct = COUNT * 100.0 / (SELECT COUNT(*) FROM page_followers).

Hint 3

Start with the tables that establish the result grain for question 21, select the required output aliases, and add the remaining joins, filters, aggregation, and ordering one clause at a time.

Verified SQL answer

Attempt the problem first, then compare structure and reasoning—not just syntax.

Reveal solution and explanation
SELECT p.page_name, p.followers_count, COUNT(pf.follow_id) AS tracked_followers, ROUND(COUNT(pf.follow_id) * 100.0 / NULLIF((SELECT COUNT(*) FROM page_followers), 0), 2) AS follower_share_pct FROM pages p LEFT JOIN page_followers pf ON p.page_id = pf.page_id GROUP BY p.page_id, p.page_name, p.followers_count ORDER BY tracked_followers DESC, p.page_id ASC

Why this works

Start from pages so zero-follow rows survive, count only matching follow identifiers, and protect the global denominator with NULLIF.

Success check

All 4 pages appear, including Community Health with zero tracked followers, and shares use a protected denominator.

Expected result

Use this output to verify values, aliases, ordering, and row count.

page_namefollowers_counttracked_followersfollower_share_pct
Tech News Daily5000250
Design Inspiration3200125
AI Research Hub8500125
Community Health000

Learn the concepts behind this answer

Strengthen your understanding with these targeted learning topics:

Continue practicing

Explore related company challenges

SQL Interview Practice →

Return to the complete interview preparation experience.