Interview Questions/SQL/First Purchase per Customer with Days-to-Convert

First Purchase per Customer with Days-to-Convert

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

Medium

First Purchase per Customer with Days-to-Convert

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

Examples

Example 1

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.

query.sql