你可以用 row_number 按每个用户的频率分配等级。如果您使用的是SQLServer、MySQL8.0、oracle、postgresql，那么下面的解决方案应该可以使用。
你可以用 rank() 也。

select
  user,
  product,
  frequency
from
(
  select
    *,
    row_number() over (partition by user order by frequency desc) as rn
  from yourTable
) subq
where rn = 1

可采用的简单方法是：

Select user, product, frequency from
(select t.*,  max(t.frequency) OVER (PARTITION BY t.user) max_frq
from tab t) where frequency = max_frq;

另一种方法是按行编号：

Select usr, product, freq from(
Select t.*, row_number() OVER (Partition by usr order by freq desc) rn
from tab t)
where rn = 1;

rank（）：

Select usr, product, freq from (
Select t.*, rank() OVER (Partition by usr order by freq desc) rn
from tab t
)where rn = 1;

密级（）：

Select usr, product, freq from(
Select t.*, dense_rank() OVER (Partition by usr order by freq desc) rn
from tab t
)where rn = 1;