-- Hint: use Ctrl+Enter for SQL autocomplete
create table user_hobbies(
id int,
age int,
hobbies varchar(32)
);
insert into user_hobbies values
(1, 18, 'fishing'),
(2, 20, 'football'),
(3, 18, 'basketball'),
(4, 18, 'fishing');
create view age_hobbies as
select age, hobbies, count(*) cnt
from user_hobbies
group by age, hobbies
;
select
age,
hobbies
from (
select
age,
hobbies,
row_number() over (partition by age order by cnt desc) rnm
from age_hobbies
) age_hobbies where rnm = 1;
select
age_hobbies.age,
age_hobbies.hobbies
from age_hobbies
join (
select age, max(cnt) maxcnt from age_hobbies group by age
) max_age_hobbies on
age_hobbies.age = max_age_hobbies.age and
age_hobbies.cnt = max_age_hobbies.maxcnt;