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

Postgresql – Calculating average time between customer orders and average order value in Postgres

In PostgreSQL I have an orders table that represents orders made by customers of a store:

SELECT * FROM orders

order_id customer_id value created_at
1 1 188.01 2020-11-24
2 2 25.74 2022-10-13
3 1 159.64 2022-09-23
4 1 201.41 2022-04-01
5 3 357.80 2022-09-05
6 2 386.72 2022-02-16
7 1 200.00 2022-01-16
8 1 19.99 2020-02-20

For a specified time range (e.g. 2022-01-01 to 2022-12-31), I need to find the following:

  • Average 1st order value
  • Average 2nd order value
  • Average 3rd order value
  • Average 4th order value

E.g. the 1st purchases for each customer are:

  • for customer_id 1, order_id 8 is their first purchase
  • customer 2, order 6
  • customer 3, order 5

So, the 1st-purchase average order value is (19.99 + 386.72 + 357.80) / 3 = $254.84

This needs to be found for the 2nd, 3rd and 4th purchases also.

I also need to find the average time between purchases:

  • order 1 to order 2
  • order 2 to order 3
  • order 3 to order 4

The final result would ideally look something like this:

order_number AOV av_days_since_last_order
1 254.84 0
2 300.00 28
3 322.22 21
4 350.00 20

Note that average days since last order for order 1 would always be 0 as it’s the 1st purchase.

Thanks.

Tags:

2

Answers


  1. 0 
    select   order_number
        ,round(avg(value),2)                           as AOV
        ,coalesce(round(avg(days_between_orders),0),0) as av_days_since_last_order
from 
(
select   *
        ,row_number() over(partition by customer_id order by created_at) as order_number
        ,created_at - lag(created_at) over(partition by customer_id order by created_at) as days_between_orders
from     t
) t
where    created_at between '2022-01-01' and '2022-12-31'
group by order_number
order by order_number
    order_number aov av_days_since_last_order
    1 372.26 0
    2 25.74 239
    3 200.00 418
    4 201.41 75
    5 159.64 175

    Fiddle

    Login or Signup to reply.
  2. 0

    Im suppose it should be something like this

    WITH prep_data AS (
    SELECT  order_id,
            cuntomer_id,
            ROW_NUMBER() OVER(PARTITION BY order_id, cuntomer_id ORDER BY created_at) AS pushcase_num,
            created_at,
            value
    FROM pushcases
    WHERE created_at BETWEEN :date_from AND :date_to
), prep_data2 AS (
    SELECT pd1.order_id,
           pd1.cuntomer_id,
           pd1.pushcase_num
           pd2.created_at - pd1.created_at AS date_diff,
           pd1.value
    FROM prep_data pd1
    LEFT JOIN prep_data pd2 ON (pd1.order_id = pd2.order_id AND pd1.cuntomer_id = pd2.cuntomer_id AND pd1.pushcase_num = pd2.pushcase_num+1)
)
SELECT  order_id,
        cuntomer_id,
        pushcase_num,
        avg(value) AS avg_val,
        avg(date_diff) AS avg_date_diff
FROM prep_data2
GROUP BY pushcase_num
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search