Calculate mode in Athena SQL / Presto

mode_calculation_athena.sql

/* It calculates the mode of a records-like `maintable`
*/
with counter as (
  select
    service,
    array[
      cast(row('Bob',  count_if(name = 'Bob')) AS row(name varchar, age interger)),
      cast(row('Alice', count_if(name = 'Alice')) AS row(name varchar, age interger)),
      cast(row('Jane',  count_if(name = 'Jane')) AS row(name varchar, age interger))
    ] as users
  from maintable
  group by service
)
select service, max_by(user.name, user.age) modal_name
from as_map, unnest(counter) t(user)
group by service