Your question is Most Common User Navigation Paths. Start with the requirements and the three tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
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.
event_id.users.is_active = TRUE.| Column | Type | Description |
|---|---|---|
| user_idPK | INT | Primary key for the user |
| user_name | VARCHAR(100) | User's full name |
| signup_date | DATE | Date the user signed up |
| is_active | BOOLEAN | Whether the user is currently active |
| Column | Type | Description |
|---|---|---|
| session_idPK | INT | Primary key for the session |
| user_id | INT | User who owns the session |
| session_start | TIMESTAMP | Timestamp when the session started |
| device_type | VARCHAR(50) | Device type used in the session |
| Column | Type | Description |
|---|---|---|
| event_idPK | INT | Primary key for the event |
| session_id | INT | Session where the event occurred |
| event_time | TIMESTAMP | Timestamp of the event |
| page_name | VARCHAR(100) | Page or screen viewed by the user |
| event_type | VARCHAR(50) | Type of event recorded |