First purchase per user and days from signup
MediumP2Coding
Tables:
users(id int primary key, signed_up_at timestamp)
orders(id int primary key, user_id int, amount int, created_at timestamp)
For each user who has placed at least one order, return their first order (earliest created_at; if two share the same timestamp, the lower id) and the number of calendar days between signup and that order.
Output columns: user_id, first_order_id, first_amount, days_to_first (date of the order minus date of signup). Users without orders are not listed.
Schema
CREATE TABLE users ( id int PRIMARY KEY, signed_up_at timestamp NOT NULL ); CREATE TABLE orders ( id int PRIMARY KEY, user_id int NOT NULL, amount int NOT NULL, created_at timestamp NOT NULL );
Example 1
- Input
- user 1 signed up 2024-01-01 10:00; orders (1, 20, 2024-01-04 09:00) and (2, 5, 2024-01-02 08:00)
- Output
- (1, 2, 5, 1)
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 6. ⌘Enter runs, ⌘⇧Enter submits.