D8LooPFocus modeCODE WORKSPACE
Days between buyer orders
For each order, return buyer_id, order_id, created_at, previous_order_at (the buyer’s previous order time), and days_since_previous. Order by buyer_id, created_at.
LAG(created_at) per buyer ordered by created_at, then subtract julianday values for the gap.
Sample input
ordersEach buyer's orders, ordered by time; LAG looks one row back per buyer.
| order_id | buyer_id | created_at |
|---|---|---|
| 1001 | 1 | 2026-01-12 08:40:00 |
| 1004 | 1 | 2026-01-15 14:20:00 |
| 1009 | 1 | 2026-01-28 18:00:00 |
| 1005 | 2 | 2026-01-18 09:05:00 |
| 1011 | 2 | 2026-02-02 09:00:00 |
Expected output
Expected outputFirst 5 of 12 rows. The first order per buyer has a NULL previous time (and NULL gap).
| buyer_id | order_id | created_at | previous_order_at | days_since_previous |
|---|---|---|---|---|
| 1 | 1001 | 2026-01-12 08:40:00 | null | null |
| 1 | 1004 | 2026-01-15 14:20:00 | 2026-01-12 08:40:00 | 3 |
| 1 | 1009 | 2026-01-28 18:00:00 | 2026-01-15 14:20:00 | 13 |
| 2 | 1005 | 2026-01-18 09:05:00 | null | null |
| 2 | 1011 | 2026-02-02 09:00:00 | 2026-01-18 09:05:00 | 14 |
Constraints
Use LAG(created_at) OVER (PARTITION BY buyer_id ORDER BY created_at) for the previous timestamp, then CAST(julianday(created_at) - julianday(previous_order_at) AS INTEGER) for the gap. The first order per buyer has a NULL previous timestamp.
Expected skills
LAG offsets, partition ordering, and date math with julianday.
SQL
Loading...
AI evaluation
Run the SQL query to inspect preview rows.