madjaewon 님의 블로그

1. 서비스, 비즈니스 파악 - 서비스의 목표, 기획서

2. 문제 정의 - 핵심 문제, 목표 정의 - 핵심 이벤트 

3. 사용 주기 파악 - 고객은 문제를 어떤 빈도로 겪을까? 실제와 예상의 차이 파악 - Daily/Weekly/Monthly 결정

4. 리텐션 커브 분석 - PMF 도달 여부 확인 - 평평해지는 시점 확인 ( * PMF = Product Market Fit ) 

5. 추가 분석 - 코호트, 유저 행동 등  

- 핵심 이벤트 정의 필요

이론 

연습 문제 1

데이터 테이블 : query_logs

사용자별 쿼리를 실행한 총 횟수를 구하는 쿼리를 작성해주세요. 
단, group by 를 사용해서 집계하는 것이 아닌 query_logs의 데이터의 우측에 새로운 컬럼을 만들어주세요. 
-- 사용자별 쿼리를 실행한 총 횟수 : count() 전체 실행
-- over(partition by user)

select *, count(query_date) over(partition by user) as total_query_cnt
from advanced.query_logs
order by user, query_date

 결과 

연습 문제 2

데이터 테이블 : query_logs

주차별로 팀 내에 쿼리를 많이 실행한 수를 구한 후, 실행한 수를 활용해 랭킹을 구해주세요.

단, 랭킹이 1등인 사람만 결과가 보이도록 해주세요.

with query_cnt_by_team as(
select
      extract(week from query_date) as week_number,
      team,
      user,
      count(user) as query_cnt
from advanced.query_logs
group by all)
select *,
      rank() over(partition by week_number, team order by query_cnt desc) as rk
from query_cnt_by_team
  -- qualify : 윈도우 함수의 조건을 설정할 때 사용한다. 
  -- where 을 쓸 수 있지만 그럴 경우 서브쿼리를 활용해야함
qualify rk = 1
order by week_number, team, query_cnt desc
--

결과 

연습 문제 3

데이터 테이블 : 2번 문제에서 사용한 주차별 쿼리 사용

쿼리를 실행한 시점 기준 1주 전에 쿼리 실횅수를 별도의 컬럼으로 확인할 수 있는 쿼리를 작성해주세요.

with query_cnt_by_team as(
select
      extract(week from query_date) as week_number,
      team,
      user,
      count(user) as query_cnt
from advanced.query_logs
group by all)

select *, 
      lag(query_cnt,1) over(partition by user order by week_number) as prev_week_qeury_cnt
from query_cnt_by_team

-- over(partition by user)

결과 

연습 문제 4

시간의 흐름에 따라, 일자별로 유저가 실행한 누적 쿼리 수를 작성해 주세요.

--누적 쿼리 : 과거의 시간(unbounded preceding) 부터 curren row 까지
--출제 의도 : default frame에 대해 알려드리고 싶었음.
select *,
      sum(query_cnt) over(partition by user order by query_date) as cumulative_sum,
      sum(query_cnt) over(partition by user order by query_date rows between unbounded preceding and current row) as cumulative_sum2
-- frame의 default 값 : unbounded preceding ~~ current row
from(
select 
  query_date, 
  team, 
  user,
  count(user) as query_cnt
from advanced.query_logs
group by all)
-- # qualify cumulative_sum != cumulative_sum2
--where, qualify 조건 설정해서 2가지 값이 모두 같은지 비교 => 모두 같으면 != 연산 결과에 반환하는 값이 없을 것 
order by user, query_date

결과 

연습 문제 5

다음 데이터는 주문 횟수를 나타낸 데이터입니다. 만약 주문 횟수가 없으면 NULL로 기록됩니다. 

이런 데이터에서 NULL 값이라고 되어있는 부분을 바로. 이전 날짜의 값으로 채워주는 쿼리를 작성해주세요. 

WITH raw_data AS (
  SELECT DATE '2024-05-01' AS date, 15 AS number_of_orders UNION ALL
  SELECT DATE '2024-05-02', 13 UNION ALL
  SELECT DATE '2024-05-03', NULL UNION ALL
  SELECT DATE '2024-05-04', 16 UNION ALL
  SELECT DATE '2024-05-05', NULL UNION ALL
  SELECT DATE '2024-05-06', 18 UNION ALL
  SELECT DATE '2024-05-07', 20 UNION ALL
  SELECT DATE '2024-05-08', NULL UNION ALL
  SELECT DATE '2024-05-09', 13 UNION ALL
  SELECT DATE '2024-05-10', 14 UNION ALL
  SELECT DATE '2024-05-11', NULL UNION ALL
  SELECT DATE '2024-05-12', NULL
)

-- 윈도우 함수의 first_value, last_value 에선 기본적으로 null을 포함해서 연상 

-- null을 제외하고 싶으면 ignore nulls  함수를 쓰자

--ignore x
select *, last_value(number_of_orders) over(order by date) as last_value_orders
from raw_data

결과 

-- ignore 사용
select *, last_value(number_of_orders ignore nulls ) over(order by date) as last_value_orders
from raw_data

결과 

연습 문제 6

5번 문제에서 null을 채운 후, 2일 전 ~ 현재 데이터의 평균을 구하는 쿼리를 작성해주세요 (이동평균) 

WITH raw_data AS (
  SELECT DATE '2024-05-01' AS date, 15 AS number_of_orders UNION ALL
  SELECT DATE '2024-05-02', 13 UNION ALL
  SELECT DATE '2024-05-03', NULL UNION ALL
  SELECT DATE '2024-05-04', 16 UNION ALL
  SELECT DATE '2024-05-05', NULL UNION ALL
  SELECT DATE '2024-05-06', 18 UNION ALL
  SELECT DATE '2024-05-07', 20 UNION ALL
  SELECT DATE '2024-05-08', NULL UNION ALL
  SELECT DATE '2024-05-09', 13 UNION ALL
  SELECT DATE '2024-05-10', 14 UNION ALL
  SELECT DATE '2024-05-11', NULL UNION ALL
  SELECT DATE '2024-05-12', NULL
)
, filled_data as(
select *, last_value(number_of_orders ignore nulls ) over(order by date) as number_orders
from raw_data)
-- with 문을 또 쓸 수 없으니 , 로 구분해 주면 된다. 
select * , avg(number_of_orders) over(order by date rows between 2 preceding and current row) as moving_avg
from filled_data
-- frame: 2일 전 => between 2 preceding and current row

결과