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.
Quick Answer: 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.