牛客sql面试题 SQL15 某店铺的各商品毛利率及店铺整体毛利率
- 问题:请计算2021年10月以来店铺901中商品 毛利率大于24.9的商品信息及店铺整体毛利率。
- 商品毛利率=(1-进价/平均单件售价)*100%
-店铺毛利率=(1-总进价成本/总销售收入)*100%
- 思路,先计算店铺汇总 再纵向连接商品毛利率。
1.店铺毛利率=(1-总进价成本/总销售收入)*100%。
– 观察三表可知
#店铺901,2021年10月以来
-- 总进价价成本= sum(in_price*cnt) as 总进价
-- 总销售收入 sum( price*cnt ) as 总销售
select shop_id,
price*cnt as 总销售,
in_price*cnt 总进货,event_time
from tb_order_detail t3 left join tb_product_info t1
on t1.product_id=t3.product_id join tb_order_overall t2 on t2.order_id=t3.order_id
where shop_id='901' and date(event_time)>='2021-10-01'
- 注意点:
错误点:date(event_time) >=2021-10-01
#要打引号。
select '店铺汇总' as product_id,
concat(round((1-sum(总进货)/sum(总销售))*100,1),'%') profit_rate
from(
select shop_id,
price*cnt as 总销售,
in_price*cnt 总进货,event_time
from tb_order_detail t3 left join tb_product_info t1
on t1.product_id=t3.product_id join tb_order_overall t2 on t2.order_id=t3.order_id
where shop_id='901' and date(event_time)>='2021-10-01'
) t;
-计算商品毛利率 公式同上
select t.product_id as product_id,
concat(round((1-sum(in_price*cnt)/sum(price*cnt))*100,1),'%') as profit_rate
from tb_product_info t join tb_order_overall t1 join tb_order_detail t2 on t.product_id = t2.product_id and t1.order_id = t2.order_id
where shop_id=901 and date(event_time)>='20211001'
group by t.product_id
having profit_rate>0.249
order by product_id
- 最后将两表连接
select '店铺汇总' as product_id,
concat(round((1-sum(in_price*cnt)/sum(price*cnt))*100,1),'%') as profit_rate
from tb_product_info t join tb_order_overall t1 join tb_order_detail t2 on t.product_id = t2.product_id and t1.order_id = t2.order_id
where shop_id=901 and date(event_time)>='20211001'
union
(select t.product_id as product_id,
concat(round((1-sum(in_price*cnt)/sum(price*cnt))*100,1),'%') as profit_rate
from tb_product_info t join tb_order_overall t1 join tb_order_detail t2 on t.product_id = t2.product_id and t1.order_id = t2.order_id
where shop_id=901 and date(event_time)>='20211001'
group by t.product_id
having profit_rate>0.249
order by product_id);