Postgresql get average period from a table with different partners and purchase days

Question

So basically I have a table where are partners and the dates when then they did their orders.

partner_id	order_date
1	2022-01-02
1	2022-01-20
2	2022-02-20
4	2022-01-15
4	2022-01-17
4	2022-01-30

And I want to have an information of each partner average of purchase period, e.g.

partner_id	period
1	18
2	0
4	8

How do I get these: 1 ID partner - (2022-01-20 - 2022-01-02) 2 ID partner - 0 3 ID partner - avg(2022-01-15 - 2022-01-17) + (2022-01-17 - 2022-01-30)))

Would it be possible and how to get these intervals?

Answer 1

You can use lag() to get the previous date, then aggregate:

select partner_id,
    avg(order_date - lag_order_date) as avg_date_diff
from (
    select p.*, 
       lag(order_date) over(partition by partner_id order by order_date) lag_order_date
    from partners p
) p
group by partner_id

Note that lag() returns null when there is no previous row, which avg() then ignores; this seems to be what you want.