SQLize | PHPize | SQLtest

drop table if exists weather cascade; create table if not exists weather ( id int primary key, city varchar(50) not null, temperature int not null, day date not null ); delete from weather; insert into weather values (1, 'London', -1, to_date('2021-01-01','yyyy-mm-dd')), (2, 'London', -2, to_date('2021-01-02','yyyy-mm-dd')), (3, 'London', 4, to_date('2021-01-03','yyyy-mm-dd')), (4, 'London', 1, to_date('2021-01-04','yyyy-mm-dd')), (5, 'London', -2, to_date('2021-01-05','yyyy-mm-dd')), (6, 'London', -5, to_date('2021-01-06','yyyy-mm-dd')), (7, 'London', -7, to_date('2021-01-07','yyyy-mm-dd')), (8, 'London', 5, to_date('2021-01-08','yyyy-mm-dd')), (9, 'London', -20, to_date('2021-01-09','yyyy-mm-dd')), (10, 'London', 20, to_date('2021-01-10','yyyy-mm-dd')), (11, 'London', 22, to_date('2021-01-11','yyyy-mm-dd')), (12, 'London', -1, to_date('2021-01-12','yyyy-mm-dd')), (13, 'London', -2, to_date('2021-01-13','yyyy-mm-dd')), (14, 'London', -2, to_date('2021-01-14','yyyy-mm-dd')), (15, 'London', -4, to_date('2021-01-15','yyyy-mm-dd')), (16, 'London', -9, to_date('2021-01-16','yyyy-mm-dd')), (17, 'London', 0, to_date('2021-01-17','yyyy-mm-dd')), (18, 'London', -10, to_date('2021-01-18','yyyy-mm-dd')), (19, 'London', -11, to_date('2021-01-19','yyyy-mm-dd')), (20, 'London', -12, to_date('2021-01-20','yyyy-mm-dd')), (21, 'London', -11, to_date('2021-01-21','yyyy-mm-dd')); select *,count(*) over(partition by rn) as grp from( select *,id-row_number() over() as rn from weather where temperature < 0) x;