By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();
| version() |
|---|
| 8.0.27 |
CREATE TABLE audit_subscibers (
id int ,
name varchar(30),
action varchar(60),
time date );
INSERT INTO audit_subscibers VALUES
(0,'John','Insert a subscriber','2020-01-01'),
(1,'John','Deleted a subscriber','2020-03-01'),
(2,'Mark','Insert a subscriber','2020-04-05'),
(3,'Andrew','Insert a subscriber','2020-05-01'),
(4,'Andrew','Updated a subscriber','2020-05-15');
SELECT name,
MAX(case when action='Insert a subscriber' then time end) as Date_added,
MAX(case when action='Deleted a subscriber' then time end) as Date_deleted
FROM (
SELECT name,time,action
FROM audit_subscibers
WHERE name in (SELECT name
FROM audit_subscibers
WHERE action in ('Insert a subscriber','Deleted a subscriber')
GROUP BY name
HAVING COUNT(action) = 2 )
) as t1
group by name;
| name | Date_added | Date_deleted |
|---|---|---|
| John | 2020-01-01 | 2020-03-01 |