MAX_BY Analytical Function in Snowflake
When analyzing Clickstream data, one is often interested in the last few pages visited before the visitor exited the site. Snowflake has powerful aggregation function called max_by that can be used to get an array of the last few pages visited by session_id.
Query
select
session_id
, max_by(URI, visit_timestamp, 2)
from scratch.saqib_ali.webstream
group by session_id;
Output
| SESSION_ID | MAX_BY(URI, VISIT_TIMESTAMP, 2) |
|---|---|
| 9d1937da-92ee-462f-968e-2fc6d910e185 | [“donate.html”,”plant_a_tree_campaign_a.html”] |
| 3ce56b32-0a97-43a7-9227-e1c9bf4786ee | [“donate.html”,”plant_a_tree_campaign_a.html”] |
| 74abb734-d272-475a-873d-33caf655addc | [“donate.html”,”plant_a_tree_campaign_a.html”] |
| c23d473c-99ef-4e28-a79c-c0a35e0a27cc | [“donate.html”,”plant_a_tree_campaign_b.html”] |
| b0e2ff35-7e55-4b7b-a246-8857fdf05c76 | [“donate.html”,”plant_a_tree_campaign_a.html”] |
| 41f4d2ca-8d08-4c80-b407-9b97e6bd558e | [“campaigns.html”,”plant_a_tree_campaign_b.html”] |
| 2c587331-ab47-4bd6-b57f-05af3896fa56 | [“plant_a_tree_campaign_b.html”,”index.html”] |
| f597f443-4098-4ee4-9538-7076e114085c | [“donate.html”,”plant_a_tree_campaign_b.html”] |
| 022310c3-6f5a-4b65-8de2-df67ae3bcc9c | [“plant_a_tree_campaign_a.html”,”index.html”] |
| eae477e9-0dad-4450-a08f-5361603f46ec | [“plant_a_tree_campaign_b.html”,”index.html”] |
Query Highlights
Raw Data
| SESSION_ID | URI | VISIT_TIMESTAMP |
|---|---|---|
| 022310c3-6f5a-4b65-8de2-df67ae3bcc9c | index.html | 19:12:33 |
| 022310c3-6f5a-4b65-8de2-df67ae3bcc9c | plant_a_tree_campaign_a.html | 19:12:48 |
| 2c587331-ab47-4bd6-b57f-05af3896fa56 | index.html | 19:12:46 |
| 2c587331-ab47-4bd6-b57f-05af3896fa56 | plant_a_tree_campaign_b.html | 19:12:58 |
| 3ce56b32-0a97-43a7-9227-e1c9bf4786ee | index.html | 19:12:48 |
| 3ce56b32-0a97-43a7-9227-e1c9bf4786ee | plant_a_tree_campaign_a.html | 19:12:55 |
| 3ce56b32-0a97-43a7-9227-e1c9bf4786ee | donate.html | 19:13:00 |
| 41f4d2ca-8d08-4c80-b407-9b97e6bd558e | index.html | 19:12:56 |
| 41f4d2ca-8d08-4c80-b407-9b97e6bd558e | campaigns.html | 19:13:05 |
| 41f4d2ca-8d08-4c80-b407-9b97e6bd558e | plant_a_tree_campaign_b.html | 19:13:15 |
| 41f4d2ca-8d08-4c80-b407-9b97e6bd558e | campaigns.html | 19:13:25 |
| 74abb734-d272-475a-873d-33caf655addc | index.html | 19:12:37 |
| 74abb734-d272-475a-873d-33caf655addc | plant_a_tree_campaign_a.html | 19:12:57 |
| 74abb734-d272-475a-873d-33caf655addc | donate.html | 19:13:09 |
| 9d1937da-92ee-462f-968e-2fc6d910e185 | index.html | 19:12:44 |
| 9d1937da-92ee-462f-968e-2fc6d910e185 | plant_a_tree_campaign_a.html | 19:12:54 |
| 9d1937da-92ee-462f-968e-2fc6d910e185 | donate.html | 19:13:06 |
| b0e2ff35-7e55-4b7b-a246-8857fdf05c76 | index.html | 19:12:40 |
| b0e2ff35-7e55-4b7b-a246-8857fdf05c76 | plant_a_tree_campaign_a.html | 19:12:49 |
| b0e2ff35-7e55-4b7b-a246-8857fdf05c76 | donate.html | 19:12:58 |
| c23d473c-99ef-4e28-a79c-c0a35e0a27cc | index.html | 19:12:42 |
| c23d473c-99ef-4e28-a79c-c0a35e0a27cc | plant_a_tree_campaign_b.html | 19:12:58 |
| c23d473c-99ef-4e28-a79c-c0a35e0a27cc | donate.html | 19:13:05 |
| eae477e9-0dad-4450-a08f-5361603f46ec | index.html | 19:12:48 |
| eae477e9-0dad-4450-a08f-5361603f46ec | plant_a_tree_campaign_b.html | 19:13:04 |
| f597f443-4098-4ee4-9538-7076e114085c | index.html | 19:12:54 |
| f597f443-4098-4ee4-9538-7076e114085c | plant_a_tree_campaign_b.html | 19:13:02 |
| f597f443-4098-4ee4-9538-7076e114085c | campaigns.html | 19:13:19 |
| f597f443-4098-4ee4-9538-7076e114085c | plant_a_tree_campaign_b.html | 19:13:31 |
| f597f443-4098-4ee4-9538-7076e114085c | donate.html | 19:13:38 |