Interview Query

SMS Confirmations

Upvote
0
Downvote
Save
Mark Completed
Have you seen this question before?

The sms_sends table contains all the messages sent to the users and may contain various message types.

Confirmation messages (type = "confirmation") are sent when a user registers an account and requires a response. The system may send multiple confirmation messages to a single phone number, but the user may confirm only the latest one sent to them.

The ds column represents the date when SMSs are sent.

The confirmers table contains phone numbers that responded to confirmation messages and dates when users responded.

Write a query to calculate the number of responses grouped by carrier and country to the SMSs sent by the system on February 28th, 2020.

Example:

Input:

sms_sends table

Column Type
ds DATETIME
country VARCHAR
carrier VARCHAR
phone_number VARCHAR
type VARCHAR

confirmers table

Column Type
date DATE
phone_number VARCHAR

Output:

Column Type
carrier VARCHAR
country VARCHAR
unique_numbers INTEGER
Next question: Minimum Absolute Distance
.....
MySQL 8.0.17
Loading editor
Use Shift + Enter to run query

Comments

Loading comments