Write a query to get the customer with the highest total order value for each year and month. Order and Customer tables are separate, with Order_ID and Customer_ID as primary keys. The Customer table's Oid is a foreign key referencing the Orders table's Order_ID. In case of a tie, return the customer with the lower Customer_ID.

Question

Accepted Answer

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 order value for each group
Find the maximum total order value for each year, month
If there are multiple customers with the same highest total order value, select the one with the lower Customer_ID

Accepted Answer

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;

7 Eleven Senior Data Engineer interview questions & answers

Popular interview questions of Senior Data Engineer