First One Time Visitor: Find Each User's First Session Where They Visited a Page Exactly Once

Question

## Problem

You have a table of web analytics events: `(user_id, session_id, page_url, event_time)`. For each user, find the first session in which they visited at least one page exactly once (i.e., a page they did not return to in that session). Return `user_id`, `session_id`, and the qualifying page URL.

```sql
-- events: user_id INT, session_id INT, page_url VARCHAR, event_time TIMESTAMP

WITH page_counts AS (
    SELECT user_id, session_id, page_url,
           COUNT(*) AS visit_count
    FROM events
    GROUP BY user_id, session_id, page_url
),
one_time_pages AS (
    SELECT user_id, session_id, page_url
    FROM page_counts
    WHERE visit_count = 1
),
ranked AS (
    SELECT user_id, session_id, page_url,
           MIN(session_id) OVER (PARTITION BY user_id) AS first_qualifying_session
    FROM one_time_pages
)
SELECT DISTINCT user_id, session_id, page_url
FROM ranked
WHERE session_id = first_qualifying_session;
```

## Follow-ups

1. What is the difference between using `MIN() OVER (PARTITION BY ...)` and a `GROUP BY` subquery for this problem?
2. If `event_time` should determine "first session" (not `session_id` ordering), how do you change the window function?
3. How would you test this query with edge cases: a user who visits every page twice, or a user with only one session?
4. Rewrite this in Python using pandas -- what `groupby` and `transform` calls produce the same result?

First One Time Visitor: Find Each User's First Session Where They Visited a Page Exactly Once

Question Details

Full Details

Topics

More from Uber