数据运营mysql面试题目_Mysql 自测刷题 Part3 - 拼多多笔试题

第一题：活动运营数据分析

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

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

问题

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

第一步：活动表内联结订单表

第二步：筛选出下单时间在报名时间后的订单

第三步：按活动 id 分组，计算出总订单金额，总订单数

select t1.act_id,sum(order_pay) total,count(order_time) order_num from

( select act_id,act.user_id,act_time,order_pay,order_time from

act_apply act

inner join

orders od on act.user_id=od.user_id where order_time > act_time) t1

group by act_id;

2. 统计每个活动从开始后到当天(假设考试日为 “2019-06-09”)平均每天产生的订单数，活动开始时间定义为最早有用户报名的时间。

第一步：对活动表应用窗口函数，算出每个活动 id 最早报名时间当成活动开始时间

第二步：带开始时间的活动表 t1 内联结订单表 t2

第三步：筛选出下单时间在报名时间后的订单

第四步：按活动 id 分组，计算出开始时间，总订单金额，相隔时间，平均订单数

select act_id,start_time,sum(order_pay) total,datediff("2019-06-09",start_time)

day_num,count(order_time)/datediff("2019-06-09 00:00:00",start_time) avg_order from

(select user_id,act_id,act_time,min(act_time) over

(partition by act_id) start_time from act_apply) as t1

inner join

(select user_id,order