fetch last three latest order amounts by each customer based on ordered dates-CodePudding

fetch last three latest order amounts by each customer based on ordered dates and order the customer name in ascending and fetch first 15 records.

table name: order_table
columns:
order_id
ordered_date
cust_name
product
order_amount

Given data:

160001 01/01/2020 Richard Books 1120    
160008 01/02/2020 Richard Chairs 1340    
160016 01/03/2020 Richard Computer 1299   
160007 01/04/2020 Richard Desk 2929   
160013 03/01/2020 Benny Monitor 1149   
160010 03/02/2020 Benny Other Electronics 1449    
160004 03/03/2020 Benny Printer 2399    
160012 03/04/2020 Benny Software 3599    
160017 01/02/2020 Ram Speakers 5110    
160023 02/03/2020 Ram Bookcases 2330

required sample output:

Benny 3599 2399 1449    
Ram 2330 5110    
richard 2929 1299 1340

My code:

SELECT 
    order_table.cust_name,
    (SELECT 
            MAX(order_table.ordered_date)
        FROM
            order_table
        GROUP BY order_table.cust_name
        ORDER BY ordered_date DESC
        LIMIT 15)
FROM
    order_table
ORDER BY ordered_date DESC
LIMIT 15*/
SELECT 
    MAX(order_table.ordered_date)
FROM
    order_table
GROUP BY order_table.cust_name
ORDER BY ordered_date DESC
LIMIT 15;

how to handle this type of problem statement and break it down to solve this?

CodePudding user response：

This should give the answer but not in the prescribed format, any helping hand to get the required output will be highly appreciated.

select cust_name,order_amount from order_table where ( 
    select count(*) from order_table as c
    where c.cust_name =order_table.cust_name and 
    c.ordered_date<=order_table.ordered_date
    )<=3 order by cust_name, ordered_date desc ;