Create pre-defined aggregate view in SurrealD

Question

I have a user table like below:

I also have a city table like below:

I then use the RELATE statement to create graph edges between user and city in a new table called post which looks like below:

I now want to create a new pre-defined aggregate view called post_stats which derives it's data from post and should look like below. Can someone help me with the exact statements to achieve this:

Answer 1

You can do this:

define table post_stats as
select 
    out as city_id, 
    count(action = "favorites") as favorites_count,
    count(action = "check_in") as check_in,
    count(action = "bucket_list") as bucket_list
from post
group by out;