When to use self join in SQL

Requirement:

 Find acceptance rate of face book friends requests by Date. The date to be used for calculation is the date on which the request was sent.

We have a dataset of face book friends requests list.

Let us list down steps we need to take to accomplish the intended result.

• Find total number of requests accepted (A)
• Find total number of requests sent (B)
• Divide A/B to get the results

Approach:

Although in SQL, we can arrive at the same result in multiple ways, 

for this type of scenario where we have to match one record from a table with another record in the same table, self join is usually the best option

We will self join this table to match sent and accepted records to figure out how many sent requests were accepted. 

Something like this...

Next, we can now find total number of requests sent by date...

Now, at this point we have 2 options to join these two results. Either by creating CTE or by subquery.

For the reason of performance and presentations, I am going to do it by using CTE using WITH clause ...

Now its a question of doing the simple math to divide 2 columns to get acceptance rate