Problem

Write a SQL query that returns the names of all customers who have made a purchase in the last 30 days, along with the total amount spent.
Input:
customer_id | name | purchase_date | amount 
1 | John | 2020-01-01 | 20
2 | Jane | 2020-02-01 | 30
3 | Joe | 2020-03-01 | 40
Output:
customer_id | name | total_spent
1 | John | 20
2 | Jane | 30
3 | Joe | 40

Solution

SELECT customer_id, name, SUM(amount) AS total_spent
FROM customers
WHERE purchase_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
GROUP BY customer_id, name;

/*

The solution above is optimal because it uses the GROUP BY clause to group the results by customer_id and name, and the SUM() function to calculate the total amount spent by each customer.

*/