mysql - query which provides additional column with iterative number by condition
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 &gt;= t2.activity_date
  AND t1.user_id = t2.user_id
GROUP BY t1.user_id, t1.activity_date;


fiddle