[SQL] LeetCode Quest - Database(SQL Basic Query Workstation)2

Database - SQL Basic Query Workstation

오늘은 Filtering & Aggregation Operation Cabin 단계를 진행했다!

어매이징~

---

Q1. Customer Placing the Largest Number of Orders

문제 핵심

고객별 주문 개수를 구하고, 가장 많이 주문한 고객을 찾는 문제.

풀이

SELECT customer_number
FROM Orders
GROUP BY customer_number
ORDER BY COUNT(*) DESC
LIMIT 1;

핵심 개념

1) GROUP BY

고객별로 그룹 생성

예:

customer_number
1
1
2
3
3
3

↓

1 → 2개 주문
2 → 1개 주문
3 → 3개 주문

 

2) ORDER BY COUNT(*) DESC

그룹별 주문 개수를 내림차순 정렬

ORDER BY COUNT(*) DESC

결과:

3명
2명
1명

3) LIMIT 1

가장 위 데이터 하나만 반환

LIMIT 1

---

Q2. Classes With at Least 5 Students

문제 핵심

학생이 5명 이상 있는 class 찾기

풀이

SELECT class
FROM Courses
GROUP BY class
HAVING COUNT(class) >= 5;

 

핵심 개념

WHERE

그룹핑 전 데이터 필터

WHERE 조건

예:

WHERE score >= 80

 

HAVING

GROUP BY 이후 그룹 결과 필터

HAVING COUNT(*) >= 5

흐름:

전체 데이터
    ↓
GROUP BY class
    ↓
COUNT 계산
    ↓
HAVING 조건 적용

---

Q3. Monthly Transactions I

문제 핵심

월별 + 국가별 거래 통계 계산

구해야 하는 값:

• 전체 거래 수
• 승인 거래 수
• 전체 거래 금액
• 승인 거래 금액

풀이

SELECT 
    DATE_FORMAT(trans_date, '%Y-%m') AS `month`,
    country,
    COUNT(*) AS trans_count,
    SUM(
        CASE 
            WHEN state = 'approved' THEN 1 
            ELSE 0 
        END
    ) AS approved_count,
    SUM(amount) AS trans_total_amount,
    SUM(
        CASE 
            WHEN state = 'approved' THEN amount
            ELSE 0
        END
    ) AS approved_total_amount
FROM Transactions
GROUP BY
    DATE_FORMAT(trans_date, '%Y-%m'),
    country
ORDER BY month, country;

 

핵심 개념

1) DATE_FORMAT()

날짜 형식 변환

예:

2020-02-16

↓

DATE_FORMAT(date, '%Y-%m')

결과:

2020-02

월별 그룹핑할 때 사용

2) CASE WHEN

조건에 따라 값 변경

기본 문법:

CASE
    WHEN 조건 THEN 값
    ELSE 값
END

예:

CASE 
    WHEN state='approved' THEN 1
    ELSE 0
END

결과:

state결과

approved	1
declined	0

승인 건수 계산

SUM(
    CASE WHEN state='approved'
    THEN 1
    ELSE 0
    END
)

결과:

approved 개수

 

승인 금액 계산

SUM(
    CASE WHEN state='approved'
    THEN amount
    ELSE 0
    END
)

결과:

승인된 amount 합계

---

Q4. User Activity for the Past 30 Days I

문제 핵심

최근 30일 동안 날짜별 활성 사용자 수 구하기

조건:

• 2019-07-27 포함
• 이전 30일

기간:

2019-06-28 ~ 2019-07-27

 

풀이

SELECT
    activity_date AS day,
    COUNT(DISTINCT user_id) AS active_users
FROM Activity
WHERE activity_date 
BETWEEN '2019-06-28' 
AND '2019-07-27'
GROUP BY activity_date;

 

핵심 개념

DISTINCT

중복 제거

예:

user_id

1
1
1
2

COUNT(*):

4

COUNT(DISTINCT user_id):

2

 

Active User의 의미

activity 개수를 세는 것이 아님.

예:

2020-01-01

user 1
open_session

user 1
scroll_down

user 2
send_message

활성 사용자:

2명

따라서:

COUNT(DISTINCT user_id)

 

---

Review Quiz

Q1. List the Products Ordered in a Period

문제 핵심

2020년 2월에 주문된 상품 중

총 주문 수량(unit)이 100 이상인 상품 찾기

---

풀이

SELECT 
    p.product_name,
    SUM(o.unit) AS unit
FROM Products p
JOIN Orders o
ON p.product_id = o.product_id
WHERE DATE_FORMAT(order_date, '%Y-%m') = '2020-02'
GROUP BY p.product_id
HAVING SUM(o.unit) >= 100;

---

핵심 개념

JOIN

서로 다른 테이블 연결

ON p.product_id = o.product_id

상품 정보:

Products
product_id
product_name

주문 정보:

Orders
product_id
unit

같은 product_id끼리 연결

---

GROUP BY + SUM

상품별 총 주문량 계산

예:

Apple
10개
20개
80개

↓

SUM(unit)=110

---

HAVING

집계 결과 조건

잘못된 예:

WHERE SUM(unit)>=100

불가능

이유:

SUM은 GROUP BY 이후 계산되기 때문

정답:

HAVING SUM(unit)>=100

---

SQL 문제 풀이 핵심 정리

행 필터링	WHERE
그룹 생성	GROUP BY
그룹 결과 필터링	HAVING
개수 세기	COUNT
중복 제거	DISTINCT
조건별 계산	CASE WHEN THEN END
날짜 변환	DATE_FORMAT
다른 테이블 연결	JOIN
집계 결과 계산	SUM, AVG

---

두번째 단계도 완료했다~

3단계로 돌아오겠음!