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

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

---

• 제목
  • Supercloud Customer
• 문제
  • Azure 고객의 상품 구매 내역(customer_contracts)과 상품 정보(products)가 담긴 두 테이블이 주어지면 products 테이블에 존재하는 모든 상품 카테고리에서 최소 1개 이상의 상품을 구매한 고객을 찾으시오.
• 풀이

# mine
SELECT customer_id
FROM (
  SELECT
    customer_id,
    COUNT(DISTINCT product_category) AS every_category_cnt
  FROM customer_contracts cc
  JOIN products p
    ON cc.product_id = p.product_id
  GROUP BY customer_id
) a
WHERE every_category_cnt = (SELECT COUNT(DISTINCT product_category) FROM products)

# gpt
SELECT
  cc.customer_id
FROM customer_contracts cc
JOIN products p
  ON cc.product_id = p.product_id
GROUP BY cc.customer_id
HAVING COUNT(DISTINCT p.product_category) = (
  SELECT COUNT(DISTINCT product_category)
  FROM products
);

• 노트
  • gpt에 의하면 조금 더 간결하게 서브쿼리 대신 HAVING으로 바로 처리 가능하다고 함.

---

• 제목
  • Swapped Food Delivery
• 문제
  • Zomato 주문 데이터에서 오류로 인해 각 음식 항목이 다음 주문 행과 서로 뒤바뀌어 있다.
  • order_id가 1, 2, 3, 4, 5, 6인 행을 각각 한 쌍으로 보고 음식 항목이 올바른 주문 번호와 연결되도록 order_id를 교환해야 한다.
    • 홀수 주문 번호는 다음 짝수 번호로 변경
    • 짝수 주문 번호는 이전 홀수 번호로 변경
    • 마지막 주문 번호가 홀수라면 교환할 다음 행이 없으므로 그대로 유지
    • 결과는 corrected_order_id, item을 출력하고 수정된 주문 번호 순으로 정렬
• 풀이

# mine
SELECT
  COALESCE(
    CASE
      WHEN (SELECT MAX(order_id) FROM orders) % 2 = 0
        THEN
          (CASE WHEN order_id % 2 = 0 THEN order_id - 1 ELSE order_id + 1 END)
        ELSE (CASE
          WHEN order_id != (SELECT MAX(order_id) FROM orders)
            THEN (CASE WHEN order_id % 2 = 0 THEN order_id - 1 ELSE order_id + 1 END) END)
    END
  , order_id) AS corrected_order_id,
  item
FROM orders
ORDER BY corrected_order_id

# gpt
SELECT
  CASE
    WHEN order_id % 2 = 1
         AND order_id = (SELECT MAX(order_id) FROM orders)
      THEN order_id
    WHEN order_id % 2 = 1
      THEN order_id + 1
    ELSE order_id - 1
  END AS corrected_order_id,
  item
FROM orders
ORDER BY corrected_order_id;

• 노트
  • gpt가 더 간결한 풀이를 알려줌
  • 그리고 내가 작성한 풀이와 gpt 작성 풀이들 모두 해당 문제에 한정해, 주문 번호가 1, 2, 3, …처럼 연속적이라는 문제의 전제를 이용함. 실제 데이터에서 주문 번호가 1, 3, 7처럼 불연속이라면 ±1이 아니라 LEAD()와 LAG()로 실제 인접 행을 찾아야 함.
  • 문제의 핵심 조건은 단순히 “현재 행이 마지막 홀수 주문인지”만 확인하면 된다고 함.

---

• 제목
  • FAANG Stock Min-Max (Part 1)
• 문제
  • stock_prices 테이블에서 각 FAANG 종목별 전체 기간의 최고 시가와 최저 시가를 찾아라.
  • 각 종목마다 다음 정보를 한 행으로 출력.
    • 종목 티커
    • 최고 시가가 기록된 월·연도(Mon-YYYY)
    • 최고 시가
    • 최저 시가가 기록된 월·연도(Mon-YYYY)
    • 최저 시가
  • 결과는 ticker 기준 오름차순으로 정렬.
