240708(+33) | leetcode 1934,1193

Leetcode 1934

Confirmation Rate

TABLE Definition

• 테이블에서 user_id와 time_stamp의 조합은 유일(Primary Key)합니다.
• user_id는 Signups 테이블의 사용자 ID를 참조(Foreign Key)합니다.
• action 열은 'confirmed' 또는 'timeout' 값을 가집니다.
• 각 행은 사용자가 특정 시간에 확인 메시지를 요청했고, 그 메시지가 확인되었거나(confirmed), 만료되었음을(timeout) 나타냅니다.

Description

사용자의 확인율은 'confirmed' 메시지의 개수를 요청된 확인 메시지의 총 개수로 나눈 값입니다. 확인 메시지를 요청하지 않은 사용자의 확인율은 0으로 간주합니다. 확인율은 소수점 둘째 자리까지 반올림합니다.

Solution

1. 사용자 확인율
= 시스템 confirmed 메시지 수 / 시스템에 요청된 전체 메시지 수

CASE
    WHEN SUM(CASE WHEN c.action IN ('confirmed', 'timeout') THEN 1 ELSE 0 END) = 0 THEN 0
    ELSE SUM(CASE WHEN c.action = 'confirmed' THEN 1 ELSE 0 END) /
    SUM(CASE WHEN c.action IN ('confirmed', 'timeout') THEN 1 ELSE 0 END)
END

• when~then: 둘 중 하나만 있어도 1, action에 'confirmed'나 'timeout' 상태의 메시지가 하나도 없는 경우를 0을 반환 (null 값 처리)

AVG(IF(c.action = ‘confirmed’, 1, 0)

• 각 사용자별로 'confirmed' 메시지의 비율을 계산하는데, 이 비율은 해당 사용자의 총 메시지 수에 대한 'confirmed' 메시지의 비율과 같다.
• c.action이 NULL인 경우에도 0을 반환한다.
• AVG 함수는 NULL 값을 무시하고 평균을 계산한다.
• 메시지가 없는 경우에 위 과정을 걸쳐 0으로 처리된다.

2. 전체 코드

SELECT
    s.user_id,
    ROUND(AVG(IF(c.action = 'confirmed', 1, 0)), 2) #소숫점 둘째까지 반올림
    AS confirmation_rate
FROM
    Signups s
LEFT JOIN
    Confirmations c
ON
    s.user_id = c.user_id
GROUP BY
    s.user_id;

Leetcode 1193

Monthly Transactions I

TABLE Definition

Description

각 달(month)과 국가(country)별로 다음을 찾는 SQL 쿼리를 작성하세요

• 총 거래 수 (number of transactions)
• 총 거래 금액 (total amount)
• 승인된 거래 수 (number of approved transactions)
• 승인된 거래 총 금액 (total approved amount)
• 결과 테이블은 임의의 순서로 반환될 수 있습니다.

Solution

select DATE_FORMAT(trans_date , '%Y-%m') AS month,
       country,
       COUNT(*) as trans_count,
       COUNT(CASE WHEN state = 'approved' THEN 1 END) AS approved_count,
       #또는 SUM(IF(state = 'approved', 1, 0) AS approved_count
       SUM(amount) AS trans_total_amount,
       sum(CASE WHEN state = 'approved' THEN amount ELSE 0 END) as approved_total_amount
from transactions
group by 1,2

COUNT 함수는 NULL 값을 무시하지만, 0은 무시하지 않고 센다.

소감

문제를 완벽히 이해하면서 풀지는 못했다. 해답을 참고하면서 풀었다. 그치만 혼자서 머리 끙끙 싸매고 풀었다면, 시간이 쫌 걸렸을 것 같다. 오히려 실제 코테같은 느낌이 들었고, 아직 많이 부족하구나, 이렇게 풀이할 수 있구나를 빠른 시간내에 채득할 수 있어 좋았다. 👍