MySQL Forums
Forum List  »  Newbie

Classifying Northwind customers according to how much they spent
Posted by: Montana Burr
Date: December 21, 2021 12:45PM

Hey guys,

I'm working through a problem in an SQL problem set. I am to write a query that returns:
1) Each customer's name & ID
2) How much they spent in 2016
3) Whether they are classified as a "low-value customer", a "medium-value customer", a "high-value customer" or a "Very high-value customer" based on how much they spent in 2016. Someone who spent up to (but not necessarily including) $1000 in 2016 is considered "low value." Someone who spent between $1000 and $5000 is a "medium-value customer." Someone who spent between $5000 and $10000 is a "high-value customer." All others are considered "very high-value customers."

I can find #1 and #2 like so:

WITH
orderTotals AS (SELECT orders.CustomerID,
SUM(orderdetails.Quantity * orderdetails.UnitPrice) AS total,
YEAR(orders.OrderDate) AS orderYear
FROM orders
JOIN orderdetails
ON orders.OrderID = orderdetails.OrderID
group by orders.CustomerID,orderYear
)
SELECT customers.CustomerID,
customers.CompanyName,
orderTotals.total
FROM customers
LEFT JOIN orderTotals
ON customers.CustomerID = orderTotals.CustomerID
WHERE orderTotals.orderYear = "2016"
GROUP BY CustomerID
ORDER BY CustomerID;

But I'm not sure how to find #3.

I've tried using a user-defined function like so:

# Create a function that returns a classification:
# "Low" for $0-$1000,
# "Medium" for $1000 to $5000
# "High" for $5000 to $10000
# "Very High" for $10000+
DELIMITER $$
CREATE FUNCTION valueClassification(value FLOAT)
RETURNS TEXT DETERMINISTIC
BEGIN
DECLARE classification TEXT
IF value >= 10000 THEN
SET classification = "Very High"
ELSE IF value BETWEEN 5000 AND 9999.99 THEN
SET classification = "High"
ELSE IF value BETWEEN 1000 AND 4999.99 THEN
SET classification = "Medium"
ELSE
SET classification = "Low"
END IF;
RETURN classification;
END;
DELIMITER ;
WITH
orderTotals AS (SELECT orders.CustomerID,
SUM(orderdetails.Quantity * orderdetails.UnitPrice) AS total,
YEAR(orders.OrderDate) AS orderYear
FROM orders
JOIN orderdetails
ON orders.OrderID = orderdetails.OrderID
group by orders.CustomerID,orderYear
)
SELECT customers.CustomerID,
customers.CompanyName,
orderTotals.total,
valueClassification(orderTotals.total)
FROM customers
LEFT JOIN orderTotals
ON customers.CustomerID = orderTotals.CustomerID
WHERE orderTotals.orderYear = "2016"
GROUP BY CustomerID
ORDER BY CustomerID;

but MySQL doesn't seem to be paying attention to the CREATE FUNCTION statement.

Any suggestions would be greatly appreciated.

Options: ReplyQuote


Subject
Written By
Posted
Classifying Northwind customers according to how much they spent
December 21, 2021 12:45PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.