CODEWITHSUNDEEP
sqldatabasenorthwind
Shahar3s
Shahar3s

Reputation: 3

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.

Upvotes: 0

Views: 293

Answers (1)

Isolated
Isolated

Reputation: 6454

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.

Upvotes: 0

Related Questions