How to Get First Row Per Group in Snowflake using row_number Function
Let's say you have tables that contain data about users and sessions, and you want to see the first session for each user for particular day. The function you need here is row_number(). Here's an example of how to use it:
select
us.user_id,
us.session_id,
s.start_date,
s.end_date,
row_number() over (partition by user_id order by start_date desc) as row_number
from user_sessions us
left outer join sessions s on s.id = us.session_id
where to_varchar(start_date,'dd-mm-yyyy') = '02-04-2020';This gives you all the session IDs for the day, plus their row number. Since you only want the first session for the day, you only want rows that have row_number: 1. To do that, you can use a common table expression:
with cte_sessions as (
select
us.user_id,
us.session_id,
s.start_date,
s.end_date,
row_number() over (partition by user_id order by start_date desc) as row_number
from user_sessions us
left outer join sessions s on s.id = us.session_id
where to_varchar(start_date,'dd-mm-yyyy') = '02-04-2020'
)
select *
from cte_sessions
where row_number = 1;
From Snowflake query to chart to Slack in seconds
Get to answers faster, together, with PopSQL and Snowflake