【SQL】【日常实习笔试】

​cider 数据分析日常实习生笔试题

SQL题，两张表
用户消费信息表：
user_profile

user_name	fee	pay_time
A	8	2022-06-01
B	20	2022-06-08
C	15	2022-06-08

用户部门表
user_department

user_name	department
A	marketing
B	marketing
C	technical

第一问：

--思考过程：按department维度拆，所以有department的表作为主表；
--存在有的department没有消费记录的情况，置为0
select a.department,nvl(sum(b.fee),0)as total_fee
from (
	select user_name,department
	from user_department
)as a 
left join (
	select user_name,fee
	from user_profile
)as b 
on a.user_name=b.user_name
group by a.department

第二问：

--分析：看用户维度的最早消费时间，利用having子句限制
select user_name
from user_profile
group by user_name
having min(pay_time)='2020-12-01'

第三问：

--分析：为了保留2020-12-01没有消费记录的用户，不能使用where语句圈定2020-12-01。
select user_name,sum(if(pay_time='2020-12-01',1,0)) as total_times
from user_profile
group by user_name

第四问：

select department,user_name,pay_time
from(select 
	department,b.user_name,b.pay_time,
	dense_rank() over(partition by a.department, order by b.fee desc) as rk
from (
	select user_name,department
	from user_department
)as a 
right join (
	select user_name,fee,pay_time
	from user_profile
)as b 
on a.user_name=b.user_name)
where rk=2

​