Countries You Can Safely Invest In - Practice Coding Problems

Countries You Can Safely Invest In - Problem

Database Medium

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

Understanding the Visualization

Extract

Get country codes from phone numbers

Calculate

Compute global and country averages

Compare

Find countries exceeding global average

Key Takeaway

🎯 Key Insight: Use SUBSTRING to extract country codes and subquery for global comparison in investment analysis

Asked in

a Amazon 12 f Facebook 8 G Google 6

Use subquery to calculate global average duration, then JOIN Person, Calls, and Country tables, extract country codes with SUBSTRING(), and filter countries using HAVING clause where country average exceeds global average.

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

Common Approaches

Approach	Time	Space	Notes
✓ Subquery with Global Average Comparison	O(n + m)	O(k)	Calculate global average in subquery and compare with country-specific averages
CTE with Global and Country Averages	O(n + m)	O(k)	Use Common Table Expression to calculate both global and country averages clearly

Subquery with Global Average Comparison — Algorithm Steps

Step 1: Extract country codes from phone numbers using SUBSTRING
Step 2: Join Person, Calls, and Country tables to get call data with country names
Step 3: Calculate global average duration in subquery
Step 4: Group by country and compare country averages with global average

Visualization

Tap to expand

Step-by-Step Walkthrough

Extract

Get country codes from phone numbers

Join

Connect all tables

Compare

Country avg vs global avg

Code -

solution.c — C

Time & Space Complexity

Time Complexity

⏱️

O(n + m)

n calls for global average, m calls for country grouping

✓ Linear Growth

Space Complexity

O(k)

k countries for grouping

✓ Linear Space

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

Table Schema

Input & Output

Constraints

Visualization

Related Problems

Table Schema

Common Approaches

Subquery with Global Average Comparison — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler