Date each customer's cumulative spend first crossed 1000
MediumP3Coding
Table orders:
| Column | Type |
|---|---|
| id | int (primary key) |
| customer_id | int |
| amount | int (positive) |
| created_at | timestamp |
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
Give it a real attempt first. Run shows which examples fail.
Saved automatically
PostgreSQL
Run checks the 3 visible tests, Submit all 8. ⌘Enter runs, ⌘⇧Enter submits.