CODEWITHSUNDEEP
mysqlsqldatabasedatewindow-functions
Luis
Luis

Reputation: 185

geting the purchase number based on user ID in mysql

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

Upvotes: 0

Views: 149

Answers (2)

Strawberry
Strawberry

Reputation: 33945

Exclusively for versions prior to 8.0...

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(purchase_id SERIAL PRIMARY KEY
,user CHAR(1) NOT NULL
,date DATE NOT NULL
);

INSERT INTO my_table VALUES
(1,'a','2018-01-01'),
(2,'b','2018-01-02'),
(3,'a','2018-01-02'),
(4,'a','2018-01-03'),
(5,'b','2018-01-04'),
(6,'a','2018-01-04');    

SELECT a.purchase_id
     , a.user
     , a.date
     , a.i rank
  FROM 
     ( SELECT x.*
            , CASE WHEN @prev = user THEN @i:=@i+1 ELSE @i:=1 END i
            , @prev := user
         FROM my_table x
            , (SELECT @prev:=null,@i:=0) vars 
        ORDER 
           BY user
            , date
     ) a
 ORDER   
    BY purchase_id;
+-------------+------+------------+------+
| purchase_id | user | date       | rank |
+-------------+------+------------+------+
|           1 | a    | 2018-01-01 |    1 |
|           2 | b    | 2018-01-02 |    1 |
|           3 | a    | 2018-01-02 |    2 |
|           4 | a    | 2018-01-03 |    3 |
|           5 | b    | 2018-01-04 |    2 |
|           6 | a    | 2018-01-04 |    4 |
+-------------+------+------------+------+

Upvotes: 2

GMB
GMB

Reputation: 222582

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              |

Upvotes: 3

Related Questions