Countries You Can Safely Invest In - Practice Coding Problems

Countries You Can Safely Invest In - Problem

Database Medium

Investment Opportunity Analysis

A telecommunications company is looking to expand their business globally! 🌍 They want to invest in countries where people talk more than the global average - because more talk time means more revenue potential.

You have access to three key data sources:

📞 Person Table: Contains user information including phone numbers in format 'xxx-yyyyyyy' where 'xxx' is the 3-digit country code

🌏 Country Table: Maps country codes to country names

📊 Calls Table: Contains call duration data between users

Your Mission: Find all countries where the average call duration is strictly greater than the global average call duration across all countries.

Key Insight: A call's country is determined by both the caller's and callee's country codes. You need to calculate the average duration for each country and compare it against the overall global average.

Input & Output

example_1.sql — Basic Investment Analysis

$ Input: Person: [(1,'Jack','051-1234567'), (2,'Amy','051-1234568'), (3,'John','052-1234569')] Country: [('Peru','051'), ('USA','052')] Calls: [(1,2,59), (2,1,11), (1,3,20), (3,1,5), (2,3,1)]

› Output: ['Peru']

💡 Note: Global average: (59+11+20+5+1)/5 = 19.2 minutes. Peru's calls: (59+11)/2 = 35 minutes average. USA's calls: (20+5+1)/3 = 8.67 minutes average. Only Peru (35 > 19.2) qualifies for investment.

example_2.sql — Multiple Countries Above Average

$ Input: Person: [(1,'Alice','001-1234567'), (2,'Bob','002-1234568'), (3,'Carol','003-1234569'), (4,'Dave','001-1234570')] Country: [('CountryA','001'), ('CountryB','002'), ('CountryC','003')] Calls: [(1,2,100), (2,1,80), (3,4,60), (4,3,40), (1,4,30)]

› Output: ['CountryA', 'CountryB']

💡 Note: Global average: (100+80+60+40+30)/5 = 62 minutes. CountryA: (100+80+30)/3 = 70 minutes. CountryB: (100+80)/2 = 90 minutes. CountryC: (60+40)/2 = 50 minutes. CountryA and CountryB exceed the global average.

example_3.sql — No Investment Opportunities

$ Input: Person: [(1,'Eve','111-1111111'), (2,'Frank','222-2222222')] Country: [('Alpha','111'), ('Beta','222')] Calls: [(1,2,10), (2,1,10)]

› Output: []

💡 Note: Global average: (10+10)/2 = 10 minutes. Both Alpha and Beta have exactly 10 minutes average call duration. Since we need STRICTLY greater than global average, no countries qualify.

Constraints

1 ≤ Person.id ≤ 1000
Person.phone_number format: 'xxx-yyyyyyy' where x and y are digits
Country.country_code format: 'xxx' where x is digits
1 ≤ Calls.duration ≤ 1000
caller_id ≠ callee_id (no self-calls)
Country average must be strictly greater than global average

Visualization

Tap to expand

Understanding the Visualization

Data Integration

Combine call records with person and country information to create a comprehensive dataset

Country Classification

Extract country codes from phone numbers and map calls to their respective countries

Average Calculations

Calculate both global average and individual country averages simultaneously

Investment Decision

Identify countries where average call duration exceeds the global benchmark

Key Takeaway

🎯 Key Insight: By identifying countries with above-average call durations, telecommunications companies can strategically invest in markets with higher customer engagement and revenue potential.

Asked in

a Amazon 45 G Google 38 ⊞ Microsoft 32 f Meta 28

The optimal solution uses a Common Table Expression (CTE) to efficiently calculate country-specific and global averages in a single pass. Key insights: extract country codes using LEFT(phone_number, 3), handle both caller and callee countries with OR conditions, and use parallel aggregation to compare averages efficiently.

Common Approaches

Approach	Time	Space	Notes
✓ Optimized Single Query (CTE)	O(n)	O(k)	Use Common Table Expression to calculate everything in one efficient pass
Brute Force (Subqueries for Each Country)	O(n²)	O(n)	Calculate global average separately, then check each country individually

Optimized Single Query (CTE) — Algorithm Steps

Create CTE to extract country codes from phone numbers
Join calls with person data to get caller and callee countries
Aggregate total duration and call count per country
Calculate global average using window function or subquery
Filter countries with above-average call duration

Visualization

Tap to expand

Step-by-Step Walkthrough

Data Join

Join all tables to get call-country mappings

Parallel Aggregation

Simultaneously calculate country averages and global average

Filter Results

Return countries exceeding global average

Code -

solution.c — C

// Optimal SQL solution using CTE
#include <stdio.h>

char* solution() {
    static char query[] = "WITH country_calls AS ("
                         "SELECT c.name as country, AVG(ca.duration) as avg_duration "
                         "FROM Calls ca "
                         "JOIN Person p1 ON ca.caller_id = p1.id "
                         "JOIN Person p2 ON ca.callee_id = p2.id "
                         "JOIN Country c ON (LEFT(p1.phone_number, 3) = c.country_code "
                         "OR LEFT(p2.phone_number, 3) = c.country_code) "
                         "GROUP BY c.name), "
                         "global_avg AS (SELECT AVG(duration) as global_average FROM Calls) "
                         "SELECT cc.country FROM country_calls cc, global_avg ga "
                         "WHERE cc.avg_duration > ga.global_average";
    return query;
}

Time & Space Complexity

Time Complexity

⏱️

O(n)

Single pass through calls and persons data with efficient joins

✓ Linear Growth

Space Complexity

O(k)

Where k is the number of unique countries (much smaller than total records)

✓ Linear Space

47.2K Views

Medium-High Frequency

~15 min Avg. Time

1.8K 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

Input & Output

Constraints

Visualization

Related Problems

Common Approaches

Optimized Single Query (CTE) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler