
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.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 |
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 |
| path_3_steps | session_count | path_rank |
|---|---|---|
| Home > Search > Product | 3 | 1 |
| Search > Product > Cart | 2 | 2 |
| Home > Product > Cart | 1 | 3 |
| 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 |
| 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 |
| 4 | Daniel Wu | 2024-01-18 | true |
| 5 | Ella Singh | 2024-01-20 | true |
| 6 | Farah Ali | 2024-01-22 | false |
| 7 | Gavin Ross | 2024-01-25 | true |
| 8 | Hana Lee | 2024-01-28 | true |
| 1 | Riya Kapoor | 2024-01-13 | true |
| 2 | Kevin Patel | 2024-01-14 | false |
| 3 | Chloe Kim | 2024-01-08 | true |
| 4 | Isaac Park | 2024-01-27 | false |
| 5 | Chloe Kim | 2024-01-19 | true |
| 6 | Ben Ortiz | 2024-01-26 | true |
| 7 | Gavin Ross | 2024-01-17 | true |
| 8 | Quinn Foster | 2024-01-08 | false |
| 9 | Kevin Patel | 2024-01-11 | false |
| 10 | Ximena Cruz | 2024-01-10 | true |
| 11 | Quinn Foster | 2024-01-26 | true |
| 12 | Riya Kapoor | 2024-01-08 | true |
| 13 | Quinn Foster | 2024-01-16 | true |
| 14 | Daniel Wu | 2024-01-09 | false |
| 15 | Farah Ali | 2024-01-24 | false |
| 16 | Farah Ali | 2024-01-30 | false |
| 17 | Quinn Foster | 2024-01-30 | false |
| 18 | Tara Nguyen | 2024-01-11 | true |
| 19 | Yusuf Malik | 2024-01-11 | true |
| 20 | Owen Price | 2024-01-17 | false |
| 21 | Quinn Foster | 2024-01-13 | true |
| event_id | session_id | event_time | page_name | event_type |
|---|---|---|---|---|
| 1004 | 102 | 2024-03-01 10:00:10 | Home | view |
| 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 |
| 1005 | 102 | 2024-03-01 10:00:30 | Search | view |
| 1006 | 102 | 2024-03-01 10:01:10 | Product | view |
| 1007 | 102 | 2024-03-01 10:02:00 | Cart | view |
| 1008 | 103 | 2024-03-01 11:00:05 | Home | view |
| 1009 | 103 | 2024-03-01 11:00:40 | Search | view |
| 1010 | 103 | 2024-03-01 11:01:20 | Product | view |
| 1011 | 104 | 2024-03-01 12:00:05 | Home | view |
| 1012 | 104 | 2024-03-01 12:00:05 | Search | view |
| 1013 | 104 | 2024-03-01 12:01:00 | Product | view |
| 1014 | 104 | 2024-03-01 12:02:00 | Cart | view |
| 1015 | 105 | 2024-03-01 13:00:10 | Home | view |
| 1016 | 105 | 2024-03-01 13:00:20 | view | |
| 1017 | 105 | 2024-03-01 13:01:00 | Product | view |
| 1018 | 105 | 2024-03-01 13:02:00 | Cart | view |
| 1019 | 106 | 2024-03-01 14:00:10 | Home | view |
| 1020 | 106 | 2024-03-01 14:00:40 | Search | view |
| 1021 | 106 | 2024-03-01 14:01:10 | Product | view |
| 1022 | 107 | 2024-03-01 15:00:05 | Home | view |
| 1023 | 107 | 2024-03-01 15:00:25 | Search | view |
| 1024 | 107 | 2024-03-01 15:01:00 | Search | view |
| 1025 | 107 | 2024-03-01 15:02:00 | Product | view |
| 1026 | 108 | 2024-03-01 16:00:05 | Home | view |
| 1027 | 108 | 2024-03-01 16:00:45 | Search | click |
| 1028 | 108 | 2024-03-01 16:01:10 | Product | view |
| 1029 | 108 | 2024-03-01 16:02:20 | Cart | view |
| 1030 | 109 | 2024-03-01 17:00:05 | Home | view |
| session_id | user_id | session_start | device_type |
|---|---|---|---|
| 102 | 2 | 2024-03-01 10:00:00 | ios |
| 101 | 1 | 2024-03-01 09:00:00 | web |
| 104 | 4 | 2024-03-01 12:00:00 | android |
| 103 | 3 | 2024-03-01 11:00:00 | web |
| 106 | 6 | 2024-03-01 14:00:00 | web |
| 105 | 5 | 2024-03-01 13:00:00 | |
| 107 | 7 | 2024-03-01 15:00:00 | ios |
| 108 | 8 | 2024-03-01 16:00:00 | web |
| 109 | 99 | 2024-03-01 17:00:00 | android |
| 1 | 9 | 2024-02-29 23:43:44 | windows |
| 2 | 25 | 2024-03-04 06:06:37 | android |
| 3 | 64 | 2024-02-28 08:20:38 | partner_embed |
| 4 | 68 | 2024-02-27 15:24:54 | tablet |
| 5 | 95 | 2024-03-01 03:19:36 | firefox_extension |
| 6 | 19 | 2024-03-04 01:36:29 | partner_embed |
| 7 | 5 | 2024-02-29 00:11:59 | ios |
| 8 | 75 | 2024-03-02 15:50:34 | ios |
| 9 | 90 | 2024-02-29 22:48:47 | firefox_extension |
| 10 | 58 | 2024-02-29 13:07:53 | mobile_web |
| 11 | 68 | 2024-03-02 18:15:56 | voice_assistant |
| 12 | 92 | 2024-03-03 16:02:04 | voice_assistant |
| 13 | 30 | 2024-03-04 16:15:44 | firefox_extension |
| 14 | 23 | 2024-02-29 02:18:25 | kiosk |
| 15 | 39 | 2024-03-03 18:05:19 | iphone |
| 16 | 4 | 2024-02-29 08:29:17 | smart_tv |
| 17 | 86 | 2024-03-03 21:06:22 | windows |
| 18 | 43 | 2024-02-29 20:03:09 | iphone |
| 19 | 18 | 2024-03-04 01:45:08 | ios |
| 20 | 34 | 2024-03-04 08:41:32 | tablet |
| 21 | 79 | 2024-03-04 01:28:17 | smart_tv |
| path_3_steps | session_count | path_rank |
|---|---|---|
| Home > Search > Product | 4 | 1 |
| Search > Product > Cart | 3 | 2 |
| Home > Product > Cart | 1 | 3 |
| Home > Search > Search | 1 | 4 |
| Search > Search > Product | 1 | 5 |