show average time between records groups

Question

i've a little big puzzle here :)

i've a database with 2 tables: survey_reply and questions, like this:

table SURVEY_REPLY

id | question_id | data_time            | user_id | user reply and others col...
--------------------------------------------------------
522|   2         |  2016-02-29 10:07:10 | jacky
.  |   3         |  2016-02-29 10:07:22 | jacky
.  |   1         |  2016-02-29 10:07:59 | jacky
.. |   4         |  2016-02-29 10:08:40 | jacky
...|   2         |  2016-02-29 11:21:10 | paul
.  |   3         |  2016-02-29 11:21:32 | paul
.  |   2         |  2016-02-29 11:21:35 | louise
.  |   1         |  2016-02-29 11:21:50 | paul
.. |   4         |  2016-02-29 11:22:30 | paul
.. |   3         |  2016-02-29 11:23:01 | louise

The question are shown to the users following the order in this table:

table QUESTIONS

    id | q_ord | survey_id | other columns....
    -------------------------------------------
    1  |   3   |   786
    2  |   1   |   786
    3  |   2   |   786
    4  |   4   |   786

i would know the average reply time, the time spent by people for make his choice and reply to question.

calculation in this example

4th-3th. (last one minus the previus one)

for reply to question.id=4 (question.q_ord=4)

    jacky spent 41 sec (10:08:40-10:07:59)
    paul        80 sec (11:22:30-11:21:50)
    louise doesnt reply

3th-2nd. (3th one minus the 2nd one)

for the question.id=1 (question.q_ord=3)

    jacky spent 37 sec (10:07:59-10:07:22)
    paul        18 sec (11:21:50-11:21:32)
    louise doesnt reply

2nd-1st. for the question.id=3 (question.q_ord=2)

    jacky spent 12 sec (10:07:22-10:07:10)
    paul        22 sec (11:21:32-11:21:10)
    louise      86 sec (11:23:01-11:21:35)

i dont need to calculate time for the beginning question.id=2 (question.q_ord=1)

the results should be:

    q_id | q_ord | av_reply_time
    -------------------------------------------
      3  |    2  |    (12+22+86)/3
      1  |    3  |    (37+18)/2
      4  |    4  |    (41+80)/2

how to figure it out?

PS q_ord it's a consecutive integer numbers without skip any number. Always begins with 1. I always know the maximum number (total question on the survey) in this case just 4.

Answer 1

I'm assuming that your result set is slightly off, and I can't (yet) see the significance of the second table...

SELECT question_id
     , AVG(diff) avg_diff 
  FROM 
     ( SELECT x.user_id
            , x.question_id
            , TIME_TO_SEC(TIMEDIFF(MAX(y.data_time),x.data_time)) diff 
         FROM survey_reply x 
         JOIN survey_reply y 
           ON y.user_id = x.user_id 
          AND y.data_time < x.data_time 
        GROUP 
           BY x.user_id
            , x.question_id
     ) a
 GROUP 
    BY question_id;