Identify Top 10 Users by Average Call Duration
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
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.
Overview: 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.
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.
Tables
video_calls(call_id INTEGER, user_id INTEGER, start_time DATETIME, end_time DATETIME)
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).