TIL - 250207

리뷰 : case when을 활용하면 유용하다

URL : https://datalemur.com/questions/time-spent-snaps

WITH tb AS (
SELECT
  age_bucket
  ,SUM (CASE WHEN activity_type = 'send' THEN time_spent ELSE 0 END) send_time
  ,SUM (CASE WHEN activity_type = 'open' THEN time_spent ELSE 0 END) open_time
FROM activities
LEFT JOIN age_breakdown
USING (user_id)
GROUP BY 1
ORDER BY 1
)

SELECT
  age_bucket
  ,ROUND(100 * send_time / (send_time + open_time),2)
  ,ROUND(100 * open_time / (send_time + open_time),2)
FROM tb
;