I think I got it! This will give the topic with the most votes per person that has at least 5 votes on it:
WITH query as (
select
t.user_id,
ucf.value,
count(*) as num,
t.created_at,
row_number() OVER (PARTITION BY t.user_id order by count(*) desc) as rank
from
topics t
join user_custom_fields ucf on t.id = cast(ucf.value as int)
where ucf.name = 'votes'
group by ucf.value, t.user_id, t.created_at
having count(*) >= 5
order by t.user_id)
select user_id, value as topic_id, created_at granted_at
from query where rank = 1
I ended up going with this instead so we could give a badge for each topic someone puts in that gets at least X votes in the correct category:
WITH query as (
select
t.user_id,
ucf.value,
count(*) as num,
t.created_at
from
topics t
join user_custom_fields ucf on t.id = cast(ucf.value as int)
where ucf.name = 'votes' and category_id = (
SELECT id FROM categories WHERE name ilike 'UI Experience Requests'
)
group by ucf.value, t.user_id, t.created_at
having count(*) >= 5
order by t.user_id)
select user_id, value as topic_id, created_at granted_at
from query