题解 | 商品价格排名

select
    product_id,
    product_name,
    type,
    price
from
    (
        with rank_product_info as(
            select 
                product_id,
                price,
                type,
                product_name,
                rank() over(partition by type order by price desc) as ranking
            from product_info
        )
        select *
        from rank_product_info
        where ranking < 3
    ) as top2_product_info
order by 
    price desc, product_id
limit 3