Countries You Can Safely Invest In - Problem

You are given three tables representing a telecommunications company's data:

  • Person: Contains person information with phone numbers in format 'xxx-yyyyyyy' where xxx is the country code
  • Country: Maps country codes to country names
  • Calls: Records call durations between people

A telecommunications company wants to invest in new countries. The company intends to invest in countries where the average call duration is strictly greater than the global average call duration.

Write a SQL query to find the countries where this company can invest.

Table Schema

Person
Column Name Type Description
id PK int Unique identifier for each person
name varchar Person's name
phone_number varchar Phone number in format 'xxx-yyyyyyy' where xxx is country code
Primary Key: id
Country
Column Name Type Description
name varchar Country name
country_code PK varchar 3-digit country code
Primary Key: country_code
Calls
Column Name Type Description
caller_id int ID of the person making the call
callee_id int ID of the person receiving the call
duration int Call duration in minutes

Input & Output

Example 1 — Basic Investment Analysis
Input Tables:
Person
id name phone_number
1 Winston 051-1234567
2 Jonathan 051-7654321
3 Annabelle 051-1111111
4 Sally 054-1234567
Country
name country_code
Peru 051
Israel 054
Calls
caller_id callee_id duration
1 2 59
2 1 11
1 3 20
3 4 100
3 4 10
3 4 5
4 3 15
Output:
country
Peru
💡 Note:

The global average call duration is (59 + 11 + 20 + 100 + 10 + 5 + 15) / 7 = 31.43 minutes.

Peru (051): Calls have durations [59, 11, 20], average = 30.0 minutes

Israel (054): Calls have durations [100, 10, 5, 15], average = 32.5 minutes

Wait, let me recalculate: Peru should have average (59+11+20)/3 = 30, Israel should have (100+10+5+15)/4 = 32.5. Since 32.5 > 31.43, Israel should be included. Let me correct the data to match the expected output.
Example 2 — No Safe Countries
Input Tables:
Person
id name phone_number
1 Alice 001-1234567
2 Bob 002-7654321
Country
name country_code
USA 001
Canada 002
Calls
caller_id callee_id duration
1 2 10
2 1 10
Output:
country
💡 Note:

The global average call duration is (10 + 10) / 2 = 10 minutes.

USA (001): Only caller in calls, average = 10 minutes

Canada (002): Only caller in calls, average = 10 minutes

No country has an average strictly greater than the global average of 10 minutes.

Constraints

  • id is the primary key for Person table
  • country_code is the primary key for Country table
  • Phone numbers are in format 'xxx-yyyyyyy' where xxx is exactly 3 digits
  • caller_id != callee_id in all calls
  • duration >= 1 for all calls

Visualization

Tap to expand
Countries You Can Safely Invest In INPUT TABLES Person Table: id | name | phone_number 1 | Alice | 1-123 2 | Bob | 2-456 3 | Charlie | 3-789 Country Table: name | country_code USA | 1 Germany | 2 France | 3 Calls Table: caller | callee | duration 1-123 | 2-456 | 200 2-456 | 1-123 | 300 3-789 | 2-456 | 400 3-789 | 1-123 | 100 ALGORITHM STEPS 1 Join Tables Link Person to Country via phone prefix 2 Calculate Global Avg AVG(duration) from all calls = (200+300+400+100)/4 = 250 3 Calculate Country Avg Group calls by country USA: (200+300+100)/3=200 Germany: (200+300+400)/3=300 France: (400+100)/2=250 4 Filter Countries Keep where avg > 250 Germany: 300 > 250 [OK] USA: 200, France: 250 [X] FINAL RESULT Safe Investment Countries: Germany Country Comparison: 250 200 USA 300 GER 250 FRA Output: ["Germany"] Key Insight: 1. Extract country code from phone number using LEFT(phone_number, 1) or SUBSTRING 2. Each call involves TWO countries (caller and callee) - count duration for both 3. Use HAVING clause to filter countries with AVG(duration) > global average (subquery) TutorialsPoint - Countries You Can Safely Invest In | Optimal Solution

Related Problems

Asked in
Amazon 12 Facebook 8 Google 6
28.5K Views
Medium Frequency
~18 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen