SQL - 심화 2

-- 4 2020년 7월에 구매 유저의 월 평균 구매액은 어떻게 되나요? > 고객당 월 평균 구매액 -- AAPPU = Average Revenue Per Paying User

SELECT avg(revenue)
FROM (
		SELECT customer_id 
			,sum(price) AS revenue
		FROM tbl_purchase
		WHERE visited_at >= '2020-07-01'
			AND visited_at < '2020-08-01'
		group by 1 ) sub;

-- 5. 7월에 가장 많이 구매한 TOP3 고객과 TOP10~15 고객을 뽑아주세요. -- top 3

SELECT customer_id 
	,sum(price) AS revenue 
FROM tbl_purchase 
WHERE visited_at >= '2020-07-01' 
	AND visited_at < '2020-08-01' 
group by 1 order by 2 DESC limit 3 ;
-- 10~15위 
SELECT customer_id 
	,sum(price) AS revenue 
FROM tbl_purchase
WHERE visited_at >= '2020-07-01' 
	AND visited_at < '2020-08-01' 
group by 1 order by 2 DESC limit 5 offset 10 ;