Back to questions
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.
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!
Here's our algorithm for solving this question:
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.
After obtaining the necessary info, we can start with the calculation. To do so, we need 2 metrics:
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.
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.