SMS Confirmations From Users

---

QUESTION : Meta/Facebook sends SMS texts when users attempt to 2FA (2-factor authenticate) into the platform to log in. In order to successfully 2FA they must confirm they received the SMS text message. Confirmation texts are only valid on the date they were sent.

Unfortunately, there was an ETL problem with the database where friend requests and invalid confirmation records were inserted into the logs, which are stored in the 'fb_sms_sends' table. These message types should not be in the table.

Fortunately, the 'fb_confirmers' table contains valid confirmation records so you can use this table to identify SMS text messages that were confirmed by the user.

Calculate the percentage of confirmed SMS texts for August 4, 2020. Be aware that there are multiple message types, the ones you're interested in are messages with type equal to 'message'.

1. Digest the question

1. Available Data : - We have 2 tables fb_sms_sends and fb_confirmers. In fb_sms_sends : date ,country ,carrier ,phone no and type . fb_confirmers contains date and phone number

2. Expected Output : the percentage of confirmed SMS texts , inorder to calculate this we must consider the number of phone numbers confirmed by the total number of phone numbers in percentage , so inorder to calculate this we need to establish a connection between two tables .

3. Important Condition : From the question it is clear that we need to consider only for the date August 4, 2020 and also we need to consider only type:message .

2. Plan the Approach

While Calculating the percentage as we need the exact value we need to consider in FLOAT .Also, as we need all values of first table for the calculating the total number of phone numbers , we can consider LEFT JOIN ,we can connect both the tables with the date column and whenever the phone numbers are equal , which means that the SMS is confirmed , along with this we can consider the important conditions provided in the question using a WHERE Clause.

3. Solution

SELECT COUNT(B.phone_number)::FLOAT/COUNT(A.phone_number)*100 as percent_confirm_SMS
FROM fb_sms_sends A 
LEFT JOIN  fb_confirmers B 
ON A.ds =B.date
WHERE A.phone_number=B.phone_number
AND A.ds = '2020-08-04'
AND A.type ='message'