Select last rows based on increasing column value before reset?

Question

I try to write a (postgres) sql query which returns the last rows before a specific numeric column drops below it's preceding value, for multiple services.

Let's say the given data looks like:

service | captured | online_seconds
--------+----------+---------------
A       | 01:10:00 | 500 <--
A       | 01:08:00 | 100
A       | 01:07:00 | 600 <--
A       | 01:02:00 |  50
B       | 01:09:00 | 400 <--
B       | 01:06:00 | 200
B       | 01:05:00 | 700 <--

The expected result would be:

service | captured | online_seconds
--------+----------+---------------
A       | 01:10:00 | 500
A       | 01:07:00 | 600
B       | 01:09:00 | 400
B       | 01:05:00 | 700

SQL Fiddle: https://www.db-fiddle.com/f/9jZiSLa5L9tCD7pmXwFiYW/1

I was unable to find any solution until now, any ideas how this could be implemented, if possible?

Answer 1

Use LEAD to see the following row's data:

select service, captured, online_seconds
from
(
  select
    service,
    captured,
    online_seconds,
    lead(online_seconds) over (partition by service order by captured) as next_online_seconds
  from mytable
) with_next_online_seconds
where next_online_seconds < online_seconds or next_online_seconds is null
order by captured;