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 |