【SQL 复健】表联接&时间差 | 做题记录 001

22 Pdd 春招

业务背景：为了提高某店铺的营业额，运营部门上线了多个运营活动；用户参与活动后可以领取不定额的优惠券，以此鼓励用户下单。但每个用户限参与一个活动，不可同时参与多个活动。现有一张订单表orders，活动报名表act_join，分别记录了用户的下单明细和用户报名的活动明细

orders：user_id：‘用户编号’、order_id：'订单编号' 、order_sales：‘订单金额’ 、order_time：‘下单时间’

act_join：act_id：‘活动编号’、user_id：‘报名用户’、join_time：‘报名时间’

问题：

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

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

/* 统计每个活动报名的所有用户在报名后产生的总订单金额，总订单数。（每个用户限报一个活动,且默认用户报名后产生的订单均为参加活动的订单）*/

select act_join.act_id,count(distinct(orders.order_id)),sum(orders.order_sales)
from orders
inner join act_join
on orders.user_id=act_join.user_id
where join_time<=order_time
group by act_join.act_id

/* 统计每个活动从开始后到当天（数据统计日）平均每天产生的订单数，活动开始时间定义为最早有用户报名的时间。*/
select act_join.act_id, count(distinct(orders.order_id))/datediff(day,min(join_time) over(partition by act_join.act_id),now()) as avg_order
from act_join
inner join orders
on orders.user_id=act_join.user_id
group by act_join.act_id