[DataLemur] SQL 문제 풀이 - Medium 레벨 (1)

•  

•  출처
  • site: https://datalemur.com/questions
  • representator: https://www.linkedin.com/in/nick-singh-tech/
•  개요
  • SQL 쿼리 작성 능력 함양을 위해 Medium 레벨 문제 풀이를 진행함
  • 본문의 쿼리들은 PostgreSQL 14 기반으로 작성함

---

•  제목
  • Odd and Even Measurements
• 문제 지시사항
  
  • 여러 날짜에 걸쳐 Google 센서의 측정값이 기록된 measurements 테이블이 주어진다. 하루 안에서도 여러 번 측정이 이루어질 수 있으며, 각 측정값에는 측정 시각이 함께 저장되어 있다.
  • 목표는 날짜별로 측정값을 집계하되, 하루 안에서 측정된 순서를 기준으로 홀수 번째 측정값의 합과 짝수 번째 측정값의 합을 각각 계산하는 것이다.
  • 측정 순서의 기준은 measurement_time이다. 즉, 같은 날짜 안에서 measurement_time이 빠른 순서대로 1번째, 2번째, 3번째 측정값을 정한다.
    • 하루 안에서 1번째, 3번째, 5번째처럼 홀수 번째로 측정된 값은 odd-numbered measurements로 매핑.
    • 하루 안에서 2번째, 4번째, 6번째처럼 짝수 번째로 측정된 값은 even-numbered measurements로 매핑.
• 풀이

WITH base AS (
  SELECT
    *,
    DATE(measurement_time) AS measurement_day,
    ROW_NUMBER() OVER (PARTITION BY DATE(measurement_time) ORDER BY measurement_time) AS nth
  FROM measurements
)

SELECT
  measurement_day,
  SUM(CASE WHEN nth % 2 != 0 THEN measurement_value ELSE 0 END) AS odd_sum,
  SUM(CASE WHEN nth % 2 = 0 THEN measurement_value ELSE 0 END) AS even_sum
FROM base
GROUP BY measurement_day
ORDER BY measurement_day;

• 노트
  • 지문이 영어로 작성되어있다면 더 꼼꼼히 읽어서 문제의 모든 지시사항을 숙지할 필요가 있다. 알겠니 나 자신아?
  • 이 문제에 거의 30분을 썼는데 로직은 금방 짰는데 날짜 내에서 측정 순서를 지정하는 부분을 놓쳐서 삽질을...
  • 심지어 문제 지문에 별도로 측정 순서에 대한 안내를 해줬는데도 !!!
  • 영어를 좋아한다고 생각했는데 이렇게 매번 대충 읽는걸 보면.. 스스로를 한참 잘못 알고 있는 듯하다.
  • 정신차려.

---

•  제목
  • User's Third Transaction
• 문제
  • Uber 사용자의 거래 내역이 저장된 transactions 테이블에 대해 각 행은 특정 사용자가 특정 시점에 사용한 거래 금액을 의미함.
  • 목표는 각 사용자별로 시간순 세 번째 거래만 조회하는 것임.
• 풀이

SELECT
  user_id,
  spend,
  transaction_date
FROM (
  SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY transaction_date) AS transaction_cnt_per_user
  FROM transactions
) a
WHERE transaction_cnt_per_user = 3;

• 노트
  • 전체 거래 중 세 번째 행을 찾는 것이 아니라 각 user_id별로 세 번째 거래를 찾아야 한다.
  • 따라서 사용자별로 거래 순번을 매긴 뒤 세 번째 순번만 필터링하는 방식이 필요해서 ROW_NUMBER() 사용.

---

•  제목
  • Second Highest Salary
• 문제
  • 직원 정보가 저장된 employee 테이블에 대해 각 직원은 고유 ID, 이름, 급여, 부서 ID, 매니저 ID 정보를 가짐.
  • 목표는 전체 직원 중 두 번째로 높은 급여를 구하는 것임.
  • 같은 급여를 받는 직원이 여러 명 있을 수 있으므로, 중복 급여는 하나의 급여 값으로만 취급해야 함.
• 풀이

SELECT DISTINCT
  salary AS second_highest_salary
FROM (
  SELECT
    *,
    RANK() OVER (ORDER BY salary DESC) AS sal_ranks
  FROM employee
) a
WHERE sal_ranks = 2;

• 노트
  • 급여 값 중복 존재를 허용하지만 해당 급여 값 자체를 반환하기만 하면 되므로 RANK()로 충분했다.

---

•  제목
  • Sending vs. Opening Snaps
