Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version


PostgreSQL 12.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20190507 (Red Hat 8.3.1-4), 64-bit


CREATE TABLE batteries
(slot integer, day date, percentage integer);
INSERT INTO batteries
(slot, day, percentage)
VALUES
(0, '2020-05-08', 96),
(0, '2020-05-09', 96),
(0, '2020-05-10', 97),
(0, '2020-05-11', 97),
(0, '2020-05-12', 97),
(0, '2020-05-13', null),
(0, '2020-05-14', 95),
(0, '2020-05-15', 96),
(0, '2020-05-16', null),
(0, '2020-05-17', 1),
(0, '2020-05-18', 2),
(1, '2020-05-08', 10),
(1, '2020-05-09', 10),
(1, '2020-05-10', 10);
14 rows affected

select slot,
       min(day) filter (where percentage = last_percentage),
       max(day), last_percentage,
       sequence
from (select b.*,
             first_value(percentage) over (partition by slot, sequence order by day desc) as last_percentage
      from (select b.*,
                   count(*) filter (where percentage is null) over (partition by slot order by day) as sequence
            from batteries b
           ) b
       where percentage is not null
     ) b
group by slot, sequence, last_percentage
order by slot, sequence


slot
min
max
last_percentage
sequence


0
2020-05-10
2020-05-12
97
0

0
2020-05-15
2020-05-15
96
1

0
2020-05-18
2020-05-18
2
2

1
2020-05-08
2020-05-10
10
0

slot	min	max	last_percentage	sequence
0	2020-05-10	2020-05-12	97	0
0	2020-05-15	2020-05-15	96	1
0	2020-05-18	2020-05-18	2	2
1	2020-05-08	2020-05-10	10	0