本文主要是介绍NIUKE SQL：大厂面试真题（三） 【某东商城】，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
SQL168 计算商城中2021年每月的GMV
问题：请计算商城中2021年每月的GMV，输出GMV大于10w的每月GMV，值保留到整数
注：GMV为已付款订单和未付款订单两者之和。结果按GMV升序排序
SELECTDATE_FORMAT(event_time,'%Y-%m') AS month,ROUND(SUM(total_amount),0) AS GMV
FROM tb_order_overall
WHERE status IN (0,1)AND YEAR(event_time)=2021
GROUP BY month
HAVING GMV>100000
ORDER BY GMVSQL169 统计2021年10月每个退货率不大于0.5的商品各项指标
问题：请统计2021年10月每个有展示记录的退货率不大于0.5的商品各项指标
商品点展比=点击数÷展示数
加购率=加购数÷点击数
成单率=付款数÷加购数；退货率=退款数÷付款数
当分母为0时整体结果记为0，结果中各项指标保留3位小数，并按商品ID升序排序
SELECTproduct_id, ROUND(SUM(if_click)/COUNT(id),3) AS ctr, #商品点展比=点击数÷展示数ROUND(SUM(if_cart)/SUM(if_click),3) AS cart_rate, #加购率=加购数÷点击数ROUND(SUM(if_payment)/SUM(if_cart),3) AS payment_rate, #成单率=付款数÷加购数ROUND(SUM(if_refund)/SUM(if_payment),3) AS refund_rate #退货率=退款数÷付款数
FROM tb_user_event
WHERE YEAR(event_time)=2021 AND MONTH(event_time)=10
GROUP BY product_id
HAVING refund_rate<=0.5
ORDER BY product_idSQL170 某店铺的各商品毛利率及店铺整体毛利率
问题：请计算2021年10月以来店铺901中商品毛利率大于24.9%的商品信息及店铺整体毛利率
商品毛利率=(1-进价/平均单件售价)*100%
店铺毛利率=(1-总进价成本/总销售收入)*100%
结果先输出店铺毛利率，再按商品ID升序输出各商品毛利率，均保留1位小数
SELECT'店铺汇总' AS product_id,CONCAT(ROUND((1-SUM(in_price*cnt)/SUM(price*cnt))*100,1),'%') AS profit_rate
FROM tb_order_detail
JOIN tb_product_info USING(product_id)
JOIN tb_order_overall USING(order_id)
WHERE shop_id=901 AND DATE(event_time)>=20211001
AND status<>2UNIONSELECTproduct_id,CONCAT(ROUND((1-MAX(in_price)/(SUM(price*cnt)/SUM(cnt)))*100,1),'%') AS profit_rate
FROM tb_order_detail
JOIN tb_product_info USING(product_id)
JOIN tb_order_overall USING(order_id)
WHERE shop_id='901' AND DATE(event_time)>=20211001 AND status<>2
GROUP BY product_id
HAVING (1-MAX(in_price)/(SUM(price*cnt)/SUM(cnt)))*100>24.9
ORDER BY IF(product_id='店铺汇总',0,1),product_idSQL171 零食类商品中复购率top3高的商品
问题：请统计零食类商品中复购率top3高的商品。
某商品复购率 = 近90天内购买它至少两次的人数 ÷ 购买它的总人数
近90天指包含最大日期（记为当天）在内的近90天,结果中复购率保留3位小数，并按复购率倒序、商品ID升序排序
SELECT product_id,ROUND(SUM(repurchase) / COUNT(repurchase), 3) as repurchase_rate
FROM (SELECT product_id, IF(COUNT(event_time)>1, 1, 0) as repurchaseFROM tb_order_detailJOIN tb_order_overall USING(order_id)JOIN tb_product_info USING(product_id)WHERE tag="零食" AND event_time >= (SELECT DATE_SUB(MAX(event_time), INTERVAL 89 DAY)FROM tb_order_overall)GROUP BY uid, product_id
) as A
GROUP BY product_id
ORDER BY repurchase_rate DESC, product_id
LIMIT 3SQL172 10月的新户客单价和获客成本
问题：请计算2021年10月商城里所有新用户的首单平均交易金额（客单价）和平均获客成本（保留一位小数）
订单的优惠金额 = 订单明细里的{该订单各商品单价×数量之和} - 订单总表里的{订单总金额}
WITH A AS
(SELECTuid,event_time,SUM(total_amount/total_cnt) AS amount,SUM(price*cnt-total_amount/total_cnt) AS costFROM tb_order_detailJOIN tb_order_overall USING(order_id)JOIN tb_product_info USING(product_id)WHERE (uid,event_time) IN (SELECT uid,MIN(event_time) FROM tb_order_overall GROUP BY uid)AND DATE(event_time) BETWEEN 20211001 AND 20211031GROUP BY uid,event_time
)SELECTROUND(AVG(amount),1) AS avg_amount,ROUND(AVG(cost),1) AS avg_cost
FROM ASQL173 店铺901国庆期间的7日动销率和滞销率
问题：请计算店铺901在2021年国庆头3天的7日动销率和滞销率，结果保留3位小数，按日期升序排序
动销率定义为店铺中一段时间内有销量的商品占当前已上架总商品数的比例（有销量的商品/已上架总商品数)
滞销率定义为店铺中一段时间内没有销量的商品占当前已上架总商品数的比例。（没有销量的商品/已上架总商品数)
只要当天任一店铺有任何商品的销量就输出该天的结果，即使店铺901当天的动销率为0
SELECT dt, sale_rate, 1 - sale_rate as unsale_rate
FROM (SELECT dt, ROUND(MIN(sale_pid_cnt) / COUNT(all_pid), 3) as sale_rateFROM (
#国庆期间店铺901截止每天的近7天有销量的商品数SELECT dt, COUNT(DISTINCT IF(shop_id!=901, NULL, product_id)) as sale_pid_cntFROM (SELECT DISTINCT DATE(event_time) as dtFROM tb_order_overallWHERE DATE(event_time) BETWEEN '2021-10-01' AND '2021-10-03') as t_datesLEFT JOIN (SELECT DISTINCT DATE(event_time) as event_dt, product_idFROM tb_order_overallJOIN tb_order_detail USING(order_id)) as t_dt_pid ON DATEDIFF(dt,event_dt) BETWEEN 0 AND 6LEFT JOIN tb_product_info USING(product_id)GROUP BY dt) as t_dt_901_pid_cntLEFT JOIN (
#店铺901每个商品上架日期SELECT DATE(release_time) as release_dt, product_id as all_pidFROM tb_product_infoWHERE shop_id=901) as t_release_dt ON dt >= release_dt # 当天店铺901已上架在售的商品GROUP BY dt
) as t_dt_sr这篇关于NIUKE SQL：大厂面试真题（三） 【某东商城】的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
