[SQL] 쿼리 테스트 준비 (데이터 분석 직무)

SQL 쿼리 테스트를 준비하는 데 도움이 되도록 문제를 만들어 드리겠습니다. 다음은 빅쿼리를 다루는 회사에서 SQL을 사용할 수 있는 상황과 그에 대한 쿼리 및 해설입니다.

문제 세트 1

상황

인사부에서는 각 부서별 직원 수와 평균 연봉을 알고 싶어합니다. 데이터베이스에는 '직원'과 '부서' 테이블이 있으며, 직원 테이블에는 연봉 정보가 포함되어 있습니다.

쿼리

SELECT
    D.부서명,
    COUNT(E.직원ID) AS 직원수,
    AVG(E.연봉) AS 평균연봉
FROM
    직원 E
JOIN
    부서 D ON E.부서ID = D.부서ID
GROUP BY
    D.부서명;

해설

이 쿼리는 '직원' 테이블(E)과 '부서' 테이블(D)을 부서ID를 기준으로 조인합니다. GROUP BY 절을 사용하여 각 부서별로 결과를 그룹화합니다. COUNT() 함수로 각 부서의 직원 수를, AVG() 함수로 평균 연봉을 계산합니다.

문제 세트 2

상황

재고 관리 팀은 모든 제품의 재고 수량과 평균 구매 가격을 파악하고자 합니다. '제품' 테이블과 '구매' 테이블이 있습니다.

쿼리

SELECT
    P.제품명,
    P.재고수량,
    AVG(B.구매가격) AS 평균구매가격
FROM
    제품 P
LEFT JOIN
    구매 B ON P.제품ID = B.제품ID
GROUP BY
    P.제품명, P.재고수량;

해설

이 쿼리는 '제품' 테이블(P)과 '구매' 테이블(B)을 제품ID로 왼쪽 조인합니다. GROUP BY 절을 사용하여 각 제품명과 재고 수량별로 결과를 그룹화합니다. AVG() 함수는 각 제품의 평균 구매 가격을 계산합니다.

문제 세트 3

상황

영업 부서는 지난 분기 동안 가장 많이 판매된 상위 5개 제품의 목록과 판매량을 알고 싶어합니다.

쿼리

SELECT
    P.제품명,
    SUM(S.판매량) AS 총판매량
FROM
    판매 S
JOIN
    제품 P ON S.제품ID = P.제품ID
WHERE
    S.판매날짜 >= DATE_ADD(CURRENT_DATE(), INTERVAL -3 MONTH)
GROUP BY
    P.제품명
ORDER BY
    SUM(S.판매량) DESC
LIMIT 5;

해설

이 쿼리는 '판매' 테이블(S)과 '제품' 테이블(P)을 제품ID로 조인합니다. WHERE 절은 지난 분기(최근 3개월)의 판매 데이터만을 고려합니다. GROUP BY 절은 제품명별로 결과를 집계하고, ORDER BY 절은 판매량을 기준으로 내림차순 정렬합니다. LIMIT 5는 상위 5개 제품만을 선택합니다.

문제 세트 4

상황

고객 서비스 팀은 지난 해 각 고객이 제출한 평균 불만 사항의 수를 알고 싶어합니다.

쿼리

SELECT
    C.고객ID,
    AVG(C.불만사항수) AS 평균불만사항수
FROM
    고객서비스 C
WHERE
    C.접수날짜 >= DATE_ADD(CURRENT_DATE(), INTERVAL -1 YEAR)
GROUP BY
    C.고객ID;

해설

이 쿼리는 '고객서비스' 테이블(C)에서 데이터를 가져옵니다. WHERE 절은 지난 해의 데이터만을 선택합니다. GROUP BY 절은 각 고객ID별로 결과를 집계하고, AVG() 함수는 각 고객별 평균 불만 사항 수를 계산합니다.

문제 세트 5

상황

재무 팀은 지난 분기 동안 회사의 총 수입과 지출을 알고 싶어합니다. '수입'과 '지출' 테이블이 있습니다.

쿼리

SELECT
    (SELECT SUM(수입액) FROM 수입 WHERE 날짜 >= DATE_ADD(CURRENT_DATE(), INTERVAL -3 MONTH)) AS 총수입,
    (SELECT SUM(지출액) FROM 지출 WHERE 날짜 >= DATE_ADD(CURRENT_DATE(), INTERVAL -3 MONTH)) AS 총지출;

해설

이 쿼리는 두 개의 서브쿼리를 사용합니다. 첫 번째 서브쿼리는 '수입' 테이블에서 지난 분기의 총 수입액을 계산하고, 두 번째 서브쿼리는 '지출' 테이블에서 같은 기간 동안의 총 지출액을 계산합니다. 이

렇게 함으로써, 재무 팀은 지난 분기의 총 수입과 지출을 한 번에 볼 수 있습니다.

문제 세트 6

상황

회사의 데이터베이스에는 '고객', '주문', 그리고 '제품'에 대한 정보가 포함된 테이블들이 있습니다. 마케팅 팀은 최근 1년간 각 제품별 총 주문량과 총 매출을 알고 싶어 합니다. 이 정보는 향후 마케팅 전략 수립에 중요한 역할을 할 것입니다.

쿼리

SELECT
    P.제품명,
    COUNT(O.주문ID) AS 총주문량,
    SUM(O.주문금액) AS 총매출
FROM
    주문 O
