Getting only the highest counts per type in Postgres

Say I have a table with kids and their toys.

CREATE TABLE kids_toys (
  kid_name character varying,
  toy_type character varying,
  toy_name character varying
);

I want to get a list of the the most popular toy type for each kid, grouped by kid. So the result would be

Assuming Postgres 15 as the engine, how would I query to do this? I keep getting stuck on how to generate the count but then only take the max result from each per-kid count.

In Postgres, I would recommend distinct on, which can get the job done in a single pass:

select distinct on (kid_name) kid_name, toy_type, count(*) cnt
from kids_toys
group by kid_name, toy_type
order by kid_name, count(*) desc, toy_type

The query groups the dataset by kid and toy. Then distinct on ensures that only one record is returned for each kid; the order by clause puts the most popular toy of each kid first. If there are ties, the first toy is picked (alphabetically).

If you wanted to retain ties (which Postgres' distinct on cannot do), we could use rank() and fetch with ties instead:

select kid_name, toy_type, count(*) cnt
from kids_toys
group by kid_name, toy_type
order by rank() over(partition by kid_name order by count(*) desc)
fetch first row with ties

First, group by kid_name and toy_type to find how many toys the kid has from each type.

Then, add a row_number window function partitioned only by the kid_name and order by the count descending to find the position of each toy_type from highest count to lowest for each individual kid

And lastly, filter only the records with row_num = 1

Also, if you would like the top 3 toys per kid for example, you can use row_num <= 3 instead

select kid_name, toy_type, cnt
from
(select kid_name, toy_type, cnt, row_number() over(partition by kid_name order by cnt desc) as row_num
  from (
    select kid_name, toy_type, count(*) as cnt
    from kids_toys
    group by kid_name, toy_type
  ) as grouped
) as with_row_num
where row_num = 1