← All tasksSQL · Middle

Customers who spent over 1000 in 2024

List customers whose 2024 orders add up to more than 1000: columns name and spent (the 2024 total). Orders from other years don't count. Those who spent more first, and for equal totals by name.

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,
  created_at  date NOT NULL
);

SELECT c.name, sum(o.total) AS spent
FROM customers c
JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, 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, 800.00, '2024-02-10'),
  (2, 1, 400.00, '2024-11-05'),
  (3, 2, 1500.00, '2024-06-01'),
  (4, 3, 900.00, '2024-03-03');

-- 2023 and 2025 orders don't count
INSERT INTO customers VALUES (1, 'Ann'), (2, 'Bob');
INSERT INTO orders VALUES
  (1, 1, 900.00, '2023-12-31'),
  (2, 1, 200.00, '2024-01-01'),
  (3, 2, 600.00, '2024-12-31'),
  (4, 2, 600.00, '2025-01-01'),
  (5, 2, 500.00, '2024-07-15');

-- exactly 1000 is not more than 1000
INSERT INTO customers VALUES (1, 'Ann'), (2, 'Bob');
INSERT INTO orders VALUES
  (1, 1, 1000.00, '2024-05-05'),
  (2, 2, 1000.01, '2024-05-05');