Following up questions. Solved Q1 how to solve Q2?


  • 1
    S

    Following up question 1. But I don't know how to solve Q2. do we need some function to do it cumulatively?

    select if(d.req =0, 0.00, round(c.acp/d.req,2)) as accept_rate, c.month from 
    (select count(distinct requester_id, accepter_id) as acp, Month(accept_date) as month from request_accepted) c, 
    (select count(distinct sender_id, send_to_id) as req, Month(request_date) as month from friend_request) d 
    where c.month = d.month 
    group by c.month
    

  • 0
    S

    So I tried the following code for Following up question 2. But it returned 'unknown column of r1.accept_date', why is that? Appreciate if someone can help.

    select r1.accept_date, if(d.req =0, 0.00, round(c.acp/d.req,2)) as accept_rate 
    from request_accepted r1, 
         (select count(distinct requester_id, accepter_id) as acp from request_accepted r2 where r2.accept_date <= r1.accept_date) c, 
         (select count(distinct sender_id, send_to_id) as req from friend_request f2 where f2.request_date <= r1.accept_date) d  
    group by r1.accept_date
    

  • 0
    S
    This post is deleted!

  • 2
    L

    A TRY TO FOLLOW UP Q2

    SELECT ROUND(COUNT(DISTINCT requester_id, accepter_id) / COUNT(DISTINCT sender_id, send_to_id), 2) as rate, date_table.dates
    FROM request_accepted acp, friend_request req, 
    
    (SELECT request_date AS dates FROM friend_request
    UNION
    SELECT accept_date FROM request_accepted
    ORDER BY dates) as date_table
    
    WHERE acp.accept_date <= date_table.dates
    AND req.request_date <= date_table.dates
    GROUP BY date_table.dates
    

    The output from this query is

    {"headers": ["rate", "dates"], "values": [[0.25, "2016-06-03"], [0.75, "2016-06-08"], [0.80, "2016-06-09"], [0.80, "2016-06-10"], [0.67, "2016-07-01"], [0.83, "2016-07-09"], [0.83, "2016-08-01"]]}
    

    The problem with this output is, it starts to count the rate from 2016_06-03. It skips the dates from 2016_06-01 to 2016_06-02, in the friend_request, and which are earlier than the earliest date (2016_06-03 ) in the request_accepted table. Anyone knows how to fix it?


  • 0
    L

    @sarayamato said in Following up questions. Solved Q1 how to solve Q2?:

    If your sub select is in a where clause, you can probably call r1, as a correlated subquery. But you cannot call r1 in a separate table.


  • 0
    L

    @lightning_mi You can use union all in your date_table to keep all the date and left join your date_table to the other two tables


  • 2

    @sarayamato For Q1, have you tested your code? I don't think c and d will give you count for every month, you are missing the group by

     select round(c.acp/d.req,2) as accept_rate, c.month from 
     (select count(distinct requester_id, accepter_id) as acp, Month(accept_date) as month from request_accepted group by month) c, 
     (select count(distinct sender_id, send_to_id) as req, Month(request_date) as month from friend_request group by month) d 
     where c.month = d.month

  • 0
    N

    @tongzhou2
    I agree with you but I think you should do a DISTINCT before you GROUP the records BY month. Since duplicate request_ID,accept_ID pairs can only be counted once even they are spread into different months


  • 0

    I don't get your point, can you elaborate it?


  • 0
    Z

    @tongzhou2 I believe his meaning is that, if user1 requests friend for user2 in Jan, and requested again exactly same in Feb, then in your solution, this duplication is not eliminated. However, whether to eliminate this kind of duplication is controversy. Extra specification is needed.


Log in to reply
 

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