Click here to see the full article post
I tried the query in Sqlite, it returns 0. Not how MySQL handle a integer divide by integer.
THERE IS NO NEED TO USE 5 SELECT ,
(SELECT COUNT(DISTINCT requester_id, accepter_id) FROM request_accepted) /
(SELECT COUNT(DISTINCT sender_id, send_to_id) FROM friend_request )
,0) ,2) AS accept_rate
In fact, we can go with only one select, but with join, that might be less effective:
select IFNULL( ROUND (count(distinct requester_id, accepter_id) / count(distinct sender_id, send_to_id), 2), 0.00) as accept_rate
from friend_request JOIN request_accepted;
IFNULL(Round(count(Distinct concat(requester_id,accepter_id))/count(Distinct concat(sender_id,send_to_id)), 2),0) AS accept_rate
SELECT IFNULL(ROUND( ( SELECT count(*) FROM ( SELECT distinct requester_id, accepter_id FROM request_accepted ) accept_request ) / ( SELECT count(*) FROM ( SELECT distinct sender_id, send_to_id FROM friend_request ) request_sent ), 2), 0.00) as accept_rate
accepted with 512 ms
select if( accepter = 0, 0.00,round((accepter/sender),2)) as accept_rate
(select count(distinct sender_id, send_to_id) as sender from friend_request) a
(select count(distinct requester_id, accepter_id) as accepter from request_accepted ) b
I have one question, if we don't use join, we gonna get invalid results. For example, if there are no data in friend_request table, in other words, no one initiated the request, but there is still an acceptance in request_accepted table － Data inconsistency. So for my perspective, We should use the left join to resolve this.
Looks like your connection to LeetCode Discuss was lost, please wait while we try to reconnect.