Most Common User Navigation Paths

NovaFlow wants to understand how users move through its product. Write a PostgreSQL query to identify the most common 3-step navigation paths users take within a session.

Use the event stream to build ordered page paths from consecutive events. Only include sessions tied to active users, and ignore events where page_name is NULL.

Requirements

1. Build 3-step paths using each user's ordered events within the same session.
2. Use event timestamp order, and break ties with event_id.
3. Only include sessions from users where users.is_active = TRUE.
4. Exclude incomplete paths where step 2 or step 3 is missing.
5. Return each unique path, the number of distinct sessions that followed it, and its rank by popularity.
6. Sort by path rank, then path name.