SQL面试题挑战05：找出恶意购买用户

SQL面试题挑战05：找出恶意购买用户

问题

下面是某电商网站的订单数据，包括order_id,user_id,order_status和operate_time四个字段，我们需要找出所有恶意购买的用户。恶意购买的用户定义是：同一个用户，在任意半小时内（含），取消订单次数>=3次的就被视为恶意买家。比如该样例数据中c用户就是恶意买家。

order_id    user_id    order_status     operate_time
1101         a         已支付        2023-01-01 10:00:00
1102         a         已取消        2023-01-01 10:10:00
1103         a         待支付        2023-01-01 10:20:00
1104         b         已取消        2023-01-01 10:30:00
1105         a         待确认        2023-01-01 10:50:00
1106         a         已取消        2023-01-01 11:00:00
1107         b         已取消        2023-01-01 11:40:00
1108         b         已取消        2023-01-01 11:50:00
1109         b         已支付        2023-01-01 12:00:00
1110         b         已取消        2023-01-01 12:11:00
1111         c         已取消        2023-01-01 12:20:00
1112         c         已取消        2023-01-01 12:30:00
1113         c         已取消        2023-01-01 12:55:00
1114         c         已取消        2023-01-01 13:00:00

建表语句

drop table if exists order_info;
create table order_info(
  order_id varchar(20),
  user_id varchar(20),
  order_status varchar(10),
  operate_time datetime
);
insert into order_info values
(1101,'a','已支付', '2023-01-01 10:00:00'),
(1102,'a','已取消', '2023-01-01 10:10:00'),
(1103,'a','待支付', '2023-01-01 10:20:00'),
(1104,'b','已取消', '2023-01-01 10:30:00'),
(1105,'a','待确认', '2023-01-01 10:50:00'),
(1106,'a','已取消', '2023-01-01 11:00:00'),
(1107,'b','已取消', '2023-01-01 11:40:00'),
(1108,'b','已取消', '2023-01-01 11:50:00'),
(1109,'b','已支付', '2023-01-01 12:00:00'),
(1110,'b','已取消', '2023-01-01 12:11:00'),
(1111,'c','已取消', '2023-01-01 12:20:00'),
(1112,'c','已取消', '2023-01-01 12:30:00'),
(1113,'c','已取消', '2023-01-01 12:55:00'),
(1114,'c','已取消', '2023-01-01 13:00:00'

解答

解法1：自连接

思路：首先提取出'已取消'的订单，然后对其进行自连接，连接的条件就是id相同且操作时间相差在1-30分钟之间（这里用1作为下界可以排除很多自连接的情况）。然后按照id和operate_time分组，出现次数大于等于2次即是满足条件的用户（因为前面排除了自连接，这样就会少一次记录，所以这里大于等于2即可）。

with tmp as (
    select
        user_id,
        operate_time
    from
        order_info
    where
        order_status='已取消'
)
select
    distinct t1.user_id
from
    tmp t1
    inner join tmp t2 on t1.user_id=t2.user_id and timestampdiff(minute,t1.operate_time,t2.operate_time) between 1 and 30
group by
    t1.user_id,
    t1.operate_time
having
    count(*)>=2 -- 因为自连接没有包括自己所以这里是2

解法2：滑动窗口

思路：首先提取出'已取消'的订单，同时将datetime时间转为unix时间戳，方便后续开窗。然后对其开窗，窗口大小是operate_time和当前行operate_time差值在1800s内的行（即是以当前行为锚点，在[operate_time-1800,operate_time]的时间范围内的行），然后统计窗口内的行数即可。

with tmp as (
    select
        user_id,
        unix_timestamp(operate_time)operate_time
    from
        order_info
    where
        order_status='已取消'
)

select
    user_id
from
    (
        select
            user_id,
            count(*)over(
                partition by user_id 
                order by operate_time 
                range between 1800 preceding and current row
                )cnt
        from
            tmp
     )t0
group by
    user_id
having
    max(cnt)>=3