SQL_Intermediate 02. 날짜 시간별 분석

2. 날짜 시간별 분석

---

Q6. 2020년 7월의 평균 DAU를 구해주세요, Active User 수가 추세 증가하는 추세인가요?

---

DAU(Daily Active Users)는 일별 활동 사용자 수를 의미합니다

DAU의 이해,Date Format 함수사용, 시간대 바뀌지 않는지 체크추세확인

 

SELECT DATE_FORMAT(visited_at, "%Y-%m-%d") AS date_at
		, count(DISTINCT customer_id)
FROM tbl_visit 
WHERE visited_at >= '2020-07-01'
AND visited_at < '2020-08-01'
GROUP BY 1;

 

 

- 7/1 수가 이상하게 적음이 확인된다.

 

SELECT DATE_FORMAT(visited_at - interval 9 hour , "%Y-%m-%d") AS date_at1
		, count(DISTINCT customer_id)
FROM tbl_visit 
WHERE visited_at >= '2020-07-01'
AND visited_at < '2020-08-01'
GROUP BY 1;

 

- 9시간식 더 해저 있음을 확인, kst 한국표준 시간대가 utc로 변환이 되서 9시간이 추가된것 같다.

 

 

 

DAU의 추세는 위와 같다.

 

Q7. 2020년 7월의 평균 WAU를 구해주세요

---

Weekly Active Users의 이해

올바른 값을 이해 날짜 범위 좁히기

 

SELECT DATE_FORMAT(visited_at - interval 9 hour , "%Y-%m-%U") AS date_at,
			COUNT(distinct Customer_id) AS users
	FROM tbl_visit 
	WHERE visited_at >= '2020-07-01'
	AND visited_at < '2020-08-01'
	GROUP BY 1
	ORDER BY 1;

 

26, 27 ,28 ,29주, 30주의 WAU가 나왔음을 알 수 있다.

하지만, 26주와 30주는 6월과 8월의 일부 날짜가 겹쳐저 있다.

따라서 일~금 간격으로 7/5 ~ 7/26 사이를 설정하여 확인해 볼 수 있다.

 

SELECT DATE_FORMAT(visited_at - interval 9 hour , "%Y-%m-%U") AS date_at,
			COUNT(distinct Customer_id) AS users
	FROM tbl_visit 
	WHERE visited_at >= '2020-07-05'
	AND visited_at < '2020-07-26'
	GROUP BY 1
	ORDER BY 1;

 

 

우리가 구하고자 하는 것은 7월 평균 WAU 수이다.

따라서 각각의 WAU를 합산하여 평균을 구하여야 한다.

 

SELECT AVG(users)
FROM (SELECT DATE_FORMAT(visited_at - interval 9 hour , "%Y-%m-%U") AS date_at,
			COUNT(distinct Customer_id) AS users
	FROM tbl_visit 
	WHERE visited_at >= '2020-07-05'
	AND visited_at < '2020-07-26'
	GROUP BY 1
	ORDER BY 1) foo;

 

 

Q8. 2020년 7월의 Daily Revenue는 증가하는 추세인가요? 평균 Daily Revenue도 구해주세요

---

SELECT DATE_FORMAT(purchased_at - interval 9 hour, '%Y-%m-%d') AS date_at
	, SUM(price) AS revenue
	FROM tbl_purchase
	WHERE purchased_at >= '2020-07-01'
			AND purchased_at < '2020-08-01'
	GROUP BY 1 
	ORDER BY 1;

 

약간의 우상향(?) 을 보인다고도 할 수 있겠지만 생각보다 미미하다.

 

그렇다면 평균 Daily Revenue를 구해보자.

위의 결과를 FROM절에 서브쿼리로 넣고 AVG함수로 계산하면 된다.

 

SELECT AVG(revenue)
FROM( SELECT DATE_FORMAT(purchased_at - interval 9 hour, '%Y-%m-%U'), SUM(price) AS revenue
	FROM tbl_purchase
	WHERE purchased_at >= '2020-07-05'
			AND purchased_at < '2020-07-26'
	GROUP BY 1 
	ORDER BY 1) foo;

 

 

 

Q10. 2020년 7월 요일별 Revenue를 구해주세요. 어느 요일이 Revenue가 가장 높고 어느요일이 Revenue가 가장 낮나요?

---

정확한 값 계산을 위해 일별 매출 구하기

요일별로 포맷팅하기

 

SELECT DATE_FORMAT(date_at , '%W') as day_name, AVG(revenue)
FROM (
SELECT DATE_FORMAT(purchased_at - interval 9 hour, '%Y-%m-%d') as date_at
      , SUM(price) as revenue
FROM tbl_purchase
WHERE purchased_at >= '2020-07-01'
		AND purchased_at < '2020-08-01'
GROUP BY 1) foo
GROUP BY 1
ORDER BY 2;

 

 

월요일이 매출이 가장 높고, 토요일이 매출이 낮음을 확인 할 수 있다.

 

 

Q11. 2020년 7월 시간대별 Revenue를 구해주세요. 어느 시간대가 Revenue가 가장 높고 어느 시간대가 Revenue가 가장 낮나요?

---

정확한 값 계산을 위해 일별 , 시간대별 매출 구하기

시간대 별로 포맷팅 하기

 

SELECT DATE_FORMAT(purchased_at - interval 9 hour, '%Y-%m-%d') as day_at,
		DATE_FORMAT(purchased_at - interval 9 hour, '%H') as hour_at
      , SUM(price) as revenue
FROM tbl_purchase
WHERE purchased_at >= '2020-07-01'
		AND purchased_at < '2020-08-01'
GROUP BY 1,2;

 

위와 같이 일별 시간대별 매출이 구해졌음을 알 수 있다.

이제 시간대 별로 포맷팅 해야한다.

 

SELECT hour_at , AVG(revenue)
FROM (SELECT DATE_FORMAT(purchased_at - interval 9 hour, '%Y-%m-%d') as day_at,
		DATE_FORMAT(purchased_at - interval 9 hour, '%H') as hour_at
      , SUM(price) as revenue
FROM tbl_purchase
WHERE purchased_at >= '2020-07-01'
		AND purchased_at < '2020-08-01'
GROUP BY 1,2) foo
GROUP BY 1
ORDER BY 2 DESC;

 

 

Q12. 2020년 7월 요일 및 시간대별 Revenue를 구해주세요. 어느 요일 및 시간대가 Revenue가 가장 높고 어느 시간대가 Revenue가 가장 낮나요?

---

정확한 값 계산을 위해 일별 매출 구하기

요일 및 시간대 별로 포맷팅 하기

SELECT dayofweek_at, 
		hour_at,
		AVG(revenue)
FROM(
SELECT  
 DATE_FORMAT(purchased_at - interval 9 hour, '%Y-%m-%d') as date_at,
DATE_FORMAT(purchased_at - interval 9 hour, '%W') as dayofweek_at,
DATE_FORMAT(purchased_at - interval 9 hour, '%H') as hour_at,
       SUM(price) as revenue
FROM tbl_purchase
WHERE purchased_at >= '2020-07-01'
		AND purchased_at < '2020-08-01'
GROUP BY 1,2,3) foo 
GROUP BY 1, 2
ORDER BY 3 DESC;

 

 

월요일 18시가 매출평균이 가장 높고, 토요일 오전 6시가 매출평균이 가장 낮음을 알 수 있다.