UptoSkills Logo
Login
Average Order Value per CustomerEasy

Average Order Value per Customer

EasySQLPractice library · +10 XPgroup bysqlaggregation

A food delivery platform wants to understand customer spending habits. They have recorded all orders placed by users.

  • Customers (customer_id integer PRIMARY KEY, name varchar(50) NOT NULL)
  • Orders (order_id integer PRIMARY KEY, customer_id integer NOT NULL, order_value numeric(10,2) NOT NULL)

Return the customer_id and the average_order_value for each customer. Order the results by customer_id.

Handle NULLs: order_value will not be NULL. Empty results should return an empty set. Ties in average order value do not matter for ordering, as the results are ordered by customer_id.

Tables

CREATE TABLE Customers (
  customer_id integer PRIMARY KEY,
  name varchar(50) NOT NULL
);
CREATE TABLE Orders (
  order_id integer PRIMARY KEY,
  customer_id integer NOT NULL,
  order_value numeric(10,2) NOT NULL
);

Example data

INSERT INTO Customers (customer_id, name) VALUES
(101, 'Alice'),
(102, 'Bob'),
(103, 'Charlie'),
(104, 'David');

INSERT INTO Orders (order_id, customer_id, order_value) VALUES
(1, 101, 50.00),
(2, 102, 75.50),
(3, 101, 120.00),
(4, 103, 30.25),
(5, 102, 90.00),
(6, 101, 65.75),
(7, 104, 200.00),
(8, 103, 45.50);

Your query is also checked on hidden datasets with different data.

Hints

Similar problems

More in: SQL and Databases problems