Python · hard · Asked at Uber

Write a function to compute a rolling session count with a 30-min timeout.

Asked in Data Engineer interviews, in the Python round.

Short answer

Sort by time, flag new session where gap > 30min, cumsum the flags.

How to answer it

A session ends when the gap to the next event exceeds the timeout. So: sort by user and time, compute the gap to the previous event, flag where a new session starts, and cumulative-sum the flags to get a session number.

import pandas as pd

def sessionize(df, timeout="30min"):
    df = df.sort_values(["user_id", "ts"]).copy()
    gap = df.groupby("user_id")["ts"].diff()
    new_session = gap.isna() | (gap > pd.Timedelta(timeout))
    df["session_no"] = new_session.groupby(df["user_id"]).cumsum()
    return df

sessions_per_user = sessionize(events).groupby("user_id")["session_no"].max()

The groupby on both the diff and the cumsum is what keeps one user's sessions from continuing into the next user's; the isna() catches each user's first event, whose gap is undefined. Session count per user is then the max session number.

The same logic in SQL is LAG(ts) OVER (PARTITION BY user_id ORDER BY ts), a CASE for the flag, and SUM(flag) OVER (PARTITION BY user_id ORDER BY ts). Saying so shows the pattern is the point, not the language.

Related questions

Practice this for real