阿里两道面试sql题

第一道

90分位。有用户user_id，支付金额pay_amount字段，统计支付金额前90%的用户id。例如总的支付金额是10000，A用户支付金额4000，B用户支付金额3000，C用户支付金额2000，那么这三个总的支付金额就占了前90%，把这三个用户输出就可。

思路：这里还是需要用到开窗。Ntile函数把有序的数据集合平均分配到指定的数量（num）个桶中, 将桶号分配给每一行。这里要统计前90%，所以要分成10个桶，取到前9个桶。

select 
	a.user_id,
	a.sum_pay_amount,
	a.level
from
(
     select 
     	user_id,
     	sum(pay_amount) as sum_pay_amount,
     	ntile(10) over(order by sum(pay_amount) desc) as level
     from user_trade
     group by user_id
) a
where a.level <= 9

注意：这里面over（）后可省略去partition by。直接排序。

第二道

针对双十一期间，写出实现以下要求的sql语句。
要求：单笔订单金额超过1000.00元的订单数量超过3笔的买家
输出：买家id，订单数，总订单金额，总订单金额top 3的买家。
涉及的字段：buyer_id用户id，order_id订单id，amount支付金额。

思路：首先要满足要求，单笔订单金额超过1000.00元的订单数量超过3笔的买家。这句话的字面意思理解，统计到用户id，订单id的维度，筛选出订单金额超过1000的用户，再having count 取有超过3的数据。再给根据用户id作为关联口径，取得总表中该用户的相关统计数值。

1.统计笔订单金额超过1000.00元的订单数量超过3笔的买家
此表作为子查询
select 
	buyer_id,
	order_id,
	count(1) as order_nums
from
	order_detail
where 
	amount >1000
group by 
	buyer_id,order_id
having count(1)>3


2.和原表做关联，在原表中统计出amount和order_nums
select 
	t1.buyer_id,
	count(t1.order_id) as  order_nums,
	sum (amount) as total_amount
from
(
	select 
		buyer_id,
		order_id,
		amount
	from
		order_detail
)t1
join 
(
	select 
		buyer_id,
		order_id,
		count(1) as order_nums_morethan3
	from
		order_detail
	where 
		amount >1000
	group by 
		buyer_id,order_id
	having count(1)>3
)t2
on t1.buyer_id=t2.buyer_id
group by buyer_id