SQL Problems - Select, Basic Joins, Basic Aggregations

Select

584. Find Customer Referee

• referee_id가 2가 아닌 row의 name을 select한다.

• 문제는 referee_id 에 null value를 포함한 경우에는 select에서 제외된다는 점이다.

• 따라서 null value를 포함할 수 있는 조건 referee_id IS NULL 을 추가해야 한다.

SELECT name FROM Customer WHERE referee_id IS NULL or referee_id != 2

1148. Article Views I

• author_id를 기준으로 unique 한 값만 가져오려면 DISTINCT keyword를 사용해야 한다.

• 출력되는 column의 이름을 변경하려면 AS 절 origin_col AS new_col 을 사용한다.

• 특정 column을 기준으로 정렬하려면 ORDER BY 절을 사용한다.

SELECT DISTINCT author_id AS id FROM Views 
WHERE author_id = viewer_id 
ORDER BY author_id ASC

1683. Invalid Tweets

• column 값의 길이를 구하려면 length 함수를 사용한다.

SELECT tweet_id FROM Tweets WHERE length(content) > 15

Basic Joins

1378. Replace Employee ID With The Unique Identifier

• Employees table을 기준으로 하므로 LEFT JOIN 을 사용해야 한다.

SELECT EmployeeUNI.unique_id, name FROM Employees
LEFT JOIN EmployeeUNI ON Employees.id = EmployeeUNI.id

• JOIN 에 대한 여러가지 방식에 대한 설명은 아래 그림과 링크를 참고한다.

• 출처: https://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins

1581. Customer Who Visited but Did Not Make Any Transactions

• LEFT EXCLUDING JOIN 을 사용해야 한다.
• LEFT EXCLUDING JOIN 은 LEFT JOIN을 사용하되 right table에 항목이 있는 row를 제외해야 하므로 WHERE 절로 Right.key IS NULL 를 추가해야 한다.

• count를 customer_id 기준으로 해야하므로 GROUP BY 절을 사용한다.

SELECT customer_id, COUNT(Visits.visit_id) AS count_no_trans
FROM Visits
LEFT JOIN Transactions ON Visits.visit_id = Transactions.visit_id
WHERE Transactions.visit_id IS NULL
GROUP BY customer_id

197. Rising Temperature

• 이전값은 LAG 함수를 사용하여 참조할 수 있다.
• LAG(column) OVER (ORDER BY order_column)

• 문제는 LAG 함수는 WHERE 절에서 쓸 수 없으므로 sub query를 생성하여 이전값을 참조하는 column을 생성한다.
• sub query 생성 시 반드시 이름을 붙여주어야 한다.

SELECT id FROM (
    SELECT id,
        temperature AS currTemp,
        LAG(temperature) OVER (ORDER BY recordDate) AS prevTemp,
        DATEDIFF(
            recordDate, 
            LAG(recordDate) OVER (ORDER BY recordDate)
        ) AS recordDateDiff
        FROM Weather
) WeatherWithPrev
WHERE currTemp > prevTemp AND recordDateDiff = 1

1661. Average Time of Process per Machine

• activity_type으로 구분되는 시작, 종료 row를 묶어서 처리해야 한다.
• LAG OVER 절에서 PARTITION BY 를 사용하여 machine_id, process_id 를 기준으로 묶어줄 수 있도록 한다.
• activity_type을 기준으로 정렬한다.

• 평균값을 구하되 3자리만 남길 수 있도록 ROUND 함수를 사용한다.

SELECT machine_id, 
    ROUND(AVG(end_time - start_time), 3) as processing_time 
FROM (
    SELECT machine_id, process_id,
        LAG(timestamp) OVER (
            PARTITION BY machine_id, process_id
            ORDER BY activity_type
        ) AS start_time,
        timestamp AS end_time
    FROM Activity
) ActivityPair
GROUP BY machine_id

1280. Students and Examinations

• 해답의 도움을 받았던 문제. 덤으로 JOIN 할 때 테이블 이름을 축약하는 방법을 좀 배웠다.

