Objective

Write a SQL query to calculate the percentage of phone calls that are international.

Table Schema:

• phone_calls

• phone_info

Task Requirements:

• A call is international if the caller and receiver are in different countries
• Look up the country for both the caller and the receiver using the phone_info table
• Calculate the percentage of international calls out of total calls
• Round the result to 1 decimal place
  
  

Output columns: international_pct

SELECT
    ROUND(
        SUM(CASE WHEN pi1.country_id != pi2.country_id THEN 1 ELSE 0 END)::DECIMAL /
        COUNT(*) * 100,
        1
    ) AS international_pct
FROM phone_calls pc
JOIN phone_info pi1
    ON pc.caller_id = pi1.caller_id
JOIN phone_info pi2
    ON pc.receiver_id = pi2.caller_id;

Explanation

Step 1: Joining phone_info Twice

FROM phone_calls pc
JOIN phone_info pi1
    ON pc.caller_id = pi1.caller_id
JOIN phone_info pi2
    ON pc.receiver_id = pi2.caller_id

Each call has two participants, so we join phone_info twice with different aliases. pi1 looks up the caller's country using pc.caller_id, and pi2 looks up the receiver's country using pc.receiver_id. After these joins, every call row has both pi1.country_id (caller's country) and pi2.country_id (receiver's country) available.

Step 2: Counting International Calls

SUM(CASE WHEN pi1.country_id != pi2.country_id THEN 1 ELSE 0 END)

The CASE expression checks if the caller and receiver are in different countries. It returns 1 for international calls and 0 for domestic calls. Wrapping it in SUM gives us the total number of international calls.

Step 3: Calculating the Percentage

SUM(...)::DECIMAL / COUNT(*) * 100

Dividing the international call count by the total number of calls gives the ratio. The ::DECIMAL cast prevents integer division from truncating the result. Multiplying by 100 converts it to a percentage.

Step 4: Rounding the Result

ROUND(..., 1)

ROUND with 1 as the second parameter ensures the output is displayed to exactly 1 decimal place.