Topic 28 of 28 · Expert
Assign per-user batch sessions with a strict inactivity gap
Create a new per-user session at the first event or when the gap from the previous event exceeds ten units.
Apache Spark / PySpark 3.5+ with a local JVM. Not executable inside a plain Python browser runtime.
Problem statement
Create a new per-user session at the first event or when the gap from the previous event exceeds ten units.
Sessionize a finite clickstream batch with deterministic event order.
Input: user int, ts int; unique ts per user.
Output: Input plus 1-based session number per user.
- Gap exactly ten remains in the same session.
- Finite batch only; streaming requires watermark and state semantics.
Sample 1
Input: user1 times0,5,20,21 Output: sessions1,1,2,2
Gap15 creates a new session.
Sample 2
Input: times0,10 Output: sessions1,1
Strictly exceeds ten, not equal.
Brute-force solution
Collect each user's events and run a Python loop, risking huge per-key memory and serialization overhead.
O(n log n) partition sorting, followed by O(n) window work.
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,0),(1,5),(1,20),(1,21),(2,2)],"user int, ts int")
w=Window.partitionBy("user").orderBy("ts")
prev=F.lag("ts").over(w)
flag=F.when(prev.isNull() | ((F.col("ts")-prev)>10),1).otherwise(0)
result=df.withColumn("new",flag).withColumn("session",F.sum("new").over(w.rowsBetween(Window.unboundedPreceding,Window.currentRow))).drop("new").orderBy("user","ts")
rows=[tuple(r) for r in result.collect()]
assert rows==[(1,0,1),(1,5,1),(1,20,2),(1,21,2),(2,2,1)]
print(rows)
spark.stop()
Optimized solution
Use lag to mark boundaries and a cumulative sum to number sessions.
- Partition/order events.
- Mark first or large gap.
- Cumulative sum boundaries in a ROWS frame.
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,0),(1,5),(1,20),(1,21),(2,2)],"user int, ts int")
w=Window.partitionBy("user").orderBy("ts")
prev=F.lag("ts").over(w)
flag=F.when(prev.isNull() | ((F.col("ts")-prev)>10),1).otherwise(0)
result=df.withColumn("new",flag).withColumn("session",F.sum("new").over(w.rowsBetween(Window.unboundedPreceding,Window.currentRow))).drop("new").orderBy("user","ts")
rows=[tuple(r) for r in result.collect()]
assert rows==[(1,0,1),(1,5,1),(1,20,2),(1,21,2),(2,2,1)]
print(rows)
spark.stop()
Complexity analysis
Time: O(n log n) partition sorting, followed by O(n) window work.
Auxiliary space: Distributed sort and partition workspace.
Test cases
- Exactly threshold.
- Single event.
- Skewed user with huge history.
Supplied script asserts all five session labels.Common mistakes
- Using >= when the gap is strict.
- Claiming batch windows are a drop-in unbounded-streaming solution.
Key takeaways
- Define threshold strictness.
- Use deterministic event order.
- Streaming changes state and correctness contracts.
Follow-up challenges
- How would late events alter previous sessions?
- How could you segment a hot user without breaking boundary continuity?