• 문제
  • Snapchat 사용자의 활동 시간과 연령대 정보가 담긴 두 테이블에 대해 각 사용자는 스냅을 보내거나, 열어보거나, 채팅하는 활동을 할 수 있다.
  • 목표는 연령대별로 스냅을 보내는 데 사용한 시간(activity_type= send)과 스냅을 열어보는 데 사용한 시간(activity_type=open)이 전체 스냅 활동 시간에서 각각 몇 퍼센트를 차지하는지 계산하는 것.
• 풀이

SELECT
  age_bucket,
  ROUND(sum_send / total_time_spent * 100.0, 2) AS send_perc,
  ROUND(sum_open / total_time_spent * 100.0, 2) AS open_perc
FROM (
  SELECT
    age_bucket,
    a.user_id,
    SUM(time_spent) AS total_time_spent,
    SUM(CASE WHEN activity_type = 'open' THEN time_spent ELSE NULL END) AS sum_open,
    SUM(CASE WHEN activity_type = 'send' THEN time_spent ELSE NULL END) AS sum_send
  FROM activities a
  LEFT OUTER JOIN age_breakdown ab
    ON a.user_id = ab.user_id
  WHERE activity_type IN ('open', 'send')
  GROUP BY age_bucket, a.user_id
) f
ORDER BY age_bucket

• 노트
  • 소요시간: 47m 30s
  • 이번엔 문제를 잘 읽으려고 노력했는데 문제 자체를 이해하는데 시간이 오래 걸렸다.
  • 너무 헷갈려서 쿼리 쓰다가 갈아엎었음.
  • sample을 보면서 최종적으로 이해하긴 했는데 실무에서는 지시사항이 구두/텍스트 위주로 주어지거나 내가 직접 정의하게 될텐데 친절히 주어진 샘플까지 활용하면 안될 것 같다..
  • 그래도 잘 안될 땐 한번 싹 초기화시키고 재풀이하면 좀 잘 정리되는 것 같다. 마치 지저분한 연습장 넘겨버리고 그냥 새 페이지에 다시 푸는 것처럼 ..

---

•  제목
  • Tweets' Rolling Averages
• 문제
  • 사용자별 일일 트윗 수가 저장된 tweets 테이블이 주어지면 각 사용자에 대해 날짜별 3일 이동평균 트윗 수 계산
• 풀이

