select channel_id, order_id, amount, rank from ( select channel_id, order_id, amount, rank() over(partition by channel_id order by amount desc) `rank`
from order where dt = '2021-08-04' group by channel_id,order_id,amount ) t1 where t1.rank <= 3
select channel_id, order_id, amount, rank from ( select channel_id, order_id, amount, rank() over(partition by channel_id order by amount desc) `rank`
from order where dt = '2021-08-04' group by channel_id,order_id,amount ) t1 where t1.rank <= 3