Big customers

Find the customers whose orders add up to 1000 taka or more.

What to build

A grouped query that finds the customers who spent at least 1000 taka.

Requirements

• Group the orders by customer.
• Count each customer's orders and add up their amounts.
• Keep only groups whose total is 1000 or more, using HAVING.

Tables

Each test fills these tables with its own rows, then runs your SQL:

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer TEXT NOT NULL,
  amount INTEGER NOT NULL
);

Output

Columns customer, orders (how many orders) and total (their sum), from the biggest total down. Equal totals are in alphabetical order of customer. Your result is compared with the expected one, column names included, so name each column exactly as asked (use AS).

Constraints

Amounts are whole numbers. A customer may have one order or many.

Example

When a test starts with:

INSERT INTO orders VALUES
  (1, 'Rahim', 500),
  (2, 'Nadia', 1200),
  (3, 'Rahim', 300),
  (4, 'Karim', 800),
  (5, 'Nadia', 450),
  (6, 'Rahim', 200);

your SQL should print:

customer  orders  total
--------  ------  -----
Nadia     2       1650
Rahim     3       1000
(2 rows)

Why it works

The condition is on a group's total, which only exists after grouping, so it belongs in HAVING rather than WHERE.

Examples

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer TEXT NOT NULL,
  amount INTEGER NOT NULL
);
INSERT INTO orders VALUES
  (1, 'Rahim', 500),
  (2, 'Nadia', 1200),
  (3, 'Rahim', 300),
  (4, 'Karim', 800),
  (5, 'Nadia', 450),
  (6, 'Rahim', 200);

customer  orders  total
--------  ------  -----
Nadia     2       1650
Rahim     3       1000
(2 rows)

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer TEXT NOT NULL,
  amount INTEGER NOT NULL
);
INSERT INTO orders VALUES
  (1, 'Tania', 999),
  (2, 'Sumon', 1000);

customer  orders  total
--------  ------  -----
Sumon     1       1000
(1 row)

Each test makes its own tables, runs your SQL on them, and compares what your queries return. Changes never carry over from one test to the next.

Hints

Solution

SELECT customer, COUNT(*) AS orders, SUM(amount) AS total
FROM orders
GROUP BY customer
HAVING SUM(amount) >= 1000
ORDER BY total DESC, customer;

Lesson: Counting and totals: GROUP BY and HAVING →