Calculating average time between customer orders and average order value in Postgres-CodePudding

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.

CodePudding user response：

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

CodePudding user response：

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