Mysql 5.6 - Create view with progressively increment a datetime field grouped by

Question

I'm trying to create a view with progressively increment of the datetime field depending on the number of records and job_id.
This is my structure on mysql 5.6

jobs

| job_id | job_group_id | job_exec_data_sched | job_email_timeout |
+--------+--------------+---------------------+-------------------+
|  1     |     1        | 2019-10-21 10:00:00 |       30          |
|  2     |     1        | 2019-10-22 14:00:00 |        0          |
|  3     |     2        | 2019-10-22 15:00:00 |       10          |

contacts

| contact_id | contact_group_id | contact_email |
+------------+------------------+---------------+
|  1399      |       1          |  aa@mail.com  |
|  1401      |       1          |  bb@mail.com  |
|  1402      |       1          |  cc@mail.com  |
|  2800      |       1          |  dd@mail.com  |
|  2801      |       2          |  dd@mail.com  |
|  3000      |       2          |  cc@mail.com  |

My goal would be to get this

| job_id | job_group_id | contact_email | date_schedule       |
+--------+--------------+---------------+---------------------+
|    1   |     1        |  aa@mail.com  | 2019-10-21 10:00:00 |
|    1   |     1        |  bb@mail.com  | 2019-10-21 10:00:30 |
|    1   |     1        |  cc@mail.com  | 2019-10-21 10:01:00 |
|    1   |     1        |  dd@mail.com  | 2019-10-21 10:01:30 |

|    2   |     1        |  aa@mail.com  | 2019-10-22 14:00:00 |
|    2   |     1        |  bb@mail.com  | 2019-10-22 14:00:00 |
|    2   |     1        |  cc@mail.com  | 2019-10-22 14:00:00 |
|    2   |     1        |  dd@mail.com  | 2019-10-22 14:00:00 |

|    3   |     2        |  dd@mail.com  | 2019-10-25 15:00:00 |
|    3   |     2        |  cc@mail.com  | 2019-10-25 15:00:10 |

I tried to do it this way, but without success because I can't differentiate records for job_id

SELECT 
j.job_id,
j.job_group_id, 
c.contact_email, 
j.job_exec_data_sched 
+ interval j.job_email_timeout * (@curRow:=@curRow + 1) second
- interval j.job_email_timeout second
from jobs j
left join contacts c on j.job_group_id = c.contact_group_id 
join (select @curRow := 0) r

Is it possible to do this?
Do you have any suggestions for me to do this? Thanks!

Answer 1

How to enumerate records in contacts table within contact_group_id groups (emulate ROW_NUMBER) starting with 0:

SELECT t1.contact_id, 
       t1.contact_group_id, 
       t1.contact_email, 
       COUNT(t2.contact_id) - 1 row_number
FROM contacts t1
JOIN contacts t2 ON t1.contact_group_id = t2.contact_group_id
                AND t1.contact_id >= t2.contact_id
GROUP BY t1.contact_id, t1.contact_group_id, t1.contact_email

Use this enumeration in DATEADD().