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

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

제목
- Histogram of Users and Purchases
문제
- 각 사용자별로 가장 최근 거래 시점을 찾고, 그 시점에 구매한 상품 수를 계산하라.
- 출력 항목
  - 가장 최근 transaction_date
  - user_id
  - 해당 거래 시점에 구매한 상품 수 purchase_count
- 최종 결과는 transaction_date 기준 오름차순으로 정렬.
풀이

# mine
SELECT DISTINCT
  transaction_date,
  user_id,
  COUNT(product_id) OVER (PARTITION BY user_id) AS purchase_count
FROM (
  SELECT
    *,
    DENSE_RANK() OVER (PARTITION BY user_id ORDER BY transaction_date::DATE DESC) AS RECENT
  FROM user_transactions
) a
WHERE RECENT = 1
ORDER BY transaction_date;

# gpt 첨삭
SELECT DISTINCT
  transaction_date,
  user_id,
  COUNT(product_id) OVER (PARTITION BY user_id) AS purchase_count
FROM (
  SELECT
    *,
    DENSE_RANK() OVER (PARTITION BY user_id ORDER BY transaction_date DESC) AS RECENT
  FROM user_transactions
) a
WHERE RECENT = 1
ORDER BY transaction_date;

# gpt 추천
WITH ranked_transactions AS (
  SELECT
    user_id,
    product_id,
    transaction_date,
    RANK() OVER (
      PARTITION BY user_id
      ORDER BY transaction_date DESC
    ) AS transaction_rank
  FROM user_transactions
)
SELECT
  transaction_date,
  user_id,
  COUNT(product_id) AS purchase_count
FROM ranked_transactions
WHERE transaction_rank = 1
GROUP BY
  transaction_date,
  user_id
ORDER BY transaction_date;

노트
- gpt가 말하길, 내 쿼리는 같은 날 서로 다른 시각의 거래를 모두 최신 거래로 처리하니까 부정확할 가능성 존재.
- 원본 컬럼의 timestamp형을 유지하는 것을 추천함.
- most recent transaction date 에서 date w/o time 에만 집중해도 되는 것 아닐까 싶지만..., 아마도 문제가 원하는 것은 가장 최신 transaction에서, 즉 한 번의 구매에서 개별 유저의 구매량을 확인하고 싶었던 것이라면 gpt의 제안이 맞을 것 같다.

제목
- Compressed Mode
문제
- items_per_order 테이블에는 주문당 상품 수인 item_count와, 해당 상품 수를 가진 주문의 빈도인 order_occurrences가 저장되어 있음.
- order_occurrences가 가장 큰 item_count를 최빈값으로 출력.
- 만약 최고 빈도가 같은 item_count가 여러 개라면 모두 출력.
- 결과는 item_count 기준 오름차순 정렬하고 출력 컬럼명은 mode로 설정하라.
- 즉, 가장 자주 등장한 주문당 상품 수를 찾는 문제
풀이

# mine
SELECT
  item_count AS mode
FROM (
  SELECT
    *,
    RANK() OVER (ORDER BY order_occurrences DESC) AS modes
  FROM items_per_order
) a
WHERE modes = 1
ORDER BY item_count;

# gpt 추천
SELECT
  item_count AS mode
FROM items_per_order
WHERE order_occurrences = (
  SELECT MAX(order_occurrences)
  FROM items_per_order
)
ORDER BY item_count;

노트
- gpt 풀이가 아래의 이유들로 더 좋은 풀이라고 함.
  - 문제 의도를 바로 표현함.
  - CTE나 윈도우 순위가 필요 없음.
  - 모든 행을 순위 계산용으로 정렬하지 않음.
  - 동점 최빈값도 모두 반환함.
- 최대 order_occurrences를 찾고 그에 해당하는 item_count를 출력하기만 되는 문제이니 맞는 말.

제목
- Card Launch Success
문제
- 각 신용카드별로 가장 최초로 발급 실적이 기록된 연도와 월, 즉 출시 월을 찾아라.
- 카드별 출시 월과 그에 대응하는 issued_amount를 출력.
- 출시 월은 issue_year와 issue_month를 함께 고려한 가장 이른 시점.
- 결과는 issued_amount가 큰 순서대로 정렬.
풀이

# mine
SELECT
  card_name,
  issued_amount
FROM (
  SELECT
    *,
    TO_DATE(CONCAT(issue_year, '-', issue_month), 'YYYY-MM') AS issue_date,
    MIN(TO_DATE(CONCAT(issue_year, '-', issue_month), 'YYYY-MM')) OVER (PARTITION BY card_name) AS init
  FROM monthly_cards_issued
) a
WHERE issue_date = init
ORDER BY issued_amount DESC;

# gpt 추천
WITH ranked AS (
  SELECT
    card_name,
    issued_amount,
    ROW_NUMBER() OVER (
      PARTITION BY card_name
      ORDER BY issue_year, issue_month
    ) AS launch_rank
  FROM monthly_cards_issued
)
SELECT
  card_name,
  issued_amount
FROM ranked
WHERE launch_rank = 1
ORDER BY issued_amount DESC;

