9、刷牛客网SQL题（三）

题目来源

牛客网SQL大厂真题——电商场景（某东商城）

---

一、SQL1（简单）

1.题目内容

描述
现有订单总表tb_order_overall

场景逻辑说明：
用户将购物车中多件商品一起下单时，订单总表会生成一个订单（但此时未付款，status-订单状态为0，表示待付款）；
当用户支付完成时，在订单总表修改对应订单记录的status-订单状态为1，表示已付款；
若用户退货退款，在订单总表生成一条交易总金额为负值的记录（表示退款金额，订单号为退款单号，status-订单状态为2表示已退款）。

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

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

输出示例：
示例数据输出如下：

解释：
2021年10月有3笔已付款的订单，1笔未付款订单，总交易金额为109800；2021年11月有2笔已付款订单，1笔未付款订单，
总交易金额为111900（还有1笔退款订单由于已计算了付款的订单金额，无需计算在GMV中）。

2.思路分析

题目要求的是2021年每月的GMV，所以首先要根据event_time筛选出来2021年的数据，又因为GMV是由已付款订单和未付款订单两者之和组成的，所以还要筛选出来status为1和0的数据。筛选出来后，根据年—月进行分组求和即可。

3.语句实现

select
    date_format(event_time,'%Y-%m') month,
    sum(abs(total_amount)) GMV
from tb_order_overall
where year(event_time)='2021' and (status=1 or status=0)
group by date_format(event_time,'%Y-%m')
having GMV>100000
order by GMV

二、SQL1（简单）

1.题目内容

描述
现有用户对展示的商品行为表tb_user_event

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

注：
商品点展比=点击数÷展示数；
加购率=加购数÷点击数；
成单率=付款数÷加购数；退货率=退款数÷付款数，
当分母为0时整体结果记为0，结果中各项指标保留3位小数，并按商品ID升序排序。

输出示例：
示例数据的输出结果如下

解释：
在2021年10月商品8001被展示了6次，点击了5次，加购了4次，付款了3次，退款了1次，因此点击率为5/6=0.833，加购率为4/5=0.800，
成单率为3/4=0.750，退货率为1/3=0.333（保留3位小数）；

2.思路分析

题目要求的时2021年10月每个有展示记录的退货率不大于0.5的商品各项指标，首先要注意以下几点：
1）有展示记录：每一条数据就是一个展示记录
2）退货率不大于0.5：退货率是退款数除以付款数，而这里付款数可能为0，所以要注意判断分母是否为0

首先，要根据event_time过滤出来时间为2021-10月份的数据，按商品id分组，count(*)就是展示数，count(if())结合if_click、if_cart、if_payment、if_refund这些字段可以分别求出来加购数、付款数、退款数，进而做除法就可以得到最后结果了，最后按商品id升序即可。（做除法时，先要判断分母是否为0）

3.语句实现

select
    product_id,
    round(click_count/total_count,3) ctr,
    round(if(click_count=0,0,cart_count/click_count),3) cart_rate,
    round(if(cart_count=0,0,pay_count/cart_count),3) payment_rate,
    round(if(pay_count=0,0,refund_count/pay_count),3) refund_rate
from
(
    select
        product_id,
        count(id) total_count,
        sum(if(if_click=1,1,0)) click_count,
        sum(if(if_cart=1,1,0)) cart_count,
        sum(if(if_payment=1,1,0)) pay_count,
        sum(if(if_refund=1,1,0)) refund_count
    from tb_user_event
    where date_format(event_time,'%Y-%m')='2021-10'
    group by product_id
)t1
where pay_count=0 or refund_count/pay_count<=0.5
order by product_id

三、SQL3（中等）

1.题目内容

描述
商品信息表tb_product_info

订单总表tb_order_overall

订单明细表tb_order_detail

场景逻辑说明：

用户将购物车中多件商品一起下单时，订单总表会生成一个订单（但此时未付款，status-订单状态为0表示待付款），在订单明细表生成该订单中每个商品的信息；

当用户支付完成时，在订单总表修改对应订单记录的status-订单状态为1表示已付款；

若用户退货退款，在订单总表生成一条交易总金额为负值的记录（表示退款金额，订单号为退款单号，status-订单状态为2表示已退款）。

问题：请计算2021年10月以来店铺901中商品毛利率大于24.9%的商品信息及店铺整体毛利率。

