geting the purchase number based on user ID in mysql

Question

I am trying to get a fouth column where I get the purchase number of that user, I have this data:

user   date          purchase_id
a      01-01-2018    1
b      02-01-2018    2
a      02-01-2018    3
a      03-01-2018    4
b      04-01-2018    5
a      04-01-2018    6

and would like to get something like this:

user   date          purchase_id  purchase_order
a      01-01-2018    1            1
b      02-01-2018    2            1
a      02-01-2018    3            2
a      03-01-2018    4            3
b      04-01-2018    5            2
a      04-01-2018    6            4

The final use of this is to build a cohort analysis to check user retention. Thanks

Answer 1

You seem to be looking for ROW_NUMBER() (available in MySQL 8.0). This window function can be used to rank records within groups sharing the same user.

SELECT
    user,
    date,
    purchase_id,
    ROW_NUMBER() OVER(PARTITION BY user ORDER BY purchase_id ) purchase_order
FROM mytable

NB: it is unclear what column you want to use for ordering. It could be purchase_id (as show in the above query), or maybe date: you can change the query as per your requirement.

Demo on DB Fiddle:

| user | date       | purchase_id | purchase_order |
| ---- | ---------- | ----------- | -------------- |
| a    | 2018-01-01 | 1           | 1              |
| a    | 2018-01-02 | 3           | 2              |
| a    | 2018-01-03 | 4           | 3              |
| a    | 2018-01-04 | 6           | 4              |
| b    | 2018-01-02 | 2           | 1              |
| b    | 2018-01-04 | 5           | 2              |