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)

CodePudding user response：

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

CodePudding user response：

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:

db<>fiddle here