Query to get the customer with the highest total order value for each year, month.

• Join the Order and Customer tables on the foreign key

• Group the results by year, month, and customer

• Calculate the total...read more

SELECT

subquery2.year,

subquery2.month,

subquery2.customerid,

subquery2.total_monthly_order_value

FROM

(

SELECT

YEAR(orderdate) AS year,

MONTH(orderdate) AS month,

customerid,

SUM(unitprice * quantity) AS total_monthly_order_value,

ROW_NUMBER() OVER (PARTITION BY YEAR(orderdate), MONTH(orderdate) ORDER BY SUM(unitprice * quantity) DESC, customerid ASC) AS rn

FROM

orders

JOIN

order_details ON orders.orderid = order_details.orderid

GROUP BY

YEAR(orderdate),

MONTH(orderdate),

customerid

) AS subquery2

WHERE

subquery2.rn = 1

ORDER BY

subquery2.year,

subquery2.month;