SQL面试题练习 —— 用户行为路径分析

题目来源：拼多多。

1 题目

有一张用户行为日志表 ods_usr_log, 包含用户id（user_id）和页面id（page_id）以及进入页面时间（in_ts）

问题：统计每天进入A页面后，立刻进入B页面，又进入C页面的用户数（注意：进入C页面之前可能进入过其他页面）

样例数据

2 建表语句

insert into ods_usr_log(user_id, page_id, in_ts) values 
(1, 'A', '2020-1-1 12:01:03'),
(2, 'A', '2020-1-1 12:01:04'),
(3, 'A', '2020-1-1 12:01:05'),
(1, 'B', '2020-1-1 12:03:03'),
(1, 'A', '2020-1-1 12:04:03'),
(1, 'C', '2020-1-1 12:06:03'),
(1, 'D', '2020-1-1 12:11:03'),
(2, 'A', '2020-1-1 12:07:04'),
(3, 'C', '2020-1-1 12:02:05'),
(2, 'C', '2020-1-1 12:09:03'),
(2, 'A', '2020-1-1 12:10:03'),
(4, 'A', '2020-1-1 12:01:03'),
(4, 'C', '2020-1-1 12:11:05'),
(4, 'D', '2020-1-1 12:15:05'),
(1, 'A', '2020-1-2 12:01:03'),
(2, 'A', '2020-1-2 12:01:04'),
(3, 'A', '2020-1-2 12:01:05'),
(1, 'B', '2020-1-2 12:03:03'),
(1, 'A', '2020-1-2 12:04:03'),
(1, 'C', '2020-1-2 12:06:03'),
(2, 'A', '2020-1-2 12:07:04'),
(3, 'B', '2020-1-2 12:08:05'),
(3, 'E', '2020-1-2 12:09:05'),
(3, 'D', '2020-1-2 12:11:05'),
(2, 'C', '2020-1-2 12:09:03'),
(4, 'E', '2020-1-2 12:05:03'),
(4, 'B', '2020-1-2 12:06:03'),
(4, 'E', '2020-1-2 12:07:03'),
(2, 'A', '2020-1-2 12:10:03');

3 题解

要想求按照 A->B->C 序列的用户，就需要知道每个用户的行为路径。这个不难求得，只需要将每个用户进入的页面按照进入时间进行concat即可。那么如何保证A到B是直接到达的，并且B到C是可能间接到达的呢？显然可以使用正则匹配 like %A,B%C%

select date(in_ts) as dt
      ,count(distinct user_id) as cnt
from
(
    select user_id
          ,in_ts
          ,concat_ws(',',collect_set(page_id) over(partition by user_id order by in_ts)) as page_list
    from ods_usr_log
) t
where page_list like '%A,B%C%'
group by date(in_ts)
;