Python · hard · Reported 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.

Sample data

These tables are loaded into an in-browser engine on this page, so you can run your own answer against them. No account needed.

events

How to answer it

Definition

Sessionisation. A new session starts whenever the gap since the previous event by the same user exceeds 30 minutes. The pattern is: sort, diff, flag, cumsum.

Approach

• Sort by user and timestamp, so "previous event" means something.
• Take the gap to the previous event within each user.
• Flag a new session where the gap exceeds the timeout, or where there is no previous event.
• Cumulative-sum the flags. That running total is the session number.

Example

import pandas as pd

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

sessions = sessionise(events)
print(sessions.groupby("user_id")["session_id"].max())

Output

user_id
101    4
204    1
Name: session_id, dtype: int64

Why cumsum

cumsum over booleans is the trick worth naming. Each True increments the counter and every False leaves it alone, so the running total is exactly the session index. It is the same gaps-and-islands idea as the SQL streak question, expressed in pandas.

Key points

• Sort first, and sort by user as well as time, or the diff crosses user boundaries.
• The first event per user has a NaN gap. It must count as a new session, hence the isna() branch.
• groupby(...).diff() keeps the gap inside each user. A bare .diff() would measure across users.
• Strictly greater than the timeout, so an event exactly 30 minutes later continues the session. Say which convention you chose.
• The same shape works in SQL with LAG and a windowed SUM.

FAQ

• What if events share a timestamp? The gap is zero, so they land in the same session, which is usually right.
• How do I get session duration? Group by user and session id, then max minus min of the timestamp.
• Is 30 minutes standard? It is the common web-analytics default, not a law. It should match how the product is actually used.

The 30-second answer

Sort by user and timestamp, take the gap to the previous event within each user with a grouped diff, and flag a new session wherever that gap exceeds thirty minutes or is null, which covers the user's first event. Then a cumulative sum over those flags gives the session number, because each True increments the counter and each False leaves it.

Two things I would say out loud. The groupby on the diff is what stops the gap being measured across two different users. And I would state the boundary convention, that an event at exactly thirty minutes continues the session rather than starting one, because that is arbitrary and is better written down than discovered later.

In the interview

• What they are checking: the diff, flag, cumsum shape and the per-user partitioning.
• Common mistake: a Python loop over rows carrying state. It works, and on ten million events it runs for an hour.

Practice this for real

• Check your resume against a Data Engineer job description, free, no account needed.
• Every data interview question, by role and topic
• Scored AI mock interviews, two free, no card