Mar 19, 2025

Exploring Network Connections with RECURSIVE CTEs in SQL

Written By: Stephan Welzel

🚀 Exploring Network Connections with RECURSIVE CTEs in SQL 🌐

Ever wondered how to not only find who’s following a user in a social network, but also uncover their “followers of followers” and beyond? That’s where the magic of RECURSIVE CTEs (Common Table Expressions) comes into play! Let’s take a look at how you can track multi-level relationships, like second or third connections, without writing endless JOINs. In the code block is an example that retrieves users up to 3 hops away from a given user (User_name):

WITH RECURSIVE followers AS (

	-- 🍀 Level 1: Direct followers of the target user

	SELECT    
			follower_id,
			followee_id,
			followee_id::TEXT AS path,
			1 AS hierarchy_level
	FROM follows
	WHERE followee_id = (
												SELECT user_id
												FROM users
												WHERE user_name = 'User_name' 
											)
	
	UNION ALL
	
	-- 🍀 Level 2, 3, and beyond: Recursively find followers of followers
	
	SELECT 
			f.follower_id,
			f.followee_id,
			cte.path || ', ' || f.followee_id AS path,
			hierarchy_level + 1
	FROM follows f
	JOIN followers cte
		ON f.followee_id = cte.follower_id
	WHERE hierarchy_level < 4

)

SELECT 
		follower_id,
		path
FROM followers 
ORDER BY 1

🧬 How It Works:

Level 1 (Anchor Query): ⭐ The first query finds the direct followers of the given user (User_name).
Level 2 and Beyond (Recursive Query): Each iteration builds on the previous results, finding “followers of followers” and extending the chain.
Limit the Depth: You can control how far the query goes by changing the condition (WHERE hierarchy_level < 4) to explore deeper connections.

🏗️ Table Setup:

To run this query, you need two simple tables:

users table: Stores user information.
- Columns:
  - user_id: Unique ID for each user.
  - user_name: The name of the user.
follows table: Represents who follows whom in the network.
- Columns:
  - follower_id: The user following another user.
  - followee_id: The user being followed.

🔗 How path Works:

The path column in the recursive part of the query helps track the connection sequence (like a breadcrumb trail of who’s following whom).Explanation of ct.path || ', ' || f.followee_id:

cte.path: The current path of connections from the anchor query.
|| ', ' || f.followee_id: Appends the next user ID (f.followee_id) to the existing path, separated by a comma.
Example: If path = 1, 2 and the next follower is 3, the new path becomes 1, 2, 3.

💡But be cautious using more hops! As the depth increases, the number of connections grows exponentially, potentially consuming significant server resources. For large datasets, consider optimizing your queries or setting practical limits to balance performance and insights. 🚀