Top Visit Sequence - Find Most Common Consecutive Page Transition

Question

## Problem

Given a list of `(session_id, page, timestamp)` events, find the most frequent consecutive page transition (page A -> page B) across all sessions. A "transition" is two consecutive events within the same session ordered by timestamp.

Return the transition as `(page_a, page_b)` and the count. On ties, return the lexicographically smallest pair.

```python
def top_transition(
    events: list[tuple[str, str, int]]
) -> tuple[tuple[str, str], int]:
```

## Example

```
events = [
  ("s1","/home",1),("s1","/products",2),("s1","/cart",3),
  ("s2","/home",1),("s2","/products",2),("s2","/checkout",3),
]
Transitions: s1: home->products, products->cart
             s2: home->products, products->checkout
Top: ("/home","/products") with count 2
Output: (("/home", "/products"), 2)
```

## Follow-ups

1. How do you write this as a SQL query using `LAG()` window function?
2. Extend to find the top k transitions rather than just the top 1.
3. How would you detect "unusual" transitions that happen far more than expected?
4. The dataset has 1B rows. How would you compute this in Spark?

Top Visit Sequence - Find Most Common Consecutive Page Transition

Question Details

Full Details

Topics

More from Amazon