拼多多2019笔试题

第1题 —— 数据库操作

表ord（用户订单表）

表act_usr（活动参与用户表）

（1）创建表act_output，保存以下信息：
区分不同活动，统计每个活动对应所有用户在报名参与活动之后产生的总订单金额、总订单数（一个用户只能参加一个活动）。

create table act_output as
select act_id,count(ord_id) as '总订单数',sum(ord_amt)as'总金额'
from act_usr a 
left join ord o 
on a.user_id=o.user_id and a.create_time<=o.create_time
group by act_id

（2）加入活动开始后每天都会产生订单，计算每个活动截止当前（测评当天）平均每天产生的订单数，活动开始时间假设为用户最早报名时间。

select act_id,
            count(ord_id) as '总订单数', 
      datediff('2021-01-01',min(a.create_time))+1 as '时间间隔',
      count(ord_id)/(datediff('2021-01-01',min(a.create_time))+1)as '平均每日产生的订单数'
from act_usr a 
left join ord o 
on a.user_id=o.user_id and a.create_time<=o.create_time
group by act_id

第2题 —— 数据库操作

某网络用户访问操作流水表 tracking_log，

（1）计算网站每天的访客数以及他们的平均操作次数；

select date(log_time),#date（）将datetime转化为date
                count(distinct user_id)as '访客数',
                count(*)/count(distinct user_id) as '平均操作次数'
from tracking_log
group by date(log_time)

（2）统计每天符合A操作后B操作的操作模式的用户数，即要求AB相邻。

select date(log_time),count(distinct user_id)
from (
    select user_id,
                    opr_type,
                    log_time,
                    lead(opr_type)over(partition by user_id,log_time order by log_time) as next_opr_type
    from tracking_log
) as t1
where opr_type='A' and next_opr_type='B'
group by date(log_time)

3）统计用户行为序列为A-B-D的用户数
其中:A-B之间可以有任何其他浏览记录(如C,E等),B-D之间除了C记录可以有任何其他浏览记录(如A,E等)
group_concat()是研究行为路径“顺序”的万能方案
注意⚠️：问清楚是abd连续的操作，还是只要求三者顺序，中间可以还有其他操作**

select count(*)
from (
    select user_id, group_concat(opr_type order by log_time separator '-') as user_behavior
    from tracking_log
    group by user_id
    having user_behavior like '%A%B%D%' and user_behavior not like '%A%B%C%D%'
  ) as t1

第3题 —— 数据库操作

根据第2题的用户访问操作流水表 tracking_log，
（1）计算网络每日新增访客表（在这次访问之前没有访问过该网站）；

select reg, count(distinct user_id) as '新增访客数'
from (
    select user_id,min(log_time) as reg
    from tracking_log
    group by user_id
) as t1
group by reg

（2）新增访客的第2日、第30日回访比例。

create table register_log as
select user_id,min(log_time) as reg
from tracking_log
group by user_id
select reg, '时间间隔', '留存数'/'新增访客数' as '回访比例'
from (
    select reg,count(distinct user_id) as '新增访客数'
    from register_log
    group by reg
) as t1
join (
    select reg,datediff(t.log_time,reg)as '时间间隔',count(distinct user_id) as '留存数'
    from register_log r join tracking_log t on r.user_id=t.user_id
    where datediff(t2.log_time,reg)=1 or datediff(t2.log_time,reg)=29
    group by reg,t.log_time
) as t2
on t1.reg=t2.reg
order by reg,'时间间隔';