数仓面试sql题目-hive-sql

题目1：有表table1,里面有2列，列a和列b，对应社区相互关注的用户，怎么找到相互关注的一组用户

答：

select 	a
		,b
from 	(
			select 	a
					,b
			from 	table1
			union all
			select 	b
					,a
			from 	table1
		) t1
group by a
		 ,b
having 	count(1) > 1
;

题目2：orders表，dt,uid,amount，计算当日和当月的累计值，假定这个日期会跨月

select 	t1.uid
		,sum(if(t1.dt = t2.dt,amount,0)) as day_amount
		,sum(if(t2.dt between t1.dt1 and t1.dt,amount,0)) as all_amount
from 	(
			select 	uid
					,dt
					,concat(substr(dt,1,6),01) as dt1 	--当月最小日期
			from 	orders
		) t1
join 	(
			select 	uid
					,dt
					,sum(amount) as amount
			from 	orders
			group by uid
					 ,dt
		) t2
on 		t1.uid = t2.uid
group by t1.uid
;