Strong Friendship

Strong Friendship - Problem

Database Medium

In a social network, friendships are stored in a database table where each row represents a mutual friendship between two users. The challenge is to identify strong friendships - pairs of friends who have a robust social connection.

A friendship between users x and y is considered strong if they have at least three mutual friends. This indicates a deeper social bond beyond just being directly connected.

Table: Friendship

Column Name	Type
user1_id	int
user2_id	int

Key Points:

Each row represents a bidirectional friendship
user1_id < user2_id (normalized format)
Find all strong friendships with at least 3 mutual friends
Return results without duplicates in user1_id < user2_id format

Input & Output

example_1.sql — Basic Strong Friendship

$ Input: Friendship table: +----------+----------+ | user1_id | user2_id | +----------+----------+ | 1 | 2 | | 1 | 3 | | 1 | 4 | | 2 | 3 | | 2 | 4 | | 3 | 4 | +----------+----------+

› Output: +----------+----------+ | user1_id | user2_id | +----------+----------+ | 1 | 2 | | 1 | 3 | | 1 | 4 | | 2 | 3 | | 2 | 4 | | 3 | 4 | +----------+----------+

💡 Note: All pairs are strong friendships because in this complete graph of 4 users, every pair shares exactly 2 mutual friends, but we need at least 3. Actually, let me recalculate: (1,2) share friends 3,4; (1,3) share friends 2,4; (1,4) share friends 2,3; (2,3) share friends 1,4; (2,4) share friends 1,3; (3,4) share friends 1,2. Each pair has exactly 2 mutual friends, so none qualify as strong friendships.

example_2.sql — Larger Network

$ Input: Friendship = [[1,2],[1,3],[1,4],[1,5],[2,3],[2,4],[2,5],[3,4],[3,5]]

› Output: +----------+----------+ | user1_id | user2_id | +----------+----------+ | 1 | 2 | | 1 | 3 | | 2 | 3 | +----------+----------+

💡 Note: Users 1 and 2 share mutual friends 3, 4, 5 (count=3). Users 1 and 3 share mutual friends 2, 4, 5 (count=3). Users 2 and 3 share mutual friends 1, 4, 5 (count=3). All these pairs have exactly 3 mutual friends, meeting the threshold.

example_3.sql — No Strong Friendships

$ Input: Friendship table: +----------+----------+ | user1_id | user2_id | +----------+----------+ | 1 | 2 | | 2 | 3 | | 3 | 4 | | 4 | 1 | +----------+----------+

› Output: Empty result set

💡 Note: This forms a cycle where each user has exactly 2 friends, so no pair of friends can have 3 mutual friends. Maximum possible mutual friends for any pair is 2, which is below the threshold of 3.

Visualization

Tap to expand

Understanding the Visualization

Original Friendships

Start with direct friendship connections in the network

Find Mutual Friends

For each friendship pair, identify their common connections

Count Connections

Count mutual friends for each pair

Filter Strong Bonds

Keep only pairs with 3+ mutual friends

Key Takeaway

🎯 Key Insight: Strong friendships are identified by counting mutual connections efficiently using SQL self-joins, filtering for pairs with 3+ shared friends.

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Optimized joins with database indexes, logarithmic lookup for each friendship

⚡ Linearithmic

Space Complexity

O(n)

Space for intermediate join results and grouping

⚡ Linearithmic Space

Constraints

1 ≤ user1_id, user2_id ≤ 10⁴
user1_id < user2_id (normalized friendship format)
1 ≤ number of friendships ≤ 10⁵
Strong friendship requires ≥ 3 mutual friends

Asked in

f Meta 45 G Google 38 in LinkedIn 52 🐦 Twitter 29

The optimal solution uses self-joins on the friendship table to efficiently find mutual friends. By joining the table with itself twice and using CASE statements to handle bidirectional relationships, we can count mutual friends in a single query and filter for pairs with ≥3 mutual connections.

Common Approaches

Approach	Time	Space	Notes
✓ Self-Join with Union (Optimized)	O(n²)	O(n)	Create bidirectional friendship view, then use self-joins to find mutual friends
Brute Force (Triple Nested Query)	O(n³)	O(1)	Check each friendship pair and count mutual friends with nested queries
Direct Self-Join (Optimal)	O(n log n)	O(n)	Single query with self-joins to directly find strong friendships

Self-Join with Union (Optimized) — Algorithm Steps

Create a union of friendships to handle bidirectional relationships
Self-join the friendship table to find mutual friends
Group by friendship pairs and count mutual connections
Filter results where mutual friend count >= 3

Visualization

Tap to expand

Step-by-Step Walkthrough

Create Bidirectional View

Union friendship table to handle both directions

Self-Join for Mutuals

Join to find common connections

Group and Count

Count mutual friends per friendship pair

Filter Results

Keep pairs with >= 3 mutual friends

Code -

solution.c — C

// Optimized SQL in C
#include <stdio.h>

char* strong_friendship_optimized() {
    static char query[] = 
        "WITH bidirectional_friends AS ("
        "    SELECT user1_id AS user_a, user2_id AS user_b FROM Friendship "
        "    UNION "
        "    SELECT user2_id AS user_a, user1_id AS user_b FROM Friendship "
        "), "
        "mutual_friends AS ("
        "    SELECT f1.user1_id, f1.user2_id, COUNT(DISTINCT bf1.user_b) as mutual_count "
        "    FROM Friendship f1 "
        "    JOIN bidirectional_friends bf1 ON bf1.user_a = f1.user1_id "
        "    JOIN bidirectional_friends bf2 ON bf2.user_a = f1.user2_id AND bf2.user_b = bf1.user_b "
        "    WHERE bf1.user_b != f1.user1_id AND bf1.user_b != f1.user2_id "
        "    GROUP BY f1.user1_id, f1.user2_id "
        ") "
        "SELECT user1_id, user2_id FROM mutual_friends WHERE mutual_count >= 3 ORDER BY user1_id, user2_id;";
    return query;
}

Time & Space Complexity

Time Complexity

⏱️

O(n²)

Self-joins on friendship table, optimized by database indexes

⚠ Quadratic Growth

Space Complexity

O(n)

Space for union table and intermediate results

⚡ Linearithmic Space

Constraints

1 ≤ user1_id, user2_id ≤ 10⁴
user1_id < user2_id (normalized friendship format)
1 ≤ number of friendships ≤ 10⁵
Strong friendship requires ≥ 3 mutual friends

34.2K Views

High Frequency

~25 min Avg. Time

1.4K Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen