218. Total Amount Spent by Each Customer

medium microsoft

Write a SQL query to find the total amount spent by each customer. Include the customer's name and the total amount spent.
Hint: Join the customer and transaction tables and use aggregation functions.

Table 1: msft_customers

Contains details about customers, including their ID, name, age, gender, and the date they joined.


customer_id: int, name: varchar, age: int, gender: varchar, join_date: date

Table 2: msft_products

Holds information on products, such as product ID, name, price, and category.


product_id: int, product_name: varchar, price: decimal, category: varchar

Table 3: msft_sales

Records details of sales transactions, including the sale ID, customer ID, product ID, date of the transaction, and the quantity of products sold.


sale_id: int, customer_id: int, product_id: int, sale_date: date, quantity: int

Solution postgres


SELECT c.customer_id, c.name, SUM(p.price * s.quantity) AS total_spent
FROM msft_sales s
JOIN msft_customers c ON s.customer_id = c.customer_id
JOIN msft_products p ON s.product_id = p.product_id
GROUP BY c.customer_id, c.name;

    

Last Submission postgres

Expected results


More Microsoft questions

ID Title Level FTPR
219 Inactive Customers in the New Year of 2024 microsoft medium -
220 Most Expensive Product in Each Category microsoft hard -
221 Customers With More Than 5 Products in a Single Transaction microsoft hard -
222 Product Sales Ranking Within Categories microsoft hard -
223 Cumulative Total Sales by Day microsoft hard -
224 Categories with High Average Product Price microsoft medium -
225 Customer Behavior Analysis microsoft hard -
226 Customer Lifetime Value (CLV) and Segmentation microsoft hard -
227 Aggregate Sale Amounts per Customer microsoft hard -
228 Analyzing Sequential Purchase Behavior microsoft hard -
229 Time Between Repeat Purchases microsoft hard -
230 Customer Purchase Trend Analysis microsoft easy -