The Ripple Effect

20pts

Tik Tok, X, Meta

Problem Statement

When a "Root Post" is created, it can be shared by others. Those shares can then be shared again, creating a Viral Chain.

To measure the "Viral Velocity," the marketing team needs to know the Maximum Depth (how many levels deep the chain went) and the Total Reach (total number of shares) for every Root Post.

The Challenge

Find the viral metrics for every post_id that is a "Root Post" (where parent_post_id is NULL).

Rules:

A Root Post itself is Level 0.
A direct share of a Root Post is Level 1.
A share of a Level 1 share is Level 2, and so on.
Output: root_post_id, max_depth, and total_shares.
Order by max_depth DESC, then total_shares DESC.

Tests your understanding of

Recursion, CTE, Graph Traversal and Tree Structures

Input Tables

posts

post_id(INTEGER)	parent_post_id(INTEGER)	user_id(INTEGER)
1	null	10
2	1	11
3	1	12
4	2	13
5	2	14
6	4	15
7	6	16
8	3	17
100	null	20
101	100	21
102	100	22
500	null	30

Expected Output

root_post_id(INTEGER)	max_depth(INTEGER)	total_shares(INTEGER)
1	4	7
100	1	2
500	0	0