How can I determine the average of last 3 values for each user?

Question

Hello the question I'm working on is:

Determine for each customer the average of the last three order values.

I've tried this

SELECT AVG(value) AS the_average
FROM (SELECT value FROM sales ORDER BY date DESC LIMIT 9)

cust	date	value
102	2019-12-03	8
101	2019-12-07	5
102	2019-12-15	3
102	2019-12-17	5
103	2019-12-22	8
103	2020-01-02	2
103	2020-01-04	10
103	2020-01-10	5
102	2020-01-15	6
101	2020-01-19	2
101	2020-02-02	4
101	2020-02-04	5
103	2020-02-12	4
102	2020-02-15	5

Answer 1

You can try

    with cte as (
        SELECT  
            cust, value, row_number() over(partition by cust order by date desc) num
        from 
            your_table 
    ),
cte2 as (
    select * from cte where num <4 
)
    select cust, avg(value)
    from cte2
    group by cust

Answer 2

Schema and insert statements:

 create table table_name(cust int, date date, value int);

 insert into table_name values(102, '2019-12-03',   8);
 insert into table_name values(101, '2019-12-07',   5);
 insert into table_name values(102, '2019-12-15',   3);
 insert into table_name values(102, '2019-12-17',   5);
 insert into table_name values(103, '2019-12-22',   8);
 insert into table_name values(103, '2020-01-02',   2);
 insert into table_name values(103, '2020-01-04',  10);
 insert into table_name values(103, '2020-01-10',   5);
 insert into table_name values(102, '2020-01-15',   6);
 insert into table_name values(101, '2020-01-19',   2);
 insert into table_name values(101, '2020-02-02',   4);
 insert into table_name values(101, '2020-02-04',   5);
 insert into table_name values(103, '2020-02-12',   4);
 insert into table_name values(102, '2020-02-15',   5);

Query:

  with CTE as (
         SELECT  
             cust, value, row_number() over(partition by cust order by date desc) rn
         from 
             table_name
     )
     select cust, avg(value)
     from CTE
     where rn<=3
     group by cust

Output:

cust	avg(value)
101	3.6666666666667
102	5.3333333333333
103	6.3333333333333

db<>fiddle here