Investment-Ready Countries by Call Duration

Medium

A telecom strategy team is evaluating where to expand premium network infrastructure. Table: subscriber subscriber_id: unique user identifier subscriber_name: user display name phone_number: in format ccc-local_number, where ccc is a 3-digit country code Table: country_code_map country_name: country display name country_code: unique 3-digit country code Table: call_log caller_id: subscriber who initiated the call callee_id: subscriber who received the call duration_minutes: call duration in minutes Duplicate rows may exist and each row is a separate call event. caller_id != callee_id Business metric definitions: Global average duration: Average duration_minutes across all rows in call_log. Country endpoint average duration: For every call row, evaluate caller and callee endpoints separately. If an endpoint's subscriber phone code maps to a country in country_code_map, include that call duration once in that country's aggregate. A single call may contribute: once (only one mapped endpoint), twice to two different countries, or twice to the same country (both endpoints mapped there). Task: Return countries whose endpoint average duration is strictly greater than the global average duration. Output requirements: Return exactly one column named country. Sort result by country ascending. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

subscriber:
| subscriber_id | subscriber_name | phone_number |
|---------------|-----------------|--------------|
| 1             | Moncef          | 212-1234567  |
| 2             | Maroua          | 212-6523651  |
| 3             | Jonathan        | 051-1234567  |
| 4             | Elvis           | 051-7654321  |
| 5             | Rachel          | 972-0011100  |
| 6             | Meir            | 972-1234567  |

country_code_map:
| country_name | country_code |
|--------------|--------------|
| Peru         | 051          |
| Israel       | 972          |
| Morocco      | 212          |
| Germany      | 049          |

call_log:
| caller_id | callee_id | duration_minutes |
|-----------|-----------|------------------|
| 1         | 5         | 30               |
| 2         | 5         | 10               |
| 1         | 2         | 60               |
| 3         | 4         | 120              |
| 3         | 4         | 300              |
| 4         | 3         | 6                |
| 6         | 5         | 12               |
| 6         | 1         | 4                |
| 5         | 6         | 2                |
| 1         | 6         | 8                |

Output

[
  {"country":"Peru"}
]

Explanation

Global average is 55.2. Peru's endpoint average is 142.0, while Morocco and Israel are below global average.

Example 2

Input

subscriber:
| subscriber_id | subscriber_name | phone_number |
|---------------|-----------------|--------------|
| 10            | Asha            | 101-1200001  |
| 11            | Bren            | 202-1200002  |
| 12            | Cato            | 303-1200003  |

country_code_map:
| country_name | country_code |
|--------------|--------------|
| Northland    | 101          |
| Eastoria     | 202          |
| Southbay     | 303          |

call_log:
| caller_id | callee_id | duration_minutes |
|-----------|-----------|------------------|
| (no rows) |

Output

[]

Explanation

With no call rows, no country can satisfy a strictly-above-global comparison.

Example 3

Input

subscriber:
| subscriber_id | subscriber_name | phone_number |
|---------------|-----------------|--------------|
| 10            | Aria            | 101-2222000  |
| 11            | Blake           | 101-3333000  |
| 12            | Cyra            | 202-4444000  |
| 13            | Dinesh          | 303-5555000  |
| 14            | Eren            | 404-6666000  |

country_code_map:
| country_name | country_code |
|--------------|--------------|
| Alpha        | 101          |
| Beta         | 202          |
| Gamma        | 303          |

call_log:
| caller_id | callee_id | duration_minutes |
|-----------|-----------|------------------|
| 10        | 11        | 40               |
| 10        | 12        | 10               |
| 12        | 13        | 10               |
| 13        | 14        | 100              |

Output

[
  {"country":"Gamma"}
]

Explanation

Code 404 is unmapped and contributes only to global average. Gamma's endpoint average is 55, which is above global average 40.

Constraints

1 <= rows in subscriber <= 200000
0 <= rows in country_code_map <= 1000
0 <= rows in call_log <= 300000
1 <= subscriber_id <= 1000000000
phone_number format is ccc-local where ccc is exactly 3 digits
country_code is exactly 3 digits and unique within country_code_map
1 <= duration_minutes <= 1000000
caller_id and callee_id refer to valid subscriber_id values
caller_id != callee_id for every call row
Duplicate call rows may appear and must be counted normally
Country endpoint average must be strictly greater than global average
Use SQLite 3.27.2 semantics for SQL submissions
For Pandas submissions, return a DataFrame with exact column: country

subscriber: | subscriber_id | subscriber_name | phone_number | |---------------|-----------------|--------------| | 1 | Moncef | 212-1234567 | | 2 | Maroua | 212-6523651 | | 3 | Jonathan | 051-1234567 | | 4 | Elvis | 051-7654321 | | 5 | Rachel | 972-0011100 | | 6 | Meir | 972-1234567 | country_code_map: | country_name | country_code | |--------------|--------------| | Peru | 051 | | Israel | 972 | | Morocco | 212 | | Germany | 049 | call_log: | caller_id | callee_id | duration_minutes | |-----------|-----------|------------------| | 1 | 5 | 30 | | 2 | 5 | 10 | | 1 | 2 | 60 | | 3 | 4 | 120 | | 3 | 4 | 300 | | 4 | 3 | 6 | | 6 | 5 | 12 | | 6 | 1 | 4 | | 5 | 6 | 2 | | 1 | 6 | 8 |

subscriber: | subscriber_id | subscriber_name | phone_number | |---------------|-----------------|--------------| | 10 | Asha | 101-1200001 | | 11 | Bren | 202-1200002 | | 12 | Cato | 303-1200003 | country_code_map: | country_name | country_code | |--------------|--------------| | Northland | 101 | | Eastoria | 202 | | Southbay | 303 | call_log: | caller_id | callee_id | duration_minutes | |-----------|-----------|------------------| | (no rows) |

subscriber: | subscriber_id | subscriber_name | phone_number | |---------------|-----------------|--------------| | 10 | Aria | 101-2222000 | | 11 | Blake | 101-3333000 | | 12 | Cyra | 202-4444000 | | 13 | Dinesh | 303-5555000 | | 14 | Eren | 404-6666000 | country_code_map: | country_name | country_code | |--------------|--------------| | Alpha | 101 | | Beta | 202 | | Gamma | 303 | call_log: | caller_id | callee_id | duration_minutes | |-----------|-----------|------------------| | 10 | 11 | 40 | | 10 | 12 | 10 | | 12 | 13 | 10 | | 13 | 14 | 100 |