笔试题 |2020 PDD分析师

一、活动运营数据分析

表1：订单表 orders，大概字段有（user_id‘用户编号’, order_pay‘订单金额’ , order_time‘下单时间’）

表2：活动报名表act_apply，大概字段有（act_id‘活动编号’, user_id‘报名用户’,act_time‘报名时间’）

需求：

1、统计每个活动对应所有用户在报名后产生的总订单金额，总订单数。（每个用户限报一个活动，题干默认用户报名后产生的订单均为参加活动的订单）。

2、统计每个活动从开始后到当天（考试日）平均每天产生的订单数，活动开始时间定义为最早有用户报名的时间。（涉及到时间的数据类型均为：datetime）。

解答：

-- 1.需求一
select b.act_id,
       count(a.order_time) as num_order,
       sum(a.order_pay) as sum_order
from
(
    select user_id,
           order_pay,
           order_time
    from orders
) a
inner join 
(
    select user_id,
           act_id,
           act_time
    from act_apply
) b
on a.user_id = b.user_id
where a.order_time >= b.act_time
group by b.act_id

-- 2.需求二
select a.act_id,
       count(order_time)/datediff(now(), a.begin_time)
from 
(
    select act_id,
           user_id,
           act_time,
           min(act_time) over (partition by act_id) as begin_time
    from act_apply
) a
inner join 
(
    select user_id,
           order_time
    from orders
) b
on a.user_id = b.user_id
where a.act_time between a.begin_time and b.order_time
and b.order_time <= now() -- 补充添加：订单支付时间 <= 当前时间
group by a.act_id

窗口分析函数可灵活使用，利用 over() 结合聚集函数，例如 sum() 、max()、min() 、avg()等，或者 rank()、 dense_rank()、row_number() 等。

 

二、用户行为分析

表1：用户行为表tracking_log，大概字段有（user_id‘用户编号’,opr_id‘操作编号’,log_time‘操作时间’）

需求：

1、计算每天的访客数和他们的平均操作次数。

2、统计每天符合以下条件的用户数：A操作之后是B操作，AB操作必须相邻。

-- 3.需求三
select date(log_time),
       count(distinct user_id) as user_cnt,
	   avg(num_cnt) as avg_cnt
from
(
	select date(log_time),
		   user_id,
		   count(opr_id) as num_cnt
	from tracking_log
	group by date(log_time),
			 user_id
)
group by date(log_time)

-- 4.需求四  用户、操作、时间
select date(log_time),
       count(distinct user_id)
from 
(
	select user_id,
		   date(log_time),
		   opr_id,
		   lead(opr_id, 1) over (partition by user_id order by log_time) as opr_id_2
	from tracking_log
)
where opr_id = 'A'
and opr_id_2 = 'B'
group by date(log_time)
-- lag 滞后 让数据向后移动
-- lead 超前 让数据向前移动

 

三、用户新增留存分析

表1：用户登陆表user_log，大概字段有（user_id‘用户编号’，log_time‘登陆时间’）

需求：

每天新增用户数，以及他们第2天、30天的回访比例。

如何定义新增用户：用户登陆表中最早的登陆时间所在的用户数为当天新增用户数；

第2天回访用户数：第一天登陆的用户中，第二天依旧登陆的用户；--次日留存率

第30天的回访用户数：第一天登陆用户中，第30天依旧登陆的用户；

-- 5.需求五 用户、登录时间
select date(a.user_begin),
       count(distinct a.user_id) as '新增用户',
	   count(distinct b.user_id) as '第2日留存用户',
	   count(distinct c.user_id) as '第30日留存用户'
from
(
	select user_id,
		   min(log_time) as user_begin
	from user_log
	group by user_id
) a
left join
(
	select user_id,
	       log_time
	from user_log
) b
on a.user_id = b.user_id
and date(b.log_time) = date(a.user_begin) + 1
left join
(
	select user_id,
	       log_time
	from user_log
) c
on a.user_id = c.user_id
and date(c.log_time) = date(a.user_begin) + 29
group by date(a.user_begin)

此处的 date 日期函数有待商榷，日常oracle中使用 trunc() 函数即可截取，hive中使用to_date() 亦可。

【参考】

【1】2020 PDD数据分析笔试题