실전 문제 풀이 (3)

✅ 문제 1. 유저별 첫 주문 이후 7일 이내에 재구매한 유저 수 구하기

📋 문제 설명

orders 테이블에서 유저가 첫 주문을 한 날로부터 7일 이내에 재주문한 적이 있는 유저를 찾고,
그런 유저의 수를 구하라.

CREATE TABLE orders (
    order_id INT,
    user_id INT,
    order_date DATE
);

INSERT INTO orders VALUES
(1, 101, '2023-01-01'),
(2, 101, '2023-01-05'),
(3, 102, '2023-01-03'),
(4, 102, '2023-01-20'),
(5, 103, '2023-01-04'),
(6, 103, '2023-01-10'),
(7, 104, '2023-01-01'),
(8, 104, '2023-01-09');

SELECT
  COUNT(*) AS reorder_count
FROM(
  SELECT
   order_id,
   user_id,
   order_date,
   FIRST_VALUE(order_date) OVER (
     PARTITION BY user_id
     ORDER BY order_date
     ) AS first_order
  FROM orders
  ) AS t
WHERE first_order != order_date
	AND DATEDIFF(order_date, first_order) < 7

 

 

 

✅ 문제 2. 카테고리별로 매출 상위 2위까지의 상품 조회

📋 문제 설명

products 테이블에서 각 상품은 카테고리와 매출 금액을 가지고 있다.
각 카테고리 내에서 매출 상위 2위까지의 상품명과 매출을 출력하라.

CREATE TABLE products (
    product_id INT,
    product_name VARCHAR(50),
    category VARCHAR(50),
    revenue INT
);

INSERT INTO products VALUES
(1, 'A', '음료', 3000),
(2, 'B', '음료', 5000),
(3, 'C', '음료', 1000),
(4, 'D', '간식', 6000),
(5, 'E', '간식', 4000),
(6, 'F', '간식', 3000),
(7, 'G', '과일', 2000),
(8, 'H', '과일', 1800);

SELECT
  product_name,
  revenue
FROM(
  SELECT
    product_id,
    product_name,
    category,
    revenue,
    ROW_NUMBER() OVER (
      PARTITION BY category
      ORDER BY revenue DESC
      ) AS rn
  FROM products
  ) AS t
WHERE rn <= 2

 

 

 

✅ 문제 3. 각 고객의 주문 중 가장 비싼 상품과 해당 주문일자 구하기

📋 문제 설명

order_items 테이블에는 고객이 주문한 상품, 가격, 주문일자가 있다.
각 고객이 주문한 상품 중 가장 비싼 상품과 그 주문일자를 구하라.
같은 가격이 있을 경우, 가장 최신 주문일자를 우선한다.

CREATE TABLE order_items (
    user_id INT,
    product_name VARCHAR(50),
    price INT,
    order_date DATE
);

INSERT INTO order_items VALUES
(1, '콜라', 3000, '2023-01-01'),
(1, '사이다', 3000, '2023-01-03'),
(1, '주스', 4000, '2023-01-02'),
(2, '과자', 1500, '2023-01-01'),
(2, '초콜릿', 1500, '2023-01-04'),
(3, '아이스크림', 5000, '2023-01-02');

SELECT
  user_id,
  product_name,
  price,
  order_date
FROM (
  SELECT
    user_id,
    product_name,
    price,
    order_date,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY price DESC, order_date DESC
      ) AS rn
  FROM order_items
  ) AS t
WHERE rn = 1

 

 

 

✅ 문제 4. 리뷰를 2번 이상 작성한 유저의 최근 리뷰 출력

📋 문제 설명

reviews 테이블에서 리뷰를 2번 이상 작성한 유저 중에서
각 유저별로 가장 최근에 쓴 리뷰 내용과 작성일을 출력하라.

CREATE TABLE reviews (
    review_id INT,
    user_id INT,
    review_text VARCHAR(100),
    review_date DATE
);

INSERT INTO reviews VALUES
(1, 101, '좋아요', '2023-01-01'),
(2, 101, '별로에요', '2023-01-05'),
(3, 102, '보통이에요', '2023-01-03'),
(4, 103, '최고에요', '2023-01-04'),
(5, 103, '그냥 그래요', '2023-01-10');

WITH second_time AS (
  SELECT
    review_id,
    user_id,
    review_text,
    review_date,
    COUNT(review_id) OVER (
      PARTITION BY user_id
      ) AS review_count,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY review_date DESC
      ) AS rn
  FROM reviews
  )
  

SELECT
  user_id,
  review_text,
  review_date
FROM second_time
WHERE review_count >= 2
  AND rn = 1

 

 

 

✅ 문제 5. 연속 3일 이상 로그인한 유저 ID 출력

📋 문제 설명

logins 테이블에서 유저별 로그인 기록이 주어진다.
연속으로 3일 이상 로그인한 유저의 ID를 구하라.

CREATE TABLE logins (
    user_id INT,
    login_date DATE
);

INSERT INTO logins VALUES
(1, '2023-01-01'),
(1, '2023-01-02'),
(1, '2023-01-03'),
(1, '2023-01-05'),
(2, '2023-01-01'),
(2, '2023-01-03'),
(2, '2023-01-04'),
(3, '2023-01-10'),
(3, '2023-01-11'),
(3, '2023-01-12'),
(3, '2023-01-13');

SELECT
  l1.user_id
FROM logins l1
LEFT JOIN logins l2
  ON l1.user_id = l2.user_id
LEFT JOIN logins l3
  ON l1.user_id = l3.user_id
WHERE DATE_ADD(l1.login_date, INTERVAL 1 DAY) = l2.login_date
  AND DATE_ADD(l1.login_date, INTERVAL 2 DAY) = l3.login_date
GROUP BY user_id

 

 

---

 

 

깔끔한 5정답은 처음인 것 같다. 아주 좋다. 성장하는 느낌이다.

 

 

5번의 경우 더 좋은 풀이 방법이 있다. 지금 내가 짠 코드는 3일 연속 로그인만 판단할 수 있게 만들었는데 4일 연속, 5일 연속 로그인 까지 편하게 추출하는 방법이 있다.

WITH ranked_logins AS (
  SELECT
    user_id,
    login_date,
    DATE_SUB(login_date, INTERVAL ROW_NUMBER() OVER ( PARTITION BY user_id ORDER BY login_date ) DAY ) AS grp
  FROM logins
),

grouped_logins AS (
  SELECT
    user_id,
    grp,
    COUNT(*) AS streak_days
  FROM ranked_logins
  GROUP BY user_id, grp
)

SELECT 
  user_id
FROM grouped_logins
WHERE streak_days >= 3

 

GPT 클라스 진짜 미쳤다. 윈도우 함수를 다른 함수 안에 집어넣어서 계산을 먼저 하는 것도 가능하다는 것을 깨닫게 되었다.