First Purchase per Customer with Days-to-Convert

Preview mode. Log in to edit, run, submit, and save progress.

Medium

Description

You have two tables: Customers (with a registration date) and Orders (with purchase amounts and dates). A customer may place multiple orders. Write a SQL query to return, for each customer who has placed at least one order: their customer_id, name, the date of their first order, the amount of that first order, and the number of days between registration and first order (days_to_first_order). Customers with no orders should NOT appear in the result. Return results ordered by customer_id. Tables: Customers, Orders Customers:

Column Name	Type	Description
id	INT	Primary key
name	VARCHAR	Customer name
registered_date	DATE	Account registration date

Orders:

Column Name	Type	Description
id	INT	Primary key
customer_id	INT	FK to Customers.id
amount	INT	Order amount
order_date	DATE	Date order was placed

Database Schema (Inferred)

Customers

Column Name	Example Value
id	1
name	Alice
registered_date	2023-01-01

Orders

Column Name	Example Value
id	1
customer_id	1
amount	250
order_date	2023-01-10

Example

Customers

id	name	registered_date
1	Alice	2023-01-01
2	Bob	2023-02-15
3	Carol	2023-03-01

Orders

id	customer_id	amount	order_date
1	1	250	2023-01-10
2	1	100	2023-01-20
3	2	400	2023-03-01
4	2	50	2023-04-05
5	3	300	2023-05-20

Output

customer_id	name	first_order_date	first_amount	days_to_first_order
1	Alice	2023-01-10	250	9
2	Bob	2023-03-01	400	14
3	Carol	2023-05-20	300	80

Explanation:

Use a CTE to find MIN(order_date) per customer_id, then join back to get the amount for that date, then JOIN with Customers to compute julianday difference.

Approach hint

Start with a simple approach, explain the trade-off, then move toward a cleaner or more scalable solution.

Common mistake

Skipping assumptions, edge cases, or trade-offs can make an otherwise good answer feel incomplete.