노트
- 처음 풀이가 틀려서 gpt에게 힌트 받아 연도를 고려안했음을 확인함.
- gpt가 추천하는 쿼리의 장점
  - 문자열 결합이 필요 없음.
  - 날짜 변환이 필요 없음.
  - 연도와 월을 직접 순서대로 비교.
  - 카드별 최초 행 하나를 바로 선택.

제목
- International Call Percentage
문제
- 전체 통화 중에서 발신자와 수신자의 국가가 서로 다른 국제전화의 비율을 계산하라.
- 발신자 국가와 수신자 국가가 다르면 국제전화로 판단.
- 국제전화 건수 ÷ 전체 통화 건수 × 100
- 결과를 소수점 첫째 자리까지 반올림하고, 출력 컬럼명은 international_calls_pct로 지정.
풀이

# mine
SELECT
  ROUND(
    CAST(SUM(CASE
            WHEN caller_country != receiver_country THEN 1 ELSE 0 END) AS NUMERIC)
    / COUNT(*) * 100.0
  , 1) AS international_calls_pct
FROM (
  SELECT
    c.caller_id,
    c.receiver_id,
    i1.country_id AS caller_country,
    i2.country_id AS receiver_country
  FROM phone_calls c
  JOIN phone_info i1
    ON c.caller_id = i1.caller_id
  JOIN phone_info i2
    ON c.receiver_id = i2.caller_id
) a

# gpt 추천1
SELECT
  ROUND(
    100.0 * SUM(
      CASE
        WHEN caller.country_id <> receiver.country_id THEN 1
        ELSE 0
      END
    ) / COUNT(*),
    1
  ) AS international_calls_pct
FROM phone_calls calls
JOIN phone_info caller
  ON calls.caller_id = caller.caller_id
JOIN phone_info receiver
  ON calls.receiver_id = receiver.caller_id;
  
 # gpt 추천2
 SELECT
  ROUND(
    100.0
    * COUNT(*) FILTER (
        WHERE caller.country_id <> receiver.country_id
      )
    / COUNT(*),
    1
  ) AS international_calls_pct
FROM phone_calls calls
JOIN phone_info caller
  ON calls.caller_id = caller.caller_id
JOIN phone_info receiver
  ON calls.receiver_id = receiver.caller_id;

노트
- gpt 추천1 풀이는 현재 풀이와 계산 원리는 같지만, 국가 컬럼을 먼저 서브쿼리로 만든 뒤 다시 집계할 필요가 없어 더 간결함.
- gpt 추천2 풀이는 PostgreSQL 의 FILTER() 를 사용해 국제전화 수를 카운팅함.

제목
- Patient Support Analysis (Part 2)
문제
- 전체 상담 전화 중 분류되지 않은 통화의 비율을 계산하라.
- 분류되지 않은 통화는 'n/a' 이거나 비어있음.
풀이

# mine
SELECT
  ROUND(100.0 *
    CAST(SUM(CASE WHEN call_category = 'n/a' OR call_category IS NULL THEN 1 ELSE 0 END) AS NUMERIC)
    / COUNT(*)
  , 1) AS uncategorised_call_pct
FROM callers;

# gpt 추천1
SELECT
  ROUND(
    100.0 * SUM(
      CASE
        WHEN call_category = 'n/a'
          OR call_category IS NULL
        -- WHERE call_category = 'n/a'
	--	OR NULLIF(TRIM(call_category), '') IS NULL
        THEN 1
        ELSE 0
      END
    ) / COUNT(*),
    1
  ) AS uncategorised_call_pct
FROM callers;

# gpt 추천2
SELECT
  ROUND(
    100.0
    * COUNT(*) FILTER (
        WHERE call_category = 'n/a'
           OR call_category IS NULL
      )
    / COUNT(*),
    1
  ) AS uncategorised_call_pct
FROM callers;

노트
- 아.. 처음에 n/a 만 보고 NULL 처리 안해서 틀렸다.
- 첫 풀이 때 FILTER () 를 써봤는데 NULL 처리 안해서 틀린걸 내가 FILTER () 를 잘못 쓴 줄 알고 원래 쓰던 걸로 돌아왔다. NULL 처리만 해줬더라면 괜찮을듯. FILTER () 써봐서 좋다.
- gpt는 100.0 으로 처리해줬으니 CAST (.. AS ..) 는 중복이라고 해줌.

이로써 1문제를 제외하고 DataLemur 의 Medium SQL 풀이를 마쳤다.

1문제는 내 컴퓨터만 그런건지 테이블 조회가 잘 안돼서 보류했다.

다음은 Hard 풀이로 만나요 ~

'Study > SQL' 카테고리의 다른 글

[DataLemur] SQL 문제 풀이 - Medium 레벨 (2) (0)	2026.07.05
[DataLemur] SQL 문제 풀이 - Medium 레벨 (1) (0)	2026.07.03
[HackerRank] SQL 문제 풀이 - Medium 레벨 (1) (0)	2026.07.01
[LeetCode] SQL 50 문제 풀이 (0)	2026.04.17
[sql-practice] SQL 문제 풀이 (6) (0)	2026.04.03

#545

About

Categories

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

'Study > SQL' 카테고리의 다른 글

티스토리툴바

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

'Study > SQL' 카테고리의 다른 글

'Study/SQL' Related Articles

티스토리툴바