题解 | 查询出不同类别商品中，销售金额排名前三且利润率超过 20%的商品信息

(
        select
            p.product_id,
            product_name,
            category_id,
            sales_amount,
            round((sales_amount - cost_amount) / sales_amount, 2) as profit_rate,
            row_number() over (
                partition by
                    category_id
                order by
                    sales_amount desc
            ) as rk
        from
            product_category p
            join sales_and_profit s on p.product_id = s.product_id
    ) a

连接两张表后，计算利润率和各种类内排名。之后用where筛选条件即可。

select
    product_id,
    product_name,
    category_id,
    sales_amount,
    profit_rate
from
    (
        select
            p.product_id,
            product_name,
            category_id,
            sales_amount,
            round((sales_amount - cost_amount) / sales_amount, 2) as profit_rate,
            row_number() over (
                partition by
                    category_id
                order by
                    sales_amount desc
            ) as rk
        from
            product_category p
            join sales_and_profit s on p.product_id = s.product_id
    ) a
where
    rk <= 3
    and profit_rate > 0.2