SQLNotes: Acceptance Rate By Date

Problem

What is the overall friend acceptance rate by date?

Your output should have the rate of acceptance by the date the request was sent. Order by the earliest date to the latest.

Assume that each friend request starts by a user sending (i.e., user_id_sender) a friend request to another user (i.e., user_id_receiver) that’s logged in the table with action = ‘sent’. If the request is accepted, the table logs action = ‘accepted’. If the request is not accepted, no record of action = ‘accepted’ is logged.

Table: fb_friend_requests
+------------------+------------+
| user_id_sender   | varchar    |
+------------------+------------+
| user_id_receiver | varchar    | 
+------------------+------------+
| date             | datetime   | 
+------------------+------------+
| action           | varchar    |
+------------------+------------+

Solution

We want to output the overall friend acceptance rate by date. How do we calculate the acceptance rate by date? Basically, we want to know out of all sent action, how many of friend requested are accepted.

acceptance rate = number of accepted / number of sent

To get the number of accepted and number of sent, we need 2 subqueries here.