[PostgreSQL] (Medium) Sending vs. Opening Snaps [Snapchat SQL Interview Question]

SELECT age_bucket
     , ROUND(sending/(sending+opening) * 100.0, 2) AS send_perc
     , ROUND(opening/(sending+opening) * 100.0, 2) AS open_perc
FROM (
  SELECT age_bucket
       , SUM(CASE WHEN activity_type = 'open' THEN time_spent ELSE 0 END) AS opening
       , SUM(CASE WHEN activity_type = 'send' THEN time_spent ELSE 0 END) AS sending
  FROM activities
       JOIN age_breakdown USING (user_id)
  WHERE activity_type IN ('open', 'send')
  GROUP BY age_bucket) sub;

 

mysql에서도 그대로 쓸 수 있는 쿼리!

 

솔루션을 보니 필터라는 함수도 있다

SELECT 
  age.age_bucket, 
  ROUND(100.0 * 
    SUM(activities.time_spent) FILTER (WHERE activities.activity_type = 'send')/
      SUM(activities.time_spent),2) AS send_perc, 
  ROUND(100.0 * 
    SUM(activities.time_spent) FILTER (WHERE activities.activity_type = 'open')/
      SUM(activities.time_spent),2) AS open_perc
FROM activities
INNER JOIN age_breakdown AS age 
  ON activities.user_id = age.user_id 
WHERE activities.activity_type IN ('send', 'open') 
GROUP BY age.age_bucket;

코드가 길어지지만,, 이해하기는 쉬운 듯!

 

문제 출처:

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