Mysql – query which provides additional column with iterative number by condition

MySQLquery

I do have a table which looks like this.
Already grouped by user_id AND activity date:

[user_id] [activity_date]
123455     '2019-07-01'
123455     '2019-07-02'
123455     '2019-07-03'
222222     '2019-07-01'
222222     '2019-07-02'

Is it possible to write a query which gives me an additional column like this:

 [user_id] [activity_date]   [additional_counter]
123455     '2019-07-01'        1
123455     '2019-07-02'        2
123455     '2019-07-03'        3
222222     '2019-07-01'        1
222222     '2019-07-02'        2

It should just count up if the user_id has the same predecessor (same user_id).

Best Answer

For MySQL 8+

SELECT user_id, activity_date, COUNT(*) OVER (PARTITION BY user_id ORDER BY activity_date ASC) additional_counter
FROM tablename;

For MySQL 5+

SELECT t1.user_id, t1.activity_date, COUNT(t2.activity_date) additional_counter
FROM tablename t1, tablename t2
WHERE t1.activity_date >= t2.activity_date
  AND t1.user_id = t2.user_id
GROUP BY t1.user_id, t1.activity_date;

fiddle

Related Question