TIL - 250220

리뷰 : 코드가 꽤나 길어서 다른 사람들의 코드를 봤는데 엄청 짧다. JOIN을 두 번 할 수도 있다는 걸 알게 되었다. 그래서 나도 JOIN을 두 번 넣어서 코드를 다시 짰다. 그랬더니 훨씬 짧게 간단하게 나왔다.

리뷰 2 : EXTRACT를 사용해서 날짜만 뺄 수 있다. 날짜에 관련해서 다시 상기시킬 것은 MySQL에서는 DATE_FORMAT(), PostgreSQL에서는 TO_CHAR()에 'Mon-YYYY'식으로 조건 주기.

리뷰 3 : SUM()과 CASE WHEN을 사용하면 금방 풀 수 있는 문제이다. 여기서 제대로 봐야 할 점은 NULL값 이외에도 'n/a'도 포함시키는 것..!

리뷰 4 : YOY를 구하는 것이다. LAG()로 이전의 값을 불러오고 적절히 계산을 해서 yoy를 구할 수 있다.

URL : https://datalemur.com/questions/international-call-percentage

WITH cte AS (
  SELECT
    a.caller_id
    ,a.receiver_id
    ,a.country_id
    ,i.country_id re_country_id
  FROM(
    SELECT 
      caller_id
      ,c.receiver_id
      ,country_id
    FROM phone_calls c
    LEFT JOIN phone_info i
    USING(caller_id)
  ) a
  LEFT JOIN phone_info i
  ON a.receiver_id=i.caller_id
)
SELECT 
  ROUND(100.0 * SUM(CASE WHEN country_id!=re_country_id THEN 1 ELSE 0 END)
  / COUNT(*),1)
FROM cte
;

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

URL : https://datalemur.com/questions/user-retention

SELECT
  EXTRACT(MONTH FROM event_date)+1 AS month
  ,COUNT(DISTINCT user_id)
FROM user_actions
WHERE EXTRACT(MONTH FROM event_date) = 6
GROUP BY 1
;

URL : https://datalemur.com/questions/uncategorized-calls-percentage

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;

URL : https://datalemur.com/questions/yoy-growth-rate

SELECT
  *
  ,ROUND(100.0 * (curr_year_spend-prev_year_spend) / prev_year_spend,2)
FROM (
  SELECT
    EXTRACT(YEAR FROM transaction_date) AS year 
    ,product_id
    ,spend AS curr_year_spend
    ,LAG(spend,1) OVER (PARTITION BY product_id ORDER BY transaction_date) prev_year_spend
  FROM user_transactions
) a
;