Schema and sample data
| customer_id | name |
|---|---|
| 1 | Alice Morgan |
| 2 | Bob Chen |
| 3 | Carla Diaz |
| 4 | David Okoro |
| order_id | customer_id | amount |
|---|---|---|
| 101 | 1 | 100 |
| 102 | 1 | 300 |
| 103 | 2 | 250 |
| 104 | 2 | 350 |
| 105 | 2 | 150 |
| 106 | 3 | 500 |
| 107 | 4 | 400 |
| 108 | 4 | 200 |
Show setup SQL (copy to run)
CREATE TABLE Customers (
customer_id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL
);
CREATE TABLE Orders (
order_id INT PRIMARY KEY,
customer_id INT NOT NULL,
amount INT NOT NULL
);
INSERT INTO Customers (customer_id, name) VALUES
(1, 'Alice Morgan'),
(2, 'Bob Chen'),
(3, 'Carla Diaz'),
(4, 'David Okoro');
INSERT INTO Orders (order_id, customer_id, amount) VALUES
(101, 1, 100),
(102, 1, 300),
(103, 2, 250),
(104, 2, 350),
(105, 2, 150),
(106, 3, 500),
(107, 4, 400),
(108, 4, 200);You have a Customers table and an Orders table. Each order belongs to one customer through customer_id. Return each customer name together with their average order amount, aliased as avg_order. Sort the result by avg_order from highest to lowest.
Expected result
| name | avg_order |
|---|---|
| Carla Diaz | 500 |
| David Okoro | 300 |
| Bob Chen | 250 |
| Alice Morgan | 200 |
Show hint
Bring the two tables together with a JOIN on customer_id, then collapse each customer into one row with GROUP BY. Wrap the amount in AVG() so every group reports its own average, and give that expression the alias the question asks for.
Try to write the query yourself before you open this.
Show solution and explanation
SELECT c.name, AVG(o.amount) AS avg_order
FROM Customers c
JOIN Orders o ON o.customer_id = c.customer_id
GROUP BY c.name
ORDER BY avg_order DESC;The JOIN matches every order to its customer on customer_id, producing one combined row per order. GROUP BY c.name then folds those rows into one group per customer, and AVG reports the mean amount inside each group. Bob has three orders (250, 350, 150) that sum to 750, so his average is 250; Alice averages (100 + 300) / 2 = 200; David averages (400 + 200) / 2 = 300; and Carla has a single 500 order, so her average is 500.
ORDER BY avg_order DESC sorts the groups by the aliased aggregate from highest to lowest. T-SQL allows an ORDER BY to reference a SELECT alias because the sort runs after the projection, which is why avg_order is visible there even though it is not a real column.
One trap here is that amount is an integer column, so AVG uses integer arithmetic and truncates any fractional part. These sample amounts all divide evenly, so the averages are exact, but with data like 100 and 101 the true 100.5 would come back as 100. When you need the decimal, cast first: AVG(CAST(o.amount AS DECIMAL(10,2))). Also note that this inner JOIN drops any customer with no orders; use a LEFT JOIN if you want them to appear.