'SQL stratascratch facebook interview question

SMS Confirmations From Users

Facebook sends SMS texts when users attempt to 2FA (2-factor authenticate) to log into the platform. In order to successfully 2FA they must confirm they received the SMS text. Confirmation texts are only valid on the date they were sent. Unfortunately, there was an ETL problem where friend requests and invalid confirmation records were inserted into the logs which are stored in the 'fb_sms_sends' table. Fortunately, the 'fb_confirmers' table contains valid confirmation records so you can use this table to identify confirmed SMS texts.

Calculate the percentage of confirmed SMS texts for August 4, 2020.

fb_sms_sends
   ds datetime
   country varchar
   carrier varchar
   phone_number int
   type varchar

fb_confirmers
   date datetime
   phone_number int

My solution -

Select s.ds, (count(c.phone_number)::Float/count(s.phone_number)::Float)*100 as perc
from fb_sms_sends s
left join fb_confirmers c
on s.phone_number = c.phone_number
where s.ds = c.date
group by s.ds

fb_sms_sends table

Not sure what is wrong here. Can someone please explain?

sql


Solution 1:[1]

I think what you're not handling is the scenario

Unfortunately, there was an ETL problem where friend requests and invalid confirmation records were inserted into the logs which are stored in the 'fb_sms_sends' table. Fortunately, the 'fb_confirmers' table contains valid confirmation records so you can use this table to identify confirmed SMS texts.

You need to remove the friend requests and invalid confirmation records from the table.

If you add

type NOT IN ('confirmation', 'friend_request')

to your WHERE clause, you should get the right answer.

Solution 2:[2]

Instead of using date in where condition, you need to use it in the join condition, it will keep all the records from the left table. Keeping in the where makes it an inner join. I slightly modified your solution.

Select s.ds, (count(c.phone_number)::Float/count(s.phone_number)::Float)*100 as perc
from fb_sms_sends s
left join fb_confirmers c
on s.phone_number = c.phone_number and s.ds = c.date
where type <> 'friend_request'
and s.ds = '2020-08-04'
group by 1

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 nlr25
Solution 2 Rajasekar

Related Questions