Compare previous row’s date in grouped query [closed]

I calculate hours that the air temperature below 7.22. I want to do this calculation on the SQL side.

I have the following table

CREATE TABLE datas (
  id INT,
  air_temperature DOUBLE(8,2),
  created_at TIMESTAMP
);

INSERT INTO datas (id, air_temperature, created_at) VALUES (1, 6.50, '2020-12-01 23:45:02');
INSERT INTO datas (id, air_temperature, created_at) VALUES (2, 6.50, '2020-12-02 03:45:02');
INSERT INTO datas (id, air_temperature, created_at) VALUES (3, 6.92, '2020-12-02 04:00:02');
INSERT INTO datas (id, air_temperature, created_at) VALUES (4, 6.99, '2020-12-02 04:15:02');
INSERT INTO datas (id, air_temperature, created_at) VALUES (5, 7.45, '2020-12-02 04:30:02');
INSERT INTO datas (id, air_temperature, created_at) VALUES (6, 7.34, '2020-12-02 04:45:02');
INSERT INTO datas (id, air_temperature, created_at) VALUES (7, 7.10, '2020-12-02 05:00:02');
INSERT INTO datas (id, air_temperature, created_at) VALUES (8, 7.00, '2020-12-02 05:20:02');
INSERT INTO datas (id, air_temperature, created_at) VALUES (9, 7.12, '2020-12-02 07:15:02');

Data’s date interval can be anything.

What I want to get

date            ch
2020-12-01      1
2020-12-02      3.083
2020-12-03      {calculatedValue}
2020-12-04      {calculatedValue}

Rules

• if there isn’t a previous row, we take this as 1 hour.

• if there is; we take the difference of two dates. BUT if it’s higher than 1 hour, we take this as 1 hour.

How 2020-12-02’s value is 3.083?

rowId 2 = take ch as 60 minutes (there's no previous row to compare, so take as 60 minutes. related to rule-1).
rowId 3 = take ch as 15 minutes. (the difference between the date of previous data and the date of current data)
rowId 4 = take ch as 15 minutes (same as rowId 3)
rowId 5 = take ch as 0 (it's not below 7.22)
rowId 6 = take ch as 0 (it's not below 7.22)
rowId 7 = take ch as 15 minutes (same as rowId 3)
rowId 8 = take ch as 20 minutes (same as rowId 3)
rowId 9 = take ch as 60 minutes (previous date is higher than 60 minutes. so take as 60 minutes. related to rule-2)

total minutes of day / 60 = 185 / 60 = 3.083 hours

If I get the previous value’s date, I can compare and calculate but I don’t know how can I get previous row while using group by.

I thought something like this but I am not sure how can apply rules above.

SELECT
    DATE_FORMAT(created_at, '%d-%m-%Y') as `date`,
    SUM((CASE WHEN air_temperature < 7.22 THEN (apply second rule here) ELSE 0 END)) as ch
FROM datas
GROUP BY `date`

EDIT: dbfiddle

Answer

You can use LEAD() to get the next created_at. Then use arithmetic to limit the difference to 60:

SELECT DATE_FORMAT(created_at, '%d-%m-%Y') as `date`,
       SUM( COALESCE(LEAST(TIMESTAMPDIFF(minute, created_at, next_created_at), 60)), 60) / 60 as hours
FROM (SELECT d.*,
             LEAD(created_at) OVER (ORDER BY created_at) as next_created_at
      FROM datas d
     ) d
WHERE air_temperature < 7.22
GROUP BY `date`