SQL - number of days between dates grouped by id-CodePudding

I have this data, where each row represent a transaction of a client:

Date       Client
2022-01-30 A
2022-01-16 A
2022-01-15 A
2022-03-15 B
2022-02-15 B
2022-03-02 C

And I'm looking for the Number of days where it's the number of days since the last transaction of that client.

Date       Client Number of days
2022-01-30 A      14
2022-01-16 A      1
2022-01-15 A      NULL
2022-03-15 B      30
2022-02-15 B      NULL
2022-03-02 C      NULL

I have tried something like this:

ROW_NUMBER() OVER (PARTITION BY Client ORDER BY Date DESC) AS Number_of_days

But I'm stuck at ROW_NUMBER() as I can't select each date and calculate the difference.

Any suggestions?

CodePudding user response：

You can try to use LEAD window function

SELECT *,
       DATE_DIFF(Date , LEAD(Date) OVER(PARTITION BY Client ORDER BY Date DESC),DAY) 
FROM T

CodePudding user response：

use lag()

select Client,d,
DATE_DIFF(d,lag(d)over(partition by Client order by d desc),DAY) as dys from table_name