Google: Common Friends Script

You are analyzing a social network dataset at Google. Your task is to find mutual friends between two users, Karl and Hans. There is only one user named Karl and one named Hans in the dataset.

The output should contain 'user_id' and 'user_name' columns.

This intermediate-level SQL interview question from Google is designed to assess your ability to manipulate and analyze complex relational databases, specifically focusing on finding mutual connections within a social network dataset.

Approach Hints:

1. Understanding WITH Clause: Learn how the WITH clause (Common Table Expression or CTE) is used to simplify complex joins and subqueries.
2. Utilizing INTERSECT: INTERSECT is key in finding common elements between two sets. Here, it helps identify mutual friends between Karl and Hans.
3. Subquery in WHERE Clause: Note how subqueries within the WHERE clause are used to fetch user_id for both Karl and Hans from the users table.
4. Join Operations: The final step involves joining the CTE results with the users table to fetch the user names of mutual friends.

Take the challenge! ??

Mastering such queries not only boosts your problem-solving skills but is also crucial in handling real-life data scenarios in tech roles, especially in companies like Google.

We encourage you to test out this query, tweak it, and maybe even optimize it. Understanding the underlying concepts will greatly enhance your SQL fluency and prepare you for similar challenges.

Happy querying!