SQL笔试题练习记录

本文记录了一位作者在SQL学习过程中的笔试题练习,涵盖从基础的行列转换、窗口函数到复杂的流失用户分析和数据统计。通过实例解析了percentile_approx函数的使用,以及如何进行用户行为分析、用户分组查询和时间序列分析。内容包括用户活动订单金额统计、网站访客行为分析、摩拜单车订单费用计算等实际问题。
摘要由CSDN通过智能技术生成

SQL笔试题练习记录

day 1

https://zhuanlan.zhihu.com/p/80905376
1、用户行为分析
(1) 取某一天查看用户资料行为(event_name=profile.index)的20,50,80分位点
用户行为表event_summary
思路:分位数采用 percentile_approx(),where定位某一天某一用户
实现:

select percentile_approx(event_num_map, array(0.2,0.5,0.8),9999)
from event_summary
where Partition_date = ${
  date}
and event_name = profile.index 

(2) 查看用户资料行为的不同行为频次的用户分布
输出表示例
思路:
event_summary: user_id 一个行为 行为次数
连接表,主表是某时间区间的用户id,一个事件为一条记录,所以id会有大量重复;
次表定义每个用户每个行为的行为频次字段。之所以用到两个表是因为占比字段需要用到主表

实现:

select t2."行为频次", count(distinct t2.user_id), 
count(distinct t2.user_id)/count(t1.user_id),
sum(count(distinct t2.user_id)) over(partition by t2."行为频次")
from (select User_id from event_summary 
where Partition_date between ${begin_date} and ${end_date}) t1
left join (select user_id,
(case when event_num_map between 0 and 5 then "0-5"
case when event_num_map between 5 and 15 then "5-15"
else "其他" end ) as "行为频次"
from event_summary 
where Partition_date between ${begin_date} and ${end_date}) t2
on t1.user_id = t2.user_id
group by t2.行为频次

2、流失用户分析
在这里插入图片描述
思路:复写表,表1输出某一天活跃的用户表,表2输出不活跃用户表,两者连接后,按日期group by,其日期差值就是流失的天数
题目感觉条件不足,先不考虑了
代码:

select Partition_date,case when datediff(t2.Partition_date,t1.Partition_date)=1 then count(distinct t2.user_id) end) as '流失1天',
case when datediff(t2.Partition_date,t1.Partition_date)=2 then count(distinct t1.user_id) end) as '流失2天',
case when datediff(t2.Partition_date,t1.Partition_date)=3 then count(distinct t1.user_id) end) as '流失3天',
case when datediff(t2.Partition_date,t1.Partition_date)>=30 then count(distinct t1.user_id) end) as '流失30天以上'from (
select user_id,Partition_date
    from usre_active
    where daily_active_status_map=1
)t1
left join (
    select user_id,Partition_date
    from
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值