Difference of dates using lag function postgres

Question

I have customer ID and transaction Date(yyyy-mm-dd) as shown below

Cust_id Trans_date
1       2017-01-01
1       2017-01-03
1       2017-01-06
2       2017-01-01
2       2017-01-04
2       2017-01-05

I need to find the difference in no_of_days for each transaction grouped at Cust_id

I tried with date_diff and extract using lag function, but I am getting error

function lag(timestamp without time zone) may only be called as a window function

I looking for the result as below

Cust_id Trans_date difference
1       2017-01-01   0
1       2017-01-03   3
1       2017-01-05   2 
2       2017-01-01   0
2       2017-01-04   4
2       2017-01-05   1

How to find the difference in postgreSQL?

Oto Shavadze · Accepted Answer · 2017-10-01 10:03:38Z

3

This is what you want?

with t(Cust_id,Trans_date) as( 
    select 1       ,'2017-01-01'::timestamp union all
    select 1       ,'2017-01-03'::timestamp union all
    select 1       ,'2017-01-06'::timestamp union all
    select 2       ,'2017-01-01'::timestamp union all
    select 2       ,'2017-01-04'::timestamp union all
    select 2       ,'2017-01-05'::timestamp 
)

select 
Cust_id, 
Trans_date, 
coalesce(Trans_date::date - lag(Trans_date::date) over(partition by Cust_id order by Trans_date), 0) as difference
from t;

answered Oct 1, 2017 at 10:03

Oto Shavadze

43.3k56 gold badges168 silver badges248 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Difference of dates using lag function postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related