Question

What is the total amount each customer spent at the restaurant?

Solution

SELECT customer_id, '$' || SUM(price) as total_amount_spent
FROM sales
INNER JOIN menu on sales.product_id = menu.product_id
GROUP BY customer_id;

Let’s start by asking who has visited our restaurant so far?

SELECT DISTINCT(customer_id) 
FROM sales;

Okay, what kinda purchases were made by A ?

SELECT *
FROM sales 
WHERE customer_id = 'A';

Nice. Can we get the prices for these products next to their sale records?

SELECT *
FROM sales
INNER JOIN menu ON sales.product_id = menu.product_id
WHERE customer_id = 'A';

customer_id	order_date	product_id	product_id	product_name	price
A	2021-01-01	1	1	sushi	10
A	2021-01-07	2	2	curry	15
A	2021-01-01	2	2	curry	15
A	2021-01-11	3	3	ramen	12
A	2021-01-11	3	3	ramen	12
A	2021-01-10	3	3	ramen	12

Okay, so the total amount that customer A spent must be the sum of all these prices. There’s a SUM aggregator we could use here.

SELECT SUM(price)
FROM sales
INNER JOIN menu ON sales.product_id = menu.product_id
WHERE customer_id = 'A';

sum
76

Can we perform this summation for every customer and store it as one row per customer. Yup, GROUP BY comes to the rescue.

SELECT customer_id, SUM(price)
FROM sales
INNER JOIN menu ON sales.product_id = menu.product_id
GROUP BY customer_id;

Excellent, we got the data we need. Maybe polish it slightly by prefixing the numbers with a $ and naming the column to something better?

SELECT customer_id, '$' || SUM(price) as total_amount_spent
FROM sales
INNER JOIN menu on sales.product_id = menu.product_id
GROUP BY customer_id;