• 여러 테이블을 JOIN 할 때는 기준을 잘 잡는 것이 중요하다는 것을 보여주는 문제였다.

• 아래와 같은 순서로 접근하면 쉽게 이해될 수 있다.
• 먼저 이 문제는 각 학생 Students x 과목 Subjects 의 조합을 기준으로 참여한 시험의 숫자를 시험 기록 Examinations 에서 세어야 하는 문제이다.
• 따라서 기준은 Students 의 Subjects 조합이 되어야 한다. 이를 구하려면 CROSS JOIN을 사용하여 두 테이블의 row의 조합을 생성해야 한다.
• 이제 이렇게 기준점이 잡혔다면 시험 기록 Examinations을 LEFT JOIN 하여 기준점에 따라 counting을 할 수 있도록 한다.
• student_id, subject_name 마다 counting 할 수 있도록 GROUP BY를 적용한다.
• 정렬이 필요하므로 ORDER BY를 적용한다.

SELECT S.student_id, S.student_name, SUB.subject_name,
  COUNT(E.subject_name) AS attended_exams
FROM Students S
CROSS JOIN Subjects SUB
LEFT JOIN Examinations E ON (
  S.student_id = E.student_id AND
  SUB.subject_name = E.subject_name
)
GROUP BY S.student_id, SUB.subject_name
ORDER BY S.student_id, SUB.subject_name

570. Managers with at Least 5 Direct Reports

• 먼저 managerId를 기준으로 report 숫자 (row 숫자)를 센다.

• 그렇게 만들어진 결과를 기준으로 manager의 이름 name 을 JOIN 하여 가져온다.

• 전체 결과에서 report 숫자, 이름이 없는 경우를 제외한다 (WHERE 절).

SELECT B.name FROM (
    SELECT managerId, COUNT(managerId) as report
    FROM Employee
    GROUP BY managerId
) E
LEFT JOIN Employee B ON E.managerId = B.id
WHERE E.report >= 5 AND B.name IS NOT NULL

1934. Confirmation Rate

• 사용자 ID user_id 를 기준으로 confirmation rate를 구해야 하므로 Signups table이 기준이 되어야 한다.

• Confirmation rate를 구하려면 전체 action 숫자와 confirmed 된 action 숫자를 따로 구해야 한다.
• CASE 문을 사용하여 action column 값이 confirmed 인 것만 값을 1, 나머지는 NULL로 하여 COUNT에서 action = confirmed 인 것만 셀 수 있도록 한다.

• 이렇게 구한 숫자를 user_id 기준으로 JOIN 한다.

• Confirmation rate를 계산하되 값이 NULL 인 경우 0을 돌려줄 수 있도록 COALESCE 함수를 사용한다.
• COALESCE 함수는 인자로 들어온 값 중 NULL이 아닌 첫번째 값을 돌려준다.

• 소수점 2자리로 올림하여 구해야 하므로 ROUND 함수를 이용한다.

# Write your MySQL query statement below
SELECT S.user_id,
    ROUND(COALESCE(C.count / C.total, 0), 2) as confirmation_rate
FROM Signups S
LEFT JOIN (
    SELECT user_id,
        COUNT(CASE action WHEN 'confirmed' THEN 1 ELSE NULL END) as count,
        COUNT(*) as total
    FROM Confirmations
    GROUP BY user_id
) C ON S.user_id = C.user_id

Basic Aggregations

1251. Average Selling Price

• Prices에 기재된 제품 ID product_id 가 기준이 되어야 한다.

• 판매된 제품 기록 UnitsSold 과 제품 ID product_id 뿐 아니라 기간 start_date, end_date, purchase_date도 맞아야 하므로 해당 조건을 모두 포함하여 JOIN 한다.

• 값이 NULL인 경우 처리를 위해 COALESCE 함수를 사용한다.

SELECT
    P.product_id, 
    ROUND(COALESCE(SUM(U.units * P.price) / SUM(U.units), 0), 2) as average_price
FROM Prices P
LEFT JOIN UnitsSold U ON U.product_id = P.product_id AND U.purchase_date >= P.start_date AND U.purchase_date <= P.end_date
GROUP BY P.product_id

