SQL习题随记

1. 求累积和
   

-- 80%的订单金额最少是由多少用户贡献的
SELECT
	COUNT( b.uid ) 
FROM
	((
		SELECT
			a.uid AS uid,
			a.amount,
			SUM( a.amount ) over ( ORDER BY a.amount DESC ) AS consum_amount,(  -- 获取该用户与其之前用户的订单和
				SUM( a.amount ) over ( ORDER BY a.amount DESC ))/(
			SELECT
				SUM( amount ) 
			FROM
				consum_order_table 
			) AS consum_amount_rate 
		FROM
			( SELECT uid, SUM( amount ) amount FROM consum_order_table GROUP BY uid ) a 
		)) b 
WHERE
	b.consum_amount_rate < 0.8

2. 随机抽取五个用户

   	SELECT DISTINCT uid
   	FROM consum_order_table
   	ORDER BY RAND() LIMIT 5

3. 统计一下牛客新登录用户的次日成功的留存率
   

	SELECT ROUND(COUNT(*)/(select count(distinct user_id)from login),3)
	from login l1 join
	(select 
			user_id,DATE_ADD(min(date),INTERVAL 1 day) dad
	from 
			login l
	GROUP BY 
			user_id) a 
	on l1.user_id=a.user_id
	WHERE l1.date=a.dad  # 求出留存用户数量
	-- ----------------------------------------
	SELECT
		round( count( l2.date )/ count(l1.user_id), 3 ) p 
	FROM
		( SELECT user_id, min( date ) first_date FROM login GROUP BY user_id ) l1
		LEFT JOIN login l2 ON l1.user_id = l2.user_id 
		AND l2.date = date_add(
		l1.first_date,
		INTERVAL 1 DAY)