推荐阅读文章列表

大数据开发面经汇总【持续更新...】

我的大数据学习之路

大数据开发面试笔记V6.0

SQL题目

来自阿里数据分析二面

有一张淘宝购物订单表dwd_tb_order_detail_di，包含order_id（订单id）、commodity_id （商品id）、ds（下单时间）

问题：计算连续下单大于或等于3次的商品id

-- 举例如下：
-- 输入
order_id commodity_id       ds
    1         A      2024-12-21 10:10
    2         B      2024-12-21 10:12
    3         B      2024-12-21 10:15
    4         B      2024-12-21 10:20
    5         A      2024-12-21 10:40
    6         C      2024-12-21 11:05
    7         C      2024-12-21 11:10
    8         C      2024-12-21 11:30

-- 输出
commodity_id
    B
    C

答案解析

模拟数据

create table dwd_tb_order_detail_di (
order_id varchar(20),
commodity_id varchar(20),
ds varchar(20)
);
INSERT INTO dwd_tb_order_detail_di VALUES 
('1','A','2024-12-21 10:10'),
('2','B','2024-12-21 10:12'),
('3','B','2024-12-21 10:15'),
('4','B','2024-12-21 10:20'),
('5','A','2024-12-21 10:40'),
('6','C','2024-12-21 11:05'),
('7','C','2024-12-21 11:10'),
('8','C','2024-12-21 11:30')
;

思路分析

本题不同于常见的连续多次登录问题

何为连续购买三次及以上的商品？即此次购买商品后，下一次购买仍然是该商品，下下次购买仍然是该商品，下下下次购买可以是该商品也可以不是（不考虑下下下次）

具体代码

select commodity_id
from (
    select 
        commodity_id,
        lead(commodity_id, 1) over(order by ds) commodity_id_1,
        lead(commodity_id, 2) over(order by ds) commodity_id_2
    from dwd_tb_order_detail_di
) t
where commodity_id = commodity_id_1 
and commodity_id = commodity_id_2
group by commodity_id
;