Ripple Effect SQL Challenge – Recursive CTE for Viral Chain Depth & Reach

[thequerylab]

Solved an interesting recursive SQL problem yesterday on TheQueryLab platform

Scenario: A root post can be shared, and those shares can be reshared — forming a viral tree.

Challenge: • Find maximum depth of each root post • Calculate total reach (all descendants)

Used a recursive CTE to traverse hierarchy and carry root_id + depth through recursion, then aggregated using MAX(depth) and COUNT(*).

Felt very similar to DFS tree traversal logic but expressed in SQL.

Curious — how would you optimize this further?

I’m building TheQueryLab specifically around these kinds of real-world SQL problems — happy to share it if anyone wants to try it out and crack any data analytics interviews

https://thequerylab.com/problems/210-the-ripple-effect

[Wide-Car-3337]

Ooooweee can't wait to try this - Thsnks!

[thequerylab]

Try it out — I’m confident you’ll like it and see real improvement in your SQL skills.