문제 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%'
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 |
| 74. 특정 기간동안 대여 가능한 자동차들의 대여비용 구하기 (1) | 2024.12.14 |
| 입양 시각 구하기2 (0) | 2024.12.14 |
