Lv1. 데이터 속 김서방 찾기
- 상황 : 여러분들은 스파르타코딩클럽의 분석가로 취직했습니다. DBeaver를 테스트 해볼 겸 “김”씨로 시작하는 이용자들 수를 세어 보기로 했습니다.
- 문제 : 다음과 같은 결과테이블을 만들어봅시다.
- name_cnt : “김”씨 성을 가지고 있는 교육생의 수
select count(distinct user_id) name_cnt
from users
where name like '김%'
Lv2. 날짜별 획득포인트 조회하기
- 상황 : 이번에는 이용자들이 잘 활동하고 있는지 보고자 합니다. 포인트가 많을수록 활동을 잘하고 있다고 생각할 수 있습니다. 날짜별로 획득한 포인트가 점점 늘어나는지 줄어드는지 확인해 봅시다.
- 문제 : 다음과 같은 결과테이블을 만들어봅시다.
- created_at: 익명화된 유저들의 아이디(varchar255)
- average_points: 유저가 획득한 날짜별 평균 포인트(int), 반올림 필수
select date(created_at) created_at,
round(avg(point)) average_points
from point_users
group by 1
Lv3. 이용자의 포인트 조회하기
- 상황: 이번에는 이용자들 별로 획득한 포인트를 학생들에게 이메일로 보내려고 합니다. 이를 위한 자료를 가공해봅시다. 특히 users 테이블에는 있으나 point_users 에는 없는 유저가 있어요. 이 유저들의 경우 point를 0으로 처리합시다.
- 문제: 다음과 같은 결과 테이블을 만들어봅시다.
- user_id: 익명화된 유저들의 아이디
- email: 유저들의 이메일
- point: 유저가 획득한 포인트
- users 테이블에는 있지만 point_users에는 없는 user는 포인트가 없으므로 0 으로 처리
- 포인트 기준으로 내림차순 정렬
select u.user_id,
u.email,
coalesce(p.point, 0) point
from users u left join point_users p on u.user_id=p.user_id
order by 3 desc
해당 컬럼의 NULL값을 대체할 때 COALESCE 함수를 사용
COALESCE(A, B)
A 컬럼 값이 NULL인 경우 B 값을 리턴
A 컬럼 값이 NULL이 아닌 경우 A 값을 리턴
Lv4. 단골 고객님 찾기
- Orders 테이블
|
OrderID
|
CustomerID
|
OrderDate
|
TotalAmount
|
|
101
|
1
|
2024-01-01
|
150
|
|
102
|
2
|
2024-01-03
|
200
|
|
103
|
1
|
2024-01-04
|
300
|
|
104
|
3
|
2024-01-04
|
50
|
|
105
|
2
|
2024-01-05
|
80
|
|
106
|
4
|
2024-01-06
|
400
|
- Customers 테이블
|
CustomerID
|
CustomerName
|
Country
|
|
1
|
Alice
|
USA
|
|
2
|
Bob
|
UK
|
|
3
|
Charlie
|
USA
|
|
4
|
David
|
Canada
|
요구사항 :
1. 고객별로 주문 건수와 총 주문 금액을 조회하는 SQL 쿼리를 작성해주세요.
- 출력 결과에는 고객 이름, 주문 건수, 총 주문 금액이 포함되어야 합니다. 단, 주문을 한 적이 없는 고객도 결과에 포함되어야 합니다.
select c.CustomerName,
count(*) OrderCount,
sum(TotalAmount) TotalSpent
from orders o left join customers c on o.CustomerID=c.CustomerID
group by 1
2. 나라별로 총 주문 금액이 가장 높은 고객의 이름과 그 고객의 총 주문 금액을 조회하는 SQL 쿼리를 작성해주세요.
select Country,
CustomerName,
TotalSpent Top_Spent
from
(
select Country,
CustomerName,
TotalSpent,
rank() over (partition by Country order by TotalSpent desc) ranking
from
(
select c.Country,
c.CustomerName,
sum(TotalAmount) TotalSpent
from orders o left join customers c on o.CustomerID=c.CustomerID
group by 1, 2
) a
) b
where ranking=1
Lv4. 가장 높은 월급을 받는 직원은?
- Employees 테이블 :
|
EmployeeID
|
Name
|
Department
|
Salary
|
ManagerID
|
|
1
|
Alice
|
HR
|
70000
|
NULL
|
|
2
|
Bob
|
IT
|
90000
|
1
|
|
3
|
Charlie
|
IT
|
80000
|
2
|
|
4
|
David
|
IT
|
85000
|
2
|
|
5
|
Eve
|
HR
|
75000
|
1
|
|
6
|
Frank
|
Finance
|
95000
|
NULL
|
|
7
|
Grace
|
Finance
|
80000
|
6
|
|
8
|
Heidi
|
IT
|
95000
|
2
|
요구사항 :
- 각 직원의 이름, 부서, 월급, 그리고 그 직원이 속한 부서에서 가장 높은 월급을 받고 있는 직원의 이름과 월급을 조회하는 SQL 쿼리를 작성해주세요.
select e.Name,
e.Department,
e.Salary,
b.Name Top_Earner,
b.Salary Top_Salary
from employees e left join
(
select Department,
Name,
Salary,
rn
from
(
select Department,
Name,
Salary,
rank() over (partition by Department order by Salary desc) rn
from employees
) a
where rn=1
) b on e.Department=b.Department
- 부서별로 평균 월급이 가장 높은 부서의 이름과 해당 부서의 평균 월급을 조회하는 SQL 쿼리를 작성해주세요.
select Department,
Avg_Salary
from
(
select Department,
avg(Salary) Avg_Salary,
rank() over (order by avg(Salary) desc) rn
from employees
group by 1
) a
where rn=1
퀘스트 페이지의 기대결과는 row가 하나였는데, rank over 함수값이 1인 부서는 두 곳이라 그냥 같이 출력함