TIL - 250416_(SQL_datalemur_4개)

문제1 (링크)

SELECT 
  ROUND(100.0*SUM(CASE WHEN b.country_id != c.country_id THEN 1 ELSE 0 END)/COUNT(*),1)
FROM phone_calls a 
LEFT JOIN phone_info b 
ON a.caller_id = b.caller_id
LEFT JOIN phone_info c
ON a.receiver_id = c.caller_id

문제2 (링크)

SELECT
  ROUND(100.0 *
  SUM(CASE WHEN call_category IS NULL OR call_category = 'n/a' THEN 1 ELSE 0 END)
  / COUNT(*),1)
FROM callers

문제3 (링크)

WITH a AS (
  SELECT
    user_id
    , MIN(event_date) first_mon
  FROM user_actions
  WHERE event_type = 'sign-in'
  GROUP BY user_id
), b AS (
  SELECT
    EXTRACT(MONTH FROM event_date) MON
    , COUNT(DISTINCT user_id) MAU
  FROM user_actions
  LEFT JOIN a 
  USING (user_id)
  WHERE EXTRACT(MONTH FROM event_date) > EXTRACT(MONTH FROM first_mon)
  GROUP BY 1
)
SELECT * FROM b WHERE mon = 7

문제4 (링크)

SELECT 
  EXTRACT(YEAR FROM transaction_date) yr
  , product_id
  , spend
  , LAG(spend) OVER (PARTITION BY product_id ORDER BY transaction_date) prev_year_spend
  , ROUND(100.0 * spend / LAG(spend) OVER (PARTITION BY product_id ORDER BY transaction_date),2) - 100
FROM user_transactions

비고 : LAG와 윈도우 함수를 사용하여 쉽게 전년도 데이터를 뽑을 수 있다.