Order total for every customer — interview question

Tables:

SQL

CREATE TABLE customers (id int PRIMARY KEY, name text NOT NULL);
CREATE TABLE orders (id int PRIMARY KEY, customer_id int NOT NULL REFERENCES customers(id), amount int NOT NULL);

Return name and total (sum of that customer's order amounts) for every customer, including customers with no orders (total 0). Sort by total descending, then name ascending.

Input and output

Output: name|total per row.

Examples

Input 1

CREATE TABLE customers (id int PRIMARY KEY, name text NOT NULL);
CREATE TABLE orders (id int PRIMARY KEY, customer_id int NOT NULL REFERENCES customers(id), amount int NOT NULL);
INSERT INTO customers VALUES (1,'Anil'),(2,'Bina'),(3,'Chitra');
INSERT INTO orders VALUES (1,1,500),(2,1,300),(3,3,200);

Output

Anil|800
Chitra|200
Bina|0

Bina has no orders.

Input 2

CREATE TABLE customers (id int PRIMARY KEY, name text NOT NULL);
CREATE TABLE orders (id int PRIMARY KEY, customer_id int NOT NULL REFERENCES customers(id), amount int NOT NULL);
INSERT INTO customers VALUES (1,'Zed'),(2,'Amy');

Output

Amy|0
Zed|0

No orders at all; ties sorted by name.

Show hints

Which join keeps customers who have no matching order?
SUM over no rows is NULL.

Show the explanation

A LEFT JOIN keeps every customer even without orders; for those, SUM(o.amount) is NULL, so wrap it in COALESCE(..., 0). Group by the customer id (not just the name: two customers can share a name). An inner join would silently drop customers with no orders — the most common mistake here.

Original UniCircle question. Last reviewed 21 Oct 2026.