# Following up questions. Solved Q1 how to solve Q2?

• 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
``````

• 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
``````

• This post is deleted!

• 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?

• 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.

• @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

• @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``````

• @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

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

• @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.

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