Task
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
- Build 3-step paths using each user's ordered events within the same session.
- Use event timestamp order, and break ties with
event_id.
- Only include sessions from users where
users.is_active = TRUE.
- Exclude incomplete paths where step 2 or step 3 is missing.
- Return each unique path, the number of distinct sessions that followed it, and its rank by popularity.
- Sort by path rank, then path name.
Table Definitions
users
| column | type | description |
|---|
| user_id | INT | Primary key for the user |
| user_name | VARCHAR(100) | User name |
| signup_date | DATE | Date the user signed up |
| is_active | BOOLEAN | Whether the user is active |
sessions
| column | type | description |
|---|
| session_id | INT | Primary key for the session |
| user_id | INT | User who owns the session |
| session_start | TIMESTAMP | Session start time |
| device_type | VARCHAR(50) | Device used for the session |
events
| column | type | description |
|---|
| event_id | INT | Primary key for the event |
| session_id | INT | Session where the event occurred |
| event_time | TIMESTAMP | Event timestamp |
| page_name | VARCHAR(100) | Product page or screen name |
| event_type | VARCHAR(50) | Event type |
Sample Data
users
| user_id | user_name | signup_date | is_active |
|---|
| 1 | Ava Chen | 2024-01-10 | true |
| 2 | Ben Ortiz | 2024-01-12 | true |
| 3 | Chloe Kim | 2024-01-15 | false |
sessions
| session_id | user_id | session_start | device_type |
|---|
| 101 | 1 | 2024-03-01 09:00:00 | web |
| 102 | 2 | 2024-03-01 10:00:00 | ios |
| 103 | 3 | 2024-03-01 11:00:00 | web |
events
| event_id | session_id | event_time | page_name | event_type |
|---|
| 1001 | 101 | 2024-03-01 09:00:05 | Home | view |
| 1002 | 101 | 2024-03-01 09:00:20 | Search | view |
| 1003 | 101 | 2024-03-01 09:01:00 | Product | view |
| 1004 | 102 | 2024-03-01 10:00:10 | Home | view |
| 1005 | 102 | 2024-03-01 10:00:30 | Search | view |
| 1006 | 102 | 2024-03-01 10:01:10 | Product | view |
Expected Output
| path_3_steps | session_count | path_rank |
|---|
| Home > Search > Product | 3 | 1 |
| Search > Product > Cart | 2 | 2 |
| Home > Product > Cart | 1 | 3 |
