[SQL] The success ratio of sending messages given sent and receive table

Data Science Interview QuestionsCategory: Data Science[SQL] The success ratio of sending messages given sent and receive table
1 Answers
MockInterview Staff answered 4 years ago

You can use similar logic that was used here: https://mockinterview.co/index.php/question/sql-given-two-tables-friend_request-requester_id-sent_to_id-time-request_accepted-acceptor_id-requestor_id-time-find-the-overall-acceptance-rate-of-requests/

  1. Define the success ratio and schema first
  2. Sent table (S) = message_id, sender_id, time
  3. Receive table (R) = message_id, received_id, time
  4. Assuming success ratio is the % of messages delivered successfully then you can write SQL to:
    1. Remove recent data (like last 24 hours) — this is your threshold after which you consider the message not delivered! 
    2. Count(R.message_id) / Count(S.message_id)

[Content Sponsor #1] Start learning through a free trial on Datacamp!
[Content Sponsor #2] $750 discount on any springboard courses including Data Science Career Track which comes with a Job Guarantee!

Your Answer

5 + 10 =

Data Science Career Bootcamp