19.07.28拼多多数分笔试存档

三道sql，一道概率，一道统计

1. 给了两个表一个是user另一个是active，user的字段好像有user_id和creta_time(还有个啥不记得了)，active的字段有act_id,user_id,订单金额和create_time ，两个create_time意义不同。
  两个问题：建一个表记录每个活动平均每天总订单数，总订单金额；第二个忘记了
  分析：sql基础，create table 和一些聚合函数结合group by的使用
  注意sql里日期相关函数的使用，一般原始数据的date都是日期加时间格式的，但在数据处理时考虑的维度可能是年、月或者日
  参考这个博客

提取年：datepart(yy,getdate())
提取月：datepart(mm,getdate())
提取日：datepart(dd,getdate())
获取其他日期格式：convert(varchar(),getdate(),112)

1. 给的是一个历史点击记录表，包括user_id,opr_type和log_time三个字段
  难点在于要找出满足A操作后是B，AB必须相连的记录，我的思路是利用row number() over (partition by user_id order by log_time )进行排序然后再选择满足AB的rank差1的数据
  据说可以用 lead() over(partition by order by) 但对这个函数不熟
  但看这个函数介绍lag和lead真的很好用
1. tracking_log表，计算每日新增用户，新增用户的次日留存和30日留存
  需要用到日期函数，convert()和dateadd() (留存率相关好像是一个高频考点)
  由于给的是所有历史数据，笔试时的思路是先建一个表用来存用户的id及第一次出现的日期，然后利用这个user_first表里的create_time和log表里的log_time做一个关联，使其满足
  convert(varchar(20),b.log_time,112) = convert(varchar(20),dateadd(day,1,a.create_time),112)
  现在想一想，也可以建一个表包含

select a.user_id
         ,log_time
         ,create_time
         ,datediff(log_time,create_time) as dd ##用来记录距第一次登录后的天数
......

然后group by日期得到dd=1和dd=29的相关数据
这里参考第一种思路和第二种思路

没有总结，随缘笔

最后编辑于：2019.07.29 14:36:45