• 풀이

# mine
WITH high_filtered AS (
  SELECT
    ticker,
    MAX(open) AS highest_open
  FROM stock_prices
  GROUP BY ticker
), low_filtered AS (
  SELECT
    ticker,
    MIN(open) AS lowest_open
  FROM stock_prices
  GROUP BY ticker
  ORDER BY ticker
), highest AS (
  SELECT
    hf.ticker,
    TO_CHAR(sp.date, 'Mon-YYYY') AS highest_mth,
    highest_open
  FROM high_filtered hf
  JOIN stock_prices sp
    ON hf.ticker = sp.ticker AND hf.highest_open = sp.open
), lowest AS (
  SELECT
    lf.ticker,
    TO_CHAR(sp.date, 'Mon-YYYY') AS lowest_mth,
    lowest_open
  FROM low_filtered lf
  JOIN stock_prices sp
    ON lf.ticker = sp.ticker AND lf.lowest_open = sp.open
)

SELECT
  h.ticker,
  highest_mth,
  highest_open,
  lowest_mth,
  lowest_open
FROM highest h
JOIN lowest l
  ON h.ticker = l.ticker
ORDER BY ticker

# gpt with Accuracy and Readability
WITH ranked AS (
  SELECT
    ticker,
    date,
    open,
    ROW_NUMBER() OVER (
      PARTITION BY ticker
      ORDER BY open DESC, date ASC
    ) AS highest_rank,
    ROW_NUMBER() OVER (
      PARTITION BY ticker
      ORDER BY open ASC, date ASC
    ) AS lowest_rank
  FROM stock_prices
)
SELECT
  ticker,
  TO_CHAR(
    MAX(date) FILTER (WHERE highest_rank = 1),
    'Mon-YYYY'
  ) AS highest_mth,
  MAX(open) FILTER (
    WHERE highest_rank = 1
  ) AS highest_open,
  TO_CHAR(
    MAX(date) FILTER (WHERE lowest_rank = 1),
    'Mon-YYYY'
  ) AS lowest_mth,
  MAX(open) FILTER (
    WHERE lowest_rank = 1
  ) AS lowest_open
FROM ranked
GROUP BY ticker
ORDER BY ticker;

# gpt with Aggregation-centric Efficiency
WITH extrema AS (
  SELECT
    ticker,
    MAX(open) AS highest_open,
    MIN(open) AS lowest_open
  FROM stock_prices
  GROUP BY ticker
)
SELECT
  e.ticker,
  TO_CHAR(
    MIN(sp.date) FILTER (
      WHERE sp.open = e.highest_open
    ),
    'Mon-YYYY'
  ) AS highest_mth,
  e.highest_open,
  TO_CHAR(
    MIN(sp.date) FILTER (
      WHERE sp.open = e.lowest_open
    ),
    'Mon-YYYY'
  ) AS lowest_mth,
  e.lowest_open
FROM extrema e
JOIN stock_prices sp
  ON sp.ticker = e.ticker
 AND sp.open IN (e.highest_open, e.lowest_open)
GROUP BY
  e.ticker,
  e.highest_open,
  e.lowest_open
ORDER BY e.ticker;

