Coding questions · Meta
Analyze Cross-Country Call Data for Recent Trends
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.
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.
Cross-country call metrics over a 30-day window
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.
Table Users
| column | type | constraints |
|---|---|---|
| user_id | INTEGER | PRIMARY KEY, NOT NULL |
| signup_date | DATE | NOT NULL |
| country | CHAR(2) | NOT NULL |
Sample data:
| country | signup_date | user_id |
|---|---|---|
| US | 2021-01-04 | 1 |
| CA | 2021-02-10 | 2 |
| US | 2021-03-17 | 3 |
| IN | 2021-04-23 | 4 |
| GB | 2021-05-05 | 5 |
Table Calls
| column | type | constraints |
|---|---|---|
| call_id | INTEGER | PRIMARY KEY, NOT NULL |
| caller_id | INTEGER | NOT NULL, FOREIGN KEY REFERENCES Users(user_id) |
| receiver_id | INTEGER | NOT NULL, FOREIGN KEY REFERENCES Users(user_id) |
| start_time | DATETIME | NOT NULL |
| end_time | DATETIME | NOT NULL |
Sample data:
| call_id | caller_id | end_time | receiver_id | start_time |
|---|---|---|---|---|
| 1001 | 1 | 2022-07-01 10:15:00 | 2 | 2022-07-01 10:00:00 |
| 1002 | 3 | 2022-07-01 11:05:00 | 1 | 2022-07-01 11:00:00 |
| 1003 | 4 | 2022-07-02 09:45:00 | 5 | 2022-07-02 09:30:00 |
| 1004 | 2 | 2022-07-02 12:25:00 | 4 | 2022-07-02 12:00:00 |
| 1005 | 5 | 2022-07-03 08:30:00 | 3 | 2022-07-03 08:00:00 |
Expected output
One row per (caller_country, receiver_country) pair with the total number of completed calls and the average call duration in minutes, limited to calls with start_time in [2022-07-01, 2022-07-31), end_time not NULL, and end_time > start_time. For the provided sample data (all calls are in-window and completed), the output is:
| avg_duration_minutes | caller_country | receiver_country | total_calls |
|---|---|---|---|
| 25 | CA | IN | 1 |
| 30 | GB | US | 1 |
| 15 | IN | GB | 1 |
| 15 | US | CA | 1 |
| 5 | US | US | 1 |
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).
- In PostgreSQL, compute duration in minutes as EXTRACT(EPOCH FROM (end_time - start_time)) / 60.0.
- Group by caller_country and receiver_country.