Problem Description –
Write an SQL query to find the overall acceptance rate of requests, which is the number of acceptance divided by the number of requests. Return the answer rounded to 2 decimals places.
Note That –
- The accepted requests are not necessarily from the table friend_request. In this case, Count the total accepted requests (no matter whether they are in the original requests), and divide it by the number of requests to get the acceptance rate.
- It is possible that a sender sends multiple requests to the same receiver, and a request could be accepted more than once. In this case, the ‘duplicated’ requests or acceptances are only counted once.
- If there are no requests at all, you should return 0.00 as the accept_rate.
The query result format is in the following example.
Follow up:
- Could you write a query to return the acceptance rate for every month?
- Could you write a query to return the cumulative acceptance rate for every day?
문제 풀이
sender가 동일한 수신자에게 여러번 요청을 보낼 수 있고, 요청이 두 번 이상 수락될 수 있기 때문에 DISTINCT하게 sender_id, send_to_id를 COUNT해야함 -- requester/accepter도 동일
소수점 이하 두자리 까지 반올림 -> round,2
request가 전혀 없으면 accept_rate를 0으로 반환 -> ifnull
SELECT(
IFNULL(
ROUND(
(SELECT COUNT(DISTINCT requester_id, accepter_id) FROM RequestAccepted)/
(SELECT COUNT(DISTINCT sender_id, send_to_id) FROM FriendRequest)
,2)
,0)
) AS accept_rate
'SQL > LeetCode' 카테고리의 다른 글
[1141_EASY] User Activity for the Past 30 Days I (0) | 2022.12.08 |
---|---|
[603#_EASY]Consecutive Available Seats (0) | 2022.10.29 |
[596_EASY] Classes More Than 5 Students (0) | 2022.10.04 |
[595_EASY] Big Countries (0) | 2022.10.03 |
[586_EASY] Customer Placing the Largest Number of Orders (0) | 2022.10.03 |