sqldatabasenorthwind

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


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.


Solution

  • 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.