Days Between Consecutive Orders per Customer - Medium SQL Window Functions Interview Problem

Problem

Retention analytics wants to understand how frequently customers re-order. Using the orders table, return customer_id, order_id, created_at, and days_since_prev (days since that customer's previous order, NULL for their first order), ordered by customer_id then created_at.

Schema

orders

column	type
id	INTEGER
customer_id	INTEGER
amount	NUMERIC
created_at	DATE

Sample Data

id	customer_id	created_at
1	1	2024-01-10
2	1	2024-01-25
3	1	2024-03-01
4	2	2024-02-01
5	2	2024-02-14

Expected Output

customer_id	order_id	created_at	days_since_prev
1	1	2024-01-10	NULL
1	2	2024-01-25	15
1	3	2024-03-01	35
2	4	2024-02-01	NULL
2	5	2024-02-14	13

Hints