Sumsar
0
Given a table orders
| id | customer_id | created_at |
|---|---|---|
| 1 | 1 | 2022-09-01 |
| 2 | 2 | 2022-09-02 |
| 3 | 1 | 2022-09-03 |
| 4 | 1 | 2022-09-04 |
| 5 | 2 | 2022-09-04 |
How do I produce a column that describes which number in the series for the customers the order is?
Example
| id | customer_id | created_at | order number |
|---|---|---|---|
| 1 | 1 | 2022-09-01 | 1 |
| 2 | 2 | 2022-09-02 | 1 |
| 3 | 1 | 2022-09-03 | 2 |
| 4 | 1 | 2022-09-04 | 3 |
| 5 | 2 | 2022-09-5 | 2 |
You can use a window function for that. With a cumulative count over a partition by customer id, you get exactly the order number you need:
select orders.*,
count(*) over (partition by customer_id order by id) order_number
from orders
order by id;
In MySQL 5.7 you could do this:
select customer_id,
(select count(*)
from orders
where customer_id = main.customer_id and id <= main.id)
from orders main;