Problem

Write a SQL query that returns all customers who have made a purchase in the last 30 days, and the total amount they have spent.
Example input:
customers table:
id | name | date_of_last_purchase
1 | John | 2020-01-10
2 | Jane | 2020-02-15
3 | Joe | 2019-12-20
4 | Sarah | 2020-03-01
5 | Dave | 2020-03-10
6 | Karen | 2020-03-20
7 | Steve | 2020-01-01
8 | Rachel | 2020-02-01
purchases table:
id | customer_id | amount | purchase_date
1 | 1 | 20 | 2020-01-11
2 | 2 | 50 | 2020-02-16
3 | 3 | 10 | 2019-12-21
4 | 4 | 30 | 2020-03-02
5 | 5 | 40 | 2020-03-11
6 | 6 | 60 | 2020-03-21
7 | 7 | 70 | 2020-01-02
8 | 8 | 80 | 2020-02-02
Example output:
customer_id | name | total_spent
1 | John | 20
2 | Jane | 50
4 | Sarah | 30
5 | Dave | 40
6 | Karen | 60

Solution

SELECT c.id, c.name, SUM(p.amount) AS total_spent
FROM customers c
JOIN purchases p ON c.id = p.customer_id
WHERE p.purchase_date >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY c.id, c.name
ORDER BY total_spent DESC;