supabase group by and populate foreign key function

Question

I got two tables in supabase:

users:

uuid, name, created_at

messages:

uuid, text, created_at, created_by (foreign key to users.uuid)

What I want to do is a function that returns the top 10 most active members within the latest 24 hours. So I want to group and count created_by in the messages table and after I've done that I want to fetch the users real name instead of created_by that is a foreign key pointing to the users.uuid

Answer 1

If names are unique

select users.name,count(*)
from messages join users on messages.created_by=users.uuid
where messages.created_at>=now()-'24h'::interval
group by 1 order by 2 desc limit 10;

Otherwise

select users.name,a.count from
(   select created_by,count(*)
    from messages
    where messages.created_at>=now()-'24h'::interval
    group by 1 order by 2 desc limit 10 ) a
join users on a.created_by=users.uuid order by 2 desc;

If you really need a function, you can wrap the statement in one:

create function top_10_chatters_24h()
returns table (name text,count bigint) as $f$
select users.name,a.count from
(   select created_by,count(*)
    from messages
    where messages.created_at>=now()-'24h'::interval
    group by 1 order by 2 desc limit 10 ) a
join users on a.created_by=users.uuid order by 2 desc; 
$f$ language sql;

select * from top_10_chatters_24h();

Demo at db<>fiddle