Self joins in Apache Spark - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ Predict Output

intermediate

Output of a self join with aliasing

What is the output of the following Apache Spark code snippet that performs a self join on a DataFrame of employees to find pairs of employees working in the same department?

Apache Spark

from pyspark.sql import SparkSession
from pyspark.sql.functions import col

spark = SparkSession.builder.getOrCreate()
data = [(1, "Alice", "HR"), (2, "Bob", "HR"), (3, "Charlie", "IT"), (4, "David", "IT"), (5, "Eve", "Finance")]
columns = ["id", "name", "dept"]
df = spark.createDataFrame(data, columns)
df1 = df.alias("df1")
df2 = df.alias("df2")
joined = df1.join(df2, (col("df1.dept") == col("df2.dept")) & (col("df1.id") < col("df2.id")), "inner")
result = joined.select(col("df1.name").alias("emp1"), col("df2.name").alias("emp2"))
result.show()

A [Row(emp1='Alice', emp2='Bob'), Row(emp1='Charlie', emp2='David')]

B [Row(emp1='Alice', emp2='Bob'), Row(emp1='Bob', emp2='Alice'), Row(emp1='Charlie', emp2='David'), Row(emp1='David', emp2='Charlie')]

C [Row(emp1='Bob', emp2='Alice'), Row(emp1='David', emp2='Charlie')]

D [Row(emp1='Alice', emp2='Eve'), Row(emp1='Charlie', emp2='David')]

💡 Hint

Look at the join condition carefully, especially the part with id comparison.

The join matches employees in the same department but only pairs where df1.id < df2.id to avoid duplicate pairs and self-pairing. So Alice-Bob and Charlie-David pairs appear once each.

❓ data_output

intermediate

Number of rows after self join with filter

Given a DataFrame of products with columns (product_id, category), what is the number of rows in the result after performing a self join on category with the condition product_id from left < product_id from right?

Apache Spark

from pyspark.sql import SparkSession
from pyspark.sql.functions import col

spark = SparkSession.builder.getOrCreate()
data = [(101, "A"), (102, "A"), (103, "B"), (104, "B"), (105, "B"), (106, "C")]
columns = ["product_id", "category"]
df = spark.createDataFrame(data, columns)
df1 = df.alias("df1")
df2 = df.alias("df2")
joined = df1.join(df2, (col("df1.category") == col("df2.category")) & (col("df1.product_id") < col("df2.product_id")), "inner")
count = joined.count()
print(count)

A 6

B 4

C 5

D 3

💡 Hint

Count pairs per category where left product_id is less than right product_id.

Category A has 2 products → 1 pair (101,102). Category B has 3 products → 3 pairs (103,104), (103,105), (104,105). Category C has 1 product → 0 pairs. Total pairs = 1 + 3 + 0 = 4.

🔧 Debug

advanced

Identify the error in self join code

What error does the following Apache Spark code raise when trying to perform a self join on a DataFrame of users to find pairs with the same city?

Apache Spark

from pyspark.sql import SparkSession
from pyspark.sql.functions import col

spark = SparkSession.builder.getOrCreate()
data = [(1, "John", "NY"), (2, "Jane", "LA"), (3, "Doe", "NY")]
columns = ["user_id", "name", "city"]
df = spark.createDataFrame(data, columns)
joined = df.join(df, df.city == df.city, "inner")
joined.show()

A AnalysisException: Reference 'city' is ambiguous, could be: city, city.

B ValueError: Join condition must be a boolean expression.

C TypeError: Column is not iterable.

D No error, outputs all pairs of users.

💡 Hint

Think about what happens when you join a DataFrame with itself without aliasing.

Joining a DataFrame with itself without aliasing causes ambiguity because Spark cannot distinguish which 'city' column is from which side. This raises an AnalysisException about ambiguous references.

🚀 Application

advanced

Use self join to find manager-subordinate pairs

You have an employee DataFrame with columns (emp_id, name, manager_id). How would you use a self join to find each employee's manager's name?

Apache Spark

