D8LooPFocus modeCODE WORKSPACE
First and latest order per buyer
Return buyer_id, first_order_at (earliest created_at), and latest_order_at (latest created_at) for each buyer. Order by buyer_id.
GROUP BY buyer_id with MIN(created_at) AS first_order_at and MAX(created_at) AS latest_order_at.
Sample input
ordersEach buyer has several orders; MIN/MAX collapse them to first and latest times.
| 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 |
| 1002 | 3 | 2026-01-12 10:02:00 |
| 1012 | 3 | 2026-02-05 10:00:00 |
Expected output
Expected outputOne row per buyer with first and latest order timestamps.
| buyer_id | first_order_at | latest_order_at |
|---|---|---|
| 1 | 2026-01-12 08:40:00 | 2026-01-28 18:00:00 |
| 2 | 2026-01-18 09:05:00 | 2026-02-02 09:00:00 |
| 3 | 2026-01-12 10:02:00 | 2026-02-05 10:00:00 |
| 4 | 2026-01-13 11:15:00 | 2026-01-25 12:00:00 |
| 6 | 2026-01-20 16:45:00 | 2026-01-30 21:10:00 |
Constraints
Use MIN(created_at) and MAX(created_at) with GROUP BY buyer_id. Since only the timestamps are needed, aggregates are simpler than ROW_NUMBER here.
Expected skills
Choosing MIN/MAX for first/last values versus ROW_NUMBER for whole rows.
SQL
Loading...
AI evaluation
Run the SQL query to inspect preview rows.