[패스트캠퍼스] SQL 강의 : 날짜를 응용하여 active user 및 revenue 구하기

1. 2020년 7월의 총 Revenue를 구해주세요

where 절에 날짜를 넣어 필터 가능

집계함수 sum 사용하였으나 따로 그룹함수 지정하지는 않음

 

Select sum(price)
from fastcampus.tbl_purchase
WHERE purchased_at >= '2020-07-01' 
 AND purchased_at < '2020-08-01';
 

 

2. 2020년 7월의 MAU를 구해주세요

Monthly Active User 이해, 방문기록을 기반으로 MAU 구하기

 

집계함수 COUNT 사용 3가지 예시

1. COUNT * : 모든 테이블에 있는 모든 레코드 조회

2. COUNT(컬럼이름) : 컬럼이름에 있는 모든 레코드 조회, null 값 제외

3. COUNT(DISTINT 컬럼이름) : 컬럼이름에 중복 제외 레코드 조회

 

SELECT COUNT(distinct customer_id)
FROM fastcampus.tbl_visit
WHERE visited_at >='2020-07-07' 
AND visited_at <'2020-08-01' ;

 

3. 2020년 7월에 우리 Active 유저의 구매율(Paying Rate)은 어떻게 되나요?

SELECT 문을 계산기 처럼 사용하는 방법

유저의 구매율 = 구매자/방문자

 

/* SELECT COUNT(distinct customer_id)
FROM fastcampus.tbl_visit
WHERE visited_at >= '2020-07-01'
AND visited_at < '2020-08-01' 
값 : 11174 */


/* SELECT COUNT(distinct customer_id)
FROM fastcampus.tbl_purchase
WHERE purchased_at >= '2020-07-01'
AND purchased_at < '2020-08-01' 
값 : 16414 */

 

SELECT ROUNT(11174/16414*100,2)

 

4. 2020년 7월에 구매 유저의 월 평균 구매액은 어떻게 되나요?

ARPPU(Average Revenue per Paying user)

그룹함수 사용 :고객당 월 평균 구매액을 구하기 위해서

서브쿼리 사용 : 만든 sum 쿼리의 평균을 구하기 위해 서브쿼리 사용하고 서브쿼리 사용 후 문자를 뒤에 써서 명시해 줌 

 

SELECT avg(revenue)
FROM
(SELECT customer_id,sum(price)AS revenue
FROM fastcampus.tbl_purchase
WHERE purchased_at >= '2020-07-01' 
AND purchased_at <'2020-08-01'
group by customer_id) foo;

 

5. 2020년 7월에 가장 많이 구매한 Top3 고객과 Top10~15 고객을 뽑아주세요

 

limit 3 offset 10 : 10개를 off해버리고 그 이후부터 3개 보여주는 것 

 

Top3 고객 보기

SELECT customer_id, sum(price)as revenue
FROM fastcampus.tbl_purchase
WHERE purchased_at >= '2020-07-01'
AND purchased_at < '2020-08-01' 
group by customer_id
order by 2 desc
limit 3;

 

Top10~15 고객 보기

SELECT customer_id, sum(price)as revenue
FROM fastcampus.tbl_purchase
WHERE purchased_at >= '2020-07-01'
AND purchased_at < '2020-08-01' 
group by customer_id
order by 2 desc
limit 6 offset 9;

---

날짜, 시간별 분석 문제 모음

 

<Date Functions Examples>

 

현재 날짜, 시간 : SELECT NOW();

현재 날짜 : SELECT CURRENT_DATE();

입력한 날짜 month 추출 : SELECT EXTRACT(MONTH FROM ʻ2021-01-01’);

입력한 날짜 day 추출 : SELECT DAY(ʻ2021-01-01’);

입력한 날짜에서 더하기 : SELECT DATE_ADD(ʻ2021-01-01’, INTERVAL 7 DAY);

입력한 날짜에서 빼기 : SELECT DATE_SUB(ʻ2017-06-15’, INTERVAL 7 DAY);

날짜 차이 구하기 : SELECT DATEDIFF("2017-06-25", "2017-06-15");

날짜, 시간 차이 구하기 : SELECT TIMEDIFF("2021-01-25 12:10:00", "2021-01-25 10:10:00");

날짜 형식 지정 : SELECT DATE_FORMAT(NOW(), “%Y-%m-%d”);

 

<Date Format 함수 사용>