from pyspark.sql import SparkSession
from pyspark.sql.functions import col

spark = SparkSession.builder.getOrCreate()
data = [(1, "Alice", None), (2, "Bob", 1), (3, "Charlie", 1), (4, "David", 2)]
columns = ["emp_id", "name", "manager_id"]
df = spark.createDataFrame(data, columns)
df_emp = df.alias("emp")
df_mgr = df.alias("mgr")
joined = df_emp.join(df_mgr, col("emp.manager_id") == col("mgr.emp_id"), "left")
result = joined.select(col("emp.name").alias("employee"), col("mgr.name").alias("manager"))
result.show()

A [Row(employee='Alice', manager=None), Row(employee='Bob', manager=None), Row(employee='Charlie', manager=None), Row(employee='David', manager=None)]

B [Row(employee='Alice', manager='Bob'), Row(employee='Bob', manager='Charlie'), Row(employee='Charlie', manager='David'), Row(employee='David', manager=None)]

C [Row(employee='Alice', manager=None), Row(employee='Bob', manager='Alice'), Row(employee='Charlie', manager='Alice'), Row(employee='David', manager='Bob')]

D [Row(employee='Alice', manager='Alice'), Row(employee='Bob', manager='Bob'), Row(employee='Charlie', manager='Charlie'), Row(employee='David', manager='David')]

💡 Hint

Match employee's manager_id with manager's emp_id using aliases.

The self join matches each employee's manager_id with the emp_id of the manager. Alice has no manager, Bob and Charlie's manager is Alice, David's manager is Bob.

🧠 Conceptual

expert

Why use self join with condition on IDs?

Why do we often add a condition like df1.id < df2.id when performing a self join to find pairs of related rows?

A To join only rows with identical IDs.

B To increase the number of pairs by including reversed duplicates.

C To filter out all pairs where IDs are equal, but allow reversed pairs.

D To avoid duplicate pairs and self-pairing by ensuring each pair is unique and ordered.

💡 Hint

Think about how pairs like (1,2) and (2,1) relate and how to avoid counting both.

The condition df1.id < df2.id ensures that for each pair, only one ordering is included, preventing duplicates like (1,2) and (2,1) and also excluding self-pairs where IDs are equal.

Practice

1.

What is the main purpose of a self join in Apache Spark?

easy

A. To join a DataFrame with itself to compare rows within the same dataset

B. To join two different DataFrames based on a common key

C. To merge two DataFrames vertically by stacking rows

D. To filter rows based on a condition

Solution

1. Step 1: Understand the concept of self join

   A self join means joining a DataFrame to itself to compare or relate rows within the same dataset.

2. Step 2: Differentiate from other joins

   Other joins combine different DataFrames or filter rows, but self join specifically uses the same DataFrame twice.

3. Final Answer:

   To join a DataFrame with itself to compare rows within the same dataset -> Option A

4. Quick Check:

   Self join = join DataFrame to itself [OK]

Hint: Self join means joining the same table twice [OK]

Common Mistakes:

• Confusing self join with joining different DataFrames
• Thinking self join stacks rows vertically
• Assuming self join is just filtering rows

2.

Which of the following is the correct way to create aliases for a self join in Spark using DataFrame API?

df1 = df.alias('a')
df2 = df.alias('b')
joined = df1.join(df2, ...)

easy

A. df1 = df.alias('a'); df2 = df.alias('b'); joined = df1.join(df2, 'id')

B. df1 = df; df2 = df; joined = df1.join(df2, 'id')

C. df1 = df.as('a'); df2 = df.as('b'); joined = df1.join(df2, 'id')

D. df1 = df.copy(); df2 = df.copy(); joined = df1.join(df2, 'id')

Solution

1. Step 1: Use alias() method for self join

   In Spark, to join a DataFrame to itself, you create aliases using alias() to distinguish the two references.

2. Step 2: Check the syntax correctness

   df1 = df.alias('a'); df2 = df.alias('b'); joined = df1.join(df2, 'id') uses alias() correctly and joins on a column 'id'. df1 = df.as('a'); df2 = df.as('b'); joined = df1.join(df2, 'id') uses as() which is not a valid DataFrame method in Spark.

