Back to questions
International Call Percentage [Verizon SQL Interview Question]
A phone call is considered an international call when the person calling is in a different country than the person receiving the call.
What percentage of phone calls are international? Round the result to 1 decimal.
Assumption:
- The in table refers to both the caller and receiver.
Table:
| Column Name | Type |
|---|---|
| caller_id | integer |
| receiver_id | integer |
| call_time | timestamp |
Example Input:
| caller_id | receiver_id | call_time |
|---|---|---|
| 1 | 2 | 2022-07-04 10:13:49 |
| 1 | 5 | 2022-08-21 23:54:56 |
| 5 | 1 | 2022-05-13 17:24:06 |
| 5 | 6 | 2022-03-18 12:11:49 |
Table:
| Column Name | Type |
|---|---|
| caller_id | integer |
| country_id | integer |
| network | integer |
| phone_number | string |
Example Input:
| caller_id | country_id | network | phone_number |
|---|---|---|---|
| 1 | US | Verizon | +1-212-897-1964 |
| 2 | US | Verizon | +1-703-346-9529 |
| 3 | US | Verizon | +1-650-828-4774 |
| 4 | US | Verizon | +1-415-224-6663 |
| 5 | IN | Vodafone | +91 7503-907302 |
| 6 | IN | Vodafone | +91 2287-664895 |
Example Output:
| international_calls_pct |
|---|
| 50.0 |
Explanation
There is a total of 4 calls with 2 of them being international calls (from caller_id 1 => receiver_id 5, and caller_id 5 => receiver_id 1). Thus, 2/4 = 50.0%
The dataset you are querying against may have different input & output - this is just an example!
Solution
Step 1: Join the tables to obtain the caller's and receiver's country information
To determine whether a call is international or not, we need for both caller and receiver. This can be achieved by joining twice, first for the caller, and second for the receiver.
Step 2: Count the international calls and the total number of calls
After obtaining the necessary info, we can start with the calculation. To do so, we need 2 metrics:
- number of total calls
- number of international calls
Getting the number of total calls is easy with .
As for the number of international calls, we can use the statement to check if the caller's country is different from the receiver's country. If it is, assign the value of 1 for international calls, otherwise for non-international calls. This is known as a conditional count function.
Then, we wrap the statement with either or to add up the numbers with a value of 1 to obtain the count of international calls.
| caller_country | receiver_country | international |
|---|---|---|
| DE | US | 1 |
| US | US | 0 |
| IN | IN | 0 |
Step 3: Calculate the percentage of international calls
For the final step, we calculate the percentage of international calls. To obtain this, divide the count of international calls by the total.
However, it is important to multiply this result with 100.0 (instead of 100) due to integer division. To elaborate, as both numbers are integers, the result would be also an integer, truncating the decimals from the result.
Lastly, round the result to 1 decimal.
We are done, good job!
Solution #2: Using FILTER Calling out user @acejetman12345 for this fantastic solution using , which we have adapted slightly.
Solution #3: Using CTE
Instead of filtering the in the statement, we're doing it in the clause.
PostgreSQL 14