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

Data Science Interview Questions › Category: Data Science › [SQL] The success ratio of sending messages given sent and receive table
0 Vote Up Vote Down
MockInterview Staff asked 6 years ago

Source: https://www.glassdoor.com/Interview/The-success-ratio-of-sending-messages-given-sent-and-receive-table-QTN_1873892.htm

Question Tags:
1 Answers
0 Vote Up Vote Down
MockInterview Staff answered 6 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)

Your Answer

19 + 8 =