Topic 25 of 28 · Intermediate
Select the highest customer version with an explicit tie-breaker
Keep the highest version for each customer; break same-version ties by lexicographically smallest non-null city.
Apache Spark / PySpark 3.5+ with a local JVM. Not executable inside a plain Python browser runtime.
Problem statement
Keep the highest version for each customer; break same-version ties by lexicographically smallest non-null city.
Produce deterministic current-state customer rows.
Input: id int, version int, city string all non-null.
Output: One row per id.
- Business contract explicitly accepts city ordering as tie-breaker for this exercise.
- Production may instead reject conflicting versions.
Sample 1
Input: (1,1,A),(1,2,B) Output: (1,2,B)
Higher version wins.
Sample 2
Input: (1,2,B),(1,2,A) Output: (1,2,A)
Stated city tie-breaker applies.
Brute-force solution
Aggregate maximum version then join back; ties remain unless separately resolved.
O(n log n) partition sorting, with shuffle.
from pyspark.sql import SparkSession,functions as F,Window
spark=SparkSession.builder.master("local[2]").appName("interview-check").config("spark.sql.shuffle.partitions","2").getOrCreate()
spark.sparkContext.setLogLevel("ERROR")
df=spark.createDataFrame([(1,1,"A"),(1,2,"B"),(2,1,"C")],"id int, version int, city string")
w=Window.partitionBy("id").orderBy(F.desc("version"),F.asc("city"))
result=df.withColumn("rn",F.row_number().over(w)).filter("rn=1").drop("rn").orderBy("id")
rows=[tuple(r) for r in result.collect()]
assert rows==[(1,2,"B"),(2,1,"C")]
print(rows)
spark.stop()
Optimized solution
row_number over a total precedence order selects one row consistently.
- Partition by id.
- Order version descending, city ascending.
- Keep row one.
from pyspark.sql import SparkSession,functions as F,Window
spark=SparkSession.builder.master("local[2]").appName("interview-check").config("spark.sql.shuffle.partitions","2").getOrCreate()
spark.sparkContext.setLogLevel("ERROR")
df=spark.createDataFrame([(1,1,"A"),(1,2,"B"),(2,1,"C")],"id int, version int, city string")
w=Window.partitionBy("id").orderBy(F.desc("version"),F.asc("city"))
result=df.withColumn("rn",F.row_number().over(w)).filter("rn=1").drop("rn").orderBy("id")
rows=[tuple(r) for r in result.collect()]
assert rows==[(1,2,"B"),(2,1,"C")]
print(rows)
spark.stop()
Complexity analysis
Time: O(n log n) partition sorting, with shuffle.
Auxiliary space: Distributed window/sort workspace.
Test cases
- Same-version ties.
- Single row.
- Exact duplicates.
Supplied script asserts latest B and single C.Common mistakes
- Using dropDuplicates to select latest.
- Not specifying tie behavior.
Key takeaways
- Define version ordering.
- Deterministic tie-breaker.
- Keep whole row together.
Follow-up challenges
- How would a quality check reject conflicts?
- What changes for streaming state?