PostgreSQL 풀이 | Tweets' Rolling Averages (윈도우함수 ROWS BETWEEN)

문제1.  Tweets' Rolling Averages

Given a table of tweet data over a specified time period, calculate the 3-day rolling average of tweets for each user. Output the user ID, tweet date, and rolling averages rounded to 2 decimal places.

Notes:
A rolling average, also known as a moving average or running mean is a time-series technique that examines trends in data over a specified period of time.
In this case, we want to determine how the tweet count for each user changes over a 3-day period.

 

https://datalemur.com/questions/rolling-average-tweets

Twitter SQL Interview Question: 3-Day Rolling Tweets

---

💡 문제 풀이 

`내 풀이`

소요 시간 08:00

SELECT user_id, 
  tweet_date, 
  ROUND(AVG(tweet_count) OVER(
    PARTITION BY user_id
    ORDER BY tweet_date
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW),2) AS rolling_avg_3d -- round2, the 3-day rolling average of tweets for each user
FROM tweets

 

`오답노트`

• 이동평균 개념 이해하는데 시간 걸림. 설명 예시를 보니 이전 3일 기록으로 평균 내는 것
• 직전 행과 비교할 때는 보통 윈도우함수에서 LEAD(다음행값), LAG(이전행값)를 사용했는데 3개 행에 대해서는 함수가 헷갈렸음.
  • ROWS BETWEEN start AND end : 특정 행부터 특정 행까지 범위를 지정해 분석할 때 사용
  • `current row` : 현재의 행
    `n preceding` : n행 앞
    `n following` : n행 뒤
    `unbounded preceding` : 이전 행 전부
    `unbounded following` : 이후 행 전부

-- 예시1 : 1행 전부터 1행 후까지 분석
ROWS BETWEEN 1 preceding AND 1 following

-- 예시2 : 2행 전부터 현재 행까지
ROWS BETWEEN 2 preceding AND current row

-- 예시3 : 첫번째 행부터 현재 행까지
ROWS BETWEEN unbounded preceding AND current row

-- 예시4 : 전부 다 (현재 행 기준, 이전 행 전부와 이후 행 전부)
ROWS BETWEEN unbounded preceding AND unbounded following

-- 윈도우함수 기본 구조
집계/순서/순위함수 OVER (
	PARTITION BY 파티션 나눌 컬럼명 
    ORDER BY 정렬할 컬럼명
    ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)

---

💯결과