select 
t1.product_id,
pdiff as cnt
from(
select 
product_id,
pdiff,
dense_rank()over(order by pdiff desc,product_id asc) as py
from(
select 
product_id,
count(trace_id) as pdiff
from user_client_log
where
step="order"
group by product_id
) as t 
) as t1
where
t1.py=1