注：商品毛利率=(1-进价/平均单件售价)*100%；
店铺毛利率=(1-总进价成本/总销售收入)*100%。
结果先输出店铺毛利率，再按商品ID升序输出各商品毛利率，均保留1位小数。

输出示例：
示例数据的输出结果如下：

解释：
店铺901有两件商品8001和8003；8001售出了3件，销售总额为25500，进价总额为18000，毛利率为1-18000/25500=29.4%，8003售出了1件，售价为18000，进价为12000，毛利率为33.3%；
店铺卖出的这4件商品总销售额为43500，总进价为30000，毛利率为1-30000/43500=31.0%

2.思路分析

题目要求的是2021年10月以来店铺901中商品毛利率大于24.9%的商品信息以及店铺整体毛利率。
要注意以下几个地方：
1）2021年10月以来
2）店铺901
3）毛利率大于24.9%的商品信息
4）店铺的总毛利率也要包括那些毛利率低于24.9%的商品数据

所以，我们首先要从tb_order_overall表中筛选出来时间在2021年10月及以后的数据，同时它的status还必须为1，这样我们可以拿到order_id。
然后跟tb_order_detail表进行连接，得到对应时间范围内的所有的明细数据，我们可以按product_id进行分组，统计每个商品的总售价和总件数。
接着，从tb_product_info表中筛选出来shop_id为901的数据，然后再跟第二步中得到的表进行内连接，可以得到这个商铺下卖的商品的卖出的总件数、总售价，当然也可以算出来总进价。

因为最终结果还让求出该店铺的总毛利率，所以我把上面的得到的表用with表示以下，方便下面重复使用。
可以直接sum所有的总进价和总售价，作计算可以得出总毛利率，然后跟每个product_id的毛利率进行union起来（商品的毛利率要大于24.9%），就可以得到最终的结果了。

3.语句实现

with tmp as
(
    select
        t3.product_id,
        sale_price,
        in_price*count_num in_price
    from
    (
        select
            product_id,
            sum(sale_price) sale_price,
            sum(cnt) count_num
        from
        (
            select
                order_id
            from tb_order_overall
            where date_format(event_time,'%Y-%m')>='2021-10' and status=1
        )t1
        inner join
        (
            select
                order_id,
                product_id,
                price*cnt sale_price,
                cnt
            from tb_order_detail
        )t2
        on t1.order_id=t2.order_id
        group by product_id
    )t3
    inner join
    (
        select
            product_id,
            in_price
        from tb_product_info
        where shop_id=901
    )t4
    on t3.product_id=t4.product_id
)
select
    '店铺汇总' as product_id,
    concat(round(100-sum(in_price)/sum(sale_price)*100,1),'%') profit_rate
from tmp
union
(
    select
        product_id,
        concat(round(100-in_price*1.0/sale_price*100,1),'%') profit_rate
    from tmp
    where (1-in_price/sale_price)>0.249
    order by product_id
)

四、SQL4（中等）

1.题目内容

描述
商品信息表tb_product_info

订单总表tb_order_overall

订单明细表tb_order_detail

场景逻辑说明：
用户将购物车中多件商品一起下单时，订单总表会生成一个订单（但此时未付款， status-订单状态-订单状态为0表示待付款），在订单明细表生成该订单中每个商品的信息；

当用户支付完成时，在订单总表修改对应订单记录的status-订单状态-订单状态为1表示已付款；

若用户退货退款，在订单总表生成一条交易总金额为负值的记录（表示退款金额，订单号为退款单号，订单状态为2表示已退款）。

问题：请统计零食类商品中复购率top3高的商品。

注：复购率指用户在一段时间内对某商品的重复购买比例，复购率越大，则反映出消费者对品牌的忠诚度就越高，也叫回头率
此处我们定义：某商品复购率 = 近90天内购买它至少两次的人数 ÷ 购买它的总人数
近90天指包含最大日期（记为当天）在内的近90天。结果中复购率保留3位小数，并按复购率倒序、商品ID升序排序

输出示例：
示例数据的输出结果如下：

解释：
商品8001、8002、8003都是零食类商品，8001只被用户102购买了两次，复购率1.000；
商品8002被101购买了两次，被105购买了1次，复购率0.500；
商品8003被102购买两次，被101和105各购买1次，复购率为0.333。

2.思路分析

题目过程有点复杂，但是难度不大。

