Puzzle 13 - Generate Unique Match Schedule Between Departments

Create a round-robin schedule where each department plays with every other department exactly once. No department should play against itself, and duplicate matchups like A vs B and B vs A should be avoided.

Input Data:

fabricofdata.com

Expected Output:

Input DataFrame Script

from pyspark.sql.types import StructType,StructField,StringType
from pyspark.sql.functions import col,row_number
from pyspark.sql.window import Window

data= [("Engineering",),("Marketing",),("Sales",),("HR",),("Finance",),("Product",)]
schema = "DepartmentName string"
df=spark.createDataFrame(data,schema)
df.show()

Try solving the question yourself! If you need help, click below to reveal the solution.

from pyspark.sql.types import StructType,StructField,StringType
from pyspark.sql.functions import col,row_number
from pyspark.sql.window import Window

#Option I
df=df.withColumn("DepartmentID",row_number().over(Window.partitionBy().orderBy("DepartmentName")))
matches_DF=df.alias("a").join(
    df.alias("b"),
    on = (col("a.DepartmentID")<col("b.DepartmentID")),
    how="inner"
).select(col("a.DepartmentName"),col("b.DepartmentName").alias("Opponent"))

matches_DF.show()

#Option II
matches = df.alias("a").join(
    df.alias("b"),
    on = (col("a.DepartmentName")<col("b.DepartmentName")),
    how="inner"
).select(col("a.DepartmentName"),col("b.DepartmentName").alias("Opponent"))
matches.orderBy(col("DepartmentName")).show()