Skip to content
Onsite

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

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