Postgres 12

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

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

SELECT *
     , fuel - lag(fuel, 1, 0) OVER (PARTITION BY device_id ORDER BY id) > 50 AS fillup
FROM   tbl;


id
device_id
fuel
fillup


1
19
100
t

2
19
97
f

3
19
100
f

4
19
96
f

5
19
94
f

6
19
66
f

7
19
33
f

8
19
31
f

9
19
30
f

10
19
10
f

11
19
6
f

12
19
5
f

13
19
6
f

14
19
100
t

15
19
99
f

16
19
98
f

17
19
100
f

18
19
99
f

19
19
97
f

20
19
96
f

21
19
90
f


SELECT *, count(*) FILTER (WHERE fillup) OVER (PARTITION BY device_id ORDER BY id) AS tank
FROM  (
   SELECT *
        , fuel - lag(fuel, 1, 0) OVER (PARTITION BY device_id ORDER BY id) > 50 AS fillup
   FROM   tbl
   ) sub1;


id
device_id
fuel
fillup
tank


1
19
100
t
1

2
19
97
f
1

3
19
100
f
1

4
19
96
f
1

5
19
94
f
1

6
19
66
f
1

7
19
33
f
1

8
19
31
f
1

9
19
30
f
1

10
19
10
f
1

11
19
6
f
1

12
19
5
f
1

13
19
6
f
1

14
19
100
t
2

15
19
99
f
2

16
19
98
f
2

17
19
100
f
2

18
19
99
f
2

19
19
97
f
2

20
19
96
f
2

21
19
90
f
2


SELECT *
FROM  (
   SELECT *, count(*) FILTER (WHERE fillup) OVER (PARTITION BY device_id ORDER BY id) AS tank
   FROM  (
      SELECT *
           , fuel - lag(fuel, 1, 0) OVER (PARTITION BY device_id ORDER BY id) > 50 AS fillup
      FROM   tbl
      ) sub1
   ) sub2
WHERE device_id = 19
AND   tank = 1;


id
device_id
fuel
fillup
tank


1
19
100
t
1

2
19
97
f
1

3
19
100
f
1

4
19
96
f
1

5
19
94
f
1

6
19
66
f
1

7
19
33
f
1

8
19
31
f
1

9
19
30
f
1

10
19
10
f
1

11
19
6
f
1

12
19
5
f
1

13
19
6
f
1

id	device_id	fuel	fillup
1	19	100	t
2	19	97	f
3	19	100	f
4	19	96	f
5	19	94	f
6	19	66	f
7	19	33	f
8	19	31	f
9	19	30	f
10	19	10	f
11	19	6	f
12	19	5	f
13	19	6	f
14	19	100	t
15	19	99	f
16	19	98	f
17	19	100	f
18	19	99	f
19	19	97	f
20	19	96	f
21	19	90	f

id	device_id	fuel	fillup	tank
1	19	100	t	1
2	19	97	f	1
3	19	100	f	1
4	19	96	f	1
5	19	94	f	1
6	19	66	f	1
7	19	33	f	1
8	19	31	f	1
9	19	30	f	1
10	19	10	f	1
11	19	6	f	1
12	19	5	f	1
13	19	6	f	1
14	19	100	t	2
15	19	99	f	2
16	19	98	f	2
17	19	100	f	2
18	19	99	f	2
19	19	97	f	2
20	19	96	f	2
21	19	90	f	2

id	device_id	fuel	fillup	tank
1	19	100	t	1
2	19	97	f	1
3	19	100	f	1
4	19	96	f	1
5	19	94	f	1
6	19	66	f	1
7	19	33	f	1
8	19	31	f	1
9	19	30	f	1
10	19	10	f	1
11	19	6	f	1
12	19	5	f	1
13	19	6	f	1

id	device_id	fuel	fillup
1	19	100	t
2	19	97	f
3	19	100	f
4	19	96	f
5	19	94	f
6	19	66	f
7	19	33	f
8	19	31	f
9	19	30	f
10	19	10	f
11	19	6	f
12	19	5	f
13	19	6	f
14	19	100	t
15	19	99	f
16	19	98	f
17	19	100	f
18	19	99	f
19	19	97	f
20	19	96	f
21	19	90	f

id	device_id	fuel	fillup	tank
1	19	100	t	1
2	19	97	f	1
3	19	100	f	1
4	19	96	f	1
5	19	94	f	1
6	19	66	f	1
7	19	33	f	1
8	19	31	f	1
9	19	30	f	1
10	19	10	f	1
11	19	6	f	1
12	19	5	f	1
13	19	6	f	1
14	19	100	t	2
15	19	99	f	2
16	19	98	f	2
17	19	100	f	2
18	19	99	f	2
19	19	97	f	2
20	19	96	f	2
21	19	90	f	2

id	device_id	fuel	fillup	tank
1	19	100	t	1
2	19	97	f	1
3	19	100	f	1
4	19	96	f	1
5	19	94	f	1
6	19	66	f	1
7	19	33	f	1
8	19	31	f	1
9	19	30	f	1
10	19	10	f	1
11	19	6	f	1
12	19	5	f	1
13	19	6	f	1

id	device_id	fuel	fillup
1	19	100	t
2	19	97	f
3	19	100	f
4	19	96	f
5	19	94	f
6	19	66	f
7	19	33	f
8	19	31	f
9	19	30	f
10	19	10	f
11	19	6	f
12	19	5	f
13	19	6	f
14	19	100	t
15	19	99	f
16	19	98	f
17	19	100	f
18	19	99	f
19	19	97	f
20	19	96	f
21	19	90	f

id	device_id	fuel	fillup	tank
1	19	100	t	1
2	19	97	f	1
3	19	100	f	1
4	19	96	f	1
5	19	94	f	1
6	19	66	f	1
7	19	33	f	1
8	19	31	f	1
9	19	30	f	1
10	19	10	f	1
11	19	6	f	1
12	19	5	f	1
13	19	6	f	1
14	19	100	t	2
15	19	99	f	2
16	19	98	f	2
17	19	100	f	2
18	19	99	f	2
19	19	97	f	2
20	19	96	f	2
21	19	90	f	2

id	device_id	fuel	fillup	tank
1	19	100	t	1
2	19	97	f	1
3	19	100	f	1
4	19	96	f	1
5	19	94	f	1
6	19	66	f	1
7	19	33	f	1
8	19	31	f	1
9	19	30	f	1
10	19	10	f	1
11	19	6	f	1
12	19	5	f	1
13	19	6	f	1