By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE tbl (id int, device_id int, fuel int);
INSERT INTO tbl VALUES
(1, 19, 100),
(2, 19, 97),
(3, 19, 100),
(4, 19, 96),
(5, 19, 94),
(6, 19, 66),
(7, 19, 33),
(8, 19, 31),
(9, 19, 30),
(10, 19, 10),
(11, 19, 6),
(12, 19, 5),
(13, 19, 6),
(14, 19, 100),
(15, 19, 99),
(16, 19, 98),
(17, 19, 100),
(18, 19, 99),
(19, 19, 97),
(20, 19, 96),
(21, 19, 90);
21 rows affected
-- only get the last "tank"
-- defined by "last time it was filled from 20 or below to 100":
SELECT *
FROM (
SELECT *, count(*) FILTER (WHERE fillup) OVER (ORDER BY id DESC) AS tank
FROM (
SELECT *, lag(fuel, 1, 0) OVER (ORDER BY id DESC) = 100
AND fuel <= 20 AS fillup
FROM tbl
WHERE device_id = 19
) sub1
) sub2
WHERE tank = 0;
| id | device_id | fuel | fillup | tank |
|---|---|---|---|---|
| 21 | 19 | 90 | f | 0 |
| 20 | 19 | 96 | f | 0 |
| 19 | 19 | 97 | f | 0 |
| 18 | 19 | 99 | f | 0 |
| 17 | 19 | 100 | f | 0 |
| 16 | 19 | 98 | f | 0 |
| 15 | 19 | 99 | f | 0 |
| 14 | 19 | 100 | f | 0 |