SQL Challenge 세션 #2 이동평균과 비율

240523 Today I Learn

이동 평균을 사용한 날짜별 매출 추이 보기

이동평균이란 무엇일까? 왜 사용할까?

💡 이동평균법
이동 평균법은 전체 데이터 집합의 여러 하위집합에 대한 일련의 평균을 만들어 데이터 요소를 분석하는 계산이다.

ROWS BETWEEN lower_bound AND upper_bound​

• `UNBOUNDED PRECEDING/ FOLLOWING`- 현재 row 앞/뒤의 모든 row
• `n PRECEDING/ FOLLOWING` - 현재 row 앞/뒤의 n개의 row
• `CURRENT ROW' - 현재 row

이동평균 구하기  - 7일 이동평균 구하기

purchase_log

SELECT dt,
       SUM(purchase_amount),
       AVG(SUM(purchase_amount)) OVER(ORDER BY dt ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) seven_day_avg,
       CASE WHEN 7 = AVG(COUNT(*)) OVER(ORDER BY dt ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
            THEN AVG(SUM(purchase_amount)) OVER(ORDER BY dt ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
            ELSE NULL END seven_day_avg_strict
FROM purchase_log
GROUP BY dt
ORDER BY dt;

• `seven_day_avg`: dt를 기준으로 purchase amount의 합계의 7일간의 평균 구하기
  → ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
• `seven_day_avg_strict`: CASE WHEN 구문을 이용해 ROWS BETWEEN 6 PRECEDING AND CURRENT ROW에 해당하는 ROW가 7개일때만 7일간의 이동평균을 구하고, 그 외에는 NULL로 표시

적용 과제 - 이동 평균 구하기 (ROLLING AVERAGE) 

Twitter SQL Interview Question: 3-Day Rolling Tweets

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.

• 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.

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) rolling_avg_3d
FROM tweets;

비율 구하기

💡 CASE WHEN문을 활용해 조건에 맞는 수  세기

 COUNT(CASE WHEN 조건 THEN 1 ELSE NULL END)​

 

보통 비율을 구하는 방법은 `내가 원하는 조건에 맞는 값 / 전체`의 형태로 구하는 경우가 많다. 조건에 맞는 값이 몇개인지 셀 때에는 바로 COUNT문안에 CASE WHEN 문을 넣어서 조건에 맞는 경우 1, 아닌 경우 NULL을 반환하게 하면 조건에 맞는 경우의 수만 1로 세어준다. 

 

⚠️
이때 주의해야 할 점은
COUNT한 값에 0.1을 곱해줘야한다는 것이다.

 

SQL에서는 정수를 나누면 결과 역시 정수로 반환된다. 때문에 5/10을 하는 경우에도 0.5(실수)가 아닌 0(정수)형으로 반환하기 때문이다. 이 문제는 COUNT된 값에 1.0을 곱해서 간단히 해결할 수 있다. 이 과정에서 정수는 실수형태로 변환되고, 실수형으로 정상적으로 계산된다. 비율을 구할때에는 이 점에 특히 유의하도록 하자.

Activation이란?

• MAU (Monthly active users)
• WAU(Weekly active users)
• DAU(Daily active users)

적용 과제 - 활성 비율 구하기 

TikTok SQL Interview Question | DataLemur

New TikTok users sign up with their emails. They confirmed their signup by replying to the text confirmation to activate their accounts. Users may receive multiple text messages for account confirmation until they have confirmed their new account.

A senior analyst is interested to know the activation rate of specified users in the emails table. Write a query to find the activation rate. Round the percentage to 2 decimal places.

• emails table contain the information of user signup details.
• texts table contains the users' activation information.

Assumptions:
The analyst is interested in the activation rate of specific users in the emails table, which may not include all users that could potentially be found in the texts table. For example, user 123 in the emails table may not be in the texts table and vice versa.

SELECT ROUND(COUNT(CASE WHEN signup_action = 'Confirmed' THEN 1 ELSE NULL END)*1.0
            /COUNT(DISTINCT user_id),2) as confirm_rate
FROM emails e LEFT JOIN texts t ON e.email_id = t.email_id;

 

적용 과제 - 퍼센트 구하기 

Verizon SQL Interview Question | DataLemur

A phone call is considered an international call when the person calling is in a different country than the person receiving the call. What percentage of phone calls are international? Round the result to 1 decimal.

• Assumption: The caller_id in phone_info table refers to both the caller and receiver.

SELECT ROUND(COUNT(CASE WHEN p1_country <> p2_country THEN 1 ELSE NULL END)*1.0
             /COUNT(*)*100, 1) international_calls_pct
FROM (
        SELECT c.caller_id, c.receiver_id, 
               p1.country_id p1_country, p2.country_id p2_country
        FROM phone_calls c
        LEFT JOIN phone_info p1 ON c.caller_ID = p1.caller_ID
        LEFT JOIN phone_info p2 ON c.receiver_id = p2.caller_id
      )a ;