Windows 함수 연습

💡 문제: 가장 최근 주문 가져오기

테이블명: orders

order_id (int) – 주문 고유 ID
user_id (int) – 사용자 ID
order_date (date) – 주문일
amount (int) – 주문 금액

✏️ 예시 출력
user_id order_id order_date amount
101 9 2024-12-01 35000
102 5 2024-11-30 12000
103 8 2024-12-02 50000

방법 1. 서브쿼리 방식

SELECT user_id, order_id, order_date, amount
FROM (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date DESC) AS rn
  FROM orders
) AS sub
WHERE rn = 1;

방법 2. CTE 방식

WITH ranked_orders AS (
  SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date DESC) AS rn
  FROM orders
)
SELECT
  user_id,
  order_id,
  order_date,
  amount
FROM ranked_orders
WHERE rn = 1;

방법 3. QUALIFY 방식

SELECT user_id, order_id, order_date, amount
FROM orders
QUALIFY ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date DESC) = 1;