By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE tablename (
`id` INTEGER,
`customer` INTEGER,
`field` VARCHAR(7),
`status` VARCHAR(10),
`timestamp` DATETIME
);
INSERT INTO tablename
(`id`, `customer`, `field`, `status`, `timestamp`)
VALUES
('1', '37399262', 'phone', 'successful', '2020-10-08 20:16:39'),
('2', '37399262', 'website', 'failed', '2020-10-08 20:16:39'),
('3', '37399262', 'website', 'failed', '2020-10-09 21:25:22'),
('4', '37399262', 'website', 'successful', '2020-10-10 09:08:35');
select customer, field,
id success_id, timestamp success_timestamp,
last_fail_id, last_fail_timestamp
from (
select *,
lag(status) over (partition by customer, field order by timestamp) prev_status,
lag(id) over (partition by customer, field order by timestamp) last_fail_id,
lag(timestamp) over (partition by customer, field order by timestamp) last_fail_timestamp
from tablename
) t
where status = 'successful' and prev_status = 'failed'
| customer | field | success_id | success_timestamp | last_fail_id | last_fail_timestamp |
|---|---|---|---|---|---|
| 37399262 | website | 4 | 2020-10-10 09:08:35 | 3 | 2020-10-09 21:25:22 |
select t.customer, t.field,
t.id success_id, t.timestamp success_timestamp,
g.last_fail_id, g.last_fail_timestamp
from tablename t
inner join (
select customer, field,
max(case when status = 'failed' then timestamp end) last_fail_timestamp,
max(case when status = 'failed' then id end) last_fail_id
from tablename
group by customer, field
having last_fail_timestamp is not null
) g on g.customer = t.customer and g.field = t.field
where t.status = 'successful'
| customer | field | success_id | success_timestamp | last_fail_id | last_fail_timestamp |
|---|---|---|---|---|---|
| 37399262 | website | 4 | 2020-10-10 09:08:35 | 3 | 2020-10-09 21:25:22 |