MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table Signups (
  user_id integer not null,
  time_stamp timestamp not null
)
insert into Signups (user_id, time_stamp) values
(15     ,'2020-07-31 18:26:35'),
(16     ,'2021-05-20 01:38:09'),
(7      ,'2020-08-02 08:45:14'),
(10     ,'2020-06-24 17:13:14'),
(5      ,'2020-06-27 17:59:29'),
(9      ,'2021-11-08 03:05:14'),
(8      ,'2021-12-13 03:38:58'),
(12     ,'2020-09-16 11:17:39');

Records: 8  Duplicates: 0  Warnings: 0


create table Confirmations (
    user_id integer not null,
  time_stamp timestamp not null,
  action text not null
);
insert into Confirmations (user_id, time_stamp, action) values
(7,       '2020-03-31 13:11:43', 'timeout'  ),
(7,       '2021-03-25 07:40:25', 'timeout'  ),
(8,       '2020-07-27 19:43:25', 'confirmed'),
(8,       '2021-03-07 19:48:06', 'timeout'  ),
(7,       '2020-01-24 15:43:47', 'confirmed');

Records: 5  Duplicates: 0  Warnings: 0


select 
  Signups.user_id,
  timeoutTable.*,
  confirmedTable.*
from Signups
left join (
  select * from Confirmations where action = 'timeout'
) as timeoutTable on Signups.user_id = timeoutTable.user_id
left join (
  select * from Confirmations where action = 'confirmed'
) as confirmedTable on Signups.user_id = confirmedTable.user_id


user_id
user_id
time_stamp
action
user_id
time_stamp
action


15
null
null
null
null
null
null

16
null
null
null
null
null
null

7
7
2021-03-25 07:40:25
timeout
7
2020-01-24 15:43:47
confirmed

7
7
2020-03-31 13:11:43
timeout
7
2020-01-24 15:43:47
confirmed

10
null
null
null
null
null
null

5
null
null
null
null
null
null

9
null
null
null
null
null
null

8
8
2021-03-07 19:48:06
timeout
8
2020-07-27 19:43:25
confirmed

12
null
null
null
null
null
null


select 
  Signups.user_id,
  timeoutTable.*,
  confirmedTable.*
from Signups
left join (
  select user_id, count(user_id) as timeouts
  from Confirmations
  where action = 'timeout'
  group by user_id
) as timeoutTable on Signups.user_id = timeoutTable.user_id
left join (
  select user_id, count(user_id) as confirmations
  from Confirmations
  where action = 'confirmed'
  group by user_id
) as confirmedTable on Signups.user_id = confirmedTable.user_id


user_id
user_id
timeouts
user_id
confirmations


15
null
null
null
null

16
null
null
null
null

7
7
2
7
1

10
null
null
null
null

5
null
null
null
null

9
null
null
null
null

8
8
1
8
1

12
null
null
null
null

user_id	user_id	time_stamp	action	user_id	time_stamp	action
15	null	null	null	null	null	null
16	null	null	null	null	null	null
7	7	2021-03-25 07:40:25	timeout	7	2020-01-24 15:43:47	confirmed
7	7	2020-03-31 13:11:43	timeout	7	2020-01-24 15:43:47	confirmed
10	null	null	null	null	null	null
5	null	null	null	null	null	null
9	null	null	null	null	null	null
8	8	2021-03-07 19:48:06	timeout	8	2020-07-27 19:43:25	confirmed
12	null	null	null	null	null	null

user_id	user_id	timeouts	user_id	confirmations
15	null	null	null	null
16	null	null	null	null
7	7	2	7	1
10	null	null	null	null
5	null	null	null	null
9	null	null	null	null
8	8	1	8	1
12	null	null	null	null