3. Final Answer:

   df1 = df.alias('a'); df2 = df.alias('b'); joined = df1.join(df2, 'id') -> Option A

4. Quick Check:

   Use alias() for self join [OK]

Hint: Use alias() to rename DataFrame before self join [OK]

Common Mistakes:

• Using as() instead of alias()
• Not creating aliases and causing ambiguous columns
• Copying DataFrame instead of aliasing

3.

Given the following Spark code, what will be the output count?

from pyspark.sql import SparkSession
spark = SparkSession.builder.getOrCreate()
data = [(1, 'A'), (2, 'B'), (3, 'A')]
df = spark.createDataFrame(data, ['id', 'group'])
df1 = df.alias('df1')
df2 = df.alias('df2')
joined = df1.join(df2, (df1.group == df2.group) & (df1.id != df2.id))
print(joined.count())

medium

A. 2

B. 4

C. 3

D. 6

Solution

1. Step 1: Understand the data and join condition

   Data has 3 rows: (1, 'A'), (2, 'B'), (3, 'A'). The join matches rows with the same group but different ids.

2. Step 2: Count matching pairs

   Group 'A' has ids 1 and 3, so pairs are (1,3) and (3,1) = 2 matches. Group 'B' has only one id, so no match. Total matches = 2 rows. However, since the join is a cross join on the condition, each pair appears twice (once as (1,3) and once as (3,1)), so total count is 4.

3. Final Answer:

   4 -> Option B

4. Quick Check:

   Count matching pairs with condition = 4 [OK]

Hint: Count pairs with same group but different ids [OK]

Common Mistakes:

• Counting only one direction of pairs
• Including rows where id equals
• Ignoring symmetric pairs

4.

Identify the error in this Spark self join code snippet:

df1 = df.alias('a')
df2 = df.alias('b')
joined = df1.join(df2, df1.id == df2.id)
joined.show()

What is the problem?

medium

A. Syntax error in join condition

B. Missing alias() method for DataFrame

C. The join condition causes each row to join with itself, no filtering

D. DataFrame df is not defined

Solution

1. Step 1: Analyze the join condition

   The join condition is df1.id == df2.id, which matches rows with the same id, including the same row joined to itself.

2. Step 2: Identify missing filter to exclude self matches

   There is no condition to exclude rows where the two aliases refer to the same row, so the join includes self matches.

3. Final Answer:

   The join condition causes each row to join with itself, no filtering -> Option C

4. Quick Check:

   Join condition includes self matches [OK]

Hint: Add filter to exclude self matches in self join [OK]

Common Mistakes:

• Not excluding rows where both aliases are same
• Assuming alias() is missing
• Confusing syntax error with logic error

5.

You have a DataFrame df with columns employee_id, manager_id, and department. You want to find pairs of employees who work in the same department and share the same manager but are different employees. Which self join condition is correct?

hard

A. df1.employee_id == df2.employee_id AND df1.manager_id == df2.manager_id

B. df1.employee_id == df2.manager_id AND df1.department == df2.department

C. df1.manager_id != df2.manager_id AND df1.department == df2.department

D. df1.department == df2.department AND df1.manager_id == df2.manager_id AND df1.employee_id != df2.employee_id

Solution

1. Step 1: Understand the requirement

   We want pairs of employees in the same department, with the same manager, but different employee IDs.

2. Step 2: Translate to join condition

   The join condition must check equality of department and manager_id, and inequality of employee_id to avoid self pairs.

3. Final Answer:

   df1.department == df2.department AND df1.manager_id == df2.manager_id AND df1.employee_id != df2.employee_id -> Option D

4. Quick Check:

   Same department & manager, different employees [OK]

Hint: Match same manager and department, exclude same employee [OK]

Common Mistakes:

• Matching employee_id to manager_id incorrectly
• Not excluding same employee pairs
• Checking for different managers instead of same