Math Operations

🎬 Scenario: Analyzing Order Totals and Customer Spending

Your manager at Amazon, Sophia, wants a report on customer spending patterns and order totals.

Can you calculate total revenue per order and round values for easier reporting?

Your task is to use SQL math functions to analyze and format financial data. Use the following tables to solve the questions.

amazon_customers table:

amazon_products table:

amazon_orders table:

1. Using Basic Math Operations in SQL

SQL supports basic arithmetic operations for performing calculations:

Example Query: Calculating Total Revenue per Order

1SELECT order_id, quantity, price, quantity * price AS total_revenue  
2FROM amazon_orders  
3JOIN amazon_products ON amazon_orders.product_id = amazon_products.product_id  
4ORDER BY total_revenue DESC;

What’s Happening?

• quantity * price calculates total revenue for each order.
• The highest-value orders appear first.

2. Using ROUND() to Format Decimal Values

The ROUND() function formats numeric results to a specific number of decimal places.

Syntax

1SELECT ROUND(number, decimal_places) FROM table_name;

Example Query: Rounding Total Revenue

1SELECT order_id, quantity, price,  
2       ROUND(quantity * price, 2) AS total_revenue  
3FROM amazon_orders  
4JOIN amazon_products ON amazon_orders.product_id = amazon_products.product_id  
5ORDER BY total_revenue DESC;

What’s Happening?

• ROUND(quantity * price, 2) ensures two decimal places for currency formatting.

3. Using AVG() to Calculate Average Customer Spending

The AVG() function computes the average value of a numeric column.

Example Query: Finding Average Customer Spending

1SELECT membership_status,  
2       ROUND(AVG(total_spent), 2) AS avg_spent  
3FROM amazon_customers  
4GROUP BY membership_status  
5ORDER BY avg_spent DESC;

What’s Happening?

• AVG(total_spent) calculates average spending per membership type.
• ROUND(..., 2) ensures clean financial reporting.

✍️ SQL Exercises

Exercise 1: Calculating Total Revenue

Sophia wants to calculate the total revenue per order based on quantity and price.

Filter condition:

• Retrieve order ID, quantity, price, and total revenue.
• Round total revenue to two decimal places.
• Sort by total_revenue in descending order.

Write an SQL query to return the requested data.

Exercise 2: Finding Average Customer Spending

Sophia also wants to analyze average customer spending per membership type.

Filter condition:

• Retrieve membership status and average total spent.
• Round the average to two decimal places.
• Sort by avg_spent in descending order.

Write an SQL query to return the requested data.