Skip to content
Onsite

Date each customer's cumulative spend first crossed 1000

MediumP3Coding

Table orders:

ColumnType
idint (primary key)
customer_idint
amountint (positive)
created_attimestamp

For each customer, find the date on which their running total of amount (orders in created_at order, ties by id) first reached 1000 or more. Customers who never reach 1000 are not listed.

Output columns: customer_id, crossed_on (date).

Schema

CREATE TABLE orders (
  id int PRIMARY KEY,
  customer_id int NOT NULL,
  amount int NOT NULL CHECK (amount > 0),
  created_at timestamp NOT NULL
);

Example 1

Input
customer 1: 400 on Jan 1, 400 on Jan 5, 300 on Jan 9
Output
(1, 2024-01-09)
Why
The running total is 400, 800, then 1100.

Constraints

  • PostgreSQL dialect.

Hints

PostgreSQL
Run checks the 3 visible tests, Submit all 8.