Creating an SQL query to list all clients, who spent over 1000$

Question

So, I have three tables with the following rows: Customers (Customer_id and Name), Orders (Customer_id, Product_id, Quantity) and Products (Price).

How do I write a query which shows all customers who spent more than 1000$? Do I have to join the tables?

Answer 1

Because this is homework, I'm not going to give you the answer, just information on how to arrive at the answer.

You need to include the customers table, because that's what your looking for, customers. You'll need to join in aggregate with the orders table, so you can find out how many of each product they've ordered, and you'll need to join with the products table to find the prices of all those items in order to total them up to determine if they've spent more than $1000.

Go to it. Tell us how you get on.

Answer 2

SELECT * FROM customer c JOIN (
  SELECT a.customer_id,SUM(a.quantity*b.price) spent FROM orders a 
  JOIN products b ON b.product_id=a.product_id GROUP BY a.customer_id
) d ON d.customer_id=c.customer_id WHERE d.spent>1000