Format	Description	Format	Description
%j	n번째 일(100,365	%M	Month 월(January, February...)
%H	Hour 시(00, 01, 24) 24시간 형태	%m	Month 월(01, 02, 03 ...)
%h	Hour 시(00, 02, 12) 12시간 형태	%W	Day of Week 요일(Sunday, Monday...)
%T	hh:mm:ss	%D	Month 월(1st, 2dn, 3rd...)
%S, %s	Second 초	%Y	Year 연도(1990, 2010, 2021)
%p	AM, PM	%y	Year 연도(90, 10, 21)
%w	Day of Week(0, 1, 2) 0부터 일요일	%a	Day of Week 요일(Sun, Mon, Tue...)
%U	Week 주(시작 : 일요일)	%d	Day 일(00, 01, 02 ...)
%u	Week 주(시작 : 월요일)

 

참조 url : https://www.w3schools.com/sql/sql_ref_mysql.asp

MySQL Functions

 

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

 

SELECT date_format(visited_at, '%Y-%m-%d') As date_at, COUNT(distinct customer_id)
FROM fastcampus.tbl_visit
WHERE visited_at >= '2020-07-01'
AND visited_at < '2020-08-01'
group by 1 

 

데이터를 포맷할때는 시간이 변하지 않는 지 확인 필수, 간혹 시간이 변경되어 날짜가 오류가 나는 경우가 있음

시간을 확인을 다시 하는 쿼리

SELECT *, date_format(visited_at, '%y-%m-%d %T') as date_at
FROM fastcampus.tbl_visit
WHERE visited_at >= '2020-07-01'
AND visited_at < '2020-08-01'

 

현재 쿼리에서 시간이 +9시간이 많았음

그래서 시간을 -9시간을 하여 원하는 데이터로 시간을 변경하여 확인해보는 쿼리

SELECT *, date_format(visited_at - interval 9 hour , '%y-%m-%d %T') as date_at,
date_format(visited_at, '%y-%m-%d %T') as date_at1
FROM fastcampus.tbl_visit
WHERE visited_at >= '2020-07-01'
AND visited_at < '2020-08-01'

 

데일리 액티브 유저 수를 확인하는 쿼리 및 데일리액티브유저 추세 볼 수 있음(엑셀로 추출하여 확인)

SELECT date_format(visited_at - interval 9 hour , '%Y-%m-%d') as date_at,
count(distinct customer_id)
FROM fastcampus.tbl_visit
WHERE visited_at >= '2020-07-01'
AND visited_at < '2020-08-01'
group by 1
ORDER BY 1 ;

 

데일리 액티브유저수 평균 확인하는 쿼리

select avg(USERS)
FROM
(SELECT date_format(visited_at - interval 9 hour , '%Y-%m-%d') as date_at,
count(distinct customer_id) AS USERS
FROM fastcampus.tbl_visit
WHERE visited_at >= '2020-07-01'
AND visited_at < '2020-08-01'
group by 1
ORDER BY 1) foo ;

 

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

7월에 7일이 온전히 있는 주의 일을 변경하여 입력하여 데이터의 신뢰도 높이기

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 fastcampus.tbl_visit
WHERE visited_at >= '2020-07-05'
AND visited_at < '2020-07-26'
group by 1
order by 1) foo;

 

8-1. 2020년 7월의 daily revenue는 증가하는 추세인가요? 평균 daily revenue도 구해주세요.

 

2020년 7월 daily revenue 추세구하는 쿼리-> 엑셀추출하여 표로 확인

select date_format(purchased_at - interval 9 hour, '%Y - %m - %d') as date_at, sum(price) as revenue
from fastcampus.tbl_purchase
where purchased_at >= '2020-07-01'
and purchased_at < '2020-08-01'
group by 1
order by 1;

 

daily revenue 평균 구하는 쿼리

select avg(revenue)
from
(select date_format(purchased_at - interval 9 hour, '%Y - %m - %d') as date_at, sum(price) as revenue
from fastcampus.tbl_purchase
where purchased_at >= '2020-07-01'
and purchased_at < '2020-08-01'
group by 1
order by 1) foo;

 

8-2  2020년 평균 weekly revenue도 구해주세요. 

select avg(revenue)
from
(select date_format(purchased_at - interval 9 hour, '%Y - %m - %U') as date_at, sum(price) as revenue
from fastcampus.tbl_purchase
where purchased_at >= '2020-07-05'
and purchased_at < '2020-07-26'
group by 1
order by 1) foo;

 

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

 

요일의 avg를 바로 구하면 각 요일의 결제금액 평균이 됨, 요일의 총 revenue를 구해야하기 때문에 데일리 베이스로 데이터를 구한 후, 요일별로 그룹바이를해서 평균값을 구해야한다

select date_format(date_at, '%W') as day_name,date_format(date_at, '%w') as day_order, avg(revenue)
from 
(select date_format(purchased_at - interval 9 hour , '%Y-%m-%d') as date_at
,sum(price) as revenue
from fastcampus.tbl_purchase
WHERE purchased_at >= '2020-07-01'
AND purchased_at < '2020-08-01'
group by 1
order by 1) foo
group by 1, 2
order by 2;

 

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

select 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 , '%H') as hour_at, sum(price) as revenue
from fastcampus.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;

 

11. 2020년 7월 요일 및 시간대별 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 fastcampus.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;