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;
database icon
Real-time SQL collaboration is here
Get started with PopSQL and Snowflake in minutes