By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table mytable(callTimestamp datetime, httpStatus int, endpoint varchar(100));
insert into mytable values('2021-04-01 10:21:11',200, 'https://someserver/someapi/v1/endpoint1');
insert into mytable values('2021-04-01 10:25:00',500, 'https://someserver/someapi/v1/endpoint1');
insert into mytable values('2021-04-01 11:33:15',200, 'https://someserver/someapi/v1/endpoint1');
insert into mytable values('2021-04-01 11:34:31',200, 'https://someserver/someapi/v1/endpoint1');
insert into mytable values('2021-04-01 11:35:22',500, 'https://someserver/someapi/v1/endpoint1');
insert into mytable values('2021-04-01 12:22:54',200, 'https://someserver/someapi/v1/endpoint1');
insert into mytable values('2021-04-01 10:21:11',200, 'https://someserver/someapi/v1/endpoint2');
insert into mytable values('2021-04-01 10:25:32',500, 'https://someserver/someapi/v1/endpoint2');
insert into mytable values('2021-04-01 10:59:12',200, 'https://someserver/someapi/v1/endpoint2');
select endpoint, 100*(TIME_TO_SEC(TIMEDIFF(NOW(), max(mincalltime)))-sum(TIME_TO_SEC(TIMEDIFF(nexttime, calltimestamp))))/ TIME_TO_SEC(TIMEDIFF(NOW(), max(mincalltime))) uptimeRate
from
(select *,lead(calltimestamp)over (partition by endpoint order by calltimestamp)nexttime,
min(calltimestamp)over(partition by endpoint order by calltimestamp) mincalltime
from mytable
)t
where httpstatus=500
group by endpoint
| endpoint | uptimeRate |
|---|---|
| https://someserver/someapi/v1/endpoint1 | 99.7700 |
| https://someserver/someapi/v1/endpoint2 | 99.9331 |