1193. Monthly Transactions I

• 특정 조건을 만족하는 거래의 숫자, 거래량을 구하는 문제.

• 레코드의 숫자를 셀 때는 CASE문을 쓰되 조건을 만족할 때는 아무 값 (예: 1) 아닐때는 NULL이 되도록 한다 (ELSE 생략).

• 레코드 값을 합할때는 CASE문을 쓰되 필드 ELSE 0로 조건을 명시해야 한다.
• 그렇지 않으면 조건에 해당되는 레코드가 하나도 없을 경우 값이 NULL이 되는 문제가 있기 때문이다.
• COALESCE를 조합할 수도 있겠지만 직관적이지는 않다.

SELECT 
    DATE_FORMAT(T.trans_date, '%Y-%m') as month,
    T.country,
    COUNT(*) AS trans_count,
    COUNT(CASE WHEN T.state = "approved" THEN 1 END) AS approved_count,
    SUM(T.amount) AS trans_total_amount,
    SUM(CASE WHEN T.state = "approved" THEN T.amount ELSE 0 END) AS approved_total_amount
FROM Transactions AS T
GROUP BY month, T.country

1174. Immediate Food Delivery II

• 특정 조건을 만족하는 레코드를 골라서 값을 요약해야 한다.

• 먼저 여기서는 고객별 첫번째 주문을 골라내야 하므로 customer_id로 그룹핑된 order_date의 최소값을 구해야 한다.

• 이렇게 얻어진 결과를 customer_id, order_date를 기준으로 원래 테이블과 INNER JOIN하여 나머지 연산을 수행하면 된다.

SELECT
    ROUND(
        COUNT(
            CASE WHEN order_date = customer_pref_delivery_date THEN 1 END
        ) * 100 / COUNT(*), 
        2
    ) AS immediate_percentage
FROM Delivery AS D
JOIN (
    SELECT
        customer_id,
        MIN(order_date) AS first_date
    FROM Delivery
    GROUP BY customer_id
) F ON D.customer_id = F.customer_id AND D.order_date = F.first_date

• WHERE IN 절을 사용하여 위의 내용을 좀 더 축약해서 표현 가능하다.
• WHERE (일치해야될 column 목록) IN (서브 쿼리)

SELECT
    ROUND(
        COUNT(
            CASE WHEN order_date = customer_pref_delivery_date THEN 1 END
        ) * 100 / COUNT(*), 
        2
    ) AS immediate_percentage
FROM Delivery AS D
WHERE (customer_id, order_date) IN (
    SELECT
        customer_id,
        MIN(order_date) AS order_date
    FROM Delivery
    GROUP BY customer_id
)

550. Game Play Analysis IV

• 문제의 조건(첫번째 로그인 날짜를 기준으로 연속 2일 로그인한 플레이어)을 제대로 확인할 필요가 있다.

• 각 플레이어 별 첫번째 로그인 날짜를 구하고 player_id를 기준으로 원래 테이블과 INNER JOIN 한다.

• 전체 플레이어의 수, 그리고 조건을 만족하는 플레이어의 수를 센다.

• 문제는 동일한 player_id를 가진 레코드가 여러개 존재하므로 DISTINCT 문을 사용하여 unique한 플레이어의 수를 세야 한다.

• COUNT + CASE를 사용한 경우에도 앞쪽에 DISTINCT를 붙여서 unique 한 값을 셀 수 있다.
• CASE 문 내부에 DISTINCT를 붙이면 제대로 체크가 안된다. 반드시 가장 앞에 붙여야 한다.

SELECT
    ROUND(
        COUNT(
            DISTINCT CASE 
                WHEN DATEDIFF(A.event_date, F.first_date) = 1 
                THEN A.player_id 
            END
        ) /
        COUNT(DISTINCT A.player_id),
        2
    ) as fraction
FROM Activity AS A
JOIN (
    SELECT 
        player_id,
        MIN(event_date) AS first_date
    FROM Activity
    GROUP BY player_id
) F ON A.player_id = F.player_id