SQL Query to select the Max sales for each customer and the date-CodePudding

Data Structure

SQL Query to find the Customer ID, their Max Purch_amt and Date when the Max Purch happened. Can anyone please help? Struggling a bit here. Data structure attached in the Link.

CodePudding user response：

Maybe something like this

select 
ord_no, 
purch_amt,
ord_date
from 
(
select 
*,
rank() over (partition by customerid order by purch_amt desc) as r
from orders
)O
where r=1

CodePudding user response：

Try this! I am using a subquery to SELECT the maximum purchase amount per customer and then I added their respective date.

WITH A AS (SELECT customer_id, MAX(Purch_amt) FROM orders)
SELECT a.*, o.ord_date
FROM A
JOIN orders o ON o.customer_id = a.customer_id