Last Minute SQL Interview Prep Kit: Finding User Sessions and Managing Event Logs

1. Why Do We Need Sessionization in Web Analytics?

In modern web applications, tracking user activity is fundamental to understanding behavior, engagement, and conversion patterns. Platforms log every user interaction—such as clicks, scrolls, hovers, and page views—into a centralized database table.

However, raw event logs are granular, noisy, and disconnected. Business analysts and data engineers rarely care about isolated clicks; instead, they need to aggregate these clicks into user sessions.

• The Status Quo: Raw logs contain an unbroken stream of timestamps per user with no contextual boundaries.
• The Problem: Without session boundaries, calculating metrics like session duration, events per session, or bounce rates is impossible.
• The Business Goal: Group continuous bursts of user activity into discrete sessions using a time-threshold rule so that downstream analytics engines can accurately measure user engagement.

2. Core Mental Model: The 30-Minute Inactivity Window

To transform a continuous stream of timestamps into discrete sessions, we apply a time-based threshold rule.

Real-Life Analogy

Think of a user browsing an e-commerce website like walking through a physical department store. If a shopper is constantly moving from aisle to aisle, picking up items every 5 to 10 minutes, they are on one continuous shopping trip (session).

However, if the shopper stops browsing, leaves the store for lunch, and returns 2 hours later, their initial trip has ended, and they have started an entirely new shopping trip. In our digital log, any gap exceeding 30 minutes acts as that trip to lunch—it forces the closure of the current session and opens a brand-new one.

3. Defining the Sessionization Rules and Expected Output

A session is formally defined as a sequence of activities by a user where the time difference between consecutive events is less than or equal to 30 minutes ($\le 30$ minutes). If the time difference between two consecutive events exceeds 30 minutes ($> 30$ minutes), it marks the absolute start of a new session.

Expected Output Schema

For each identified session, our query must synthesize the raw event stream into aggregate metrics:

4. Step-by-Step Data Walkthrough

Let's examine how raw events map into structured sessions for user_id = 1:

Resulting Sessions for User 1:

• Session ID 1: Starts at 09:00, Ends at 09:00, Duration = $0$ minutes, Event Count = $1$.
• Session ID 2: Starts at 10:00, Ends at 10:50, Duration = $50$ minutes, Event Count = $3$.
• Session ID 3: Starts at 11:40, Ends at 11:40, Duration = $0$ minutes, Event Count = $1$.

5. Technical Approach: Why Window Functions?

To solve this problem in SQL, we must compare each event's timestamp against the timestamp of the event that immediately preceded it for the same user.

• Why LAG() over LEAD()? We need to look backward in time to inspect the previous event's timestamp. Therefore, LAG() is the ideal window function.
• Partitioning Strategy: We must partition by user_id so that User A's events never accidentally bleed into User B's session calculations.
• Ordering Strategy: We must order by event_time ASC to establish a chronological timeline of user actions.

6. Code Evolution: Step 1 - Fetching Previous Event Times

We begin by writing a query using MySQL's LAG() function to pull the previous event time alongside the current event time.

SELECT 
    user_id,
    event_type,
    event_time,
    LAG(event_time, 1) OVER (
        PARTITION BY user_id 
        ORDER BY event_time ASC
    ) AS prev_event_time
FROM user_events;

Handling the First Row Null Problem

In the code above, the very first event for every user will have prev_event_time as NULL because there is no preceding row. To prevent null pointer errors and ensure difference calculations yield $0$ for single-event sessions, the LAG() function accepts a third parameter: a default value if no prior row exists.

SELECT 
    user_id,
    event_type,
    event_time,
    LAG(event_time, 1, event_time) OVER (
        PARTITION BY user_id 
        ORDER BY event_time ASC
    ) AS prev_event_time
FROM user_events;

7. Code Evolution: Step 2 - Calculating Time Differences

Next, we calculate the time elapsed between the current event and the previous event in minutes. In MySQL, we use the TIMESTAMPDIFF() or DATEDIFF() equivalent for minutes. Here, we wrap our logic inside a Common Table Expression (CTE) to keep our transformations clean and modular.

WITH lagged_events AS (
    SELECT 
        user_id,
        event_type,
        event_time,
        LAG(event_time, 1, event_time) OVER (
            PARTITION BY user_id 
            ORDER BY event_time ASC
        ) AS prev_event_time
    FROM user_events
),
timed_events AS (
    SELECT 
        user_id,
        event_type,
        event_time,
        prev_event_time,
        TIMESTAMPDIFF(MINUTE, prev_event_time, event_time) AS time_diff_minutes
    FROM lagged_events
)
SELECT * FROM timed_events;

Execution Behavior:

• For the first event of a user, prev_event_time equals event_time, resulting in a time_diff_minutes of $0$.
• For subsequent events, it calculates the exact delta (e.g., $60$ minutes, $20$ minutes).

8. Summary and Key Takeaways

• Problem Statement: Raw user event streams must be segmented into discrete sessions based on a maximum inactivity threshold of 30 minutes.
• Window Function Selection: Use LAG(column, offset, default) partitioned by user_id and ordered by event_time ASC to inspect historical timestamps.
• Null Mitigation: Pass the current column value as the third argument in LAG() to handle the first row of each partition gracefully, yielding a $0$ minute difference.
• CTE Modularization: Break complex analytical queries down into readable CTE stages (lagged_events $\rightarrow$ timed_events $\rightarrow$ final aggregation).
• Time Complexity: Relying on window functions requires sorting per user partition, resulting in an algorithmic complexity of $\mathcal{O}(N \log N)$ where $N$ is the total number of events.
• Business Value: Accurate sessionization unlocks critical product metrics including session duration, bounce rates, and user engagement funnels.