why this query did not pass?


  • 0
    C
    select(
        round (
    ifnull(
    (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
    

  • 1

    Your code may cause math exception if there is no record(0) in this table friend_request.


  • 0
    C

    Thanks for your reply Mr.Bin. The only difference between my code and the editorial solution is the following:

    (select count(*) from (select distinct sender_id, send_to_id from friend_request) as B)

    Would the be the same as "(select count(distinct requester_id,accepter_id) from request_accepted)"?


  • 0

    @crystal.huangjin These are two different tables. But the following two are the same:

    select count(*) from (select distinct sender_id, send_to_id from friend_request) as b ;
    
    select count(distinct sender_id, send_to_id) from friend_request ;
    

    Feel free to try it out yourself and you will learn.


  • 0
    C

    sorry! I meant the two you referred to in your reply. So if those two tables are the same, then my code is essentially the same as the editorial answer. ifnull function handles the case where there is no record in the table friend_request.

    Interestingly, I tried the following code and it was accepted. I only flipped round and ifnull. Still not understanding why.

    Select 
    ( ifnull(
            Round ((select count(distinct requester_id, accepter_id) from request_accepted) /(Select count(distinct sender_id, send_to_id) from friend_request) , 2)
             , 0)
    ) as accept_rate 
    

    @Mr.Bin said in why this query did not pass?:

    @crystal.huangjin These are two different tables. But the following two are the same:

    select count(*) from (select distinct sender_id, send_to_id from friend_request) as b ;
    
    select count(distinct sender_id, send_to_id) from friend_request ;
    

    Feel free to try it out yourself and you will learn.


  • 0

    @crystal.huangjin You're right. The ifnull could handle the exception. After my double check, I noticed your previous code have a syntax error. You can try this one which was accepted after my modification.

    select
        round (
    ifnull(
    (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
        ;
    

  • 0
    C

    @Mr.Bin said in why this query did not pass?:

    @crystal.huangjin You're right. The ifnull could handle the exception. After my double check, I noticed your previous code have a syntax error. You can try this one which was accepted after my modification.

    select
        round (
    ifnull(
    (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
        ;
    

    Ah... I see. My bad! There should not be parentheses after select. Many thanks!


  • 0
    N

    @crystal.huangjin said in why this query did not pass?:

    select(
    round (
    ifnull(
    (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

    did you check your input source?


Log in to reply
 

Looks like your connection to LeetCode Discuss was lost, please wait while we try to reconnect.