Each customer's first order
Mediumsqljoingroup byaggregate
For each customer with at least one order, return customer (their name), first_order (the earliest ordered_on) and orders (their order count). Sort by name.
Tables
- customers (id INTEGER, name TEXT)
- orders (id INTEGER, customer_id INTEGER, ordered_on TEXT)
Example 1
customers
| id | name |
|---|---|
| 1 | Mona |
| 2 | Omar |
| 3 | Laila |
orders
| id | customer_id | ordered_on |
|---|---|---|
| 1 | 1 | 2026-03-01 |
| 2 | 2 | 2026-01-15 |
| 3 | 1 | 2026-02-10 |
Expected result
| customer | first_order | orders |
|---|---|---|
| Mona | 2026-02-10 | 2 |
| Omar | 2026-01-15 | 1 |
Constraints
- 0 ≤ rows ≤ 1000
- Customer names are unique
- The order of rows matters
Submit checks your solution against 6 tests, including hidden ones.
SQL
-- Write one SELECT query. Tables: customers, orders SELECT