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

/*
需要注意在
		sum(sales_amount) as total_sales_amount,
        (sum(sales_amount) - sum(cost_amount)) / sum(sales_amount) as profit_rate
这部分为sum(sales_amount)指定的别名不能为sales_amount,最外层的查询再次指定为sales_amount即可
*/
select
    product_id,
    product_name,
    category_id,
    total_sales_amount as sales_amount,
    round(profit_rate, 2) as profit_rate
from
    (
        select
            p.product_id,
            product_name,
            category_id,
            total_sales_amount,
            profit_rate,
            rank() over(partition by category_id order by total_sales_amount desc) as ranking
        from 
            product_category as p
            left join 
            (
                select
                    product_id,
                    sum(sales_amount) as total_sales_amount,
                    (sum(sales_amount) - sum(cost_amount)) / sum(sales_amount) as profit_rate
                from sales_and_profit
                group by product_id                 
            ) as s on p.product_id=s.product_id
    ) as ranking_tb
where profit_rate > 0.3 and ranking < 4
order by 
    category_id, sales_amount desc, product_id