• 노트
  • gpt에게 더 효율적인 풀이를 제시해달라고 했음.
  • 현재 내 풀이는 동점 처리에 취약하고 반복 참조가 많다는 단점이 있음. ROW_NUMBER() 를 사용하면 이해하기 쉽고, 집계 & 단일 조인 방식을 통해 정렬 비용이나 메모리를 줄이는 방향을 최적화할 수 있다고 함.
  • 최대, 최소 값 찾기에 ROW_NUMBER() 가 자주 사용되는 것 같음. 익숙해지자.
  • 집계함수(컬럼) FILTER (WHERE 조건) 은 집계 함수에 들어갈 행만 선택적으로 제한하는 문법이라고 함.
    • FILTER 조건이 참인 행만 해당 집계 함수의 입력으로 전달되고 나머지 행은 그 집계 계산에서 제외됨.
    • FILTER 의 장점은 같은 그룹에서 서로 다른 조건의 집계를 동시에 계산할 수 있다는 점임.
    • 전체 쿼리의 행이 한 번에 필터링되는 WHERE 과 달리 각 집계 함수마다 서로 다른 조건을 적용할 수 있음.
    • 조건부 집계에 사용하는 CASE WHEN 과 거의 동일한 역할을 수행함.
    • SUM(), COUNT(), AVG() 의 경우, 집계함수() FILTER (WHERE 조건) OVER () 도 가능함.

---

• 제목
  • Best-Selling Product
• 문제
  • 각 상품 카테고리에서 판매량이 가장 높은 상품 1개를 찾아라.
    • 우선 sales_quantity가 높은 상품을 선택.
    • 판매량이 같으면 rating이 더 높은 상품을 선택.
    • 결과에는 category_name, product_name만 출력하고 category_name 기준 알파벳 오름차순으로 정렬.
• 풀이

# mine
SELECT
  category_name,
  product_name
FROM (
  SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY category_name ORDER BY sales_quantity DESC, rating DESC) AS ranks
  FROM products p
  JOIN product_sales ps
    ON p.product_id = ps.product_id
) a
WHERE ranks = 1
ORDER BY category_name, product_name

• 노트
  • 아 정렬 빼먹었다. 우연히 맞는다는 것은... 도움이 안된다.

---

• 제목
  • User Shopping Sprees
• 문제
  • 사용자별 구매 기록에서 3일 이상 연속으로 매일 구매한 이력이 한 번이라도 있는 사용자를 찾아라.
  • 같은 사용자가 3일 이상 연속된 날짜에 구매해야 한다.
  • 결과는 user_id 오름차순으로 정렬.
• 풀이

# gpt 첨삭
WITH daily_transactions AS (
  SELECT DISTINCT
    user_id,
    transaction_date::DATE AS transaction_day
  FROM transactions
),
date_sequence AS (
  SELECT
    user_id,
    transaction_day,
    LAG(transaction_day) OVER (
      PARTITION BY user_id
      ORDER BY transaction_day
    ) AS previous_day,
    LEAD(transaction_day) OVER (
      PARTITION BY user_id
      ORDER BY transaction_day
    ) AS next_day
  FROM daily_transactions
)
SELECT DISTINCT
  user_id
FROM date_sequence
WHERE transaction_day = previous_day + 1
  AND transaction_day = next_day - 1
ORDER BY user_id;

# solution by Rithu Soumyaj
SELECT DISTINCT T1.user_id
FROM transactions AS T1
INNER JOIN transactions AS T2
  ON DATE(T2.transaction_date) = DATE(T1.transaction_date) + 1
INNER JOIN transactions AS T3
  ON DATE(T3.transaction_date) = DATE(T1.transaction_date) + 2
ORDER BY T1.user_id;

• 노트
  • 첫 풀이에서 접근방식을 틀렸는데 gpt가 알려준 정답을 봐버렸음.
  • 첫 풀이가 해당 사이트에서는 또 우연히 정답 처리 되었으나 지시사항에 부합하려면 여러 조건이 더 필요했음.
  • transaction_date 가 timestamp 이므로 INTERVAL ± 1 은 정확히 24시간 차이가 나야 감지됨.
  • 같은 날 여러 거래가 있다면 LAG(), LEAD() 의 순서가 왜곡되므로 날짜 기준으로 중복을 제거해야 함.
  • 4일 이상 연속인 경우 동일 사용자가 여러 번 출력될 수 있음 -> 문제가 됨.
  • 아아........ "연속 3일 이상 구매한 이력이 존재" 라는 조건이 얼마나 많은 장치로 구현돼야 하는가..... 꼼꼼해야지..

---