[19주차_SQL] SQL 풀이 모음

[icegosimperson]

추천 문제와 전 주의 본인 순번 전 사람이 풀었던 문제입니다.

고다혜:
1084. Sales Analysis III

배수빈:
1174. Immediate Food Delivery II

백제완:
602. Friend Requests II: Who Has the Most Friends

이지영:
1193. Monthly Transactions I

이혜원:
185. Department Top Three Salaries

## 문제: [문제명](링크)


### 설명


### 코드


### 아이디어

[icegosimperson]

문제: Department Top Three Salaries

설명

• 부서 상위 3개 급여 구하는 문제

코드

SELECT b.name AS Department, a.name AS Employee, a.salary AS Salary
FROM EMPLOYEE a
JOIN DEPARTMENT b
ON a.departmentId = b.id
WHERE 3 >
(
    SELECT COUNT(DISTINCT c.salary)
    FROM EMPLOYEE c
    WHERE c.salary > a.salary AND a.departmentId = c.departmentId
)

아이디어

• 저번에는 DENSE_RANK로 풀었어서 이번에는 서브쿼리로 풀기
• 서브쿼리로 부서별 상위 3명을 추출
  • 서브쿼리(c)의 employee > 메인 쿼리(a)의 employee && 동일한 부서
  • DISTINCT로 중복 제거
  • 부서별로 상위 3위(서브 쿼리 결과 0(1위), 1(2위), 2(3위)) 급여 반환

[KodaHye]

문제: Sales Analysis III

설명

• Product(product_id, product_name, unit_price)
• Sales(seller_id, product_id, buyer_id, sales_date, quantity, price)
• 2019년 1분기에만 팔린 물건의 정보 출력

코드

SELECT a.product_id, b.product_name
FROM SALES a LEFT JOIN product b
ON a.product_id = b.product_id
GROUP BY a.product_id
having MIN(QUARTER(sale_date)) = 1 
        and MAX(QUARTER(sale_date)) = 1 
        and MIN(YEAR(sale_date)) = 2019 
        and MAX(YEAR(sale_date)) = 2019

아이디어

• HAVING 절을 통해 집계합수를 사용함
• MIN(YEAR(sales_date), MAX(YEAR(sales_date)
  • 2019년에만 팔린 상품인지 확인
• MIN(QUARTER(판매날짜)), MAX(QUARTER(판매날짜))
  • 1분기에만 팔린 상품인지 확인

[baexxbin]

문제: 1174. Immediate Food Delivery II

설명

• 고객이 선호하는 배송 날짜가 주문 날짜와 같으면 주문은 즉시 주문이라고 불리며, 그렇지 않으면 예약된 주문이라고 합니다.
• 고객의 첫 번째 주문은 고객이 가장 빠른 주문일을 가진 주문입니다. 고객이 정확히 하나의 첫 주문을 한다는 것이 보장됩니다.
• 모든 고객의 첫 주문에서 즉시 주문 비율을 소수점 이하 2자리까지 반올림하여 찾는 솔루션을 작성하세요

코드

SELECT ROUND(AVG(order_date = customer_pref_delivery_date)*100,2) as immediate_percentage
FROM Delivery
WHERE (customer_id, order_date) in (
    SELECT customer_id, min(order_date)
    FROM Delivery
    GROUP BY customer_id
)

아이디어

• 처음 아이디어
  • 고객별로 (GROUP BY) 제일 첫 주문을 구해(ORDER BY) 첫 주문과 선호 배송날짜가 같은 비율을 확인
    • GROUP BY와 ORDER BY를 사용하면 전체 비율이 아닌 각 고객별로 값이 나옴
    • AVG 시 전체 데이터에 대한 값이 아님
• WHERE절을 통해 (customer_id, order_date) 값을 가져옴
  • 이때 첫 주문은 MIN(order_date)을 통해 가져옴
  • 해당 값들에 대해 첫 주문과 선호 배송날짜가 같은 비율 계산

[yeongleej]

문제: 1193. Monthly Transactions I

설명

• Transactions 테이블에서
• 월별, 나라별
• 거래 총 수와, 승인된 거래 수,
• 총 거래 금액, 승인된 거래 금액을 구하기

코드

SELECT DATE_FORMAT(trans_date, "%Y-%m") AS month, country
        , COUNT(*) AS trans_count
        , COUNT(IF(state = 'approved', state, NULL)) AS approved_count
        , SUM(amount) AS trans_total_amount
        , SUM(IF(state = 'approved', amount, 0)) AS approved_total_amount
FROM TRANSACTIONS
GROUP BY YEAR(trans_date), MONTH(trans_date), country

아이디어

• 그룹 묶기 (GROUP BY)
  1. 월별 통계이지만, 년도별로도 달라야 하기 때문에 -> Year도 추가
  2. 나라별로 묶기
     => GROUP BY YEAR(), MONTH(), country
• 승인된(approved) 거래 수와 거래 금액 구하기
  • COUNT()함수와 SUM()함수 내부에 IF()함수를 통해서 state='approved'인 거래만 카운트 or 계산하기
  • **다른 솔루션)
    • COUNT() or SUM() 함수안에 IF문을 사용하지 않고 조건식 state='approved'만 사용해도 결과 개수를 카운트할 수 있다
    • 단, 거래 금액 구하기에서 SUM()은 행 개수로 카운트되기 때문에 거래 금액인 amount를 곱해줘야 함

    , SUM(state = 'approved') AS approved_count
    , SUM((state = 'approved') * amount) AS approved_total_amount

[jewan100]

문제: 602. Friend Requests II: Who Has the Most Friends

설명

• 친구가 가장 많이 있는 친구와, 몇 명의 친구가 있는 지를 출력
• 결과는 단 1개라고 보장

코드

WITH TOTAL AS (
    SELECT
        requester_id `id`
    FROM
        RequestAccepted
    UNION ALL
    SELECT
        accepter_id `id`
    FROM
        RequestAccepted
)

SELECT
    id
    , COUNT(id) as num    
FROM 
    TOTAL
GROUP BY
    id
ORDER BY
    COUNT(id) DESC
LIMIT 1

아이디어

• requester_id , accepter_id에 가장 많이 등장한 번호와 해당 번호가 몇 번 등장했는지를 출력
• UNION ALL : 중복을 포함해서 결합
  • 가장 많이 등장한 번호를 찾아야하기 때문에 중복이 허락되어야 함!