← All tasksSQL · Junior

All customers and their order counts

List every customer with the number of their orders: columns name and orders_count. Customers without orders must be in the result too, with zero.

Those who ordered the most first, and for equal counts by name. Names can repeat: those are different customers.

Tables

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),
  total       numeric(10, 2) NOT NULL
);

SELECT c.name, count(*) AS orders_count
FROM customers c
JOIN orders o ON o.customer_id = c.id
GROUP BY c.name;

Ctrl+Enter runs the tests. Tab indents; to leave the editor with the keyboard, press Esc and then Tab. Your code is saved in this browser.

Tests (3)

Each test fills the tables with its own data and compares your query's result with the expected one: the same columns (names and order) and the same rows in the same order, so ORDER BY matters. The query runs in real PostgreSQL right in your browser; the first run downloads it (about 5 MB).

INSERT INTO customers VALUES (1, 'Ann'), (2, 'Bob'), (3, 'Cid');
INSERT INTO orders VALUES (1, 1, 10.00), (2, 1, 20.00), (3, 2, 5.00);

-- nobody has ordered yet
INSERT INTO customers VALUES (1, 'Ann'), (2, 'Bob');

-- two customers with the same name
INSERT INTO customers VALUES (1, 'Ann'), (2, 'Ann'), (3, 'Zed');
INSERT INTO orders VALUES (1, 1, 10.00), (2, 2, 20.00), (3, 2, 30.00);