SQL - Northwind database - Display for each year, the customer who purchased the highest amount

Question

I'm trying to Display for each year, the customer who purchased the highest amount in Northwind database using SQL.

SELECT 
   YEAR(o.OrderDate) AS OrderYear, 
   MAX(od.Quantity) AS MaxQuantity
FROM
  Customers c 
  JOIN Orders o ON c.CustomerID = o.CustomerID
  JOIN [Order Details] od ON o.OrderID = od.OrderID
GROUP BY 
  YEAR(o.OrderDate)

That's what I managed to do. I just need some help with displaying the customer id for each one of the years.

Answer 1

I think it would make more sense to get the SUM rather than MAX per person for each year. Here we can use the row_number() function to rank people.

select orderYear, customerId, quantity
  from (
    select c.customerId, year(o.orderDate) as orderYear, 
      sum(od.quantity) as quantity, 
      row_number() over (partition by year(o.orderDate) order by sum(od.quantity) desc) as rn
    from customers c
    join orders o
      on c.customerId = o.customerId
    join order_details od
      on o.orderId = od.orderId
    group by c.customerId, year(o.orderDate)
    )z
where rn = 1
order by 1

If you really want MAX, then replace SUM with MAX in both occurrences.