Storing the minimum event time for each user id in the same table in SQL

October 28, 2022

I’m using snowflake to solve a problem. I’m trying to find the minimum event time for each user id and store it in another column in the same table.

For instance,

user_id	client_event_time
1	2022-07-28
1	2022-08-04
1	2022-08-21
2	2022-07-29
2	2022-07-31
2	2022-08-03

I want to store it like the following:

user_id	client_event_time	MinEventTime
1	2022-07-28	2022-07-28
1	2022-08-04	2022-07-28
1	2022-08-21	2022-07-28
2	2022-07-29	2022-07-29
2	2022-07-31	2022-07-29
2	2022-08-03	2022-07-29

I started with the following SQL query:

  SELECT user_id,client_event_time, 
    row_number() over (partition by user_id order by client_event_time) row_number,
    case 
        when row_number = 1  then client_event_time
     end as MinEventTime
    FROM Data
    ORDER BY user_id, client_event_time;

user_id	client_event_time	row_number	MinEventTime
1	2022-07-28	1	2022-07-28
1	2022-08-04	2	Null
1	2022-08-21	3	Null
2	2022-07-29	1	2022-07-29
2	2022-07-31	2	Null
2	2022-08-03	3	Null

I’m a little confused about how to proceed after this. I would also appreciate a suggestion on another approach. Thanks!

>Solution :

You may also use MIN() here as an analytic function:

SELECT user_id, client_event_time,
       ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY client_event_time) row_number,
       MIN(client_event_time) OVER (PARTITION BY user_id) MinEventTime
FROM Data
ORDER BY user_id, client_event_time;