max_by.md

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

See also

{% for page in site.pages %} {% if (page.title contains "MAX_BY" or page.title contains "MIN_BY") %}
• {{ page.title | escape }}
{% endif %} {% endfor %}