mysql - LeetCode 不接受 SQL 解决方案

Question

我为 LeetCode 问题编写了以下解决方案:

SELECT ROUND(1.0*sum(t1.accepted_requests)/sum(t2.total_requests), 2) AS accept_rate
FROM (SELECT 'cool' AS common, ra.accepter_id, COUNT(DISTINCT ra.requester_id) AS accepted_requests
FROM request_accepted ra 
GROUP BY 1, 2) AS t1
JOIN
(SELECT 'cool' AS common, fr.send_to_id, COUNT(DISTINCT fr.sender_id) AS total_requests
FROM friend_request fr
GROUP BY 1, 2) AS t2
ON t1.common = t2.common

我运行了查询，输出与 LeetCode 发布的预期答案相同。然而，LeetCode 的算法说我的查询是错误的，我不明白为什么。

LeetCode问题描述如下:

In social network like Facebook or Twitter, people send friend requests and accept others’ requests as well. Now given two tables as below:

Write a query to find the overall acceptance rate of requests rounded to 2 decimals, which is the number of acceptance divide the number of requests.

Note:

The accepted requests are not necessarily from the table friend_request. In this case, you just need to simply 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 is no requests at all, you should return 0.00 as the accept_rate.

Explanation: There are 4 unique accepted requests, and there are 5 requests in total. So the rate is 0.80.

Answer 1

There are 4 unique accepted requests, and there are 5 requests in total. So the rate is 0.80.

我想这个查询应该可以做到:

SELECT
    ROUND(COALESCE(acc.cnt/NULLIF(req.cnt, 0), 0), 2)
FROM 
    (SELECT COUNT(*) cnt FROM friends_request) req,
    (SELECT COUNT(DISTINCT requester_id, accepter_id) cnt FROM request_accepted) acc

该查询所做的只是使用两个子查询来计算在外部查询中执行的除法的两个部分:

• req 计算分母，即请求总数
• acc计算分子，即不同接受请求的计数

我在 this db fiddle 中测试过它并且它正确地为您的测试数据返回 0.80。