题解 | #某店铺的各商品毛利率及店铺整体毛利率#

# 请计算2021年10月以来店铺901中商品毛利率大于24.9%的商品信息及店铺整体毛利率
with
    a as (
        select
            product_id,
            in_price,
            price,
            cnt
        from
            tb_product_info
            join tb_order_detail using (product_id)
            join tb_order_overall using (order_id)
        where
            date (event_time) >= '2021-10-01'
            and shop_id = 901
    ),
    b as (
        (
             # 店铺毛利率=(1-总进价成本/总销售收入)*100%
            select
                '店铺汇总' product_id,
                round(
                    (1 - sum(in_price * cnt) / sum(price * cnt)) * 100,
                    1
                ) profit_rate
            from
                a
        )
        union
        (
            # 商品毛利率=(1-进价/平均单件售价)*100%
            select
                product_id,
                round(
                    (1 - avg(in_price) / (sum(price * cnt) / sum(cnt))) * 100,
                    1
                ) profit_rate
            from
                a
            group by
                product_id
            having
                profit_rate > 24.9
            order by
                product_id
        )
    )
select
    product_id,
    concat (profit_rate, "%") profit_rate
from
    b