SQLize | PHPize | SQLtest

create table t ( name varchar, event char, event_time timestamp ); insert into t values ('Вася', 'А', now()::timestamp - INTERVAL '10 min'), ('Петя', 'Б', now()::timestamp - INTERVAL '9 min'), ('Вася', 'Б', now()::timestamp - INTERVAL '8 min'), ('Женя', 'А', now()::timestamp - INTERVAL '7 min'), ('Петя', 'Б', now()::timestamp - INTERVAL '6 min'), ('Вася', 'Б', now()::timestamp - INTERVAL '5 min'), ('Женя', 'А', now()::timestamp - INTERVAL '4 min'), ('Петя', 'А', now()::timestamp - INTERVAL '3 min'), ('Петя', 'Б', now()::timestamp - INTERVAL '2 min'), ('Вася', 'Б', now()::timestamp - INTERVAL '1 min'); with s as ( select * , row_number() over (partition by name order by event_time asc) - row_number() over (partition by name, event order by event_time asc) gr from t ) select name, event, count(*) from s group by name, event, gr order by count desc limit 1;