Coding questions · Meta
Identify Top 10 Users by Average Call Duration
This question evaluates SQL-based data manipulation and time-series aggregation skills, specifically computing per-user metrics from timestamped event data such as call durations.
video_calls
| call_id | user_id | start_time | end_time |
|---|---|---|---|
| 1 | 101 | 2023-07-01 09:00:00 | 2023-07-01 09:45:00 |
| 2 | 102 | 2023-07-01 10:15:00 | 2023-07-01 10:40:00 |
| 3 | 101 | 2023-07-02 14:05:00 | 2023-07-02 14:25:00 |
| 4 | 103 | 2023-07-02 15:00:00 | 2023-07-02 16:30:00 |
| 5 | 102 | 2023-07-03 11:20:00 | 2023-07-03 12:00:00 |
Scenario
A video-calling product team wants to know which users had the longest average call duration over the last 7 days.
Question
Write a SQL query that returns the top 10 users by average call duration (in minutes) for calls started in the last 7 days.
Hints
Compute duration as TIMESTAMPDIFF, filter on start_time, use ORDER BY and LIMIT.
Top Users by Average Video Call Duration
You are given a table video_calls that records video call sessions. Write a SQL query that returns the top 10 users by average call duration (in minutes), considering only calls whose start_time is between 2023-06-27 (inclusive) and 2023-07-04 (exclusive). Return two columns: user_id and avg_duration_minutes. Order the results by avg_duration_minutes in descending order, and by user_id in ascending order to break ties. Limit the output to at most 10 rows.
Table video_calls
| column | type | constraints |
|---|---|---|
| call_id | INTEGER | PRIMARY KEY, NOT NULL |
| user_id | INTEGER | NOT NULL |
| start_time | DATETIME | NOT NULL |
| end_time | DATETIME | NOT NULL |
Sample data:
| call_id | end_time | start_time | user_id |
|---|---|---|---|
| 1 | 2023-07-01 09:45:00 | 2023-07-01 09:00:00 | 101 |
| 2 | 2023-07-01 10:40:00 | 2023-07-01 10:15:00 | 102 |
| 3 | 2023-07-02 14:25:00 | 2023-07-02 14:05:00 | 101 |
| 4 | 2023-07-02 16:30:00 | 2023-07-02 15:00:00 | 103 |
| 5 | 2023-07-03 12:00:00 | 2023-07-03 11:20:00 | 102 |
Expected output
Return up to 10 users with the highest average call duration in minutes, considering only calls whose start_time is in the interval [2023-06-27, 2023-07-04). The output should contain user_id and avg_duration_minutes, ordered by avg_duration_minutes DESC and user_id ASC for ties.
| avg_duration_minutes | user_id |
|---|---|
| 90 | 103 |
| 32.5 | 101 |
| 32.5 | 102 |
Hints
- Compute the duration of each call as the difference between end_time and start_time, converted to minutes (e.g., EXTRACT(EPOCH FROM (end_time - start_time)) / 60.0 in PostgreSQL).
- Filter rows so that start_time is between 2023-06-27 (inclusive) and 2023-07-04 (exclusive).
- Group by user_id, then order by avg_duration_minutes DESC and user_id ASC, and apply LIMIT 10.