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.
Overview: 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.
Using the Users and Calls tables, write a SQL query that returns, for each pair of caller_country and receiver_country, the total number of completed calls and the average call duration in minutes. Consider only calls whose start_time is in the 30-day window from 2022-07-01 (inclusive) to 2022-07-31 (exclusive). Treat a call as completed if end_time is not NULL and end_time > start_time.
Tables
Users(user_id INTEGER, signup_date DATE, country CHAR(2))
Calls(call_id INTEGER, caller_id INTEGER, receiver_id INTEGER, start_time DATETIME, end_time DATETIME)
Hints
- Join Calls to Users twice: once on caller_id and once on receiver_id.
- Filter using the fixed window [2022-07-01, 2022-07-31).