Find the mode in BigQuery

Question

The mode is the value that appears most often in a set.

I would like something like:

SELECT
    t.id as t_id,
    GROUP_CONCAT(t.value) as value_list,
    MODE(t.value) AS value_mode
FROM dataset.table as t
GROUP BY t_id

such that, for example:

t_id    value_list     value_mode
1       2,2,2,3,6,6    2

How is that done?

EDIT: The value_list is just there for illustration purpose. Only need the mode

Answer 1

select id, value as value_list, v as value_mode
from (
  select 
    id, value, v, 
    count(1) as c, 
    row_number() over(partition by id order by c desc) as top
  from (
    select id, value, split(value) as v 
    from dataset.table 
  )
  group by id, value, v
)
where top = 1