Question 196977 in PostgreSQL Question posted in
The official documentation can be found here.

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

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
2 ID partner – 0
3 ID partner – avg(2022-01-15 – 2022-01-17) / 2 + (2022-01-17 – 2022-01-30) / 2))

Would it be possible and how to get these intervals?

Tags:

2

Answers


  1. 0

    You just have to do a sub-query where difference between dates is calculated, then the average is calculated, like this:

    select partner_id, coalesce (avg(diff), 0) period from (
    select p.partner_id, sum(p.order_date - p2.order_date)/count(p.partner_id) diff
    from partners p left join partners p2  
    on p.partner_id=p2.partner_id and p.order_date > p2.order_date
    group by 1, p.order_date 
    order by 1, p.order_date
) a  
group by 1
order by 1;

    Working sample here

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search