Analyze Cross-Country Call Data for Recent Trends
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
Users
+---------+-------------+----------+
| user_id | signup_date | country |
+---------+-------------+----------+
| 1 | 2021-01-04 | US |
| 2 | 2021-02-10 | CA |
| 3 | 2021-03-17 | US |
| 4 | 2021-04-23 | IN |
| 5 | 2021-05-05 | GB |
+---------+-------------+----------+
Calls
+---------+-----------+-------------+---------------------+---------------------+
| call_id | caller_id | receiver_id | start_time | end_time |
+---------+-----------+-------------+---------------------+---------------------+
| 1001 | 1 | 2 | 2022-07-01 10:00:00 | 2022-07-01 10:15:00 |
| 1002 | 3 | 1 | 2022-07-01 11:00:00 | 2022-07-01 11:05:00 |
| 1003 | 4 | 5 | 2022-07-02 09:30:00 | 2022-07-02 09:45:00 |
| 1004 | 2 | 4 | 2022-07-02 12:00:00 | 2022-07-02 12:25:00 |
| 1005 | 5 | 3 | 2022-07-03 08:00:00 | 2022-07-03 08:30:00 |
+---------+-----------+-------------+---------------------+---------------------+
##### Scenario
Video-calling product team wants insights on cross-country usage.
##### Question
Using the tables below, write a SQL query that returns, for each pair of caller_country and receiver_country, the total number of completed calls and average call duration in the last 30 days.
##### Hints
Join Calls to Users twice (caller and receiver), filter last-30-days, compute duration as TIMESTAMPDIFF/datediff, group by both countries.
Quick Answer: This question evaluates a candidate's competency in relational data manipulation, including joining tables, aggregations, time-based filtering, and duration calculations using SQL or equivalent Python tools.