[SQL/Classicmodels Database] 심화문제 3,4(고객의 주문 상세 분석,복합 조건 고객 분류)

3. 고객의 주문 상세 분석

문제
2005년에 주문한 고객 중, 주문한 제품 개수(제품 ID 기준)가 가장 많은 고객의 고객 이름과 주문 개수를 출력하시오.
사용 테이블
customers
orders
orderdetails
출력 컬럼
customerName, total_products

select customerName, sum(quantityOrdered) as total_products
from customers a inner join orders b on a.customerNumber = b.customerNumber
inner join orderdetails c on b.orderNumber = c.orderNumber
where year(orderDate) in (2005)
group by b.customerNumber 
order by sum(quantityOrdered) desc limit 1;

4. 복합 조건 고객 분류

문제
총 구매 금액이 상위 10%에 속하는 고객 중, 총 구매 금액이 10,000달러 이상이고 2003년 이후 첫 주문을 한 고객의 고객 이름과 총 구매 금액을 출력하시오.
사용 테이블
customers
orders
orderdetails
출력 컬럼
customerName, total_spent

-- 총 구매 금액이 상위 10%에 속하는 고객 분류
with cte_1 as (
select customerNumber
from
(
select customerNumber , percent_rank() over (order by total_revenue) as rank_revenue
from
(
select b.customerNumber, sum(quantityOrdered*priceEach) as total_revenue 
from customers a inner join orders b on a.customerNumber = b.customerNumber
inner join orderdetails c on b.orderNumber = c.orderNumber
group by b.customerNumber
) t
) t2
where rank_revenue >=0.9)

-- 해당 고객 대상 內 총 구매 금액이 10,000달러 이상이고 2003년 이후 첫 주문을 한 고객의 고객이름과 구매 금액 출력
select customerName,sum(quantityOrdered * priceEach) as total_spent
from customers a inner join orders b on a.customerNumber = b.customerNumber
inner join orderdetails c on b.orderNumber = c.orderNumber 
inner join cte_1 d on a.customerNumber = d.customerNumber

group by a.customerNumber
having min(year(orderDate) ) >=2003 and sum(quantityOrdered * priceEach) >= 10000;
;

5. 연속된 주문 기간 식별

아직 풀지 못한 문제..sql 답만 공유한다 나중에는 이 문제 꼭 풀어봐야지!

문제
가장 긴 연속 주문 기간(날짜 간격이 1일씩 유지되는 기간)을 식별하고, 그 기간의 시작 날짜와 종료 날짜를 출력하시오.
사용 테이블
orders
출력 컬럼
start_date, end_date, days_length
SQL 정답

WITH date_diff_cte AS (
    SELECT
        orderDate,
        ROW_NUMBER() OVER (ORDER BY orderDate) AS row_num
    FROM orders
),
date_groups AS (
    SELECT
        orderDate,
        DATE_SUB(orderDate, INTERVAL row_num DAY) AS group_id
    FROM date_diff_cte
),
grouped_orders AS (
    SELECT
        group_id,
        MIN(orderDate) AS start_date,
        MAX(orderDate) AS end_date,
        DATEDIFF(MAX(orderDate), MIN(orderDate)) + 1 AS days_length
    FROM date_groups
    GROUP BY group_id
)
SELECT 
    start_date, 
    end_date, 
    days_length
FROM grouped_orders
ORDER BY days_length DESC
LIMIT 1;