sql 面试题

三三呀 / 2023-07-20 / 原文

1. 统计近30天登录的新用户数量（date_add 用法和开窗函数）

题目：表为用户登录表，包含字段：user_id和order_date，需要通过该表查询近一个月每天的新用户数（用户在某一天第一次下单，在这天之前没下过单，该用户即为新用户）

问题拆解：

新用户查找
-- 以用户为主键进行分组，寻找该用户登录日期的最小值，当登录日期 == 最小日期时，即为新用户
近30天
-- 今天之前的30天。date_add

date_add(start_date, num_days)
Returns the date that is num_days after start_date.

select order_date, count(distinct user_id)
from (
	-- 计算min_date
	select *
	        , min(order_date)  over(partition by user_id) as min_date
	from input 
	)
where order_date =  min_date and  order_date >= date_add(order_date, -30) -- 筛选新用户和近30天
group by order_date 

# count distinct 用户而不是count 用户是为了应对一个用户一天内登录多次的情况

衍生：
近30天也可以用datediff函数

datediff(endDate, startDate)
Returns the number of days from startDate to endDate.

where datediff(current_date, order_date) <= 30

2. 找出前20%及后20%的数据

题目：有一张用户访问次数表，列名包括用户编号、用户类型、访问量。要求在剔除访问次数前20%的用户后，每类用户的平均访问次数。

问题拆解：

访问量前20%数据。
--按照访问量降序排序后，排名小于等于全部用户的20%，即为前20%。（可以类比为年级前几名）

select 用户类型,  avg(访问量) 平均访问次数
from (
	select * 
			, row_number() over (order by 访问量 desc )  as rank
	from  input
	)
where rank >= 0.2 * ( select count(用户编号) from input )
group by 用户类型