How to Get First Row Per Group in Snowflake in Snowflake

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;

Previous

Next

Ready for a modern SQL editor?