要求的是零食类商品中复购率top3高的商品。
1）首先，我们要先从tb_order_overall表中过滤出来最近90天内的数据，而且必须是已付款状态（status=1），要想拿到最近90天的数据，要先知道最大的时间，然后才能作比较。

2）tb_order_overall表过滤后的数据要跟tb_order_detail表进行内连接，拿到最细的粒度，商品项—用户粒度的信息。
3）我们要从tb_product_info表中筛选出来tag为零食的数据，拿到对应的product_id
4）将2和3中的表进行内连接过滤出来tag为零食的所有商品的销售记录，再按product_id和uid进行分组，统计出来每个用户购买每个商品的次数
5）在4的基础上按product_id分组，可以统计出来该商品购买大于1次的人数和购买的总人数，最后相除，再按要求排序即可。

3.语句实现

with tmp1 as
#找到零食类商品的product_id
(
    select
        product_id
    from tb_product_info
    where tag='零食'
),
#找到已付款状态的订单，且订单时间为3个月内
tmp2 as
(
    select
        order_id,
        uid
    from
    (
        select
            order_id,
            uid,
            date(event_time) the_time
        from tb_order_overall
        where status=1
    )t1
    inner join
    (
        select
            max(date(event_time)) today
        from tb_order_overall
    )t2
    on 1=1
    where datediff(today,the_time)<=89
)

select
    product_id,
    round(sum(if(the_count>1,1,0))/count(*),3) repurchase_rate
from
(
    #一个用户买一种商品买了几次
    select
        product_id,
        uid,
        count(*) the_count
    from
    (
        #找到90天内订单项信息,一个用户买了一种商品的信息
        select
            t2.product_id,
            uid
        from
        (
            select
                order_id,
                uid
            from tmp2
        )t1
        inner join
        (
            select
                order_id,
                product_id
            from tb_order_detail
        )t2
        on t1.order_id=t2.order_id
        inner join tmp1
        on tmp1.product_id=t2.product_id
    )t3
    group by product_id,uid
)t4
group by product_id
order by sum(if(the_count>1,1,0))/count(*) desc,product_id asc
limit 3

五、SQL5（较难）

1.题目内容

描述
商品信息表tb_product_info

订单总表tb_order_overall

订单明细表tb_order_detail

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

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

输出示例：
示例数据的输出结果如下：

解释：
2021年10月有3个新用户，102的首单为301002，订单金额为235，商品总金额为85+180=265，优惠金额为30；
101的首单为301003，订单金额为300，商品总金额为140+180=320，优惠金额为20；
104的首单为301005，订单金额为160，商品总金额为180，优惠金额为20；
平均首单客单价为(235+300+160)/3=231.7，平均获客成本为(30+20+20)/3=23.3

2.思路分析

题目要求的是2021年10月商城里所有新用户的首单平均交易金额（客单价）和平均获客成本（保留一位小数）。

1）首先，要找到2021年10月的新用户，那么就要过滤出来这个所有用户最早下单的时间，如果这个时间在2021年10月内，那么这个用户就是新用户，这样找到了用户的id和第一次下单的时间
2）在1的基础上跟tb_order_overall表进行内连接，找到这些用户第一次下单的订单id，同时还可以获得对应订单的总价格（这里先不要求出来所有订单的总金额，因为下面还要根据订单id筛选出来对应的订单明细表的数据）
3）先在tb_order_detail表中按order_id分组，求出来每个订单的原始金额
4）用2中的表和2中的表进行内连接，可以得到对应首次订单的最终金额和原始金额，直接avg求平均即可得到最终结果（avg(最终金额）、avg(原始金额-最终金额））

3.语句实现

select
    round(avg(total_amount),1) avg_amount,
    round(avg(the_money-total_amount),1) avg_cost
from
(
    #得到的是所有新用户第一次的购买记录
    select
        order_id,
        total_amount
    from
    (
        select
            order_id,
            uid,
            event_time,
            total_amount
        from tb_order_overall
    )t1
    inner join
    (
        #找到2021年10月所有的用户第一次登录时间
        select
            uid,
            min(event_time) first_time
        from tb_order_overall
        group by uid
        having date_format(first_time,'%Y-%m')='2021-10'
    )t2
    on t1.uid=t2.uid and t1.event_time=t2.first_time
)t3
inner join
(
    select
        order_id,
        sum(price*cnt) the_money
    from tb_order_detail
    group by order_id
)t4
on t3.order_id=t4.order_id