글로벌 회사의 SQL 입사 코딩 테스트 (TikTok, Twitter, Verizon)

sql을 공부하다가 글로벌 회사 (TikTok, Twitter, Verizon)의 sql코딩 테스트를 풀어보게 되었다. 각 문제를 풀면서 내가 어떻게 풀었고 그 과정에서 배운점들을 기록하고자 한다.

1.TikTok
https://datalemur.com/questions/signup-confirmation-rate
위의 링크를 통해 문제를 풀어볼 수 있다.

내용을 요약하자면 틱톡을 가입하면 문자로 가입 확정 문자가 가는데 이를 수락해야 진짜 가입이 완료가 되어 계정이 활성화 된다고 한다. 여기서 계정이 얼마나 활성화가 되었는지 활성화 비율을 구하는 문제이다.

#정답 코드
SELECT
ROUND(count (DISTINCT CASE WHEN signup_action = 'Confirmed' THEN user_id END) * 1.0 / count(DISTINCT user_id),2)
FROM emails e LEFT JOIN texts t on e.email_id = t.email_id 

여기서 내가 몰랐던 점은 case 문을 count 와 함께 쓸 수 있다는 점이었다.
나는 case문의 쓰임에 대하여 gpt에다가 물어보았다.

크게 아래와 같이 2가지 쓰임세로 쓸 수 있었는데

1. 첫번째로는 조건에 따른 값을 반환할 때 쓰인다.

SELECT 
    employee_id,
    name,
    CASE 
        WHEN department = 'Sales' AND salary > 50000 THEN 'Senior Sales'
        WHEN department = 'Sales' AND salary <= 50000 THEN 'Junior Sales'
        WHEN department = 'IT' AND experience > 5 THEN 'Senior IT'
        ELSE 'Other'
    END AS position
FROM 
    employees;

이러한 경우가 내가 원래 알고 있던 쓰임이었다.

2. 두번째로 조건에 따른 집계를 할 때 쓰일 수 있다.

SELECT 
    region,
    COUNT(CASE WHEN sales > 1000 THEN 1 END) AS high_sales,
    COUNT(CASE WHEN sales <= 1000 THEN 1 END) AS low_sales
FROM 
    sales_data
GROUP BY 
    region;

위와 같은 경우는 이 문제와 같은 경우이다. count나 sum과 함께 select절에 쓰일 수 있다는 것을 새로 알게되었다.

또 새롭게 알게 된 점은 정수 나누기 정수를 하였을 때 값이 0이 반환될 때도 있다는 것이다.
그리하여 정수 / 정수 를 할 때는 둘중 하나에 대하여 실수로 바꾸어 주어야한다. 실수로 바꾸어주기 위해서는 크게 두가지 방법이 있다고 한다.

SELECT 
  CAST((SELECT COUNT(DISTINCT email_id) FROM texts WHERE signup_action = 'Confirmed') AS DECIMAL) /
  CAST((SELECT COUNT(DISTINCT email_id) FROM emails) AS DECIMAL) AS confirmed_ratio

위처럼 as DECIMAL을 쓰는 방법과 1.0을 곱해주는 방법이 있다.

2.Twitter
https://datalemur.com/questions/rolling-average-tweets

위 문제는 각 트위터 유저에 대한 트위터 사용 횟수의 3일 이동 평균을 구하는 것이다.

이동 평균이란?

SELECT user_id,
       tweet_date,
       ROUND(AVG(tweet_count) OVER(PARTITION BY user_id ORDER BY user_id,tweet_date ROWS BETWEEN 2 preceding and current row),2) as rolling_avg_3d
FROM tweets
GROUP BY tweet_date, user_id,tweet_count

user_id로 나누어 ROWS BETWEEN 2 PRECEDING AND CURRENT ROW 를 통해 앞에 2개의 행의 tweer_count 값과 현재의 값을 가지고 평균을 낸다. 첨에 partition by를 빼먹어 많이 헤맸었다..

3.Verizon
https://datalemur.com/questions/international-call-percentage

이 문제도 첫번째 TikTok문제와 유사하다.
해외 통화를 한 퍼센티지를 구하는 문제이다.

WITH receiver as (
SELECT caller_id as receiver_id,
       country_id as receiver_country_id
FROM phone_info
)

SELECT ROUND(COUNT(CASE WHEN i.country_id != receiver_country_id THEN 1 END )*1.0 / COUNT(*) *100,1) as international_calls_pct
FROM phone_calls c 
JOIN phone_info i on c.caller_id = i.caller_id 
JOIN receiver r on c.receiver_id = r.receiver_id

나는 이렇게 풀어 정답을 맞추었지만 구지 with절을 쓰지 않아도 되었다.
나는 JOIN을 할 때에 이상하게 컬럼명이 똑같아야 되는줄 알았다😂
그래서 WITH절을 구지 써서 컬럼명을 맞추었다.

그런데 아래처럼 JOIN을 활용할 수 있어 새로 알게되었다.

SELECT 
ROUND(CAST(SUM(CASE WHEN b.country_id <> c.country_id THEN 1 ELSE 0 END)*100 AS DECIMAL)/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;

컬럼명이 달라도 테이블을 조인할 수 있었다!!
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
위처럼 똑같은 phone_info 테이블을 각각 b,c로 지정하여 풀어낼 수 있다.