Time Between First and Second Records SQL-CodePudding

I am trying to calculate the time between the first and second records. My thought was to add a ranking for each record and then do a calculation on RN 2 - RN 1. I'm struggling to actually get the subquery to do RN2-RN1.

SAMPLE Data:

user_id	date	rn
698998737289929044	2021-04-08 11:27:38	1
698998737289929044	2021-04-08 12:20:25	2
698998737289929044	2021-04-01 13:23:59	3
732850336550572910	2021-03-23 06:13:25	1
598830651911547855	2021-03-11 11:56:53	1

SELECT 
  user_id,
  date,
  row_number() over(partition by user_id order by date) as RN
FROM event_table
GROUP BY user_id, date

CodePudding user response：

You can join the result with itself to get the first and second row.

For example:

with
q as (
  -- your query here
)
select
  f.user_id,
  f.date,
  s.date - f.date as diff
from q f
left join q s on s.user_id = f.user_id and s.rn = 2
where f.rn = 1