SELECT
  user_id,
  tweet_date,
  ROUND(AVG(tweet_count)
    OVER (PARTITION BY user_id
          ORDER BY tweet_date
            ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
  , 2) AS rolling_avg_3d
FROM tweets;

• 노트
  • 소요시간: 16m 2s
  • ROWS/RANGE BETWEEN (UNBOUNED) PRECEDING/FOLLOWING AND CURRENT ROW 문법이 생각 안나서 구글링했다.
  • 이동평균 구하는 데에 사용한다고 하니 기억을 해둘 필요가 있다.
  • ROWS/RANGE 범위 설정이 헷갈렸는데 샘플 보고 현재 행 기준 앞 2행 (없으면 제외) 임을 확인했다.

---

•  제목
  • Highest-Grossing Items
• 문제
  • Amazon 고객의 상품 구매 내역이 저장된 product_spend 테이블에 대해 각 행은 특정 사용자가 특정 상품에 지출한 금액과 거래 시점을 의미함.
  • 2022년에 발생한 거래만 대상으로, 각 카테고리별 매출 상위 2개 상품을 구하라.
• 풀이

WITH filtered AS (
  SELECT
    a.category,
    a.product,
    b.total_spend,
    DENSE_RANK() OVER (PARTITION BY a.category ORDER BY b.total_spend DESC) AS crank
  FROM product_spend a
  LEFT OUTER JOIN
    (
      SELECT
        category,
        product,
        SUM(spend) AS total_spend
      FROM product_spend
      WHERE EXTRACT(YEAR FROM transaction_date) = 2022
      GROUP BY category, product
    ) b
      ON a.category = b.category AND a.product = b.product
      WHERE EXTRACT(YEAR FROM transaction_date) = 2022
)

SELECT DISTINCT
  category,
  product,
  total_spend
FROM filtered
WHERE crank <= 2
ORDER BY category, total_spend DESC

• 노트
  • 소요시간: 1h 24m 12s
  • "identify the top two highest-grossing products within each category" 는 총매출이 가장 높은 카테고리 내 상품 상위 2개였음.
  • 개별 매출에서 카테고리 내부 상품의 최댓값에 대해 top를 구해서 삽질을 1시간 넘게 했다.. 악악악
  • 제출 시에 답이 똑같아서 의문 가득한 채로 gpt에 물어보고서야 총매출 기준으로 계산했어야 함을 알았음.
  • 궁극적으로 영미권 국가에서 일을 해보고 싶었는데 이대로는 개판날 것 같다.
  • DataLemur는 외국 기업들이 실제 진행한 테스트를 기반으로 가공하여 만든 문제 모음이라 실감나서 좋은 것 같다. 동기부여가 더 잘되는 느낌 !!

---

•  제목
  • Top Three Salaries
• 문제
  • 직원 정보가 담긴 employee 테이블과 부서 정보가 담긴 department 테이블에 대해 각 부서별 급여 순위 상위  3위 직원을 조회
• 풀이

SELECT
  department_name,
  name,
  salary
FROM (
  SELECT
    department_name,
    name,
    salary,
    DENSE_RANK() OVER (PARTITION BY e.department_id ORDER BY salary DESC) AS srank
  FROM employee e
  LEFT OUTER JOIN department d
    ON e.department_id = d.department_id
) a
WHERE srank <= 3
ORDER BY department_name, salary DESC, name

• 노트
  • 소요시간: 12m 7s
  • 부서 안에서 급여 금액을 기준으로 1위, 2위, 3위 급여 구간에 속한 직원을 찾아야 하므로 급여 기준 ranking을 부여하고 3 이하인 직원을 필터링해야 함.
  • 이때, 지문에서는 "handle duplicate salaries effectively" 라고 언급했던 것은 같은 부서에서 여러 직원이 같은 급여를 받으면 모두 같은 순위를 할당받아야 함을 나타냄.
  • 따라서 ROW_NUMBER(), RANK() 가 아닌 DENSE_RANK() 로써 중복값에 같은 순위를 부여하고 뒤에 이어질 순위도 중복값에 영향을 받지 않도록 설계해야 함.

---

•  제목
  • Signup Activation Rate
• 문제
  • TikTok 신규 사용자의 이메일 가입 정보와 문자 인증 응답 정보가 담긴 두 테이블이 주어지면
    emails 테이블에 있는 사용자들 중 계정 활성화를 완료한 사용자의 비율을 계산하라.
  • confirm_rate = 활성화를 완료한 사용자 수 / emails 테이블에 있는 전체 가입 사용자 수
    • 분석 대상은 texts 테이블의 모든 사용자가 아니라 emails 테이블에 있는 특정 가입 사용자들이다. 따라서 emails 테이블을 기준으로 삼아야 하며, 문자 인증 기록이 없는 사용자도 분모에 포함되어야 한다.
    • 또한 사용자는 계정 활성화 전까지 여러 개의 문자 인증 메시지를 받을 수 있으므로, 'Confirmed' 기록이 있는지 여부를 기준으로 활성화 여부를 판단해야 한다.
  • 최종 결과는 confirm_rate 하나의 컬럼으로 출력하고, 소수점 둘째 자리까지 반올림
  • 가정
    • 분석가는 emails 테이블에 있는 특정 사용자의 활성화율에 관심이 있으며, 이 테이블에는 texts 테이블에서 잠재적으로 찾을 수 있는 모든 사용자가 포함되어 있지 않을 수 있습니다.
    • 예를 들어, emails 테이블의 사용자 123이 texts 테이블에 없을 수도 있고, 그 반대의 경우도 마찬가지입니다.
• 풀이

# Wrong
SELECT
  ROUND(SUM(CAST(CASE
            WHEN signup_action = 'Confirmed'
              THEN 1 END AS NUMERIC)) / COUNT(text_id) * 1.0
  , 2) AS confirm_rate
FROM emails e
RIGHT OUTER JOIN texts t
  ON e.email_id = t.email_id
 
 # Correct
SELECT
  ROUND(CAST(SUM(CASE WHEN signup_action = 'Confirmed' THEN 1 END) AS NUMERIC)
    / COUNT(DISTINCT e.email_id)
    , 2) AS confirm_rate
FROM
  emails e
LEFT OUTER JOIN texts t
  ON e.email_id = t.email_id

• 노트
  • 소요시간: 21m + 4m 17s
  • 조인과 분모 설정에서 시간을 오래 소요했음. → 문제 파악을 한번에 잘해야 풀이 시간도 줄일 수 있을 듯
  • 고민 끝에 푼 쿼리에 정답 판정을 받았는데 gpt는 우연히 맞은거고 문제 설계가 틀렸다고 알려줌.
  • 문제의 가정에 대해 내가 이해를 잘못해서 texts 기준으로 병합했는데 문제가 원한 것은 emails 기준 병합이었음.
  • 그래서 초기화 후에 재풀이를 거쳤음.

---