Problem

Write a SQL query that returns the names of all customers who have made a purchase from the store in the last 30 days, and the total amount they have spent.
Input:
customers table:
+----+----------+------------+
| id | name     | last_order |
+----+----------+------------+
| 1  | John Doe | 1234       |
| 2  | Jane Doe | 1235       |
| 3  | Joe Smith| 1236       |
+----+----------+------------+
orders table:
+----+------------+----------+---------+
| id | customerid | date     | amount  |
+----+------------+----------+---------+
| 1  | 1          | 12/1/2016| 100.00  |
| 2  | 1          | 12/2/2016| 200.00  |
| 3  | 2          | 12/3/2016| 300.00  |
+----+------------+----------+---------+
Output:
+----------+-------------+
| name     | total_spent |
+----------+-------------+
| John Doe | 300 .00      |
| Jane Doe | 300.00      |
+----------+-------------+

Solution

SELECT name, SUM(amount) AS total_spent
FROM customers
JOIN orders ON customers.id = orders.customerid
WHERE orders.date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
GROUP BY name;