Show only the highest value in a INNER JOIN

Question

Hi I'm just learning SQL, I was asked to find the item that was the most popular for each customer. I have these tables:

    create table sales 
    (
        "customer_id" VARCHAR(1),
        "order_date" DATE,
        "product_id" INTEGER
    )
    
  create table  menu 
    (
        "product_id" INTEGER,
    
        "product_name" VARCHAR(5),
        "price" INTEGER
    )

So I did this:

        SELECT sales.customer_id, menu.product_name, COUNT(menu.product_name) AS Times_purchased
    FROM menu
    INNER JOIN sales
    ON menu.product_id = sales.product_id
    GROUP BY sales.customer_id, menu.product_name
    ORDER BY customer_id, Times_purchased DESC

And got this result:

customer_id	product_name	Times_purchased
A	ramen	3
A	curry	2
A	sushi	1
B	sushi	2
B	curry	2
B	ramen	2
C	ramen	3

And Im happy with it since I can point out the popular iteam for each customer. But Is there a way to only see the most populat items and not the rest? I looked at other questions here but I can't make it work

Answer 1

First you get most popular for each customer (get Count of Menu)

Then show popular Produnct for each customer(Row_number of Window function)

with _listWithCount as (
        select 
                 customer_id
                ,product_name
                ,COUNT(b.product_name) over(partition by a.customer_id,b.product_id) as Times_purchased
        from sales a
        inner join menu b on a.product_id=b.product_id
)
select customer_id,product_name,Times_purchased
from (
        select *,ROW_NUMBER() over(partition by  customer_id order by Times_purchased desc) rw
        from  _listWithCount
)d
where d.rw=1

Demo