数据分析SQL面试题实战(一)

笔记 专栏收录该内容
7 篇文章 0 订阅

题目为实际面试题目,分享一下,有更好的解法欢迎交流。
题目一:
一、表结构介绍
用户活跃模型表:tmp_dau,
以dt、user_id为主键,一个用户1天只出现1次,出现即表示当日登陆
字段:
(1)dt,日期,string格式,格式例如20190601
(2)user_id,用户唯一标识,string格式,无空值
(3)is_new,新用户标识,string格式,1表示新用户,0表示老用户
红包领取日志表:tmp_packet,
日志流水表,每一行为领取1次红包,
字段:
(1)dt,日期,string格式,格式例如20190601
(2)report_time, 领取时间戳,string格式
(3)user_id,用户唯一标识,string格式,无空值
(4)add_money,领取金额,string格式,表示领取金额,单位为分,无空值或0值
题目:计算最近一周内,每日领取红包的新用户数、老用户数,及其人均领取金额,人均领取次数
• 输出数据维度:dt(日期),is_new(新用户1,老用户0,未知2)
输出数据指标:mean_money(人均领取金额),mean_get_count(人均领取次数)

在这里插入图片描述
在这里插入图片描述
考点:表连接 + 子查询 + case when

select
dt,
sum(case when is_new = 1 then 用户数 else 0 end) 新用户数,
sum(case when is_new = 0 then 用户数 else 0 end) 老用户数,
总领取金额 / 用户数 人均领取金额,
总领取次数  / 用户数 人均领取次数
from
	(
Select
dau.dt,dau.is_new,
count(DISTINCT dau.user_id)用户数,
sum(pk.add_money)总领取金额,
count(pk.add_money) 总领取次数
From 
tmp_dau dau 
join tmp_packet pk
on dau.user_id = pk.user_id and dau.user_id = pk.user_id
group by dau.dt,dau.is_new) t1
group by dt

在这里插入图片描述
题目二:
在这里插入图片描述
解题思路:
1.先将visitdate格式化
2.然后计算出小计
3.累计是在小计的结果上根据userid分组再计算出来的

考点:
子查询 + 窗口函数 + 时间格式化

select 
	userid,
	月份,
	小计,
	sum(小计) over(partition by userid order by 小计) 累计
from 
	(select 
	userid,
	月份,
	sum(visitCount) 小计
from
	(select
	userid,
	DATE_FORMAT(visitDate,'%Y-%m') 月份,
	visitCount
from `练习1`) t1
group by
userid,月份
order by
userid,月份) t2
order by
userid,月份 

题目三
在这里插入图片描述
求每个店铺访问次数top3的访客信息。输出店铺名称、访客id、访问次数。

解题思路:
1.因为结果需要店铺和id,需要group by 店铺和id求出访问次数。
2.求top3,故想到用窗口函数进行分组排序。
3.在分组排序的基础上筛选去前三名。

select
	shop,
	id,
	访问次数排名
(
select
	shop,
	id,
	row_number() over(partition by shop order by 访问次数 desc) 访问次数排名
from
(
Select
	shop,
	id,
	count(id) 访问次数
from
visit
group by
shop,id
) t1
) t2
where t2.访问次数排名 < 4
  • 0
    点赞
  • 1
    评论
  • 0
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

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

余额充值