PuRaK PuRaK - 2 months ago 8
MySQL Question

Query to find the customer with highest amount of total purchases

My relationship schema looks something like this :

enter image description here

Now I am trying to write a SQL query to find the customer who has the highest total amount of purchases in the year 2016. I want to know the customer name and the total amount of purchases.

Select Customer_name, TotalAmountOfAllHisPurchases from ...


I cannot think of a neat way of doing this. Anyone who can help me get started on this please?

Answer

I believe the following query should work to identify the customer name with the highest amount derived from all orders in the current calendar year:

SELECT CUSTOMER_NAME, Y.QNTY 
FROM CUSTOMER_T CUST,
(
  SELECT X.CUSTOMER_ID, X.QNTY, MAX(X.QNTY) MAXAMT
  FROM (
        SELECT ORD.CUSTOMER_ID, SUM(OLN.QUANTITY * PRD.UNIT_PRICE) QNTY
        FROM ORDER_T ORD, ORDER_LINE_T OLN, PRODUCT_T PRD
        WHERE TRUNC(OLN.ORDER_DATE,'YEAR') = TRUNC(SYSDATE,'YEAR')
        AND ORD.ORDER_ID = OLN.ORDER_ID
        AND PRD.PRODUCT_ID = OLN.PRODUCT_ID
        GROUP BY ORD.CUSTOMER_ID
        ORDER BY SUM(OLN.QUANTITY) DESC
       ) X 
) Y
WHERE CUST.CUSTOMER_ID = Y.CUSTOMR_ID
AND Y.QNTY = Y.MAXAMT;

The inner-most query joins the ORDER, ORDER_LINE, and PRODUCT tables grouping by the customer ID to sum the total purchases for the current year (orders by summed amount descending). The query up one level uses the inner query results and adds on the max summed total of purchases. The outer-most query joins the CUSTOMER_T table with the inner results to get the customer Name and total order amount for all customers that match the MAXAMT value.

You may modify the date condition to always restrict to 2016 (regardless of current year) as follows:

WHERE TRUNC(OLN.ORDER_DATE,'YEAR') = TRUNC(SYSDATE,'YEAR')

Write as:

WHERE TRUNC(OLN.ORDER_DATE,'YEAR') = TRUNC(TO_DATE('01/01/2016','MM/DD/YYYY'),'YEAR')

You may also pull the outer query of CUSTOMER_T inside the inner table and add it to the joins. It accomplishes the same thing, but I'm not sure which is more efficient (which is applicable if your data set is large).

Note: Written from an Oracle DB perspective, hopefully this helps and you are able to convert the syntax.

Edit: I realized my suggestion would overlook multiple customers with the same total spending amount. I've reworked it to use MAX and show multiple customers (if applicable). Hope this works for your/can be converted to MYSQL.

Comments