NIUKE SQL：大厂面试真题（三） 【某东商城】

SQL168 计算商城中2021年每月的GMV

问题：请计算商城中2021年每月的GMV，输出GMV大于10w的每月GMV，值保留到整数

注：GMV为已付款订单和未付款订单两者之和。结果按GMV升序排序

SELECT
    DATE_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 GMV

SQL169 统计2021年10月每个退货率不大于0.5的商品各项指标

问题：请统计2021年10月每个有展示记录的退货率不大于0.5的商品各项指标

商品点展比=点击数÷展示数

加购率=加购数÷点击数

成单率=付款数÷加购数；退货率=退款数÷付款数

当分母为0时整体结果记为0，结果中各项指标保留3位小数，并按商品ID升序排序

SELECT
    product_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_id

SQL170 某店铺的各商品毛利率及店铺整体毛利率

问题：请计算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<>2

UNION

SELECT
    product_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_id

SQL171 零食类商品中复购率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 repurchase
    FROM tb_order_detail
        JOIN 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 3

SQL172 10月的新户客单价和获客成本

问题：请计算2021年10月商城里所有新用户的首单平均交易金额（客单价）和平均获客成本（保留一位小数）

订单的优惠金额 = 订单明细里的{该订单各商品单价×数量之和} - 订单总表里的{订单总金额}

WITH A AS 
(
    SELECT
        uid,
        event_time,
        SUM(total_amount/total_cnt) AS amount,
        SUM(price*cnt-total_amount/total_cnt) AS cost
    FROM tb_order_detail
        JOIN 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 20211031
    GROUP BY uid,event_time
)

SELECT
    ROUND(AVG(amount),1) AS avg_amount,
    ROUND(AVG(cost),1) AS avg_cost
FROM A

SQL173 店铺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_rate
    FROM (
#国庆期间店铺901截止每天的近7天有销量的商品数
        SELECT dt, COUNT(DISTINCT IF(shop_id!=901, NULL, product_id)) as sale_pid_cnt
        FROM (
            SELECT DISTINCT DATE(event_time) as dt
            FROM tb_order_overall
            WHERE DATE(event_time) BETWEEN '2021-10-01' AND '2021-10-03'
        ) as t_dates
        LEFT JOIN (
            SELECT DISTINCT DATE(event_time) as event_dt, product_id
            FROM tb_order_overall
            JOIN tb_order_detail USING(order_id)
        ) as t_dt_pid ON DATEDIFF(dt,event_dt) BETWEEN 0 AND 6
        LEFT JOIN tb_product_info USING(product_id)
        GROUP BY dt
    ) as t_dt_901_pid_cnt
    LEFT JOIN (
#店铺901每个商品上架日期
        SELECT DATE(release_time) as release_dt, product_id as all_pid
        FROM tb_product_info
        WHERE shop_id=901
    ) as t_release_dt ON dt >= release_dt # 当天店铺901已上架在售的商品
    GROUP BY dt
) as t_dt_sr