Find Customers Who Placed Orders Above a Certain Amount
A popular online food delivery platform, "HungryPlate", wants to identify high-spending customers. They have two tables: Customers and Orders.
-
Customerstable:customer_id(integer, PRIMARY KEY)name(varchar(50))email(varchar(100))
-
Orderstable:order_id(integer, PRIMARY KEY)customer_id(integer)order_amount(numeric(10, 2))
Return the name of customers whose total order_amount across all their orders is strictly greater than 1000. The output should be ordered alphabetically by customer name. Ties in order amounts are handled by summing them. NULL order amounts are ignored. Empty results are possible.
Return columns in this order: name.
Tables
CREATE TABLE Customers ( customer_id integer PRIMARY KEY, name varchar(50) NOT NULL, email varchar(100) ); CREATE TABLE Orders ( order_id integer PRIMARY KEY, customer_id integer NOT NULL, order_amount numeric(10, 2) );
Example data
INSERT INTO Customers (customer_id, name, email) VALUES (1, 'Alice', '[email protected]'), (2, 'Bob', '[email protected]'), (3, 'Charlie', '[email protected]'), (4, 'David', '[email protected]'), (5, 'Eve', '[email protected]'); INSERT INTO Orders (order_id, customer_id, order_amount) VALUES (101, 1, 500.00), (102, 2, 700.00), (103, 1, 600.00), (104, 3, 200.00), (105, 4, 1200.00), (106, 5, 800.00), (107, 2, 350.00), (108, 3, 900.00), (109, 1, 100.00);
Your query is also checked on hidden datasets with different data.
Hints
-- Select customer names who meet the spending criteria
SELECT c.name
FROM Customers c
Your query runs on the example tables shown with the problem, then on hidden datasets when you submit.
Write standard SQL. MySQL habits work too: IFNULL, IF, YEAR(), DATE_FORMAT, GROUP_CONCAT, `backticks` and LIMIT 5, 10. It runs on PostgreSQL, so integer division keeps whole numbers (5 / 2 = 2): write 5.0 / 2 for 2.5.