CodePudding user response：

If you do a ROW_NUMBER() OVER(PARTITION BY userid ORDER BY time) in a cte, you'll get an incrementing counter in order of time, that restarts from 1 for every different user

If you then join the cte to itself on a.user_id = b.user_id and a.rn = 1 and b.rn=2 it will put the Min time and the next-Min time on the same row which makes digging the times easy

WITH cte AS (
  SELECT *, ROW_NUMBER() OVER(PARTITION BY userid ORDER BY time) rn FROM t
)
SELECT *
FROM 
  cte a 
  INNER JOIN cte b ON a.user_id = b.user_id and a.rn = 1 and b.rn=2

a.time is the min, b.time is the next-min - then whatever you want to do to diff them.. I'm not really clear on what data type they are but I'm sure that once they're on the same row of a resultset you can do whatever you need to them to diff them

CodePudding user response：

You can use row_number() to get the rows with the two smallest times, then calculate max(time) - min(time) to get the difference between the two, with group by user_id. No need for a join:

select user_id,
max(time) - min(time) as diff
from (select *, row_number() over(partition by user_id order by time) rn from table_name) t
where rn <= 2
group by user_id;

Fiddle