Interview Questions/SQL/Influence Score via 3-Iteration PageRank

Influence Score via 3-Iteration PageRank

Preview mode. Log in to edit, run, submit, and save progress.

Medium

Influence Score via 3-Iteration PageRank

You are given a Follows table representing a directed social graph. Using chained iterative CTEs (iter0 → iter1 → iter2 → iter3), compute a simplified PageRank influence score for each user after exactly 3 iterations. Rules: - All users start with score = 1.0 (iter0) - Each iteration: score(u) = 0.15 + 0.85 × SUM(score(v) / out_degree(v)) for all v that follow u - Users with no followers keep receiving only the damping base: 0.15 - ROUND all scores to 6 decimal places Return (user_id, influence_score) ordered by influence_score DESC, then user_id. Table: Follows

Column Name	Type	Description
follower_id	INT	The user who follows
followee_id	INT	The user being followed

Examples

Example 1

Build out_degrees from the Follows table. Create iter0 with score=1.0 for every user. Compute iter1, iter2, iter3 each as a LEFT JOIN of the previous iteration's scores weighted by the follower's out_degree. Users 4 and 2 have high in-degree so accumulate higher scores.

Approach hint

Start with a simple approach, explain the trade-off, then move toward a cleaner or more scalable solution.

Common mistake

Skipping assumptions, edge cases, or trade-offs can make an otherwise good answer feel incomplete.

query.sql