Getting ROW_NUMBER in MySQL, restarting at 1 for sub-groups?

Question

I have a database table containing responses to questions from users. Each question has a request and a response timestamp. The questions are asked in random order. Some users abandoned before completing all the questions and thus don't have subsequent response records.

I didn't capture the order in which the questions were asked for each user, but the sequence could be derived from the request timestamps (SELECT * FROM responses ORDER BY id_user, request_ts;).

I'm using MySQL, so I don't have ROW_NUMBER() as an available function. How would I go about getting the equivalent output, and have the counting restart on each id_user?

That is, for user_id=1, I want responses with values 1,2,..n ordered by request_ts, and then user_id=2 would have their responses with values 1,2,..n; and so on.

Ultimately, I want to get a set of data of aggregated average duration for each nth question (i.e. average duration for first question asked, ditto second question asked, etc).

+-----+-----+-------+
| Seq | Num | Avg_D |
+-----+-----+-------+
|  1  |  20 | 00:36 |
|  2  |  20 | 00:31 |
|  3  |  19 | 00:31 |
|  4  |  20 | 00:25 |
|  5  |  18 | 00:24 |
|  6  |  20 | 00:24 |
|  7  |  20 | 00:23 |
|  8  |  20 | 00:25 |
+-----+-----+-------+

This can then be used to show participant drop-off, survey fatigue, etc.

Answer 1

I created a dummy with sample data.

CREATE TABLE `test9b` ( `id` int(32) NOT NULL AUTO_INCREMENT, `user_id` int(32) NOT NULL, `num` int(32) NOT NULL, `avg` int(32) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8


INSERT INTO `test9b` (`id`, `user_id`, `num`, `avg`) VALUES
(1, 1,  21, 36),
(2, 1,  23, 32),
(3, 1,  20, 35),
(4, 2,  22, 32),
(5, 2,  25, 37),
(6, 2,  10, 39),
(7, 2,  20, 33),
(8, 3,  30, 36),
(9, 3,  40, 36),
(10,    4,  50, 36);

Query :

SELECT a.user_id, a.num, count(*) as row_number FROM test9b a
JOIN test9b b ON a.user_id = b.user_id AND a.num >= b.num
GROUP BY a.user_id, a.num

OUTPUT :

user_id num row_number
1   20  1
1   21  2
1   23  3
2   10  1
2   20  2
2   22  3
2   25  4
3   30  1
3   40  2
4   50  1