Influence Score via 3-Iteration PageRank

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

Medium

Description

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

Database Schema (Inferred)

Follows

Column Name	Example Value
follower_id	1
followee_id	2

Example

Follows

follower_id	followee_id
1	2
1	3
2	3
2	4
3	4
4	1
4	2

Output

user_id	influence_score
4	1.271469
2	1.076766
3	0.896141
1	0.755625

Explanation:

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.