코딩 테스트/SQL

QCC 3회차 풀이

susinlee 2025. 1. 12. 15:38

문제 1 : 첫 주문 고객 연도별 매출 조회

“2011년 12월”에 첫 주문을 한 고객들의 연도별 매출을 조회하는 SQL 문을 작성하세요. 고객 이름은 이름과 성을 결합하여 생성합니다. 결과는 고객 ID(customerid)기준 오름차순, 연도(year) 기준 오름차순으로 정렬합니다.'

 

[풀이]

1. 11년 12월에 첫 주문한 고객을 필터링해야 하므로 sales_order 테이블에서 고객별로 그룹화 한 뒤 MIN 함수를 통해 첫 주문을 계산하고, 계산한 값이 11년 12월인 고객만 필터링한다.

SELECT cutomerid
FROM sales_order
GROUP BY customerid
HAVING MIN(orderdate) LIKE '2011-12%'

11년 12월에 첫 주문을 한 고객들

 

2. 위 쿼리를 서브쿼리로 WHERE 절로 보내, 11년 12월 첫주문한 고객들을 customer 테이블에서 필터링해주고,

customerid 와 firstname과 lastname을 이어붙인 customer_name을 출력해준다.

SELECT 
    customerid
    , CONCAT_WS(' ', firstname, lastname) AS customer_name
FROM customer
WHERE customerid in (SELECT customerid
                     FROM sales_order
                     GROUP BY customerid
                     HAVING MIN(orderdate) LIKE '2011-12%')

 

 

위 테이블에 속해있는 고객들의 연도별 매출을 구해야하므로, sales_order 테이블과 customerid를 기준으로 JOIN한다.

그 후 고객과 연도별로 그룹화 한뒤(GROUP BY customerid, year), 매출(unitprice * orderqty의 합계)을 구해준다. 정렬 기준에 맞게 정렬해주면 끝.

 

SELECT
    sub.customerid
    , sub.customer_name
    , YEAR(s.orderdate) AS year
    , SUM(s.unitprice * s.orderqty) AS gmv
FROM sales_order s
JOIN
(
    SELECT
        customerid
        , CONCAT_WS(' ', firstname, lastname) AS customer_name
    FROM customer
    WHERE customerid IN (SELECT customerid
                         FROM sales_order
                         GROUP BY customerid
                         HAVING MIN(orderdate) LIKE '2011-12%')
) sub
ON s.customerid = sub.customerid
GROUP BY customerid, year
ORDER BY customerid, year

 

 

 

문제 2 : 고객별 연평균 총 거래액 집계

2011년 12월에 첫 주문을 한 각 고객의 첫 주문과 마지막 주문 사이의 기간을 기준으로, 해당 기간 동안의 **연평균 총거래액(GMV)**을 계산합니다. 소수점 2째자리까지 계산해주세요.

결과는 고객 ID(customerid) 기준으로 오름차순으로 정렬합니다.

 

[풀이]

1. 앞서 구한 테이블을 CTE 테이블로 보내주고, 해당 테이블에서 고객별로 마지막주문과 첫주문의 연도 차이를 계산한 후 1을 더해 고객별로 기간을 나타내는 테이블을 하나 더 만들어준다.

WITH t1 AS (
    SELECT
        sub.customerid
        , sub.customer_name
        , YEAR(s.orderdate) AS year
        , SUM(s.unitprice * s.orderqty) AS gmv
    FROM sales_order s
    JOIN
    (
        SELECT
            customerid
            , CONCAT_WS(' ', firstname, lastname) AS customer_name
        FROM customer
        WHERE customerid IN (SELECT customerid
                             FROM sales_order
                             GROUP BY customerid
                             HAVING MIN(orderdate) LIKE '2011-12%')
    ) sub
    ON s.customerid = sub.customerid
    GROUP BY customerid, year
    ORDER BY customerid, year
)
SELECT
    customerid
    , MAX(year)-MIN(year)+1 AS period
FROM t1
GROUP BY customerid

 

 

2. 만들어진 테이블도 CTE로 보내버리고, 앞서 만든 테이블과 customerid를 키로 JOIN해준 뒤 고객별로 그룹화하여 gmv의 합계를 period으로 나누어준다. 그런다음 정렬기준에 맞게 정렬하면 끝

WITH t1 AS (
    SELECT
        sub.customerid
        , sub.customer_name
        , YEAR(s.orderdate) AS year
        , SUM(s.unitprice * s.orderqty) AS gmv
    FROM sales_order s
    JOIN
    (
        SELECT
            customerid
            , CONCAT_WS(' ', firstname, lastname) AS customer_name
        FROM customer
        WHERE customerid IN (SELECT customerid
                             FROM sales_order
                             GROUP BY customerid
                             HAVING MIN(orderdate) LIKE '2011-12%')
    ) sub
    ON s.customerid = sub.customerid
    GROUP BY customerid, year
    ORDER BY customerid, year
),
t2 AS (
    SELECT
        customerid
        , MAX(year)-MIN(year)+1 AS period
    FROM t1
    GROUP BY customerid
)
SELECT
    t1.customerid
    , t1.customer_name
    , ROUND(SUM(gmv)/period, 2) AS avg_yearly_gmv
