Objective

Given a table named Orders that records customer purchases, write an SQL query to determine the average expenditure per customer. Your result should display each customer_id alongside their avg_order_value, rounded to two decimal places. Ensure the output is sorted in ascending order based on customer_id.

Additional information

• Table Schema:

  The orders table:

  Column	Type	Description
  order_id	INTEGER	Unique identifier for each order
  customer_id	INTEGER	Identifier for the customer who made the order
  total_amount	DECIMAL	Total amount spent on the order
  order_date	DATE	Date when the order was placed

• Constraints:

  • Each customer_id may have multiple associated orders.
  • There is at least one order present in the table.

• Output Requirements:

  • Columns: customer_id, avg_order_value
  • avg_order_value must be rounded to two decimal places.
  • Results must be ordered by customer_id in ascending order.

SELECT
    customer_id,
    ROUND(AVG(total_amount), 2) AS avg_order_value
FROM
    orders
GROUP BY
    customer_id
ORDER BY
    customer_id;

Explanation

Step 1: SELECT clause

Specify the columns to retrieve and apply aggregation.

SELECT customer_id, ROUND(AVG(total_amount), 2) AS avg_order_value

• customer_id - selects the unique identifier for each customer from the Orders table.
• AVG(total_amount) - calculates the average of total_amount for each customer.
• ROUND(..., 2) - rounds the calculated average to two decimal places.
• AS avg_order_value - assigns an alias to the rounded average, so the output column has a clean readable name.

Step 2: FROM clause

Specify the source table.

FROM orders

Specifies that the data is being retrieved from the Orders table.

Step 3: GROUP BY clause

Group records so the average is computed per customer.

GROUP BY customer_id

Groups all records by each customer_id. This means AVG(total_amount) is calculated separately for each customer rather than across the whole table.

Step 4: ORDER BY clause

Sort the result for readability.

ORDER BY customer_id;

Sorts the result in ascending order based on customer_id, listing customers from smallest to largest ID.