JOIN
    제품 P ON O.제품ID = P.제품ID
WHERE
    O.주문날짜 >= DATE_ADD(CURRENT_DATE(), INTERVAL -1 YEAR)
GROUP BY
    P.제품명;

해설

이 쿼리는 '주문' 테이블(O)과 '제품' 테이블(P)을 제품ID를 기준으로 조인합니다. WHERE 절은 최근 1년간의 주문만을 고려합니다(CURRENT_DATE() 함수와 DATE_ADD() 함수를 사용하여 계산). GROUP BY 절은 각 제품명별로 결과를 집계합니다. COUNT() 함수는 각 제품별 주문량을, SUM() 함수는 총 매출을 계산합니다. 결과적으로, 이 쿼리는 각 제품의 이름과 해당 기간 동안의 총 주문량 및 총 매출을 반환합니다.

 

 

 

 

 

문제 세트 1

상황

회사는 가장 인기 있는 출발지와 목적지 조합을 파악하고 싶어합니다. '여행' 테이블에는 각 여행의 출발지, 목적지, 그리고 이용자 수가 기록되어 있습니다.

쿼리

SELECT
    출발지,
    목적지,
    COUNT(이용자ID) AS 이용자수
FROM
    여행
GROUP BY
    출발지, 목적지
ORDER BY
    이용자수 DESC
LIMIT 10;

해설

이 쿼리는 '여행' 테이블에서 출발지와 목적지별로 데이터를 그룹화하고, 각 조합에 대한 이용자 수를 계산합니다. ORDER BY 절은 이용자 수를 기준으로 내림차순 정렬하여 가장 인기 있는 조합을 상위에 위치시킵니다. LIMIT 10은 상위 10개 조합만을 표시합니다.

문제 세트 2

상황

회사는 특정 시간대에 가장 많이 사용되는 서비스 경로를 분석하고자 합니다. '여행' 테이블에는 각 여행의 시작 시간, 출발지, 목적지 정보가 있습니다.

쿼리

SELECT
    HOUR(시작시간) AS 시간대,
    출발지,
    목적지,
    COUNT(*) AS 여행수
FROM
    여행
GROUP BY
    시간대, 출발지, 목적지
ORDER BY
    여행수 DESC;

해설

이 쿼리는 '여행' 테이블에서 시작 시간의 시간대별(시간 단위)로 데이터를 그룹화합니다. 각 시간대, 출발지, 목적지 조합별로 여행 수를 계산하고, 가장 많이 사용된 경로를 상위에 표시합니다.

문제 세트 3

상황

운영 팀은 특정 기간 동안 각 지역별로 출발 및 도착 횟수를 파악하고자 합니다. '여행' 테이블에는 여행 날짜, 출발지, 목적지 정보가 있습니다.

쿼리

SELECT
    지역,
    SUM(CASE WHEN 출발지 = 지역 THEN 1 ELSE 0 END) AS 출발횟수,
    SUM(CASE WHEN 목적지 = 지역 THEN 1 ELSE 0 END) AS 도착횟수
FROM
    여행,
    (SELECT DISTINCT 출발지 AS 지역 FROM 여행 UNION SELECT DISTINCT 목적지 FROM 여행) 지역목록
WHERE
    여행날짜 BETWEEN '2024-01-01' AND '2024-01-31'
GROUP BY
    지역;

해설

이 쿼리는 주어진 기간 동안 각 지역별로 출발 및 도착 횟수를 계산합니다. 지역 목록은 출발지와 목적지의 유니크한 조합으로 생성됩니다. CASE 문을 사용하여 각 여행이 특정 지역에서 출발하거나 도착하는 경우를 세어 출발횟수와 도착횟수를 계산합니다.

문제 세트 4

상황

마케팅 팀은 일주일 중 어느 요일에 여행 서비스의 이용률이 가장 높은지 알고 싶어합니다.

쿼리

SELECT
    DAYOFWEEK(여행날짜) AS 요일,
    COUNT(*) AS 여행수
FROM
    여행
GROUP BY
    요일
ORDER BY
    여행수 DESC;

해설

이 쿼리는 '여행' 테이블에서 각 여행의 날짜를 기준으로 요일별로 그룹화합니다. DAYOFWEEK() 함수를 사용하여 여행 날짜의 요일을 구하고, 각 요일별 여행 횟수를 계산합니다. ORDER BY 절은 가장 많은 여행이 발생한 요일을 상위에 표시합니다.

문제 세트 5

상황

회사는 특정 지역에서 출발하여 다른 특정 지역으로 가는 평균 여행 시간을 알고 싶어합니다. '여행' 테이블에는 출발지, 목적지, 여행 시작 시간, 여행 종료 시간이 기록되어 있습니다.

쿼리

SELECT
    출발지,
    목적지,
    AVG(TIMESTAMPDIFF(MINUTE, 시작시간, 종료시간)) AS 평균여행시간
FROM
    여행
WHERE
    출발지 = '지역A' AND 목적지 = '지역B'
GROUP BY
    출발지, 목적지;

해설

이 쿼리는 '여행' 테이블에서 특정 출발지와 목적지를 가진 여행에 대한 평균 여행 시간을 계산합니다. TIMESTAMPDIFF()

함수를 사용하여 각 여행의 시작 시간과 종료 시간 사이의 차이(분 단위)를 구하고, 이를 평균내어 평균 여행 시간을 도출합니다.