FROM t1
JOIN t2
	ON t1.customerid = t2.customerid
GROUP BY customerid
ORDER BY customerid

 

 

문제 3 :고객별 최대 주문 수량 연도와 주문 수량 집계

2011년 12월에 첫 주문을 한 고객 대상으로, 고객별 각 연도에서의 주문 수량을 집계하고, 최대 주문 수량과 그 최대 주문 수량을 발생시킨 연도를 찾습니다.

최대 주문 수량이 같은 연도가 있다면, 가장 최근 연도를 출력합니다.

결과는 고객 ID(customerid) 기준으로 오름차순으로 정렬합니다.

 

[풀이]

1. 2011년 12월 첫주문 고객테이블과 sales_order 테이블을 병합해주고, 연도를 구해준 뒤, 고객과 연도별로 GROUP BY 해주고, orderqty를 SUM 해준다.

SELECT 
    sub.customerid
    , sub.customer_name
    , YEAR(s.orderdate) AS year
    , SUM(s.orderqty) AS total_orderqty
FROM sales_order s
JOIN
(
    SELECT
        customerid
        , CONCAT_WS(' ', firstname, lastname) AS customer_name
    FROM customer
    WHERE customerid IN (SELECT customerid
                         FROM sales_order
                         GROUP BY customerid
                         HAVING MIN(orderdate) LIKE '2011-12%')
) sub
ON s.customerid = sub.customerid
GROUP BY customerid, year
ORDER BY customerid, year

 

2. 위 테이블을 CTE로 보낸 뒤 해당 테이블에서 고객별로 파티션을 나눈 총주문수량을 내림차순으로 해서 순위를 매겨준다. 연도별로 주문수량을 같을 때는 가장 최근 연도를 출력해야하므로 총주문수량으로 내림차순 한 뒤 연도별로도 내림차순을 적용해준다.

WITH t1 AS (
    SELECT 
        sub.customerid
        , sub.customer_name
        , YEAR(s.orderdate) AS year
        , SUM(s.orderqty) AS total_orderqty
    FROM sales_order s
    JOIN
    (
        SELECT
            customerid
            , CONCAT_WS(' ', firstname, lastname) AS customer_name
        FROM customer
        WHERE customerid IN (SELECT customerid
                             FROM sales_order
                             GROUP BY customerid
                             HAVING MIN(orderdate) LIKE '2011-12%')
    ) sub
    ON s.customerid = sub.customerid
    GROUP BY customerid, year
) 
SELECT 
	*
    , RANK() OVER(PARTITION BY customer_id ORDER BY total_orderqty DESC, year DESC) AS qty_rank
FROM t1

 

3. 마지막으로 위 테이블에서 qty_rank가 1인 행을 필터링하고 customerid로 오름차순 정렬해준다.

WITH t1 AS (
    SELECT 
        sub.customerid
        , sub.customer_name
        , YEAR(s.orderdate) AS year
        , SUM(s.orderqty) AS total_orderqty
    FROM sales_order s
    JOIN
    (
        SELECT
            customerid
            , CONCAT_WS(' ', firstname, lastname) AS customer_name
        FROM customer
        WHERE customerid IN (SELECT customerid
                             FROM sales_order
                             GROUP BY customerid
                             HAVING MIN(orderdate) LIKE '2011-12%')
    ) sub
    ON s.customerid = sub.customerid
    GROUP BY customerid, year
), 
t2 AS (
SELECT 
	*
    , RANK() OVER(PARTITION BY customerid ORDER BY total_orderqty DESC, year DESC) AS qty_rank
FROM t1
)
SELECT
    customerid
    , customer_name
    , year AS max_qty_year
    , total_orderqty AS max_total_qty
FROM t2
WHERE qty_rank = 1
ORDER BY customerid

'코딩 테스트 > SQL' 카테고리의 다른 글

이빠진 데이터를 채워넣어보자 (재귀 테이블, 윈도우 함수)  (0) 2024.12.14
상품을 구매한 회원 비율 구하기  (1) 2024.12.14
자동차 대여 기록 별 대여 금액 구하기  (1) 2024.12.14
76. 특정 기간동안 대여 가능한 자동차들의 대여비용 구하기  (1) 2024.12.14
입양 시각 구하기2  (0) 2024.12.14

'코딩 테스트/SQL'의 다른글

  • 현재글QCC 3회차 풀이

관련글

티스토리툴바